---------------------------------------------
-- ###############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
---------------------------------------------
-- ###########################################
---------------------------------------------
