【PLSQL】Oracle migrate the users into another DB instance

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110

--------------------------------------------- -- ###############OnSource################### --------------------------------------------- -- WX:DBAJOE399 create or replace procedure pr_user_ddl as   cursor get_username is select username from dba_users where username like '%SYS%';       begin    dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SQLTERMINATOR', true);    dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'PRETTY', true); end;   begin   for l_user in get_username loop   DBMS_OUTPUT.PUT_LINE('-----------------------'); DBMS_OUTPUT.PUT_LINE('select (case'); DBMS_OUTPUT.PUT_LINE(' when ((select count(*)'); DBMS_OUTPUT.PUT_LINE(' from dba_users'); DBMS_OUTPUT.PUT_LINE(' where username = '''||l_user.username||''') > 0)'); DBMS_OUTPUT.PUT_LINE(' then dbms_metadata.get_ddl (''USER'', '''||l_user.username||''')'); DBMS_OUTPUT.PUT_LINE(' else to_clob ('' -- Note: User not found!'')'); DBMS_OUTPUT.PUT_LINE(' end ) "--Extracted_DDL" from dual'); DBMS_OUTPUT.PUT_LINE('UNION ALL');   DBMS_OUTPUT.PUT_LINE('-----------------------'); DBMS_OUTPUT.PUT_LINE('select (case'); DBMS_OUTPUT.PUT_LINE(' when ((select count(*)'); DBMS_OUTPUT.PUT_LINE(' from dba_ts_quotas'); DBMS_OUTPUT.PUT_LINE(' where username = '''||l_user.username||''') > 0)'); DBMS_OUTPUT.PUT_LINE(' then dbms_metadata.get_granted_ddl (''TABLESPACE_QUOTA'', '''||l_user.username||''')'); DBMS_OUTPUT.PUT_LINE(' else to_clob ('' -- Note: No TS Quotas found!'')'); DBMS_OUTPUT.PUT_LINE(' end ) from dual'); DBMS_OUTPUT.PUT_LINE('UNION ALL');   DBMS_OUTPUT.PUT_LINE('-----------------------'); DBMS_OUTPUT.PUT_LINE('select (case'); DBMS_OUTPUT.PUT_LINE(' when ((select count(*)'); DBMS_OUTPUT.PUT_LINE(' from dba_role_privs'); DBMS_OUTPUT.PUT_LINE(' where grantee = '''||l_user.username||''') > 0)'); DBMS_OUTPUT.PUT_LINE(' then dbms_metadata.get_granted_ddl (''ROLE_GRANT'', '''||l_user.username||''')'); DBMS_OUTPUT.PUT_LINE(' else to_clob ('' -- Note: No granted roles found!'')'); DBMS_OUTPUT.PUT_LINE(' end ) from dual'); DBMS_OUTPUT.PUT_LINE('UNION ALL');   DBMS_OUTPUT.PUT_LINE('-----------------------'); DBMS_OUTPUT.PUT_LINE('select (case'); DBMS_OUTPUT.PUT_LINE(' when ((select count(*)'); DBMS_OUTPUT.PUT_LINE(' from dba_role_privs'); DBMS_OUTPUT.PUT_LINE(' where grantee = '''||l_user.username||''') > 0)'); DBMS_OUTPUT.PUT_LINE(' then dbms_metadata.get_granted_ddl (''DEFAULT_ROLE'', '''||l_user.username||''')'); DBMS_OUTPUT.PUT_LINE(' else to_clob ('' -- Note: No System Privileges found!'')'); DBMS_OUTPUT.PUT_LINE(' end ) from dual'); DBMS_OUTPUT.PUT_LINE('UNION ALL');   DBMS_OUTPUT.PUT_LINE('-----------------------'); DBMS_OUTPUT.PUT_LINE('select (case'); DBMS_OUTPUT.PUT_LINE(' when ((select count(*)'); DBMS_OUTPUT.PUT_LINE(' from dba_sys_privs'); DBMS_OUTPUT.PUT_LINE(' where grantee = '''||l_user.username||''') > 0)'); DBMS_OUTPUT.PUT_LINE(' then dbms_metadata.get_granted_ddl (''SYSTEM_GRANT'', '''||l_user.username||''')'); DBMS_OUTPUT.PUT_LINE(' else to_clob ('' -- Note: No System Privileges found!'')'); DBMS_OUTPUT.PUT_LINE(' end ) from dual'); DBMS_OUTPUT.PUT_LINE('UNION ALL');   DBMS_OUTPUT.PUT_LINE('-----------------------'); DBMS_OUTPUT.PUT_LINE('select (case'); DBMS_OUTPUT.PUT_LINE(' when ((select count(*)'); DBMS_OUTPUT.PUT_LINE(' from dba_tab_privs'); DBMS_OUTPUT.PUT_LINE(' where grantee = '''||l_user.username||''') > 0)'); DBMS_OUTPUT.PUT_LINE(' then dbms_metadata.get_granted_ddl (''OBJECT_GRANT'', '''||l_user.username||''')'); DBMS_OUTPUT.PUT_LINE(' else to_clob ('' -- Note: No Object Privileges found!'')'); DBMS_OUTPUT.PUT_LINE(' end ) from dual'); DBMS_OUTPUT.PUT_LINE('/'); DBMS_OUTPUT.PUT_LINE('----------------------------------------------------------------------');   end loop;   end; /       set pages 50000 set long 20000 longchunksize 20000 pagesize 0 linesize 1000 feedback off verify off trimspool on column ddl format a1000 set serveroutput on size unlimited spool /tmp/create_user_ddl_to_be_ran.sql exec pr_user_ddl spool off     -------------------------------------------- -- ###############OnTarget################## --------------------------------------------- spool /tmp/user_created_history.log set long 1000000000 set pages 50000 exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE); @/tmp/create_user_ddl_to_be_ran.sql spool off --------------------------------------------- -- ########################################### ---------------------------------------------