How can i ignore create portioned script from create table script using get_ddl in oracle?

Question

I am working on java project in which we are getting create table ddl using get_ddl method in Oracle 12c.

For example my table name is PARENT_EMP and schema is SECONDARYUSER then I am using get_ddl method.

select dbms.get_ddl('TABLE','PARENT_EMP','SECONDARYUSER') from dual;

before executing above statement I am executing following script to ignore constrainst and table spaces from ddl.

begin
        dbms_metadata.set_transform_param (dbms_metadata.session_transform,'STORAGE',false);
        dbms_metadata.set_transform_param (dbms_metadata.session_transform,'REF_CONSTRAINTS', false);
        dbms_metadata.set_transform_param (dbms_metadata.session_transform,'TABLESPACE',false);
        dbms_metadata.set_transform_param (dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES', false); 
        dbms_metadata.set_transform_param (dbms_metadata.session_transform,'CONSTRAINTS', false);           
end;

But my issue is, some of table has REFERENCE PARTITIONED and if I get ddl using get_ddl method then also I am getting foreign constraint script.

Parent table DDl is :-

 create table parent_emp(
    empno      number  primary key,
    job        varchar2(20),
    sal        number(7,2),
    deptno     number(2)
    )
    partition by list(job)
    ( partition p_job_dba values ('DBA'),
      partition p_job_mgr values ('MGR'),
     partition p_job_vp  values ('VP')
   );

Child table DDl is :

  CREATE TABLE "SECONDARYUSER"."REFERENCE_EMP" 
   (    
    "ENAME" VARCHAR2(10), 
    "EMP_ID" NUMBER, 
    "EMPNO" NUMBER, 
     CONSTRAINT "FK_EMPNO" FOREIGN KEY ("EMPNO")
      REFERENCES "SECONDARYUSER"."PARENT_EMP" ("EMPNO") ENABLE
   ) 
  PARTITION BY REFERENCE ("FK_EMPNO") 
 (PARTITION "P_JOB_DBA" , 
 PARTITION "P_JOB_MGR" , 
 PARTITION "P_JOB_VP" ) "

Please suggest how can I get child table ddl without getting foreign constraint and partitioned ddl , or how can I creating portioned on already created table using alter table. Also is there any way to get partitioned ddl using get_ddl method?

the child table ddl it's wrong, please correct it.

F.Lazarescu
– F.Lazarescu

2019-08-14 13:25:57 +00:00
Commented Aug 14, 2019 at 13:25 — F.Lazarescu
– F.Lazarescu, Commented Aug 14, 2019 at 13:25

F.Lazarescu · Accepted Answer · 2019-08-14 12:55:13Z

1

I just found the answer.

You are missing this declaration:

exec dbms_metadata.set_transform_param (dbms_metadata.session_transform,'PARTITIONING',false);

Include it, and it will ignore the part with 'partition'.

So, you need those ones:

exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS',false);
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'REF_CONSTRAINTS',false);
exec dbms_metadata.set_transform_param (dbms_metadata.session_transform,'PARTITIONING',false);
exec dbms_metadata.set_transform_param (dbms_metadata.session_transform,'TABLESPACE',false);
exec dbms_metadata.set_transform_param (dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES', false);

Credits: one of the answers of this.

edited Aug 14, 2019 at 12:55

answered Aug 14, 2019 at 10:19

F.Lazarescu

1,3852 gold badges18 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

santosh Over a year ago

I am doing same but if table has reference partitioned then getting constraint and partitioned script. how can ignore partitioned script.

santosh Over a year ago

Thank you this command is working . After execute this command i am getting script without partitioned but foreign key script are still coming into script.

CREATE TABLE "SECONDARYUSER"."REFERENCE_EMP"     (	"ENAME" VARCHAR2(10),  	"EMP_ID" NUMBER,  	"EMPNO" NUMBER,  	 CONSTRAINT "FK_EMPNO" FOREIGN KEY ("EMPNO") 	  REFERENCES "SECONDARYUSER"."PARENT_EMP" ("EMPNO") ENABLE    )

F.Lazarescu Over a year ago

I just edited again. Try to execute again, like in my post and see if works.

santosh Over a year ago

I have already use that script only PARTITIONING are missing so i have add into script.

Collectives™ on Stack Overflow

How can i ignore create portioned script from create table script using get_ddl in oracle?

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related