使用PL/SQL获取创建用户的语句
Create procedure:
create or replace procedure get_case_sqls_for_ddls_ver1 as
cursor get_username is
select username from dba_users;
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_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;
/
The above proc when called with the foll. will give the SQLs for all users:
set head off
set pages 0
set serveroutput on size unlimited
spool /tmp/sqls_gathered_frm_trial_run_1.sql
exec get_case_sqls_for_ddls_ver1
spool off
These SQLs generated can in turn be run as follows to get the ma
