So, here is the next iteration. I admit I get a little obsessive on these things. I will probably make this the topic of my next blog. I added logic to move indexes to a new tablespace prior to the table redefinition. There is probably a way to do that using the register command, but I could not track it down. The current piece I am working on is to figure out the best way to deal with Referential integrity constraints. procedure gen_ddl(in_owner in varchar2, in_table_name in varchar2, int_table_name in varchar2 :=null, tablespace_name in varchar2 :=null, ind_tablespace_name in varchar2:=null, new_tablespace in varchar2 :=null, new_ind_tablespace in varchar2 :=null, runcrt boolean :=false, runredef boolean :=false, drop_interim_table boolean :=false) as h number; th number; h2 number; th2 number; cmd_string varchar2(2000); ddl_text varchar2(32000); orig_string varchar2(500); new_string varchar2(500); int_tabname varchar2(30); int_tabcounter number :=0; start_redef varchar2(32000); copy_dependents varchar2(500); finish_redef varchar2(500); abort_redef varchar2(500); redef_errors number(5):=0; opts_flag varchar2(200); nopts_flag number; ind_defined number :=0; success boolean :=false; dropcmd varchar2(2000); dropunused varchar2(2000); col_map_string varchar2(32000); begin dbms_output.enable(1000000); if new_ind_tablespace is not null then for ind_cursor in (select owner, index_name from all_indexes where table_name=in_table_name and owner=in_owner) loop move_indexes(in_owner=>ind_cursor.owner,in_index_name=>ind_cursor.index_name,in_tablespace_name=>new_ind_tablespace,runit=>runcrt); end loop; end if; -- dbms_output.put_line(rtrim(owner)||'.'||rtrim(table_name)); h := dbms_metadata.open('TABLE'); dbms_metadata.set_filter(h,'SCHEMA',in_owner); dbms_metadata.set_filter(h,'NAME',in_table_name); th := dbms_metadata.add_transform(h,'MODIFY'); if new_tablespace is not null then dbms_metadata.set_remap_param(th,'REMAP_TABLESPACE',tablespace_name,new_tablespace); end if; if new_ind_tablespace is not null then dbms_metadata.set_remap_param(th,'REMAP_TABLESPACE',ind_tablespace_name,new_ind_tablespace); end if; if int_table_name is null then int_tabname:=in_table_name||'_INT'; else int_tabname:=int_table_name; end if; map_cols(in_owner=>in_owner,in_table_name=>in_table_name,col_map_string=>col_map_string); th := dbms_metadata.add_transform(h,'DDL'); dbms_metadata.set_transform_param(th,'STORAGE',false); dbms_metadata.set_transform_param(th,'CONSTRAINTS',false); dbms_metadata.set_transform_param(th,'REF_CONSTRAINTS',false); if runcrt = true then dbms_metadata.set_transform_param(th,'PRETTY',false); dbms_metadata.set_transform_param(th,'SQLTERMINATOR',false); else dbms_metadata.set_transform_param(th,'PRETTY',true); dbms_metadata.set_transform_param(th,'SQLTERMINATOR',true); end if; orig_string:='CREATE TABLE "'||rtrim(in_owner)||'"'||'.'||'"'||rtrim(in_table_name)||'"'; new_string:='create table "'||in_owner||'"."'||int_tabname||'"'; select replace(replace(dbms_metadata.fetch_clob(h),orig_string,new_string),' CHAR','VARCHAR2') into ddl_text from dual; opts_flag:='dbms_redefinition.cons_use_rowid'; nopts_flag:=dbms_redefinition.cons_use_rowid; dbms_output.put_line(ddl_text); start_redef:='dbms_redefinition.start_redef_table(uname=>'||''''||in_owner||''''||',orig_table=>'||''''||in_table_name||''''||',int_table=>'||''''||int_tabname||''''||',options_flag=>'||opts_flag||',col_mapping=>'||''''||col_map_string||''''||')'; copy_dependents:='dbms_redefinition.copy_table_dependents(uname=>'||''''||in_owner||''''||',orig_table=>'||''''||in_table_name||''''||',int_table=>'||''''||int_tabname||''''||',num_errors=>redef_errors)'; finish_redef:='dbms_redefinition.finish_redef_table(uname=>'||''''||in_owner||''''||',orig_table=>'||''''||in_table_name||''''||',int_table=>'||''''||int_tabname||''''||')'; abort_redef:='dbms_redefinition.abort_redef_table(uname=>'||''''||in_owner||''''||',orig_table=>'||''''||in_table_name||''''||',int_table=>'||''''||int_tabname||''''||')'; dbms_output.put_line(start_redef||';'); dbms_output.put_line(copy_dependents||';'); dbms_output.put_line(finish_redef||';'); dbms_output.put_line(abort_redef||';'); -- dbms_output.put_line('col_map_string::'||col_map_string); dropcmd:='drop table '||in_owner||'.'||int_tabname; dropunused:='alter table '||in_owner||'.'||in_table_name||' drop unused columns'; dbms_output.put_line(dropcmd||';'); dbms_output.put_line(dropunused||';'); if runcrt=true then dbms_output.put_line('Start redef - ddl text'); execute immediate ddl_text; -- dbms_output.put_line('DDL TEXTED'); if runredef=true then begin dbms_output.put_line(start_redef); dbms_redefinition.start_redef_table(uname=>in_owner,orig_table=>in_table_name,int_table=>int_tabname,options_flag=>nopts_flag,col_mapping=>col_map_string); dbms_output.put_line('Finished start'); dbms_output.put_line(copy_dependents); dbms_redefinition.copy_table_dependents(uname=>in_owner,orig_table=>in_table_name,int_table=>int_tabname,num_errors=>redef_errors); dbms_output.put_line('Dependency Errors: '||to_char(redef_errors)); dbms_output.put_line(finish_redef); dbms_redefinition.finish_redef_table(uname=>in_owner,orig_table=>in_table_name,int_table=>int_tabname); success:=true; exception when others then success:=false; dbms_redefinition.abort_redef_table(uname=>in_owner,orig_table=>in_table_name,int_table=>int_tabname); raise; end; if success=true and drop_interim_table=true then execute immediate dropunused; execute immediate dropcmd; end if; end if; end if; end; procedure map_cols(in_owner in varchar2,in_table_name in varchar2,col_map_string in out varchar2) as map_string varchar2(32000):=' '; colcounter integer :=0; colcounter2 integer :=0; begin dbms_output.enable(1000000); for colcursor in (select column_name, data_type from all_tab_columns where owner=in_owner and table_name=in_table_name) loop colcounter:=colcounter+1; end loop; if colcounter > 0 then for colcursor2 in (select column_name, data_type,data_length from all_tab_columns where owner=in_owner and table_name=in_table_name) loop colcounter2:=colcounter2+1; if colcursor2.data_type='CHAR' and colcursor2.data_length > 2 then map_string:=rtrim(map_string)||' rtrim(ltrim('||colcursor2.column_name||')) '||colcursor2.column_name; else map_string:=rtrim(map_string)||colcursor2.column_name||' '||colcursor2.column_name; end if; if colcounter2<colcounter then map_string:=map_string||','; end if; end loop; end if; --dbms_output.put_line(map_string); col_map_string:=rtrim(ltrim(map_string)); end; procedure move_indexes(in_owner in varchar2, in_index_name in varchar2, in_tablespace_name in varchar2,runit in boolean :=false) is move_cmd varchar2(4000); begin dbms_output.enable(1000000); move_cmd:='alter index '||in_owner||'.'||in_index_name||' rebuild online parallel tablespace '||in_tablespace_name; dbms_output.put_line(move_cmd||';'); if runit = true then execute immediate move_cmd; end if; end; On Thu, Mar 19, 2015 at 8:41 AM, Andrew Kerber <andrew.kerber@xxxxxxxxx> wrote: > Well, here is where I am so far. I believe I will have to turn off the > pretty option to get the execute immediate to work. Sorry no comments, I > learned before those were a thing, and never could get used to them. Note > I haven't set up the logic to drop the interim table yet either. Regular > disclaimer, test before you use, misuse could cause issues. > > procedure gen_ddl(owner in varchar2, > > table_name in varchar2, > > int_table_name in varchar2 :=null, > > tablespace_name in varchar2 :=null, > > ind_tablespace_name in varchar2:=null, > > new_tablespace in varchar2 :=null, > > new_ind_tablespace in varchar2 :=null, > > runcrt boolean :=false, > > runredef boolean :=true, > > drop_interim_table boolean :=false) > > as > > h number; > > th number; > > cmd_string varchar2(2000); > > ddl_text varchar2(32000); > > orig_string varchar2(500); > > new_string varchar2(500); > > int_tabname varchar2(30); > > int_tabcounter number :=0; > > start_redef varchar2(500); > > copy_dependents varchar2(500); > > finish_redef varchar2(500); > > redef_errors number(5):=0; > > opts_flag varchar2(200); > > nopts_flag number; > > ind_defined number :=0; > > begin > > dbms_output.enable(1000000); > > dbms_output.put_line(rtrim(owner)||'.'||rtrim(table_name)); > > h := dbms_metadata.open('TABLE'); > > dbms_metadata.set_filter(h,'SCHEMA',owner); > > dbms_metadata.set_filter(h,'NAME',table_name); > > th := dbms_metadata.add_transform(h,'MODIFY'); > > if new_tablespace is not null > > then > > > dbms_metadata.set_remap_param(th,'REMAP_TABLESPACE',tablespace_name,new_tablespace); > > end if; > > if new_ind_tablespace is not null > > then > > > dbms_metadata.set_remap_param(th,'REMAP_TABLESPACE',ind_tablespace_name,new_ind_tablespace); > > end if; > > if int_table_name is null > > then > > int_tabname:=table_name||'_INT'; > > else > > int_tabname:=int_table_name; > > end if; > > th := dbms_metadata.add_transform(h,'DDL'); > > dbms_metadata.set_transform_param(th,'STORAGE',false); > > dbms_metadata.set_transform_param(th,'PRETTY',true); > > dbms_metadata.set_transform_param(th,'SQLTERMINATOR',false); > > dbms_metadata.set_transform_param(th,'CONSTRAINTS',false); > > dbms_metadata.set_transform_param(th,'REF_CONSTRAINTS',false); > > orig_string:='CREATE TABLE > "'||rtrim(owner)||'"'||'.'||'"'||rtrim(table_name)||'"'; > > new_string:='create table "'||owner||'"."'||int_tabname||'"'; > > select > replace(replace(dbms_metadata.fetch_clob(h),orig_string,new_string),'CHAR','VARCHAR2') > > into ddl_text from dual; > > dbms_output.put_line(ddl_text); > > if runcrt=true > > then > > execute immediate ddl_text; > > if runredef=true > > then > > > > start_redef:='dbms_redefinition.start_redef_table(uname=>'||owner||',orig_table=>'||table_name||',int_table=>'||int_tabname||',options_flag=>'||opts_flag||')'; > > > copy_dependents:='dbms_redefinition.copy_table_dependents(uname=>'||owner||',orig_table=>'||table_name||',int_table=>'||int_tabname||',num_errors=>redef_errors)'; > > > finish_redef:='dbms_redefinition.finish_redef_table(uname=>'||owner||',orig_table=>'||table_name||',int_table=>'||int_tabname||')'; > > begin > > dbms_output.put_line(start_redef); > > > > dbms_redefinition.start_redef_table(uname=>owner,orig_table=>table_name,int_table=>int_tabname,options_flag=>nopts_flag); > > dbms_output.put_line(copy_dependents); > > > dbms_redefinition.copy_table_dependents(uname=>owner,orig_table=>table_name,int_table=>int_tabname,num_errors=>redef_errors); > > dbms_output.put_line('Dependency Errors: '||to_char(redef_errors)); > > dbms_output.put_line(finish_redef); > > > dbms_redefinition.finish_redef_table(uname=>owner,orig_table=>table_name,int_table=>int_tabname); > > exception > > when others then > > > dbms_redefinition.abort_redef_table(uname=>owner,orig_table=>table_name,int_table=>int_tabname); > > raise; > > end; > > end if; > > end if; > > end; > > Sent from my iPad > > On Mar 18, 2015, at 3:30 PM, Chris Taylor < > christopherdtaylor1994@xxxxxxxxx> wrote: > > Here's the one I used: > > Step 1: Get a list of OBJECTs you want metadata for: > > select 'select > dbms_metadata.get_ddl('''||object_type||''','''||object_name||''','''||owner||''') > txt from dual;'--object_type, object_name, owner > from dba_objects > where object_name in > (select object_name from test1) > and object_type not in ('SYNONYM') > and owner not in ('SYS','SYSTEM') > and object_type not like '%PARTITION%' > and owner not like '%..%' -- replace owners with whatever owners you want > to exclude > and owner not like '% .. %' -- replace owners with whatever owners you > want to exclude > order by object_name; > > Step 2: Paste Output from above into the > set pagesize 0 > set long 90000 > set lines 130 > column txt format a130 word_wrapped > set feedback off > --set longchunksize to 250 > set echo off > > spool c:\tmp\extract_ddl.sql > > begin > > DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',true); > > 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,'PRETTY',true); > > dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',TRUE); > > 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); > > dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SPECIFICATION',TRUE); > > dbms_metadata.set_transform_param(dbms_metadata.session_transform,'BODY',TRUE); > end; > / > -- paste output from step 1 here and execute them > select dbms_metadata.get_ddl('TABLE','BS_APPLICATION','BS_ADMIN') txt from > dual; > > spool off > exit > > On Wed, Mar 18, 2015 at 1:56 PM, Deas, Scott <Scott.Deas@xxxxxxx> wrote: > >> Andrew, >> >> >> >> Below SQL will set some of the transform parameters to control your >> output, and then will replace double quotes with NULL, change CHAR to >> VARCHAR (protecting any existing VARCHAR), and will rename the table all at >> once (also pulls index ddl): >> >> >> >> BEGIN >> >> dbms_metadata.set_transform_param (dbms_metadata.session_transform, >> 'DEFAULT'); >> >> >> dbms_metadata.set_transform_param(dbms_metadata.session_transform,'PRETTY',TRUE); >> >> >> dbms_metadata.set_transform_param(dbms_metadata.session_transform,'CONSTRAINTS',FALSE); >> >> >> dbms_metadata.set_transform_param(dbms_metadata.session_transform,'CONSTRAINTS_AS_ALTER',TRUE); >> >> >> dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',TRUE); >> >> >> dbms_metadata.set_transform_param(dbms_metadata.session_transform,'STORAGE',FALSE); >> >> END; >> >> / >> >> >> >> SELECT REPLACE(REPLACE(REPLACE(REPLACE(dbms_metadata.get_ddl('TABLE', >> table_name,owner),'VARCHAR','CHAR'),'CHAR','VARCHAR'),chr(34),NULL),table_name,table_name >> >> ||'_CPY') AS ddl_create >> >> FROM dba_tables >> >> WHERE TABLE_NAME = 'x' >> >> AND OWNER = 'y' >> >> UNION ALL >> >> SELECT REPLACE(REPLACE(REPLACE(dbms_metadata.get_ddl('INDEX', >> INDEX_NAME,OWNER),chr(34),NULL),index_name,index_name||'_CPY'),table_name,table_name||'_CPY') >> AS ddl_indexes >> >> FROM dba_INDEXES >> >> WHERE TABLE_NAME = 'x' and TABLE_OWNER = 'y' >> >> >> >> I’m sure there’s a better way to write this than having table_name and >> table_owner hard-coded twice. >> >> >> >> Thanks, >> >> Scott >> >> >> >> >> >> *From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto: >> oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Andrew Kerber >> *Sent:* Wednesday, March 18, 2015 2:38 PM >> *To:* Steve Harville >> *Cc:* Stefan Knecht; <oracle-l@xxxxxxxxxxxxx> >> *Subject:* Re: Dbms_metadata experts? >> >> >> >> Below is what I have so far. I would like to have a neater way of >> changing char to varchar2, and I would like to change the table names to >> tablename_int. >> >> >> >> select 'select >> replace(dbms_metadata.get_ddl(object_type=>'||''''||'TABLE'||''''||',name=>'||''''||table_name||''''||',schema=>'|| >> >> ''''||owner||''''||')'||'||'||''''||';'||''''|| >> >> ','||''''||'CHAR('||''''||','||''''||'varchar2('||''''||') from dual;' >> >> from dba_tables where owner='SCOTT'; >> >> >> Sent from my iPad >> >> >> >> Notice of Confidentiality: **This E-mail and any of its attachments >> may contain >> Lincoln National Corporation proprietary information, which is >> privileged, confidential, >> or subject to copyright belonging to the Lincoln National Corporation >> family of >> companies. This E-mail is intended solely for the use of the individual >> or entity to >> which it is addressed. If you are not the intended recipient of this >> E-mail, you are >> hereby notified that any dissemination, distribution, copying, or action >> taken in >> relation to the contents of and attachments to this E-mail is strictly >> prohibited >> and may be unlawful. If you have received this E-mail in error, please >> notify the >> sender immediately and permanently delete the original and any copy of >> this E-mail >> and any printout. Thank You.** >> > > -- Andrew W. Kerber 'If at first you dont succeed, dont take up skydiving.'