7 Replies Latest reply: Nov 16, 2012 5:27 AM by Balazs Papp RSS

    Unable to get DDL for a table Partition.

    719669
      Hi,

      DB version- 11.2.0.2

      We have a partitioned table in the production database.
      The following command retrieves 3 rows (which indicates there are 3 partitions for this table)

      select * from dba_tab_partitions where table_name='<table_name>'
      and table_owner='<owner>'

      but when i try to generate the DDL for the table it does not have any partition information in it
      select dbms_metadata.get_ddl('TABLE','<table_name>','<owner>') from dual;

      The output is similar to

      create table ....
      storage clause...
      tablespace name;

      (There are no partition information in this)


      I tried to create a backup table as select * from the existing table but still no partition information seems to get transferred.
      The new table has NO partitions.

      Can anyone suggest what i am overlooking or what should i do to figure out a solution.

      Thanks in Advance.

      Best Regards,
      BT.
        • 1. Re: Unable to get DDL for a table Partition.
          sb92075
          BT wrote:
          Hi,

          DB version- 11.2.0.2

          We have a partitioned table in the production database.
          The following command retrieves 3 rows (which indicates there are 3 partitions for this table)

          select * from dba_tab_partitions where table_name='<table_name>'
          and table_owner='<owner>'

          but when i try to generate the DDL for the table it does not have any partition information in it
          select dbms_metadata.get_ddl('TABLE','<table_name>','<owner>') from dual;

          The output is similar to

          create table ....
          storage clause...
          tablespace name;

          (There are no partition information in this)


          I tried to create a backup table as select * from the existing table but still no partition information seems to get transferred.
          The new table has NO partitions.

          Can anyone suggest what i am overlooking or what should i do to figure out a solution.

          Thanks in Advance.

          Best Regards,
          BT.
          it works OK for me
            1* select dbms_metadata.get_ddl('TABLE','COSTS'        ,'SH'     ) from dual
          SQL> /
          
            CREATE TABLE "SH"."COSTS"
             (    "PROD_ID" NUMBER NOT NULL ENABLE,
                  "TIME_ID" DATE NOT NULL ENABLE,
                  "PROMO_ID" NUMBER NOT NULL ENABLE,
                  "CHANNEL_ID" NUMBER NOT NULL ENABLE,
                  "UNIT_COST" NUMBER(10,2) NOT NULL ENABLE,
                  "UNIT_PRICE" NUMBER(10,2) NOT NULL ENABLE,
                   CONSTRAINT "COSTS_CHANNEL_FK" FOREIGN KEY ("CHANNEL_ID")
                    REFERENCES "SH"."CHANNELS" ("CHANNEL_ID") DISABLE,
                   CONSTRAINT "COSTS_TIME_FK" FOREIGN KEY ("TIME_ID")
                    REFERENCES "SH"."TIMES" ("TIME_ID") ENABLE NOVALIDATE,
                   CONSTRAINT "COSTS_PRODUCT_FK" FOREIGN KEY ("PROD_ID")
                    REFERENCES "SH"."PRODUCTS" ("PROD_ID") ENABLE NOVALIDATE,
                   CONSTRAINT "COSTS_PROMO_FK" FOREIGN KEY ("PROMO_ID")
                    REFERENCES "SH"."PROMOTIONS" ("PROMO_ID") DISABLE
             ) PCTFREE 5 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS  NOLOGGING
            STORAGE(
            BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
            TABLESPACE "EXAMPLE"
            PARTITION BY RANGE ("TIME_ID")
           (PARTITION "COSTS_1995"  VALUES LESS THAN (TO_DATE(' 1996-01-01 00:00:00', 'SYY
          YY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
            PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 COMPRESS BASIC NOLOGGING
            STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
            PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
          FAULT CELL_FLASH_CACHE DEFAULT)
            TABLESPACE "EXAMPLE" ,
           PARTITION "COSTS_1996"  VALUES LESS THAN (TO_DATE(' 1997-01-01 00:00:00', 'SYYY
          Y-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
            PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 COMPRESS BASIC NOLOGGING
            STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
            PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
          FAULT CELL_FLASH_CACHE DEFAULT)
            TABLESPACE "EXAMPLE" ,
           PARTITION "COSTS_H1_1997"  VALUES LESS THAN (TO_DATE(' 1997-07-01 00:00:00', 'S
          YYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
            PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 COMPRESS BASIC NOLOGGING
            STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
            PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
          FAULT CELL_FLASH_CACHE DEFAULT)
            TABLESPACE "EXAMPLE" ,
           PARTITION "COSTS_H2_1997"  VALUES LESS THAN (TO_DATE(' 1998-01-01 00:00:00', 'S
          YYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
            PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 COMPRESS BASIC NOLOGGING
            STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
            PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
          FAULT CELL_FLASH_CACHE DEFAULT)
            TABLESPACE "EXAMPLE" ,
           PARTITION "COSTS_Q1_1998"  VALUES LESS THAN (TO_DATE(' 1998-04-01 00:00:00', 'S
          YYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
            PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 COMPRESS BASIC NOLOGGING
            STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
            PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
          FAULT CELL_FLASH_CACHE DEFAULT)
            TABLESPACE "EXAMPLE" ,
           PARTITION "COSTS_Q2_1998"  VALUES LESS THAN (TO_DATE(' 1998-07-01 00:00:00', 'S
          YYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
            PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 COMPRESS BASIC NOLOGGING
            STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
            PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
          FAULT CELL_FLASH_CACHE DEFAULT)
            TABLESPACE "EXAMPLE" ,
           PARTITION "COSTS_Q3_1998"  VALUES LESS THAN (TO_DATE(' 1998-10-01 00:00:00', 'S
          YYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
            PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 COMPRESS BASIC NOLOGGING
            STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
            PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
          FAULT CELL_FLASH_CACHE DEFAULT)
            TABLESPACE "EXAMPLE" ,
           PARTITION "COSTS_Q4_1998"  VALUES LESS THAN (TO_DATE(' 1999-01-01 00:00:00', 'S
          YYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
            PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 COMPRESS BASIC NOLOGGING
            STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
            PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
          FAULT CELL_FLASH_CACHE DEFAULT)
            TABLESPACE "EXAMPLE" ,
           PARTITION "COSTS_Q1_1999"  VALUES LESS THAN (TO_DATE(' 1999-04-01 00:00:00', 'S
          YYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
            PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 COMPRESS BASIC NOLOGGING
            STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
            PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
          FAULT CELL_FLASH_CACHE DEFAULT)
            TABLESPACE "EXAMPLE" ,
           PARTITION "COSTS_Q2_1999"  VALUES LESS THAN (TO_DATE(' 1999-07-01 00:00:00', 'S
          YYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
            PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 COMPRESS BASIC NOLOGGING
            STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
            PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
          FAULT CELL_FLASH_CACHE DEFAULT)
            TABLESPACE "EXAMPLE" ,
           PARTITION "COSTS_Q3_1999"  VALUES LESS THAN (TO_DATE(' 1999-10-01 00:00:00', 'S
          YYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
            PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 COMPRESS BASIC NOLOGGING
            STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
            PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
          FAULT CELL_FLASH_CACHE DEFAULT)
            TABLESPACE "EXAMPLE" ,
           PARTITION "COSTS_Q4_1999"  VALUES LESS THAN (TO_DATE(' 2000-01-01 00:00:00', 'S
          YYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
            PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 COMPRESS BASIC NOLOGGING
            STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
            PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
          FAULT CELL_FLASH_CACHE DEFAULT)
            TABLESPACE "EXAMPLE" ,
           PARTITION "COSTS_Q1_2000"  VALUES LESS THAN (TO_DATE(' 2000-04-01 00:00:00', 'S
          YYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
            PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 COMPRESS BASIC NOLOGGING
            STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
            PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
          FAULT CELL_FLASH_CACHE DEFAULT)
            TABLESPACE "EXAMPLE" ,
           PARTITION "COSTS_Q2_2000"  VALUES LESS THAN (TO_DATE(' 2000-07-01 00:00:00', 'S
          YYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
            PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 COMPRESS BASIC NOLOGGING
            STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
            PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
          FAULT CELL_FLASH_CACHE DEFAULT)
            TABLESPACE "EXAMPLE" ,
           PARTITION "COSTS_Q3_2000"  VALUES LESS THAN (TO_DATE(' 2000-10-01 00:00:00', 'S
          YYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
            PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 COMPRESS BASIC NOLOGGING
            STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
            PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
          FAULT CELL_FLASH_CACHE DEFAULT)
            TABLESPACE "EXAMPLE" ,
           PARTITION "COSTS_Q4_2000"  VALUES LESS THAN (TO_DATE(' 2001-01-01 00:00:00', 'S
          YYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
            PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 COMPRESS BASIC NOLOGGING
            STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
            PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
          FAULT CELL_FLASH_CACHE DEFAULT)
            TABLESPACE "EXAMPLE" ,
           PARTITION "COSTS_Q1_2001"  VALUES LESS THAN (TO_DATE(' 2001-04-01 00:00:00', 'S
          YYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
            PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 COMPRESS BASIC NOLOGGING
            STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
            PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
          FAULT CELL_FLASH_CACHE DEFAULT)
            TABLESPACE "EXAMPLE" ,
           PARTITION "COSTS_Q2_2001"  VALUES LESS THAN (TO_DATE(' 2001-07-01 00:00:00', 'S
          YYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
            PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 COMPRESS BASIC NOLOGGING
            STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
            PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
          FAULT CELL_FLASH_CACHE DEFAULT)
            TABLESPACE "EXAMPLE" ,
           PARTITION "COSTS_Q3_2001"  VALUES LESS THAN (TO_DATE(' 2001-10-01 00:00:00', 'S
          YYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
            PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 COMPRESS BASIC NOLOGGING
            STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
            PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
          FAULT CELL_FLASH_CACHE DEFAULT)
            TABLESPACE "EXAMPLE" ,
           PARTITION "COSTS_Q4_2001"  VALUES LESS THAN (TO_DATE(' 2002-01-01 00:00:00', 'S
          YYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
            PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 COMPRESS BASIC NOLOGGING
            STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
            PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
          FAULT CELL_FLASH_CACHE DEFAULT)
            TABLESPACE "EXAMPLE" ,
           PARTITION "COSTS_Q1_2002"  VALUES LESS THAN (TO_DATE(' 2002-04-01 00:00:00', 'S
          YYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
            PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 COMPRESS BASIC NOLOGGING
            STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
            PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
          FAULT CELL_FLASH_CACHE DEFAULT)
            TABLESPACE "EXAMPLE" ,
           PARTITION "COSTS_Q2_2002"  VALUES LESS THAN (TO_DATE(' 2002-07-01 00:00:00', 'S
          YYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
            PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 COMPRESS BASIC NOLOGGING
            STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
            PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
          FAULT CELL_FLASH_CACHE DEFAULT)
            TABLESPACE "EXAMPLE" ,
           PARTITION "COSTS_Q3_2002"  VALUES LESS THAN (TO_DATE(' 2002-10-01 00:00:00', 'S
          YYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
            PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 COMPRESS BASIC NOLOGGING
            STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
            PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
          FAULT CELL_FLASH_CACHE DEFAULT)
            TABLESPACE "EXAMPLE" ,
           PARTITION "COSTS_Q4_2002"  VALUES LESS THAN (TO_DATE(' 2003-01-01 00:00:00', 'S
          YYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
            PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 COMPRESS BASIC NOLOGGING
            STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
            PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
          FAULT CELL_FLASH_CACHE DEFAULT)
            TABLESPACE "EXAMPLE" ,
           PARTITION "COSTS_Q1_2003"  VALUES LESS THAN (TO_DATE(' 2003-04-01 00:00:00', 'S
          YYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
            PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 COMPRESS BASIC NOLOGGING
            STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
            PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
          FAULT CELL_FLASH_CACHE DEFAULT)
            TABLESPACE "EXAMPLE" ,
           PARTITION "COSTS_Q2_2003"  VALUES LESS THAN (TO_DATE(' 2003-07-01 00:00:00', 'S
          YYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
            PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 COMPRESS BASIC NOLOGGING
            STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
            PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
          FAULT CELL_FLASH_CACHE DEFAULT)
            TABLESPACE "EXAMPLE" ,
           PARTITION "COSTS_Q3_2003"  VALUES LESS THAN (TO_DATE(' 2003-10-01 00:00:00', 'S
          YYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
            PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 COMPRESS BASIC NOLOGGING
            STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
            PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
          FAULT CELL_FLASH_CACHE DEFAULT)
            TABLESPACE "EXAMPLE" ,
           PARTITION "COSTS_Q4_2003"  VALUES LESS THAN (TO_DATE(' 2004-01-01 00:00:00', 'S
          YYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
            PCTFREE 5 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
            STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
            PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
          FAULT CELL_FLASH_CACHE DEFAULT)
            TABLESPACE "EXAMPLE" )
          
          
          SQL> 
          • 2. Re: Unable to get DDL for a table Partition.
            mschnatt
            Try via SQL Developer (Table -> SQL). There the partition information should be included.
            • 3. Re: Unable to get DDL for a table Partition.
              Mark D Powell
              Check to see what you have SET_TRANSFORM_PARAM set to for Object Type TABLE and INDEX with Name PARTITIONING. If it is FALSE then dbms_metadata has been told to supress partitions from the DDL, that is, just generate a normal table.

              HTH -- Mark D Powell --
              • 4. Re: Unable to get DDL for a table Partition.
                rp0428
                >
                but when i try to generate the DDL for the table it does not have any partition information in it
                select dbms_metadata.get_ddl('TABLE','<table_name>','<owner>') from dual;
                >
                If that is the ONLY statement that has been executed you would get the partition data. But, as Mark said, if a transform has been run that sets the partitioning parameter to false then you won't.

                This gives partitioning metadata
                select dbms_metadata.get_ddl('TABLE','TEST3_PG','SCOTT') from dual;
                But this doesn't
                EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PARTITIONING',false);
                select dbms_metadata.get_ddl('TABLE','TEST3_PG','SCOTT') from dual;
                • 5. Re: Unable to get DDL for a table Partition.
                  Mark D Powell
                  BT, please post the header from an SQLPlus log on or the result of the following query: select parameter, value from v$option where parameter = 'Partitioning';

                  You can also try changing FALSE to TRUE in rp0428's last example.


                  HTH -- Mark D Powell --
                  • 6. Re: Unable to get DDL for a table Partition.
                    719669
                    Hi,

                    Thanks for your inputs.

                    I dont know how, but now i am able to get the partition information in the DDL statement.

                    I havent set anything at session level. it was the same command which was executed earlier.
                    Strange!!..

                    The current table has 3 partitions but when i create another table its is non -partitioned.

                    Steps:

                    Tablename: TAB1 (with 3 partitions)

                    CREATE TABLE TAB2 NOLOGGING PARALLEL 4 AS SELECT * FROM TAB1;
                    The resulting Table TAB2 has no partitions associated to it.

                    Is the outcome expected ???

                    Thanks .

                    Best Regards,
                    BT.
                    • 7. Re: Unable to get DDL for a table Partition.
                      Balazs Papp
                      The current table has 3 partitions but when i create another table its is non -partitioned.

                      Steps:

                      Tablename: TAB1 (with 3 partitions)

                      CREATE TABLE TAB2 NOLOGGING PARALLEL 4 AS SELECT * FROM TAB1;
                      The resulting Table TAB2 has no partitions associated to it.

                      Is the outcome expected ???
                      yes it is
                      without the partitioning clause, the new table will be non-partitioned

                      do it like this (customize for your table):
                      create table tab2
                      partition by range (part_key)
                      (
                      partition p1 values less than (...),
                      partition p2 values less than (...),
                      ...
                      )
                      as
                      select * from TAB1;