7 Replies Latest reply: Jul 20, 2011 2:15 PM by 846392 RSS

    How to omit the segment attributes to get the DDL from a primary key?

    846392
      Hi to all.
      Help, please.

      The statement below applies only to tables:
      ...
       DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', FALSE);
      ...
      I can not remove the attributes of a segment of a primary key with the following statement:
      ...
       cDDL := DBMS_METADATA.GET_DEPENDENT_DDL('CONSTRAINT', tableName, owner);
      ...
      Result:
      ALTER TABLE "OWNER"."AREA" ADD PRIMARY KEY ("AREA")
        USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 -- ???
        STORAGE(INITIAL 3145728 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 -- ???
        PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) -- ???
        TABLESPACE "TS_IXPCDC01"  ENABLE; -- ???
      How I can remove it?

      I desire this only:
      ALTER TABLE "OWNER"."AREA" ADD PRIMARY KEY ("AREA");
      Thanks,
      Philips

      Edited by: Philips on 19/07/2011 15:42
        • 1. Re: How to omit the segment attributes to get the DDL from a primary key?
          sb92075
          Philips wrote:
          Hi to all.
          Help, please.

          I can not remove the attributes of a segment of a primary key with the following statement:
          ...
          cDDL := DBMS_METADATA.GET_DEPENDENT_DDL('CONSTRAINT', tableName, owner);
          ...
          How I can do it?

          Thanks,
          Philips
          what specifically do you expect/desire?
          give us an example
          • 3. Re: How to omit the segment attributes to get the DDL from a primary key?
            AzharHusain
            Works for me. Atucally it apply at the session level and in the same session it apply for CONSTRAINT also.
            begin
            DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', FALSE);
            end;
            
            select 
            DBMS_METADATA.GET_DEPENDENT_DDL('CONSTRAINT', 'TABLE_1', 'SOME_OWNER')
            from dual
            • 4. Re: How to omit the segment attributes to get the DDL from a primary key?
              846392
              Not working how expected.
              begin
              DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', FALSE);
              end;
              select 
              DBMS_METADATA.GET_DEPENDENT_DDL('CONSTRAINT', 'AREA', 'OWNER')
              from dual;
              Result clob:
              ALTER TABLE "OWNER"."AREA" ADD CONSTRAINT "AREA_FL_DEFAULT_CK" CHECK ( FL_DEFAULT IN ('0','1') ) ENABLE
               
                ALTER TABLE "OWNER"."AREA" ADD PRIMARY KEY ("AREA")
                USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 
                STORAGE(INITIAL 3145728 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
                PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
                TABLESPACE "TS_IXPCDC01"  ENABLE
              ...,
              Philips
              • 5. Re: How to omit the segment attributes to get the DDL from a primary key?
                AzharHusain
                SQL> select * from v$version where rownum<=1;
                
                BANNER
                --------------------------------------------------------------------------------
                Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
                
                
                SQL> create table test_22(seq_nu number);
                
                Table created.
                
                SQL> alter table test_22 add constraint seq_nu_pk primary key(seq_nu);
                
                Table altered.
                
                SQL> select 
                  2  DBMS_METADATA.GET_DEPENDENT_DDL('CONSTRAINT', 'TEST_22', 'OWNER')
                  3  from dual;
                
                DBMS_METADATA.GET_DEPENDENT_DDL('CONSTRAINT','TEST_22','OWNER')
                --------------------------------------------------------------------------------
                
                  ALTER TABLE "OWNER"."TEST_22" ADD CONSTRAINT "SEQ_NU_PK" PRIMARY KEY ("SEQ_NU")
                  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
                  STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
                  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
                  TABLESPACE "DATA_TBL"  ENABLE
                
                
                
                SQL> begin
                  2  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', FALSE);
                  3  end;
                  4  /
                
                PL/SQL procedure successfully completed.
                
                SQL> select 
                  2  DBMS_METADATA.GET_DEPENDENT_DDL('CONSTRAINT', 'TEST_22', 'OWNER')
                  3  from dual;
                
                DBMS_METADATA.GET_DEPENDENT_DDL('CONSTRAINT','TEST_22','OWNER')
                --------------------------------------------------------------------------------
                
                  ALTER TABLE "OWNER"."TEST_22" ADD CONSTRAINT "SEQ_NU_PK" PRIMARY KEY ("SEQ_NU") ENABLE
                
                
                
                SQL> begin
                  2  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', TRUE);
                  3  end;
                  4  /
                
                PL/SQL procedure successfully completed.
                
                SQL> select 
                  2  DBMS_METADATA.GET_DEPENDENT_DDL('CONSTRAINT', 'TEST_22', 'OWNER')
                  3  from dual;
                
                DBMS_METADATA.GET_DEPENDENT_DDL('CONSTRAINT','TEST_22','OWNER')
                --------------------------------------------------------------------------------
                
                  ALTER TABLE "OWNER"."TEST_22" ADD CONSTRAINT "SEQ_NU_PK" PRIMARY KEY ("SEQ_NU")
                  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
                  STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
                  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
                  TABLESPACE "DATA_TBL"  ENABLE
                
                
                
                SQL> 
                • 6. Re: How to omit the segment attributes to get the DDL from a primary key?
                  846392
                  select * from v$version where rownum<=1;
                  ----------------------------------------------------------------
                  Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production

                  Not working...

                  It works for tables, but not for constraints...

                  Philips

                  Edited by: Philips on 20/07/2011 09:19
                  • 7. Re: How to omit the segment attributes to get the DDL from a primary key?
                    846392
                    Why is not working on my edition?