Oracle Script to Create or Drop Series of Users or Schemas

advertisements

_____________________________________________________________________________________________________________________

Sometimes DBAs are asked to create series of users of same pattern like xyz01 to xyNN. In such situations it is very difficult in case we are approaching with manual user creation. You can use below enclosed script for this purpose.

Script to create series of users

BEGIN

 

  FOR u IN 1..10

  LOOP

    EXECUTE IMMEDIATE 'CREATE USER CLERK' || TO_CHAR (U)||' IDENTIFIED BY
CLERK'||TO_CHAR (U) ;

    EXECUTE IMMEDIATE 'GRANT CONNECT, RESOURCE TO CLERK'||TO_CHAR(U);

  END LOOP;

END;

Script to drop series of Oracle schema users.

BEGIN

  FOR u IN (SELECT *

              FROM dba_users

             WHERE username LIKE 'CLERK%')

  LOOP

    EXECUTE IMMEDIATE 'DROP USER ' || u.username;

  END LOOP;

END;

_____________________________________________________________________________________________________________________

Website Stats