dbms_metadata

username = tom

1. user 생성
select dbms_metadata.get_ddl('USER','TOM') from dual;

2. user role
select dbms_metadata.get_granted_ddl('ROLE_GRANT','TOM') from dual;

3. user system grant
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT','TOM') from dual;

4. user object grant
select dbms_metadata.get_granted_ddl('OBJECT_GRANT','TOM') from dual;




owner=system


USER 생성 sql
select 'select dbms_metadata.get_ddl(''USER'','''||username||''') from dual;' from dba_users
where username not in ('SYS','SYSTEM','OUTLN','DIP','TSMSYS','DBSNMP','WMSYS','EXFSYS','DMSYS','CTXSYS',
'ANONYMOUS','XDB','ORDPLUGINS','ORDSYS','MDSYS','SI_INFORMTN_SCHEMA','OLAPSYS','MDDATA','SYSMAN','MGMT_VIEW')
order by username;

select 'select dbms_metadata.get_granted_ddl(''ROLE_GRANT'','''||username||''') from dual;' from dba_users
where username not in ('SYS','SYSTEM','OUTLN','DIP','TSMSYS','DBSNMP','WMSYS','EXFSYS','DMSYS','CTXSYS',
'ANONYMOUS','XDB','ORDPLUGINS','ORDSYS','MDSYS','SI_INFORMTN_SCHEMA','OLAPSYS','MDDATA','SYSMAN','MGMT_VIEW')
order by username;

select 'select dbms_metadata.get_granted_ddl(''SYSTEM_GRANT'','''||username||''') from dual;' from dba_users
where username not in ('SYS','SYSTEM','OUTLN','DIP','TSMSYS','DBSNMP','WMSYS','EXFSYS','DMSYS','CTXSYS',
'ANONYMOUS','XDB','ORDPLUGINS','ORDSYS','MDSYS','SI_INFORMTN_SCHEMA','OLAPSYS','MDDATA','SYSMAN','MGMT_VIEW')
order by username;

select 'select dbms_metadata.get_granted_ddl(''OBJECT_GRANT'','''||username||''') from dual;' from dba_users
where username not in ('SYS','SYSTEM','OUTLN','DIP','TSMSYS','DBSNMP','WMSYS','EXFSYS','DMSYS','CTXSYS',
'ANONYMOUS','XDB','ORDPLUGINS','ORDSYS','MDSYS','SI_INFORMTN_SCHEMA','OLAPSYS','MDDATA','SYSMAN','MGMT_VIEW')
order by username;

TABLESPACE 생성 sql
select 'select dbms_metadata.get_ddl(''TABLESPACE'','''||NAME||''') from dual;' from v$tablespace;

VIEW 생성 sql
select 'select dbms_metadata.get_ddl(''VIEW'','''||VIEW_NAME||''',''SYSTEM'') from dual;' from dba_views where owner='SYSTEM';

PACKAGE 생성 sql
select 'select dbms_metadata.get_ddl(''PACKAGE'','''||object_name||''',''SYSTEM'') from dual;' from dba_objects where object_type='PACKAGE' and owner='SYSTEM';

TABLE 생성 sql
select 'select dbms_metadata.get_ddl(''TABLE'','''||table_name||''',''SYSTEM'') from dual;' from dba_tables where owner='SYSTEM';