Re-create user SQL script

This is a script I wrote to generate users from an existing Oracle database for reloading or moving to another.

 

-- Jed S. Walker, 2014/02/24
-- Generate create users for duplication

set serveroutput on size 100000
set pages 0 
set long 10000
set echo off
set feedback off
set heading off
set verify off
--> necessary to prevent breaks in user create ddl
set linesize 2000
col extracted_dll format a2000 
--< necessary to prevent breaks in user create ddl

execute DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);
execute DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',false);

spool tmp_cr_std_users.sql

prompt spool tmp_cr_std_users.lst

select extracted_dll 
from (
select username, 
 (case 
 when ((select count(*)
 from dba_users 
 where username = du.username) > 0)
 then dbms_metadata.get_ddl ('USER', du.username)
 else to_clob (' -- Note: User not found!')
 end ) extracted_dll 
from dba_users du where profile = 'COMPANY_STD'
UNION ALL
select username, 
 (case 
 when ((select count(*)
 from dba_ts_quotas
 where username = du.username) > 0)
 then dbms_metadata.get_granted_ddl( 'TABLESPACE_QUOTA', du.username) 
 else to_clob (' -- Note: No TS Quotas found!')
 end ) extracted_dll 
from dba_users du where profile = 'COMPANY_STD'
UNION ALL
select username, 
 (case 
 when ((select count(*)
 from dba_role_privs
 where grantee = du.username) > 0)
 then dbms_metadata.get_granted_ddl ('ROLE_GRANT', du.username) 
 else to_clob (' -- Note: No granted Roles found!')
 end ) extracted_dll 
from dba_users du where profile = 'COMPANY_STD'
UNION ALL
select username, 
 (case 
 when ((select count(*)
 from dba_sys_privs
 where grantee = du.username) > 0)
 then dbms_metadata.get_granted_ddl ('SYSTEM_GRANT', du.username) 
 else to_clob (' -- Note: No System Privileges found!')
 end ) extracted_dll 
from dba_users du where profile = 'COMPANY_STD'
UNION ALL
select username, 
 (case 
 when ((select count(*)
 from dba_tab_privs
 where grantee = du.username) > 0)
 then dbms_metadata.get_granted_ddl ('OBJECT_GRANT', du.username) 
 else to_clob (' -- Note: No Object Privileges found!')
 end ) extracted_dll 
from dba_users du where profile = 'COMPANY_STD'
order by username
)
/

prompt spool off

spool off

 

Then run the following (this is for Linux) to clean it up:

cp tmp_cr_std_users.sql temp.sql
sed -e 's/[[:blank:]]\+$//' temp.sql > tmp_cr_std_users.sql

 

Advertisements