Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

Version 1 Current »

Extract user information from Oracle Data Dictionary

– (o) Tom Kyte
set pagesize 999
set long 10000
select (case
when ((select count(star)
from dba_users
where username = '&&Username') > 0)
then dbms_metadata.get_ddl ('USER', '&&Username')
else to_clob (' – Note: User not found!')
end ) Extracted_DDL from dual
UNION ALL
select (case
when ((select count(star)
from dba_ts_quotas
where username = '&&Username') > 0)
then dbms_metadata.get_granted_ddl( 'TABLESPACE_QUOTA', '&&Username')
else to_clob (' – Note: No TS Quotas found!')
end ) from dual
UNION ALL
select (case
when ((select count(star)
from dba_role_privs
where grantee = '&&Username') > 0)
then dbms_metadata.get_granted_ddl ('ROLE_GRANT', '&&Username')
else to_clob (' – Note: No granted Roles found!')
end ) from dual
UNION ALL
select (case
when ((select count(star)
from dba_sys_privs
where grantee = '&&Username') > 0)
then dbms_metadata.get_granted_ddl ('SYSTEM_GRANT', '&&Username')
else to_clob (' – Note: No System Privileges found!')
end ) from dual
UNION ALL
select (case
when ((select count(star)
from dba_tab_privs
where grantee = '&&Username') > 0)
then dbms_metadata.get_granted_ddl ('OBJECT_GRANT', '&&Username')
else to_clob (' – Note: No Object Privileges found!')
end ) from dual
/

  • No labels