This discussion is archived
7 Replies Latest reply: Nov 16, 2012 3:27 AM by Balazs Papp RSS

Unable to get DDL for a table Partition.

719669 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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.
    761512 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points