28 | 06 | 2017
Latest Articles
Popular Articles

Scripts

Script to generate user DDL

User Rating:  / 1
PoorBest 

Script to generate user DDL.

 

set long 100000
set longchunksize 200
set heading off
set feedback off
set echo off
set verify off
undefine username

select (case
when ((select count(*)
from dba_users
where username = '&&Username') > 0)
then dbms_metadata.get_ddl ('USER', '&&Username')
else to_clob ('User does not exist')
end ) Extracted_DDL from dual
UNION ALL
select (case
when ((select count(*)
from dba_ts_quotas
where username = '&&Username') > 0)
then dbms_metadata.get_granted_ddl( 'TABLESPACE_QUOTA', '&&Username')
end ) from dual
UNION ALL
select (case
when ((select count(*)
from dba_role_privs
where grantee = '&&Username') > 0)
then dbms_metadata.get_granted_ddl ('ROLE_GRANT', '&&Username')
end ) from dual
UNION ALL
select (case
when ((select count(*)
from dba_role_privs
where grantee = '&&Username') > 0)
then dbms_metadata.get_granted_ddl ('DEFAULT_ROLE', '&&Username')
end ) from dual
UNION ALL
select (case
when ((select count(*)
from dba_sys_privs
where grantee = '&&Username') > 0)
then dbms_metadata.get_granted_ddl ('SYSTEM_GRANT', '&&Username')
end ) from dual
UNION ALL
select (case
when ((select count(*)
from dba_tab_privs
where grantee = '&&Username') > 0)
then dbms_metadata.get_granted_ddl ('OBJECT_GRANT', '&&Username')
end ) from dual;