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
==================
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:
Post a Comment