Tuesday, October 2, 2012

How to generate create USER script in oracle Database?

Just follow the steps (below i have gave an live example also)

1) create a table where you insert all the users name those for
you want to generate script for migration (with migrated_flag 'Y') .



drop table migrated_users ;


CREATE TABLE migrated_users
(old_name VARCHAR2(30),
new_name VARCHAR2(30),
migrated_flag VARCHAR2(10) DEFAULT 'Y'
);


2) insert all the users which you want migrate (with migrated_flag ='Y')


INSERT INTO migrated_users
(old_name, new_name, migrated_flag)
SELECT username, username, 'Y'
FROM all_users
WHERE username NOT IN ('SYS', 'SYSTEM', 'SYSMAN');


COMMIT ;



3) change the migrated_flag='N' for those which you are not wanted to migrate


SQL> select * from migrated_users ;


SQL> select count(*) from migrated_users
where migrated_flag='Y' ;


4) make ready sqlplus for spooling in a text file well.


begin
dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SQLTERMINATOR', true);
dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'PRETTY', true);
end;
/

set long 2000000000
set head off
set pages 0
set feedback off
set termout off



5) Execute the following sql statement



SELECT REPLACE (DBMS_METADATA.get_ddl ('USER', UPPER (mig.old_name)),
UPPER (mig.old_name),
UPPER (mig.new_name)
)
FROM migrated_users mig JOIN dba_users usr ON usr.username =
UPPER (old_name)
WHERE migrated_flag = 'Y'
UNION ALL
SELECT REPLACE (DBMS_METADATA.get_granted_ddl ('TABLESPACE_QUOTA',
UPPER (old_name)
),
UPPER (old_name),
UPPER (new_name)
)
FROM migrated_users mig JOIN dba_ts_quotas tq
ON tq.username = UPPER (mig.old_name)
WHERE migrated_flag = 'Y'
UNION ALL
SELECT REPLACE (DBMS_METADATA.get_granted_ddl ('ROLE_GRANT', UPPER (old_name)),
UPPER (old_name),
UPPER (new_name)
)
FROM migrated_users mig JOIN dba_role_privs rpv
ON rpv.grantee = UPPER (mig.old_name)
WHERE migrated_flag = 'Y'
UNION ALL
SELECT REPLACE (DBMS_METADATA.get_granted_ddl ('SYSTEM_GRANT',
UPPER (old_name)
),
UPPER (old_name),
UPPER (new_name)
)
FROM migrated_users mig JOIN dba_sys_privs spv
ON spv.grantee = UPPER (mig.old_name)
WHERE migrated_flag = 'Y'
UNION ALL
SELECT REPLACE (DBMS_METADATA.get_granted_ddl ('OBJECT_GRANT',
UPPER (old_name)
),
UPPER (old_name),
UPPER (new_name)
)
FROM migrated_users mig JOIN dba_tab_privs tpv
ON tpv.grantee = UPPER (mig.old_name)
WHERE migrated_flag = 'Y'
UNION ALL
SELECT CASE
WHEN ((SELECT COUNT (*)
FROM dba_role_privs
WHERE grantee = UPPER (old_name)
AND default_role = 'YES'
AND ROWNUM = 1) > 0
)
THEN REPLACE (DBMS_METADATA.get_granted_ddl ('DEFAULT_ROLE',
UPPER (old_name)
),
UPPER (old_name),
UPPER (new_name)
)
END
FROM migrated_users
WHERE migrated_flag = 'Y'
UNION ALL
SELECT REPLACE (DBMS_METADATA.get_ddl ('PROFILE', PROFILE), old_name,
new_name)
FROM migrated_users mig JOIN dba_users usr
ON usr.username = UPPER (mig.old_name)
WHERE usr.PROFILE <> 'DEFAULT' AND migrated_flag = 'Y'


---double enter


6) spool it in a text file.

SQL> spool make_script_for_mig_user.sql
SQL> /
SQL> spool off

SQL> ed make_script_for_mig_user.sql


7) Now copy the script and execute it where you want to create these users.




Live example
==================


 SQL*Plus: Release 10.2.0.1.0 - Production on Tue Oct 2 11:58:18 2012  
 Copyright (c) 1982, 2005, Oracle. All rights reserved.  
 SQL>  
 SQL>  
 SQL>  
 SQL> set sqlprompt 'HalimDba >'  
 HalimDba >  
 HalimDba >  
 HalimDba >  
 HalimDba >set sqlprompt 'HalimDba@SQL>'  
 HalimDba@SQL>  
 HalimDba@SQL>  
 HalimDba@SQL>CREATE TABLE migrated_users  
  2 (old_name VARCHAR2(30),  
  3 new_name VARCHAR2(30)  
  4 );  
 SP2-0640: Not connected  
 HalimDba@SQL>  
 HalimDba@SQL>  
 HalimDba@SQL>conn test@orcl  
 Enter password:  
 Connected.  
 HalimDba@SQL>  
 HalimDba@SQL>  
 HalimDba@SQL>  
 HalimDba@SQL>  
 HalimDba@SQL>drop table migrated_users ;  
 Table dropped.  
 HalimDba@SQL>  
 HalimDba@SQL>  
 HalimDba@SQL>CREATE TABLE migrated_users  
  2 (old_name VARCHAR2(30),  
  3 new_name VARCHAR2(30),  
  4 migrated_flag VARCHAR2(10) DEFAULT 'Y'  
  5 );  
 Table created.  
 HalimDba@SQL>  
 HalimDba@SQL>  
 HalimDba@SQL>  
 HalimDba@SQL>  
 HalimDba@SQL>INSERT INTO migrated_users  
  2       (old_name, new_name, migrated_flag)  
  3   SELECT username, username, 'Y'  
  4    FROM all_users  
  5   WHERE username NOT IN ('SYS', 'SYSTEM', 'SYSMAN');  
 62 rows created.  
 HalimDba@SQL>  
 HalimDba@SQL>commit ;  
 Commit complete.  
 HalimDba@SQL>  
 HalimDba@SQL>  
 HalimDba@SQL>  
 HalimDba@SQL>  
 HalimDba@SQL>select * from migrated_users ;  
 
 BI               BI               N  
 PM               PM               N  
 SH               SH               Y  
 IX               IX               N  
 OE               OE               Y  
 HR               HR               Y  
 SCOTT             SCOTT             Y  
 OWBSYS_AUDIT          OWBSYS_AUDIT          N  
 KIOSK             KIOSK             N  
 OWBSYS             OWBSYS             N  
 APEX_030200          APEX_030200          N  
 APEX_PUBLIC_USER        APEX_PUBLIC_USER        N  
 FLOWS_FILES          FLOWS_FILES          N  
 MGMT_VIEW           MGMT_VIEW           N  
 SPATIAL_CSW_ADMIN_USR     SPATIAL_CSW_ADMIN_USR     N  
 SPATIAL_WFS_ADMIN_USR     SPATIAL_WFS_ADMIN_USR     N  
 MDDATA             MDDATA             N  
 MDSYS             MDSYS             N  
 SI_INFORMTN_SCHEMA       SI_INFORMTN_SCHEMA       N  
 ORDPLUGINS           ORDPLUGINS           N  
 ORDDATA            ORDDATA            N  
 ORDSYS             ORDSYS             N  
 OLAPSYS            OLAPSYS            N  
 ANONYMOUS           ANONYMOUS           N  
 XDB              XDB              N  
 CTXSYS             CTXSYS             N  
 EXFSYS             EXFSYS             N  
 XS$NULL            XS$NULL            N  
 WMSYS             WMSYS             N  
 APPQOSSYS           APPQOSSYS           N  
 DBSNMP             DBSNMP             N  
 ORACLE_OCM           ORACLE_OCM           N  
 DIP              DIP              N  
 OUTLN             OUTLN             N  
 HalimDba@SQL>  
 HalimDba@SQL>  
 HalimDba@SQL>  
 HalimDba@SQL>select count(*) from migrated_users where migrated_flag='Y' ;  
      8  
 HalimDba@SQL>  
 HalimDba@SQL>  
 HalimDba@SQL>select * from migrated_users where migrated_flag='Y' ;  
 EMON              EMON              Y  
 ORAPROBE            ORAPROBE            Y  
 SCOTT2             SCOTT2             Y  
 FAR              FAR              Y  
 SH               SH               Y  
 OE               OE               Y  
 HR               HR               Y  
 SCOTT             SCOTT             Y  
 HalimDba@SQL>  
 HalimDba@SQL>  
 HalimDba@SQL>  
 HalimDba@SQL>  
 HalimDba@SQL>  
 HalimDba@SQL>  
 HalimDba@SQL>  
 HalimDba@SQL>  
 HalimDba@SQL>begin  
  2   dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SQLTERMINATOR', true);  
  3   dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'PRETTY', true);  
  4 end;  
  5 /  
 PL/SQL procedure successfully completed.  
 HalimDba@SQL>  
 HalimDba@SQL>set long 2000000000  
 HalimDba@SQL>set head off  
 HalimDba@SQL>set pages 0  
 HalimDba@SQL>set feedback off  
 HalimDba@SQL>set termout off  
 HalimDba@SQL>  
 HalimDba@SQL>  
 HalimDba@SQL>  
 HalimDba@SQL>  
 HalimDba@SQL>SELECT REPLACE (DBMS_METADATA.get_ddl ('USER', UPPER (mig.old_name)),  
  2         UPPER (mig.old_name),  
  3         UPPER (mig.new_name)  
  4         )  
  5  FROM migrated_users mig JOIN dba_users usr ON usr.username =  
  6                                UPPER (old_name)  
  7  WHERE migrated_flag = 'Y'  
  8 UNION ALL  
  9 SELECT REPLACE (DBMS_METADATA.get_granted_ddl ('TABLESPACE_QUOTA',  
  10                         UPPER (old_name)  
  11                        ),  
  12         UPPER (old_name),  
  13         UPPER (new_name)  
  14         )  
  15  FROM migrated_users mig JOIN dba_ts_quotas tq  
  16     ON tq.username = UPPER (mig.old_name)  
  17  WHERE migrated_flag = 'Y'  
  18 UNION ALL  
  19 SELECT REPLACE (DBMS_METADATA.get_granted_ddl ('ROLE_GRANT', UPPER (old_name)),  
  20         UPPER (old_name),  
  21         UPPER (new_name)  
  22         )  
  23  FROM migrated_users mig JOIN dba_role_privs rpv  
  24     ON rpv.grantee = UPPER (mig.old_name)  
  25  WHERE migrated_flag = 'Y'  
  26 UNION ALL  
  27 SELECT REPLACE (DBMS_METADATA.get_granted_ddl ('SYSTEM_GRANT',  
  28                         UPPER (old_name)  
  29                        ),  
  30         UPPER (old_name),  
  31         UPPER (new_name)  
  32         )  
  33  FROM migrated_users mig JOIN dba_sys_privs spv  
  34     ON spv.grantee = UPPER (mig.old_name)  
  35  WHERE migrated_flag = 'Y'  
  36 UNION ALL  
  37 SELECT REPLACE (DBMS_METADATA.get_granted_ddl ('OBJECT_GRANT',  
  38                         UPPER (old_name)  
  39                        ),  
  40         UPPER (old_name),  
  41         UPPER (new_name)  
  42         )  
  43  FROM migrated_users mig JOIN dba_tab_privs tpv  
  44     ON tpv.grantee = UPPER (mig.old_name)  
  45  WHERE migrated_flag = 'Y'  
  46 UNION ALL  
  47 SELECT CASE  
  48      WHEN ((SELECT COUNT (*)  
  49           FROM dba_role_privs  
  50          WHERE grantee = UPPER (old_name)  
  51           AND default_role = 'YES'  
  52           AND ROWNUM = 1) > 0  
  53         )  
  54        THEN REPLACE (DBMS_METADATA.get_granted_ddl ('DEFAULT_ROLE',  
  55                              UPPER (old_name)  
  56                              ),  
  57               UPPER (old_name),  
  58               UPPER (new_name)  
  59              )  
  60     END  
  61  FROM migrated_users  
  62  WHERE migrated_flag = 'Y'  
  63 UNION ALL  
  64 SELECT REPLACE (DBMS_METADATA.get_ddl ('PROFILE', PROFILE), old_name,  
  65         new_name)  
  66  FROM migrated_users mig JOIN dba_users usr  
  67     ON usr.username = UPPER (mig.old_name)  
  68  WHERE usr.PROFILE <> 'DEFAULT' AND migrated_flag = 'Y'  
  69  
 HalimDba@SQL>  
 HalimDba@SQL>  
 HalimDba@SQL>spool make_script_for_mig_user.sql  
 HalimDba@SQL>  
 HalimDba@SQL>/  
   CREATE USER "SCOTT" IDENTIFIED BY VALUES 'S:5032AB0C7ABA383E0D28092737A91D932  
 3FD910459A177C3E9D8DB5569DA;F894844C34402B67'  
    DEFAULT TABLESPACE "USERS"  
    TEMPORARY TABLESPACE "TEMP";  
   CREATE USER "HR" IDENTIFIED BY VALUES 'S:FE1054E863661B1CE8191B2810D3AB0BFAC6  
 63C2B11E5800D35998377F3F;4C6D73C3E8B0F0DA'  
    DEFAULT TABLESPACE "USERS"  
    TEMPORARY TABLESPACE "TEMP";  
   CREATE USER "OE" IDENTIFIED BY VALUES 'S:5EE7A0404572BB90A2A789DC86B1BD368725  
 F4A92E05EADD02F9666AE230;D1A2DFC623FDA40A'  
    DEFAULT TABLESPACE "USERS"  
    TEMPORARY TABLESPACE "TEMP";  
   CREATE USER "FAR" IDENTIFIED BY VALUES 'S:ECC63700AB0394C7DA58EE01D6F58611988  
 EDC3E80C904917610869E2D35;7645F42D0980A970'  
    DEFAULT TABLESPACE "USERS"  
    TEMPORARY TABLESPACE "TEMP";  
   CREATE USER "SCOTT2" IDENTIFIED BY VALUES 'S:C08B6C101681BAF61FFADB424757B94F  
 1F3789111A75DB84B680281201A0;FC9CF3389C9FDC3A'  
    DEFAULT TABLESPACE "USERS"  
    TEMPORARY TABLESPACE "TEMP";  
   CREATE USER "ORAPROBE" IDENTIFIED BY VALUES 'S:2D8EBE0FDDE28A4E88292A9462A40F  
 EEAA15D4221B00D334DFE410B70F47;2E3EA470A4CA2D94'  
    DEFAULT TABLESPACE "USERS"  
    TEMPORARY TABLESPACE "TEMP";  
   CREATE USER "EMON" IDENTIFIED BY VALUES 'S:C40F1EF9696F7B3240C360588F9C562E3A  
 5A770E74B3B5812EF0BB1F8D34;EF9CDAF80D05BE2F'  
    DEFAULT TABLESPACE "USERS"  
    TEMPORARY TABLESPACE "TEMP";  
   CREATE USER "SH" IDENTIFIED BY VALUES 'S:96CCBC97D834A105218FB2180FB70ED528AB  
 296A7C98150C0C5439D5628E;9793B3777CD3BD1A'  
    DEFAULT TABLESPACE "USERS"  
    TEMPORARY TABLESPACE "TEMP"  
    PASSWORD EXPIRE  
    ACCOUNT LOCK;  
  DECLARE  
  TEMP_COUNT NUMBER;  
  SQLSTR VARCHAR2(200);  
 BEGIN  
  SQLSTR := 'ALTER USER "ORAPROBE" QUOTA UNLIMITED ON "USERS"';  
  EXECUTE IMMEDIATE SQLSTR;  
 EXCEPTION  
  WHEN OTHERS THEN  
   IF SQLCODE = -30041 THEN  
    SQLSTR := 'SELECT COUNT(*) FROM USER_TABLESPACES  
        WHERE TABLESPACE_NAME = ''USERS'' AND CONTENTS = ''TEMPORARY''';  
    EXECUTE IMMEDIATE SQLSTR INTO TEMP_COUNT;  
    IF TEMP_COUNT = 1 THEN RETURN;  
    ELSE RAISE;  
    END IF;  
   ELSE  
    RAISE;  
   END IF;  
 END;  
 /  
   GRANT "RESOURCE" TO "OE";  
   GRANT "XDBADMIN" TO "OE";  
   GRANT "CONNECT" TO "FAR";  
   GRANT "DBA" TO "FAR";  
   GRANT "DBA" TO "SCOTT2";  
   GRANT "CONNECT" TO "SCOTT";  
   GRANT "RESOURCE" TO "SCOTT";  
   GRANT "DBA" TO "SCOTT";  
   GRANT "RESOURCE" TO "HR";  
   GRANT "RESOURCE" TO "OE";  
   GRANT "XDBADMIN" TO "OE";  
   GRANT "SELECT_CATALOG_ROLE" TO "ORAPROBE";  
   GRANT "CONNECT" TO "SCOTT";  
   GRANT "RESOURCE" TO "SCOTT";  
   GRANT "DBA" TO "SCOTT";  
   GRANT "RESOURCE" TO "SH";  
   GRANT "SELECT_CATALOG_ROLE" TO "SH";  
   GRANT "CWM_USER" TO "SH";  
   GRANT "RESOURCE" TO "SH";  
   GRANT "SELECT_CATALOG_ROLE" TO "SH";  
   GRANT "CWM_USER" TO "SH";  
   GRANT "CONNECT" TO "FAR";  
   GRANT "DBA" TO "FAR";  
   GRANT "DBA" TO "EMON";  
   GRANT "CONNECT" TO "SCOTT";  
   GRANT "RESOURCE" TO "SCOTT";  
   GRANT "DBA" TO "SCOTT";  
   GRANT "RESOURCE" TO "SH";  
   GRANT "SELECT_CATALOG_ROLE" TO "SH";  
   GRANT "CWM_USER" TO "SH";  
  GRANT CREATE DATABASE LINK TO "HR";  
  GRANT CREATE SEQUENCE TO "HR";  
  GRANT CREATE VIEW TO "HR";  
  GRANT CREATE SYNONYM TO "HR";  
  GRANT UNLIMITED TABLESPACE TO "HR";  
  GRANT ALTER SESSION TO "HR";  
  GRANT CREATE SESSION TO "HR";  
  GRANT CREATE DATABASE LINK TO "HR";  
  GRANT CREATE SEQUENCE TO "HR";  
  GRANT CREATE VIEW TO "HR";  
  GRANT CREATE SYNONYM TO "HR";  
  GRANT UNLIMITED TABLESPACE TO "HR";  
  GRANT ALTER SESSION TO "HR";  
  GRANT CREATE SESSION TO "HR";  
  GRANT CREATE DATABASE LINK TO "HR";  
  GRANT CREATE SEQUENCE TO "HR";  
  GRANT CREATE VIEW TO "HR";  
  GRANT CREATE SYNONYM TO "HR";  
  GRANT UNLIMITED TABLESPACE TO "HR";  
  GRANT ALTER SESSION TO "HR";  
  GRANT CREATE SESSION TO "HR";  
  GRANT CREATE DIMENSION TO "SH";  
  GRANT QUERY REWRITE TO "SH";  
  GRANT CREATE MATERIALIZED VIEW TO "SH";  
  GRANT CREATE DATABASE LINK TO "SH";  
  GRANT CREATE SEQUENCE TO "SH";  
  GRANT CREATE VIEW TO "SH";  
  GRANT CREATE SYNONYM TO "SH";  
  GRANT CREATE CLUSTER TO "SH";  
  GRANT CREATE TABLE TO "SH";  
  GRANT UNLIMITED TABLESPACE TO "SH";  
  GRANT ALTER SESSION TO "SH";  
  GRANT CREATE SESSION TO "SH";  
  GRANT CREATE DIMENSION TO "SH";  
  GRANT QUERY REWRITE TO "SH";  
  GRANT CREATE MATERIALIZED VIEW TO "SH";  
  GRANT CREATE DATABASE LINK TO "SH";  
  GRANT CREATE SEQUENCE TO "SH";  
  GRANT CREATE VIEW TO "SH";  
  GRANT CREATE SYNONYM TO "SH";  
  GRANT CREATE CLUSTER TO "SH";  
  GRANT CREATE TABLE TO "SH";  
  GRANT UNLIMITED TABLESPACE TO "SH";  
  GRANT ALTER SESSION TO "SH";  
  GRANT CREATE SESSION TO "SH";  
  GRANT CREATE PROCEDURE TO "ORAPROBE";  
  GRANT CREATE TABLE TO "ORAPROBE";  
  GRANT CREATE SESSION TO "ORAPROBE";  
  GRANT UNLIMITED TABLESPACE TO "SCOTT";  
  GRANT CREATE DATABASE LINK TO "HR";  
  GRANT CREATE SEQUENCE TO "HR";  
  GRANT CREATE VIEW TO "HR";  
  GRANT CREATE SYNONYM TO "HR";  
  GRANT UNLIMITED TABLESPACE TO "HR";  
  GRANT ALTER SESSION TO "HR";  
  GRANT CREATE SESSION TO "HR";  
  GRANT QUERY REWRITE TO "OE";  
  GRANT CREATE MATERIALIZED VIEW TO "OE";  
  GRANT CREATE DATABASE LINK TO "OE";  
  GRANT CREATE VIEW TO "OE";  
  GRANT CREATE SYNONYM TO "OE";  
  GRANT UNLIMITED TABLESPACE TO "OE";  
  GRANT CREATE SESSION TO "OE";  
  GRANT QUERY REWRITE TO "OE";  
  GRANT CREATE MATERIALIZED VIEW TO "OE";  
  GRANT CREATE DATABASE LINK TO "OE";  
  GRANT CREATE VIEW TO "OE";  
  GRANT CREATE SYNONYM TO "OE";  
  GRANT UNLIMITED TABLESPACE TO "OE";  
  GRANT CREATE SESSION TO "OE";  
  GRANT CREATE DIMENSION TO "SH";  
  GRANT QUERY REWRITE TO "SH";  
  GRANT CREATE MATERIALIZED VIEW TO "SH";  
  GRANT CREATE DATABASE LINK TO "SH";  
  GRANT CREATE SEQUENCE TO "SH";  
  GRANT CREATE VIEW TO "SH";  
  GRANT CREATE SYNONYM TO "SH";  
  GRANT CREATE CLUSTER TO "SH";  
  GRANT CREATE TABLE TO "SH";  
  GRANT UNLIMITED TABLESPACE TO "SH";  
  GRANT ALTER SESSION TO "SH";  
  GRANT CREATE SESSION TO "SH";  
  GRANT CREATE DIMENSION TO "SH";  
  GRANT QUERY REWRITE TO "SH";  
  GRANT CREATE MATERIALIZED VIEW TO "SH";  
  GRANT CREATE DATABASE LINK TO "SH";  
  GRANT CREATE SEQUENCE TO "SH";  
  GRANT CREATE VIEW TO "SH";  
  GRANT CREATE SYNONYM TO "SH";  
  GRANT CREATE CLUSTER TO "SH";  
  GRANT CREATE TABLE TO "SH";  
  GRANT UNLIMITED TABLESPACE TO "SH";  
  GRANT ALTER SESSION TO "SH";  
  GRANT CREATE SESSION TO "SH";  
  GRANT CREATE DIMENSION TO "SH";  
  GRANT QUERY REWRITE TO "SH";  
  GRANT CREATE MATERIALIZED VIEW TO "SH";  
  GRANT CREATE DATABASE LINK TO "SH";  
  GRANT CREATE SEQUENCE TO "SH";  
  GRANT CREATE VIEW TO "SH";  
  GRANT CREATE SYNONYM TO "SH";  
  GRANT CREATE CLUSTER TO "SH";  
  GRANT CREATE TABLE TO "SH";  
  GRANT UNLIMITED TABLESPACE TO "SH";  
  GRANT ALTER SESSION TO "SH";  
  GRANT CREATE SESSION TO "SH";  
  GRANT UNLIMITED TABLESPACE TO "FAR";  
  GRANT CREATE DATABASE LINK TO "HR";  
  GRANT CREATE SEQUENCE TO "HR";  
  GRANT CREATE VIEW TO "HR";  
  GRANT CREATE SYNONYM TO "HR";  
  GRANT UNLIMITED TABLESPACE TO "HR";  
  GRANT ALTER SESSION TO "HR";  
  GRANT CREATE SESSION TO "HR";  
  GRANT CREATE DATABASE LINK TO "HR";  
  GRANT CREATE SEQUENCE TO "HR";  
  GRANT CREATE VIEW TO "HR";  
  GRANT CREATE SYNONYM TO "HR";  
  GRANT UNLIMITED TABLESPACE TO "HR";  
  GRANT ALTER SESSION TO "HR";  
  GRANT CREATE SESSION TO "HR";  
  GRANT CREATE DIMENSION TO "SH";  
  GRANT QUERY REWRITE TO "SH";  
  GRANT CREATE MATERIALIZED VIEW TO "SH";  
  GRANT CREATE DATABASE LINK TO "SH";  
  GRANT CREATE SEQUENCE TO "SH";  
  GRANT CREATE VIEW TO "SH";  
  GRANT CREATE SYNONYM TO "SH";  
  GRANT CREATE CLUSTER TO "SH";  
  GRANT CREATE TABLE TO "SH";  
  GRANT UNLIMITED TABLESPACE TO "SH";  
  GRANT ALTER SESSION TO "SH";  
  GRANT CREATE SESSION TO "SH";  
  GRANT CREATE DATABASE LINK TO "HR";  
  GRANT CREATE SEQUENCE TO "HR";  
  GRANT CREATE VIEW TO "HR";  
  GRANT CREATE SYNONYM TO "HR";  
  GRANT UNLIMITED TABLESPACE TO "HR";  
  GRANT ALTER SESSION TO "HR";  
  GRANT CREATE SESSION TO "HR";  
  GRANT QUERY REWRITE TO "OE";  
  GRANT CREATE MATERIALIZED VIEW TO "OE";  
  GRANT CREATE DATABASE LINK TO "OE";  
  GRANT CREATE VIEW TO "OE";  
  GRANT CREATE SYNONYM TO "OE";  
  GRANT UNLIMITED TABLESPACE TO "OE";  
  GRANT CREATE SESSION TO "OE";  
  GRANT QUERY REWRITE TO "OE";  
  GRANT CREATE MATERIALIZED VIEW TO "OE";  
  GRANT CREATE DATABASE LINK TO "OE";  
  GRANT CREATE VIEW TO "OE";  
  GRANT CREATE SYNONYM TO "OE";  
  GRANT UNLIMITED TABLESPACE TO "OE";  
  GRANT CREATE SESSION TO "OE";  
  GRANT CREATE DIMENSION TO "SH";  
  GRANT QUERY REWRITE TO "SH";  
  GRANT CREATE MATERIALIZED VIEW TO "SH";  
  GRANT CREATE DATABASE LINK TO "SH";  
  GRANT CREATE SEQUENCE TO "SH";  
  GRANT CREATE VIEW TO "SH";  
  GRANT CREATE SYNONYM TO "SH";  
  GRANT CREATE CLUSTER TO "SH";  
  GRANT CREATE TABLE TO "SH";  
  GRANT UNLIMITED TABLESPACE TO "SH";  
  GRANT ALTER SESSION TO "SH";  
  GRANT CREATE SESSION TO "SH";  
  GRANT CREATE DIMENSION TO "SH";  
  GRANT QUERY REWRITE TO "SH";  
  GRANT CREATE MATERIALIZED VIEW TO "SH";  
  GRANT CREATE DATABASE LINK TO "SH";  
  GRANT CREATE SEQUENCE TO "SH";  
  GRANT CREATE VIEW TO "SH";  
  GRANT CREATE SYNONYM TO "SH";  
  GRANT CREATE CLUSTER TO "SH";  
  GRANT CREATE TABLE TO "SH";  
  GRANT UNLIMITED TABLESPACE TO "SH";  
  GRANT ALTER SESSION TO "SH";  
  GRANT CREATE SESSION TO "SH";  
  GRANT CREATE DIMENSION TO "SH";  
  GRANT QUERY REWRITE TO "SH";  
  GRANT CREATE MATERIALIZED VIEW TO "SH";  
  GRANT CREATE DATABASE LINK TO "SH";  
  GRANT CREATE SEQUENCE TO "SH";  
  GRANT CREATE VIEW TO "SH";  
  GRANT CREATE SYNONYM TO "SH";  
  GRANT CREATE CLUSTER TO "SH";  
  GRANT CREATE TABLE TO "SH";  
  GRANT UNLIMITED TABLESPACE TO "SH";  
  GRANT ALTER SESSION TO "SH";  
  GRANT CREATE SESSION TO "SH";  
  GRANT CREATE PROCEDURE TO "ORAPROBE";  
  GRANT CREATE TABLE TO "ORAPROBE";  
  GRANT CREATE SESSION TO "ORAPROBE";  
  GRANT QUERY REWRITE TO "OE";  
  GRANT CREATE MATERIALIZED VIEW TO "OE";  
  GRANT CREATE DATABASE LINK TO "OE";  
  GRANT CREATE VIEW TO "OE";  
  GRANT CREATE SYNONYM TO "OE";  
  GRANT UNLIMITED TABLESPACE TO "OE";  
  GRANT CREATE SESSION TO "OE";  
  GRANT QUERY REWRITE TO "OE";  
  GRANT CREATE MATERIALIZED VIEW TO "OE";  
  GRANT CREATE DATABASE LINK TO "OE";  
  GRANT CREATE VIEW TO "OE";  
  GRANT CREATE SYNONYM TO "OE";  
  GRANT UNLIMITED TABLESPACE TO "OE";  
  GRANT CREATE SESSION TO "OE";  
  GRANT UNLIMITED TABLESPACE TO "SCOTT2";  
  GRANT CREATE PROCEDURE TO "ORAPROBE";  
  GRANT CREATE TABLE TO "ORAPROBE";  
  GRANT CREATE SESSION TO "ORAPROBE";  
  GRANT QUERY REWRITE TO "OE";  
  GRANT CREATE MATERIALIZED VIEW TO "OE";  
  GRANT CREATE DATABASE LINK TO "OE";  
  GRANT CREATE VIEW TO "OE";  
  GRANT CREATE SYNONYM TO "OE";  
  GRANT UNLIMITED TABLESPACE TO "OE";  
  GRANT CREATE SESSION TO "OE";  
  GRANT CREATE DIMENSION TO "SH";  
  GRANT QUERY REWRITE TO "SH";  
  GRANT CREATE MATERIALIZED VIEW TO "SH";  
  GRANT CREATE DATABASE LINK TO "SH";  
  GRANT CREATE SEQUENCE TO "SH";  
  GRANT CREATE VIEW TO "SH";  
  GRANT CREATE SYNONYM TO "SH";  
  GRANT CREATE CLUSTER TO "SH";  
  GRANT CREATE TABLE TO "SH";  
  GRANT UNLIMITED TABLESPACE TO "SH";  
  GRANT ALTER SESSION TO "SH";  
  GRANT CREATE SESSION TO "SH";  
  GRANT CREATE DIMENSION TO "SH";  
  GRANT QUERY REWRITE TO "SH";  
  GRANT CREATE MATERIALIZED VIEW TO "SH";  
  GRANT CREATE DATABASE LINK TO "SH";  
  GRANT CREATE SEQUENCE TO "SH";  
  GRANT CREATE VIEW TO "SH";  
  GRANT CREATE SYNONYM TO "SH";  
  GRANT CREATE CLUSTER TO "SH";  
  GRANT CREATE TABLE TO "SH";  
  GRANT UNLIMITED TABLESPACE TO "SH";  
  GRANT ALTER SESSION TO "SH";  
  GRANT CREATE SESSION TO "SH";  
  GRANT CREATE DIMENSION TO "SH";  
  GRANT QUERY REWRITE TO "SH";  
  GRANT CREATE MATERIALIZED VIEW TO "SH";  
  GRANT CREATE DATABASE LINK TO "SH";  
  GRANT CREATE SEQUENCE TO "SH";  
  GRANT CREATE VIEW TO "SH";  
  GRANT CREATE SYNONYM TO "SH";  
  GRANT CREATE CLUSTER TO "SH";  
  GRANT CREATE TABLE TO "SH";  
  GRANT UNLIMITED TABLESPACE TO "SH";  
  GRANT ALTER SESSION TO "SH";  
  GRANT CREATE SESSION TO "SH";  
  GRANT UNLIMITED TABLESPACE TO "EMON";  
  GRANT EXECUTE ON "SYS"."DBMS_STATS" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."COUNTRIES" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."LOCATIONS" TO "OE";  
  GRANT SELECT ON "HR"."DEPARTMENTS" TO "OE";  
  GRANT SELECT ON "HR"."JOBS" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."EMPLOYEES" TO "OE";  
  GRANT SELECT ON "HR"."JOB_HISTORY" TO "OE";  
  GRANT READ, WRITE ON DIRECTORY "SS_OE_XMLDIR" TO "OE" WITH GRANT OPTION;  
  GRANT READ, WRITE ON DIRECTORY "SUBDIR" TO "OE" WITH GRANT OPTION;  
  GRANT EXECUTE ON "SYS"."DBMS_STATS" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."COUNTRIES" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."LOCATIONS" TO "OE";  
  GRANT SELECT ON "HR"."DEPARTMENTS" TO "OE";  
  GRANT SELECT ON "HR"."JOBS" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."EMPLOYEES" TO "OE";  
  GRANT SELECT ON "HR"."JOB_HISTORY" TO "OE";  
  GRANT READ, WRITE ON DIRECTORY "SS_OE_XMLDIR" TO "OE" WITH GRANT OPTION;  
  GRANT READ, WRITE ON DIRECTORY "SUBDIR" TO "OE" WITH GRANT OPTION;  
  GRANT EXECUTE ON "SYS"."DBMS_STATS" TO "SH";  
  GRANT READ ON DIRECTORY "DATA_FILE_DIR" TO "SH";  
  GRANT READ, WRITE ON DIRECTORY "LOG_FILE_DIR" TO "SH";  
  GRANT EXECUTE ON "SYS"."DBMS_STATS" TO "SH";  
  GRANT READ ON DIRECTORY "DATA_FILE_DIR" TO "SH";  
  GRANT READ, WRITE ON DIRECTORY "LOG_FILE_DIR" TO "SH";  
  GRANT EXECUTE ON "SYS"."DBMS_STATS" TO "SH";  
  GRANT READ ON DIRECTORY "DATA_FILE_DIR" TO "SH";  
  GRANT READ, WRITE ON DIRECTORY "LOG_FILE_DIR" TO "SH";  
  GRANT SELECT ON "SYS"."DBA_USERS" TO "ORAPROBE";  
  GRANT EXECUTE ON "SYS"."DBMS_STATS" TO "SH";  
  GRANT READ ON DIRECTORY "DATA_FILE_DIR" TO "SH";  
  GRANT READ, WRITE ON DIRECTORY "LOG_FILE_DIR" TO "SH";  
  GRANT EXECUTE ON "SYS"."DBMS_STATS" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."COUNTRIES" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."LOCATIONS" TO "OE";  
  GRANT SELECT ON "HR"."DEPARTMENTS" TO "OE";  
  GRANT SELECT ON "HR"."JOBS" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."EMPLOYEES" TO "OE";  
  GRANT SELECT ON "HR"."JOB_HISTORY" TO "OE";  
  GRANT READ, WRITE ON DIRECTORY "SS_OE_XMLDIR" TO "OE" WITH GRANT OPTION;  
  GRANT READ, WRITE ON DIRECTORY "SUBDIR" TO "OE" WITH GRANT OPTION;  
  GRANT EXECUTE ON "SYS"."DBMS_STATS" TO "HR";  
  GRANT EXECUTE ON "SYS"."DBMS_STATS" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."COUNTRIES" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."LOCATIONS" TO "OE";  
  GRANT SELECT ON "HR"."DEPARTMENTS" TO "OE";  
  GRANT SELECT ON "HR"."JOBS" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."EMPLOYEES" TO "OE";  
  GRANT SELECT ON "HR"."JOB_HISTORY" TO "OE";  
  GRANT READ, WRITE ON DIRECTORY "SS_OE_XMLDIR" TO "OE" WITH GRANT OPTION;  
  GRANT READ, WRITE ON DIRECTORY "SUBDIR" TO "OE" WITH GRANT OPTION;  
  GRANT EXECUTE ON "SYS"."DBMS_STATS" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."COUNTRIES" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."LOCATIONS" TO "OE";  
  GRANT SELECT ON "HR"."DEPARTMENTS" TO "OE";  
  GRANT SELECT ON "HR"."JOBS" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."EMPLOYEES" TO "OE";  
  GRANT SELECT ON "HR"."JOB_HISTORY" TO "OE";  
  GRANT READ, WRITE ON DIRECTORY "SS_OE_XMLDIR" TO "OE" WITH GRANT OPTION;  
  GRANT READ, WRITE ON DIRECTORY "SUBDIR" TO "OE" WITH GRANT OPTION;  
  GRANT EXECUTE, READ, WRITE ON DIRECTORY "DIR_MVIEW" TO "SCOTT" WITH GRANT OPTI  
 ON;  
  GRANT EXECUTE, READ, WRITE ON DIRECTORY "DIR_MVIEW" TO "SCOTT" WITH GRANT OPTI  
 ON;  
  GRANT EXECUTE, READ, WRITE ON DIRECTORY "DIR_MVIEW" TO "SCOTT" WITH GRANT OPTI  
 ON;  
  GRANT EXECUTE ON "SYS"."DBMS_STATS" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."COUNTRIES" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."LOCATIONS" TO "OE";  
  GRANT SELECT ON "HR"."DEPARTMENTS" TO "OE";  
  GRANT SELECT ON "HR"."JOBS" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."EMPLOYEES" TO "OE";  
  GRANT SELECT ON "HR"."JOB_HISTORY" TO "OE";  
  GRANT READ, WRITE ON DIRECTORY "SS_OE_XMLDIR" TO "OE" WITH GRANT OPTION;  
  GRANT READ, WRITE ON DIRECTORY "SUBDIR" TO "OE" WITH GRANT OPTION;  
  GRANT EXECUTE ON "SYS"."DBMS_STATS" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."COUNTRIES" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."LOCATIONS" TO "OE";  
  GRANT SELECT ON "HR"."DEPARTMENTS" TO "OE";  
  GRANT SELECT ON "HR"."JOBS" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."EMPLOYEES" TO "OE";  
  GRANT SELECT ON "HR"."JOB_HISTORY" TO "OE";  
  GRANT READ, WRITE ON DIRECTORY "SS_OE_XMLDIR" TO "OE" WITH GRANT OPTION;  
  GRANT READ, WRITE ON DIRECTORY "SUBDIR" TO "OE" WITH GRANT OPTION;  
  GRANT EXECUTE ON "SYS"."DBMS_STATS" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."COUNTRIES" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."LOCATIONS" TO "OE";  
  GRANT SELECT ON "HR"."DEPARTMENTS" TO "OE";  
  GRANT SELECT ON "HR"."JOBS" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."EMPLOYEES" TO "OE";  
  GRANT SELECT ON "HR"."JOB_HISTORY" TO "OE";  
  GRANT READ, WRITE ON DIRECTORY "SS_OE_XMLDIR" TO "OE" WITH GRANT OPTION;  
  GRANT READ, WRITE ON DIRECTORY "SUBDIR" TO "OE" WITH GRANT OPTION;  
  GRANT EXECUTE ON "SYS"."DBMS_STATS" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."COUNTRIES" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."LOCATIONS" TO "OE";  
  GRANT SELECT ON "HR"."DEPARTMENTS" TO "OE";  
  GRANT SELECT ON "HR"."JOBS" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."EMPLOYEES" TO "OE";  
  GRANT SELECT ON "HR"."JOB_HISTORY" TO "OE";  
  GRANT READ, WRITE ON DIRECTORY "SS_OE_XMLDIR" TO "OE" WITH GRANT OPTION;  
  GRANT READ, WRITE ON DIRECTORY "SUBDIR" TO "OE" WITH GRANT OPTION;  
  GRANT EXECUTE ON "SYS"."DBMS_STATS" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."COUNTRIES" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."LOCATIONS" TO "OE";  
  GRANT SELECT ON "HR"."DEPARTMENTS" TO "OE";  
  GRANT SELECT ON "HR"."JOBS" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."EMPLOYEES" TO "OE";  
  GRANT SELECT ON "HR"."JOB_HISTORY" TO "OE";  
  GRANT READ, WRITE ON DIRECTORY "SS_OE_XMLDIR" TO "OE" WITH GRANT OPTION;  
  GRANT READ, WRITE ON DIRECTORY "SUBDIR" TO "OE" WITH GRANT OPTION;  
  GRANT EXECUTE ON "SYS"."DBMS_STATS" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."COUNTRIES" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."LOCATIONS" TO "OE";  
  GRANT SELECT ON "HR"."DEPARTMENTS" TO "OE";  
  GRANT SELECT ON "HR"."JOBS" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."EMPLOYEES" TO "OE";  
  GRANT SELECT ON "HR"."JOB_HISTORY" TO "OE";  
  GRANT READ, WRITE ON DIRECTORY "SS_OE_XMLDIR" TO "OE" WITH GRANT OPTION;  
  GRANT READ, WRITE ON DIRECTORY "SUBDIR" TO "OE" WITH GRANT OPTION;  
  GRANT EXECUTE ON "SYS"."DBMS_STATS" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."COUNTRIES" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."LOCATIONS" TO "OE";  
  GRANT SELECT ON "HR"."DEPARTMENTS" TO "OE";  
  GRANT SELECT ON "HR"."JOBS" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."EMPLOYEES" TO "OE";  
  GRANT SELECT ON "HR"."JOB_HISTORY" TO "OE";  
  GRANT READ, WRITE ON DIRECTORY "SS_OE_XMLDIR" TO "OE" WITH GRANT OPTION;  
  GRANT READ, WRITE ON DIRECTORY "SUBDIR" TO "OE" WITH GRANT OPTION;  
  GRANT EXECUTE ON "SYS"."DBMS_STATS" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."COUNTRIES" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."LOCATIONS" TO "OE";  
  GRANT SELECT ON "HR"."DEPARTMENTS" TO "OE";  
  GRANT SELECT ON "HR"."JOBS" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."EMPLOYEES" TO "OE";  
  GRANT SELECT ON "HR"."JOB_HISTORY" TO "OE";  
  GRANT READ, WRITE ON DIRECTORY "SS_OE_XMLDIR" TO "OE" WITH GRANT OPTION;  
  GRANT READ, WRITE ON DIRECTORY "SUBDIR" TO "OE" WITH GRANT OPTION;  
  GRANT EXECUTE ON "SYS"."DBMS_STATS" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."COUNTRIES" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."LOCATIONS" TO "OE";  
  GRANT SELECT ON "HR"."DEPARTMENTS" TO "OE";  
  GRANT SELECT ON "HR"."JOBS" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."EMPLOYEES" TO "OE";  
  GRANT SELECT ON "HR"."JOB_HISTORY" TO "OE";  
  GRANT READ, WRITE ON DIRECTORY "SS_OE_XMLDIR" TO "OE" WITH GRANT OPTION;  
  GRANT READ, WRITE ON DIRECTORY "SUBDIR" TO "OE" WITH GRANT OPTION;  
   ALTER USER "EMON" DEFAULT ROLE ALL;  
   ALTER USER "ORAPROBE" DEFAULT ROLE ALL;  
   ALTER USER "SCOTT2" DEFAULT ROLE ALL;  
   ALTER USER "FAR" DEFAULT ROLE ALL;  
   ALTER USER "SH" DEFAULT ROLE ALL;  
   ALTER USER "OE" DEFAULT ROLE ALL;  
   ALTER USER "HR" DEFAULT ROLE ALL;  
   ALTER USER "SCOTT" DEFAULT ROLE ALL;  
 HalimDba@SQL>  
 HalimDba@SQL>  
 HalimDba@SQL>  
 HalimDba@SQL>  
 HalimDba@SQL>  
 HalimDba@SQL>  
 HalimDba@SQL>spool off  
 HalimDba@SQL>  
 HalimDba@SQL>ed make_script_for_mig_user.sql  



No comments: