Well, Like I said I sometimes obsess over this. So I have written a table
driven set of procedures to redefine a table. Below is a link to my blog
post on the subject, feel free to question and comment.
https://dbakerber.wordpress.com/2015/04/09/using-dbms_redefinition-with-dbms_metadata-to-redefine-a-table/
On Wed, Apr 8, 2015 at 2:12 PM, Steve Harville <steve.harville@xxxxxxxxx>
wrote:
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@xxxxxxxxx>
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@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.'
