Re: Dbms_metadata experts?
- From: Steve Harville <steve.harville@xxxxxxxxx>
- To: Andrew Kerber <andrew.kerber@xxxxxxxxx>, Chris Taylor <christopherdtaylor1994@xxxxxxxxx>
- Date: Thu, 19 Mar 2015 16:00:21 +0000
Wow - this is exactly what I need. Thanks a lot!
On Thu, Mar 19, 2015 at 9:42 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.**
>>
>
>
Other related posts:
