how to generate ddl for different objects in different files

All posts relating to Oracle database administration.

Moderator: Tim...

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
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 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
Tim...
Site Admin
 
Posts: 17931
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

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
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 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
Tim...
Site Admin
 
Posts: 17931
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle Database Administration

Who is online

Users browsing this forum: No registered users and 7 guests