Recreate objects using dbms metadata.get ddl

From dbawiki
Jump to: navigation, search

Recreate all the users (including grants) in a database[edit]

select dbms_metadata.get_ddl('USER', username) || '/' ddl
from dba_users
union all
select dbms_metadata.get_granted_ddl('ROLE_GRANT', username) || '/' ddl
from dba_users
union all
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', username) || '/' ddl
from dba_users
union all
select dbms_metadata.get_granted_ddl('OBJECT_GRANT', username) || '/' ddl
from dba_users;