This forum is currently locked. You can't register or post questions at this time. (read more)

how to generate ddl for different objects in different files

All posts relating to Oracle database administration.

Moderator: Tim...

kytemaniac
Senior Member
Posts: 234
Joined: Tue May 19, 2009 12:59 am

how to generate ddl for different objects in different files

Postby kytemaniac » Thu Feb 21, 2013 12:28 pm

HI,

how to generate ddl for different objects in different files with the additional requirements:

the constraints is to be generated in different files
package specifications and package body must be different files


here db version 11.2.0.2

here's what I have done

Code: Select all

--set long 9999
set long 20000

set heading off
set echo off
set feedback off

spool D:\william\excel\project\alpha_bank\abkdb3\dbms_metadata\get_tables_output_false_segment_attributes_false_constraints.log

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',false);    
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'CONSTRAINTS',false);    

SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)
     FROM USER_ALL_TABLES u
     WHERE u.nested='NO'
     AND (u.iot_type is null or u.iot_type='IOT');
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'DEFAULT');

spool off



part of output

Code: Select all

  CREATE TABLE "SUPERB"."ACTIVATED_FILTER"                                   
   (   "AFF_ID" NUMBER(8,0),                                                       
   "FADD_ID" NUMBER(8,0),                                                         
   "CF_ID" NUMBER(8,0),                                                           
   "ACTIVATE_STATUS" NUMBER,                                                     
   "LAST_UPDATE_BY" NUMBER(8,0),                                                 
   "LAST_UPDATE_DATE" DATE,                                                       
    CONSTRAINT "FK_ACTFILTER_CF_ID" FOREIGN KEY ("CF_ID")                         
     REFERENCES "SUPERB"."CARD_FILTER" ("CF_ID") ENABLE,                       
    CONSTRAINT "FK_ACTFILTER_FAD_ID" FOREIGN KEY ("FAD_ID")                       
     REFERENCES "SUPERB"."FILTER_ACTIVATION_DETAIL" ("FAD_ID") ON DELETE CASCAD
E ENABLE                                                                       

   )                                                                           




now if you notice this I have already disable the constraints , still output is showing constraints, I don't understand why?

as for the constraints part I have done a impdp with sqlfile option

Code: Select all

SCHEMAS=SUPERB
CONTENT=METADATA_ONLY
DUMPFILE=EXP_SG_PROD_SUPERB_05FEB13.DMP
LOGFILE=impdp_EXP_SG_PROD_SUPERB_METADATA_SQLFILE_20130220_1013.log
SQLFILE=create_ddl_20130220.sql
DIRECTORY=BKDB1_DUMP



partial output for constraints in SQLFILE=create_ddl_20130220.sql , is as follow

Code: Select all

ALTER TABLE "SUPERB"."ACTIVATED_FILTER" ADD CONSTRAINT "FK_ACTFILTER_FAD_ID" FOREIGN KEY ("FAD_ID")
     REFERENCES "SUPERB"."FILTER_ACTIVATION_DETAIL" ("FAD_ID") ON DELETE CASCADE ENABLE;


now how do I do the following:

place each constraints in a different files,
how do remove the double quotes
how do remove the schema name?

thanks a lot for the assistance rendered?

thanks

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: how to generate ddl for different objects in different f

Postby Tim... » Thu Feb 21, 2013 6:31 pm

Hi.

Regarding the constraints, you need to explicitly stop REF_CONSTRAINTS.

Code: Select all

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'REF_CONSTRAINTS',false);   


Regarding the splitting of things into different files, you can't really do thins with SPOOL. You will probably have to write a PL/SQL procedure to write the information our to files using UTL_FILE.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

kytemaniac
Senior Member
Posts: 234
Joined: Tue May 19, 2009 12:59 am

Re: how to generate ddl for different objects in different f

Postby kytemaniac » Fri Feb 22, 2013 12:39 am

Code: Select all


EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',false);    
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'CONSTRAINTS',false);    
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'REF_CONSTRAINTS',false);

SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)
     FROM USER_ALL_TABLES u
     WHERE u.nested='NO'
     AND (u.iot_type is null or u.iot_type='IOT');
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'DEFAULT');


output

Code: Select all

CREATE TABLE "SUPERB"."ME_BUCKET"                                         
   (   "ME_BUCKET_IID" NUMBER(10,0),                                             
   "STATUS" CHAR(1 CHAR),                                                         
   "BUCKET_UPD_CNT" NUMBER(10,0) DEFAULT 0,                                       
    CONSTRAINT "PK_MD_BUCKET" PRIMARY KEY ("ME_BUCKET_IID") ENABLE               
   ) ORGANIZATION INDEX NOCOMPRESS  OVERFLOW


if you notice the output, it does generate the primary key, I don't understand why. from http://docs.oracle.com/cd/E11882_01/app ... m#BGBJBFGE, I probably already explore all the options already.

can some one tell me how to not to display the primary key constraint? thanks

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: how to generate ddl for different objects in different f

Postby Tim... » Fri Feb 22, 2013 8:32 am

Hi.

If you read the page you linked to you would see the notes for CONSTRAINT say, "Does not include: primary key constraint for IOT".

Think about this for a second. How could you possibly have an index organized table IOT without a primary key. The table data is part of the index keys. The whole table is the primary key.

If you did your query against a regular table it would work as you expect. :)

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle Database Administration”

Who is online

Users browsing this forum: No registered users and 5 guests

cron