Re: Dbms_metadata experts?
- From: Andrew Kerber <andrew.kerber@xxxxxxxxx>
- To: Chris Taylor <christopherdtaylor1994@xxxxxxxxx>
- Date: Thu, 19 Mar 2015 08:41:57 -0500
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.**
>>
>
Other related posts:
