Yet Another Extract Oracle DDL Script

For posterity:

set echo off
set heading off
set feedback off
set long 1999999999
set linesize 32767
set longchunksize 10000
set serveroutput on size unlimited format word_wrapped
set pagesize 0
set verify off
set trimspool on

col owner format a30
col segment_name format a30
col segment_type format a30
col tablespace_name format a30

prompt This script extracts DDL for the given schema 
prompt Usage:  "md.sql "  or "md.sql .", 
prompt   e.g.  "md.sql scott or md.sql scott.departments"
prompt 
prompt Parameters: 1) schema_name.[object_name]
define sch_obj_name = &1
prompt 	              schema_name = &sch_obj_name
prompt   
prompt Output: a sql script named as "..sql", e.g. "scott.dev1.sql"
prompt 

variable v_schema_name varchar2(60)
variable v_object_name varchar2(60)

exec :v_schema_name  := upper('&&sch_obj_name')

declare 
  i int;
begin 
  i := instr(:v_schema_name, '.');
  if i > 1 then 
    :v_object_name := substr(:v_schema_name, i + 1);
    :v_schema_name := substr(:v_schema_name, 1, i - 1);
  end if;
end;
/

prompt Schema / object name:
prompt 
print v_schema_name
print v_object_name

rem Switch end of line between Windows (#13#10) and Unix (#13).
rem define eol = chr(13)
define eol = chr(13)||chr(10)

prompt Extracting: &&sch_obj_name@&_CONNECT_IDENTIFIER into  &&sch_obj_name..&_CONNECT_IDENTIFIER..sql

spool &&sch_obj_name..&_CONNECT_IDENTIFIER..sql

rem --------------------------------------------------;
rem objects ddl 
rem --------------------------------------------------;

begin 
  -- set metadata
  dbms_metadata.set_transform_param(dbms_metadata.session_transform,'PRETTY',true);
  dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',true);
  dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES',false);
  dbms_metadata.set_transform_param(dbms_metadata.session_transform,'STORAGE',false);
  dbms_metadata.set_transform_param(dbms_metadata.session_transform,'TABLESPACE',true);
  dbms_metadata.set_transform_param(dbms_metadata.session_transform,'BODY',true);
  dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SPECIFICATION',true);
  dbms_metadata.set_transform_param(dbms_metadata.session_transform,'BODY',false);
  dbms_metadata.set_transform_param(dbms_metadata.session_transform,'CONSTRAINTS',false);
  dbms_metadata.set_transform_param(dbms_metadata.session_transform,'REF_CONSTRAINTS',false);
  dbms_metadata.set_transform_param(dbms_metadata.session_transform,'CONSTRAINTS_AS_ALTER',false);
end;
/
-- select objects   
select  '/* owner=' || oo.owner || ',object_name='|| case when oo.object_type = 'INDEX' then ii.table_name else oo.object_name end ||',object_type='||oo.object_type||',status='||oo.status|| ' */' || &&eol || 
    regexp_replace(regexp_replace(dbms_metadata.get_ddl (replace(oo.object_type, ' ', '_'), oo.object_name, oo.owner), '\s+;', ';', 1, 0), ' /$',  chr(10) || '/' || chr(10), 1, 1, 'm')
  from dba_objects oo 
  left outer join 
  dba_indexes ii on ii.index_name = oo.object_name and ii.owner = oo.owner 
  where oo.owner = :v_schema_name and 
    object_name = nvl(:v_object_name, object_name) and
    oo.object_type not in ('RULE', 'LOB', 'QUEUE', 'EVALUATION CONTEXT', 'TABLE PARTITION', 'RULE SET', 'JAVA CLASS', 'TABLE SUBPARTITION', 'INDEX PARTITION', 'INDEX SUBPARTITION', 'PROGRAM', 'SCHEDULE', 'JOB', 'DATABASE LINK') and
    oo.object_name not like 'SYS_IOT_OVER%' and
    oo.object_name not like 'BIN$%' and
    oo.subobject_name is null 
  order by oo.object_name, oo.object_type;
  
-- constraints, ordered  
select '/* owner=' || owner || ',object_name=' || table_name || ',object_type=CONSTRAINT,status=' || status || ' */ ' || &&eol || 
    dbms_metadata.get_ddl(case constraint_type when 'R' then 'REF_CONSTRAINT' else 'CONSTRAINT' end , constraint_name, owner)
  from dba_constraints 
  where owner = :v_schema_name and 
    table_name = nvl(:v_object_name, table_name) and
    constraint_type in ('C', 'P', 'U', 'R') and
    constraint_name not like 'BIN$%'
  order by owner, table_name, constraint_name;


rem --------------------------------------------------;
rem user grants
rem --------------------------------------------------;
  
select '/* owner=' || owner || ',object_name=' || table_name || ',object_type=USER_GRANT,status=VALID */ ' || &&eol ||
    'grant ' || privilege  || ' on ' || owner || '.' || table_name || ' to ' || grantee ||  case grantable when 'YES' then  ' with grant option ' else  null end || ';'
  from (
    select unique owner, table_name, privilege, grantee, grantable 
      from dba_tab_privs  
      where grantor = :v_schema_name and
        table_name = nvl(:v_object_name, table_name) 
      order by 1,2,3,4,5);

rem --------------------------------------------------;
rem role grants
rem --------------------------------------------------;
  
select '/* owner=SYSTEM,object_name=ROLES,object_type=ROLE_GRANT,status=VALID */ ' || &&eol ||
   'grant ' || granted_role || ' to ' ||  grantee  ||  case admin_option when 'YES' then ' with admin option' else null end || ';'  
  from (
    select grantee, granted_role, admin_option 
      from dba_role_privs  
      where grantee = :v_schema_name and
        grantee = nvl(:v_object_name, grantee) 
      order by 1,2,3);  

rem --------------------------------------------------;
rem comments
rem --------------------------------------------------;
  
select  '/* owner=' || owner || ',object_name=' || table_name || ',object_type=COMMENT,status=VALID */ ' || &&eol ||
    'comment on table "' || owner || '".' || table_name  || ' is  ''' || comments || ''';'
  from dba_tab_comments  
    where owner = :v_schema_name and
      table_name = nvl(:v_object_name, table_name) 
    order by owner, table_name;

select  '/* owner=' || owner || ',object_name=' || mview_name || ',object_type=COMMENT,status=VALID */ ' || &&eol ||
    'comment on materialized view "' || owner || '".' || mview_name  || ' is  ''' || comments || ''';'
  from dba_mview_comments  
    where owner = :v_schema_name and
      mview_name = nvl(:v_object_name, mview_name) 
    order by owner, mview_name;

select  '/* owner=' || owner || ',object_name=' || table_name || ',object_type=COMMENT,status=VALID */ ' || &&eol ||
    'comment on column "' || owner || '".' || table_name  || '.' || column_name || ' is  ''' || comments || ''';'
  from dba_col_comments  
    where owner = :v_schema_name and
      table_name = nvl(:v_object_name, table_name) 
  order by owner, table_name, column_name;



rem --------------------------------------------------;
rem tablespaces
rem --------------------------------------------------;

select '/* owner=' || :v_schema_name || ',object_name=SEGMENTS,object_type=SEGMENT,status=VALID */' from dual;
select distinct owner, segment_name, segment_type, tablespace_name 
  from dba_segments 
  where owner = :v_schema_name and
    segment_name = nvl(:v_object_name, segment_name) 
  order by owner, segment_name, tablespace_name;

rem --------------------------------------------------;
rem extract end
rem --------------------------------------------------;

spool off

prompt Extracted: &&sch_obj_name@&_CONNECT_IDENTIFIER into  &&sch_obj_name..&_CONNECT_IDENTIFIER..sql

select max('main_object_type=' || replace(object_type, ' ', '_'))
  from dba_objects
  where owner = :v_schema_name and 
    object_name = nvl(:v_object_name, object_name);

quit

About these ads