Recreate objects using dbms metadata.get ddl

From dbawiki
Revision as of 20:06, 8 December 2011 by 127.0.0.1 (talk)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

Recreate all the users (including grants) in a database

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;