Re: Dbms_metadata experts?

From: Chris Taylor <christopherdtaylor1994_at_gmail.com>
Date: Wed, 18 Mar 2015 15:30:49 -0500
Message-ID: <CAP79kiR__LBDgx1pLoHBsTnz08r5-McGHuL0rNVn3jR6SUFOrg_at_mail.gmail.com>

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.**
>

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Mar 18 2015 - 21:30:49 CET