5

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?

santosh
  • 435
  • 1
  • 7
  • 24

1 Answers1

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.

F.Lazarescu
  • 1,385
  • 2
  • 16
  • 31
  • I am doing same but if table has reference partitioned then getting constraint and partitioned script. how can ignore partitioned script. – santosh Aug 14 '19 at 11:01
  • 1
    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 ) ` – santosh Aug 14 '19 at 12:46
  • I just edited again. Try to execute again, like in my post and see if works. – F.Lazarescu Aug 14 '19 at 12:55
  • I have already use that script only PARTITIONING are missing so i have add into script. – santosh Aug 14 '19 at 13:01