Re: Dbms_metadata experts?
Date: Wed, 08 Apr 2015 19:12:20 +0000
Message-ID: <CAGd4=DTN9qsNZdB9FRTb9i+tMSHSm3R5dUnA46Ubh+PpQhNmSA_at_mail.gmail.com>
Thanks for the inspiration Andrew. I wound up writing my own version as a package. I have saved it on github if anyone is interested : https://github.com/steveharville/move_tables
On Thu, Mar 19, 2015 at 8:10 PM Andrew Kerber <andrew.kerber_at_gmail.com> wrote:
> 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_at_gmail.com>
> 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_at_gmail.com> 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_at_lfg.com> 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_at_freelists.org [mailto:
>>> oracle-l-bounce_at_freelists.org] *On Behalf Of *Andrew Kerber
>>> *Sent:* Wednesday, March 18, 2015 2:38 PM
>>> *To:* Steve Harville
>>> *Cc:* Stefan Knecht; <oracle-l_at_freelists.org>
>>> *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.'
>
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Apr 08 2015 - 21:12:20 CEST