Hi all,
Facts: 10g.
OS: Windows 2008
I need to get all ORACLE USER STATEMENTS for each users on my database.
I had gotten this script, but this only got me for 1 user, and I need for all users created on my database.
set line 1000
SET LONG 10000
select (case
when ((select count(*)
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(*)
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(*)
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(*)
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(*)
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
/
as you can see this only works for one user.
What should I change or better, if you can help me to get all oracle USER STATEMENTS on my database.
Thank you.