Oracle Script to Create or Drop Series of Users or Schemas

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;
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google