Re: Dbms_metadata experts?
Date: Thu, 19 Mar 2015 19:10:11 -0500
Message-ID: <CAJvnOJYX8WbU9HqWO=DPpgxKtMiCYqAWjYsbBCRfA315Oojuog_at_mail.gmail.com>
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 Fri Mar 20 2015 - 01:10:11 CET