Oracle DDL 문장추출 여러가지

EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',TRUE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);
set longchunksize 20000 pagesize 0 feedback off verify off trimspool on LONG 20000
select 'select dbms_metadata.get_ddl(''TABLE'',''' || OBJECT_name||''',''' || owner||''') from dual;'  from dba_OBJECTS where object_type='TABLE' and owner not in ('SYS','SYSTEM');
select 'select dbms_metadata.get_ddl(''INDEX'',''' || OBJECT_name||''',''' || owner||''') from dual;'  from dba_OBJECTS where object_type='INDEX' and owner not in ('SYS','SYSTEM');
select 'select dbms_metadata.get_ddl(''PROCEDURE'',''' || OBJECT_name||''',''' || owner||''') from dual;'  from dba_OBJECTS where object_type='PROCEDURE' and owner not in ('SYS','SYSTEM');
select 'select dbms_metadata.get_ddl(''FUNCTION'',''' || OBJECT_name||''',''' || owner||''') from dual;'  from dba_OBJECTS where object_type='FUNCTION' and owner not in ('SYS','SYSTEM');
select 'select dbms_metadata.get_ddl(''TRIGGER'',''' || OBJECT_name||''',''' || owner||''') from dual;'  from dba_OBJECTS where object_type='TRIGGER' and owner not in ('SYS','SYSTEM');
select 'select dbms_metadata.get_ddl(''VIEW'',''' || OBJECT_name||''',''' || owner||''') from dual;'  from dba_OBJECTS where object_type='VIEW' and owner not in ('SYS','SYSTEM');

owner not in ('SYS','SYSTEM','ORDSYS','DBSNMP','OEM_WWW','PERFSTAT','WMSYS','QS_OS','QS_ES','QS','QS_WS','QS_ADM','SH','PM','OE','HR','MDSYS','XDB','QS_CB','QS_CS','QS_CBADM','WKSYS','WKPROXY','ODM','ODM_MTR','OLAPSYS','OUTLN','ORDPLUGINS','ANONYMOUS','CTXSYS','APPQOSSYS','AUDSYS','DBSFWUSER','DIP','DVF','DVSYS','GGSYS','GSMADMIN_INTERNAL','GSMADMIN','GSMCATUSER','GSMUSER','LBACSYS','MDDATA','MDDATA','OJVMSYS','ORACLE_OCM','ORDDATA','PDBUSER','REMOTE_SCHEDULER_AGENT','SI_INFORMTN_SCHEMA','SPATIAL_CSW_ADMIN_USR','SYS$UMF','SYSBACKUP','SYSBACKUP','SYSDG','SYSKM','SYSRAC','XS$NULL')
========================================================================
set longchunksize 20000 pagesize 0 feedback off verify off trimspool on
column Extracted_DDL format a1000

EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',TRUE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);
spool createUser.sql;
SELECT DBMS_METADATA.GET_DDL('USER','SYSTEM') FROM dual
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', 'SYSTEM') FROM dual
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', 'SYSTEM') FROM dual
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'SYSTEM') FROM dual;
spool off;

=======================================================================
User 권한 및 role 생성 DDL 추출하기
해당 유저의 이름은 대문자로 입력

set longchunksize 20000 pagesize 0 feedback off verify off trimspool on
column Extracted_DDL format a1000

EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',TRUE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);

undefine User_in_Uppercase;

set linesize 1000
set long 2000000000
select (case
when ((select count(*)
from dba_users
where username = '&&User_in_Uppercase' and profile <> 'DEFAULT') > 0)
then chr(10)||' -- Note: Profile'||(select dbms_metadata.get_ddl('PROFILE', u.profile) AS ddl from dba_users u where u.username = '&User_in_Uppercase')
else to_clob (chr(10)||' -- Note: Default profile, no need to create!')
end ) from dual
UNION ALL
select (case
when ((select count(*)
from dba_users
where username = '&User_in_Uppercase') > 0)
then ' -- Note: Create user statement'||dbms_metadata.get_ddl ('USER', '&User_in_Uppercase')
else to_clob (chr(10)||' -- Note: User not found!')
end ) Extracted_DDL from dual
UNION ALL
select (case
when ((select count(*)
from dba_ts_quotas
where username = '&User_in_Uppercase') > 0)
then ' -- Note: TBS quota'||dbms_metadata.get_granted_ddl( 'TABLESPACE_QUOTA', '&User_in_Uppercase')
else to_clob (chr(10)||' -- Note: No TS Quotas found!')
end ) from dual
UNION ALL
select (case
when ((select count(*)
from dba_role_privs
where grantee = '&User_in_Uppercase') > 0)
then ' -- Note: Roles'||dbms_metadata.get_granted_ddl ('ROLE_GRANT', '&User_in_Uppercase')
else to_clob (chr(10)||' -- Note: No granted Roles found!')
end ) from dual
UNION ALL
select (case
when ((select count(*)
from V$PWFILE_USERS
where username = '&User_in_Uppercase' and SYSDBA='TRUE') > 0)
then ' -- Note: sysdba'||chr(10)||to_clob (' GRANT SYSDBA TO '||'"'||'&User_in_Uppercase'||'"'||';')
else to_clob (chr(10)||' -- Note: No sysdba administrative Privilege found!')
end ) from dual
UNION ALL
select (case
when ((select count(*)
from dba_sys_privs
where grantee = '&User_in_Uppercase') > 0)
then ' -- Note: System Privileges'||dbms_metadata.get_granted_ddl ('SYSTEM_GRANT', '&User_in_Uppercase')
else to_clob (chr(10)||' -- Note: No System Privileges found!')
end ) from dual
UNION ALL
select (case
when ((select count(*)
from dba_tab_privs
where grantee = '&User_in_Uppercase') > 0)
then ' -- Note: Object Privileges'||dbms_metadata.get_granted_ddl ('OBJECT_GRANT', '&User_in_Uppercase')
else to_clob (chr(10)||' -- Note: No Object Privileges found!')
end ) from dual
/
========================================================================
set long 20000
set longchunksize 20000
set pagesize 0
set linesize 1000
set trimspool on
set column ddl format a1000
set feedback off
set verify off
--Add a semicolon at the end of each statement
execute dbms_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);
--Generate the DDL for User you enter
select dbms_metadata.get_ddl('USER', u.username) AS ddl
from dba_users u
where u.username = '&&v_username'
union all
select dbms_metadata.get_granted_ddl('TABLESPACE_QUOTA', tq.username) AS ddl
from dba_ts_quotas tq
where tq.username = '&&v_username' and rownum = 1
union all
select dbms_metadata.get_granted_ddl('ROLE_GRANT', rp.grantee) AS ddl
from dba_role_privs rp
where rp.grantee = '&&v_username'
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', sp.grantee) AS ddl
from dba_sys_privs sp
where sp.grantee = '&&v_username'
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('OBJECT_GRANT', tp.grantee) AS ddl
from dba_tab_privs tp
where tp.grantee = '&&v_username'
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('DEFAULT_ROLE', rp.grantee) AS ddl
from dba_role_privs rp
where rp.grantee = '&&v_username'
and rp.default_role = 'YES'
and rownum = 1
union all
select to_clob('/* Start profile creation script in case they are missing') AS ddl
from dba_users u
where u.username = '&&v_username'
and u.profile='DEFAULT'
and rownum = 1
union all
select dbms_metadata.get_ddl('PROFILE', u.profile) AS ddl
from dba_users u
where u.username = '&&v_username'
and u.profile='DEFAULT'
union all
select to_clob('End profile creation script */') AS ddl
from dba_users u
where u.username = '&&v_username'
and u.profile='DEFAULT'
and rownum = 1
/

===============================================
set heading off
set echo off
set flush off
set pagesize 0
set linesize 9999
set long 200000

select dbms_metadata.get_ddl('TABLE','<Table_Name_1>', '<Owner_Schema>') from dual ;

- 사용가능 object type
TABLE, INDEX, VIEW, TABLESPACE, MATERIALIZED_VIEW, MATERIALIZED_VIEW_LOG, DB_LINK (note the underscore instead of space),
DIRECTORY, SYNONYM, TRIGGER.

===============================================================
set long 20000 longchunksize 20000 pagesize 0 linesize 1000 feedback off verify off trimspool on
column ddl format a1000

begin
   dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SQLTERMINATOR', true);
   dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'PRETTY', true);
end;
/
 
variable v_username VARCHAR2(30);

exec:v_username := upper('&1');

select dbms_metadata.get_ddl('USER', u.username) AS ddl
from   dba_users u
where  u.username = :v_username
union all
select dbms_metadata.get_granted_ddl('TABLESPACE_QUOTA', tq.username) AS ddl
from   dba_ts_quotas tq
where  tq.username = :v_username
and    rownum = 1
union all
select dbms_metadata.get_granted_ddl('ROLE_GRANT', rp.grantee) AS ddl
from   dba_role_privs rp
where  rp.grantee = :v_username
and    rownum = 1
union all
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', sp.grantee) AS ddl
from   dba_sys_privs sp
where  sp.grantee = :v_username
and    rownum = 1
union all
select dbms_metadata.get_granted_ddl('OBJECT_GRANT', tp.grantee) AS ddl
from   dba_tab_privs tp
where  tp.grantee = :v_username
and    rownum = 1
union all
select dbms_metadata.get_granted_ddl('DEFAULT_ROLE', rp.grantee) AS ddl
from   dba_role_privs rp
where  rp.grantee = :v_username
and    rp.default_role = 'YES'
and    rownum = 1
union all
select to_clob('/* Start profile creation script in case they are missing') AS ddl
from   dba_users u
where  u.username = :v_username
and    u.profile <> 'DEFAULT'
and    rownum = 1
union all
select dbms_metadata.get_ddl('PROFILE', u.profile) AS ddl
from   dba_users u
where  u.username = :v_username
and    u.profile <> 'DEFAULT'
union all
select to_clob('End profile creation script */') AS ddl
from   dba_users u
where  u.username = :v_username
and    u.profile <> 'DEFAULT'
and    rownum = 1
/

set linesize 80 pagesize 14 feedback on trimspool on verify on
=============================================

-- create ddl
set long 100000
select dbms_metadata.get_ddl(d.object_type, d.object_name, d.owner) as ddl
from dba_objects d
where object_name = upper('&__OBJ_NAME')
      and owner = upper('&__SCH_NAME');

-- create ddl for indexes for table
select dbms_metadata.get_ddl(d.object_type, d.object_name, d.owner) as ddl
from dba_objects d
where '&object_type' = 'TABLE'
            and owner = upper('&__SCH_NAME')
            and object_name in (select index_name from dba_indexes
                            where owner = d.owner and table_name = upper('&__OBJ_NAME'));

set echo on
set verify on
==============================================================
- Create role
set verify off 
set feedback off 
set termout off 
set pagesize 0 
  
set termout on 
select 'Creating role build script...' from dual; 
set termout off 
  
spool tfscsrol.sql 
  
select 'CREATE ROLE ' || lower(role) || ' NOT IDENTIFIED;' 
  from sys.dba_roles 
  where role not in ('CONNECT','RESOURCE','DBA', 'EXP_FULL_DATABASE',  
                    'IMP_FULL_DATABASE') 
  and password_required='NO' 
/ 
select 'CREATE ROLE ' || lower(role) || ' IDENTIFIED BY VALUES ' || 
       '''' || password || '''' || ';' 
  from sys.dba_roles, sys.user$ 
  where role not in ('CONNECT','RESOURCE','DBA', 'EXP_FULL_DATABASE', 
                    'IMP_FULL_DATABASE') 
  and password_required='YES' and 
 dba_roles.role=user$.name 
     and user$.type#=0 
/ 
  
select 'GRANT ' || lower(granted_role) || ' TO ' || lower(grantee) || 
       decode(admin_option,'YES',' WITH ADMIN OPTION;',';') 
  from sys.dba_role_privs 
  where admin_option='YES' 
  and granted_role not in ('CONNECT','RESOURCE','DBA', 'EXP_FULL_DATABASE', 
                           'IMP_FULL_DATABASE') 
  order by grantee 
/ 
spool off 
============================================================
- Ojbect 권한 추출
set verify off 
set feedback off 
set termout off 
set pagesize 500 
set heading off 
set recsep off 
  
set termout on 
select 'Creating object grant script by user...' from dual; 
set termout off 
  
create table g_temp (seq NUMBER, grantor_owner varchar2(20), 
                    text VARCHAR2(800)); 
  
DECLARE 
   cursor grant_cursor is  
    SELECT ur$.name, uo$.name, o$.name, ue$.name, 
              m$.name, t$.sequence#,  
              decode(NVL(t$.option$,0), 1, ' WITH GRANT OPTION;',';') 
     FROM sys.objauth$ t$, sys.obj$ o$, sys.user$ ur$, 
            sys.table_privilege_map m$, sys.user$ ue$, sys.user$ uo$ 
       WHERE o$.obj# = t$.obj# AND t$.privilege# = m$.privilege AND 
             t$.col# IS NULL AND t$.grantor# = ur$.user# AND 
             t$.grantee# = ue$.user# and  
             o$.owner#=uo$.user# and  
             t$.grantor# != 0 
       order by sequence#; 
   lv_grantor    sys.user$.name%TYPE; 
   lv_owner      sys.user$.name%TYPE; 
   lv_table_name sys.obj$.name%TYPE; 
   lv_grantee    sys.user$.name%TYPE; 
   lv_privilege  sys.table_privilege_map.name%TYPE; 
   lv_sequence   sys.objauth$.sequence#%TYPE; 
   lv_option     VARCHAR2(30); 
   lv_string     VARCHAR2(800); 
   lv_first      BOOLEAN; 
  
   procedure write_out(p_seq INTEGER, p_owner VARCHAR2, p_string VARCHAR2) is 
   begin 
      insert into g_temp (seq, grantor_owner,text) 
 values (lv_sequence, lv_grantor, lv_string); 
   end; 
  
BEGIN 
  OPEN grant_cursor; 
    LOOP 
      FETCH grant_cursor INTO lv_grantor,lv_owner,lv_table_name,lv_grantee, 
         lv_privilege,lv_sequence,lv_option; 
      EXIT WHEN grant_cursor%NOTFOUND; 
      lv_string := 'GRANT ' || lv_privilege || ' ON ' || lower(lv_owner) || 
                   '.' || 
                   lower(lv_table_name) || ' TO ' || lower(lv_grantee) || 
                   lv_option; 
      write_out(lv_sequence, lv_grantor,lv_string); 
    END LOOP; 
  CLOSE grant_cursor; 
END; 
/ 
  
spool tfscsopv.lst 
break on guser skip 1 
col text format a60 word_wrap 
  
select   'connect ' || grantor_owner || '/' guser, text 
from     g_temp 
order by seq, grantor_owner 
/

 

spool off    
drop table g_temp; 
========================================
- system 권한 추출

set verify off
set feedback off
set termout off
set echo off
set pagesize 0
set head off

set termout on
prompt Creating system privilege grant script...
set termout off

spool grant_sys_privs.sql

select 'GRANT ' || rpad(lower(privilege),30) || ' TO ' || lower(grantee) ||
       decode(admin_option,'YES',' WITH ADMIN OPTION;',';')
  from sys.dba_sys_privs
  where grantee not in ('CONNECT','RESOURCE','DBA',
                       'EXP_FULL_DATABASE','IMP_FULL_DATABASE')
order by grantee
/
spool off