Toad World® Forums

Table partition NOLOGGING DDL generation issue.

I scripted and generates a table from an instance and excluded all LOGGING syntax. The tablespace is LOGGING.

Source:

CREATE TABLE BR01
(…)
TABLESPACE FATTS2
RESULT_CACHE (MODE DEFAULT)
PARTITION BY LIST (FISCAL_YEAR_ID)
SUBPARTITION BY LIST (ACTIVITY_SA_ID)
(
PARTITION FY13 VALUES (2013)
TABLESPACE FATTS2
( SUBPARTITION BR01PHNSY13 VALUES (1) TABLESPACE FATTS2,
SUBPARTITION BR01PNSY13 VALUES (2) TABLESPACE FATTS2,

Toad DDL generated after table was created.

CREATE TABLE BR01
(…
)
TABLESPACE FATTS2
RESULT_CACHE (MODE DEFAULT)
LOGGING
PARTITION BY LIST (FISCAL_YEAR_ID)
SUBPARTITION BY LIST (ACTIVITY_SA_ID)
(
PARTITION FY13 VALUES (2013)
NOLOGGING --this is wrong
TABLESPACE FATTS2
( SUBPARTITION BR01PHNSY13 VALUES (1) TABLESPACE FATTS2,
SUBPARTITION BR01PNSY13 VALUES (2) TABLESPACE FATTS2,

Where does the partition NOLOGGING come from? DBA_TAB_PARTITIONS.LOGGING can be:

YES - LOGGING included in DDL
NO - NOLOGGING included in DDL
NONE - not specified in DDL

Test case:

CREATE TABLE cost.BR01_Z
(
ACTIVITY_SA_ID NUMBER NOT NULL,
FISCAL_YEAR_ID NUMBER(4) NOT NULL
)
TABLESPACE FATTS2
RESULT_CACHE (MODE DEFAULT)
LOGGING
PARTITION BY LIST (FISCAL_YEAR_ID)
SUBPARTITION BY LIST (ACTIVITY_SA_ID)
(
PARTITION FY13 VALUES (2013)
LOGGING
TABLESPACE FATTS2
( SUBPARTITION BR01PHNSY13 VALUES (1) TABLESPACE FATTS2,
SUBPARTITION BR01PNSY13 VALUES (2) TABLESPACE FATTS2 ),
PARTITION FY08 VALUES (2008)
NOLOGGING
TABLESPACE FATTS2
( SUBPARTITION BR01PHNSY8 VALUES (1) TABLESPACE FATTS2,
SUBPARTITION BR01PNSY8 VALUES (2) TABLESPACE FATTS2 ),
PARTITION FY09 VALUES (2009)
TABLESPACE FATTS2
( SUBPARTITION BR01PHNSY9 VALUES (1) TABLESPACE FATTS2,
SUBPARTITION BR01PNSY9 VALUES (2) TABLESPACE FATTS2 )
) ;

Thanks. It’s fixed for next beta.