Here is Table DDL
CREATE TABLE CNS.COMM_CNS_dummy_data ( FILE_DT NUMBER(6), PARENT_HQ_NAME VARCHAR2(90 BYTE), PARENT_HQ_FIPS_COUNTRY_CODE VARCHAR2(2 BYTE), PARENT_HQ_COUNTRY_CODE VARCHAR2(3 BYTE), PARENT_HQ_STATE VARCHAR2(10 BYTE), HIERARCHY_CODE VARCHAR2(2 BYTE), DIAS_CODE VARCHAR2(9 BYTE), NUMBER_OF_FAMILY_MEMBERS VARCHAR2(5 BYTE), FAMILY_UPDATE_DATE VARCHAR2(8 BYTE), SIC1 VARCHAR2(32 BYTE), SIC2 VARCHAR2(32 BYTE), SIC3 VARCHAR2(32 BYTE), SIC4 VARCHAR2(32 BYTE), SIC5 VARCHAR2(32 BYTE), SIC6 VARCHAR2(32 BYTE), NATIONAL_IDENTIFICATION_NUMBER VARCHAR2(20 BYTE), FILLER10 VARCHAR2(6 BYTE), FILLER11 VARCHAR2(6 BYTE), FIRST_EXECUTIVE_FIRST_NAME VARCHAR2(13 BYTE), CENSUS_GEOCODE VARCHAR2(15 BYTE), CENSUS_GEOCODE_MATCH_FLAG VARCHAR2(1 BYTE), CENSUS_2003_NECTA_DIV_FIPS VARCHAR2(5 BYTE), ) TABLESPACE CNS_DATA PARTITION BY RANGE (FILE_DT) ( PARTITION CNS_dummy_data_201105 VALUES LESS THAN (201106) TABLESPACE CNS_DATA, PARTITION CNS_dummy_data_201106 VALUES LESS THAN (201107) TABLESPACE CNS_DATA, PARTITION CNS_dummy_data_201107 VALUES LESS THAN (201108) TABLESPACE CNS_DATA, PARTITION CNS_dummy_data_201108 VALUES LESS THAN (201109) TABLESPACE CNS_DATA, PARTITION CNS_dummy_data_201109 VALUES LESS THAN (201110) TABLESPACE CNS_DATA, PARTITION CNS_dummy_data_201110 VALUES LESS THAN (201111) TABLESPACE CNS_DATA, PARTITION CNS_dummy_data_201111 VALUES LESS THAN (201112) TABLESPACE CNS_DATA, PARTITION CNS_dummy_data_201112 VALUES LESS THAN (201201) TABLESPACE CNS_DATA, ) ;
CTAS for above table will be
CREATE TABLE cns.COMM_cns_dummy_data_BK PARTITION BY RANGE (FILE_DT) ( PARTITION cns_dummy_data_201105 VALUES LESS THAN (201106), PARTITION cns_dummy_data_201106 VALUES LESS THAN (201107), PARTITION cns_dummy_data_201107 VALUES LESS THAN (201108), PARTITION cns_dummy_data_201108 VALUES LESS THAN (201109), PARTITION cns_dummy_data_201109 VALUES LESS THAN (201110), PARTITION cns_dummy_data_201110 VALUES LESS THAN (201111), PARTITION cns_dummy_data_201111 VALUES LESS THAN (201112), PARTITION cns_dummy_data_201112 VALUES LESS THAN (201201) ) AS SELECT * FROM cns.COMM_cns_dummy_data PARALLEL;
2 comments:
Hi Jignesh, the syntax you gave above is not working. It throws this error:
Error report:
SQL Error: ORA-14020: this physical attribute may not be specified for a table partition
14020. 00000 - "this physical attribute may not be specified for a table partition"
*Cause: unexpected option was encountered while parsing physical
attributes of a table partition; valid options for Range or
Composite Range partitions are INITRANS, MAXTRANS, TABLESPACE,
STORAGE, PCTFREE, and PCTUSED; only TABLESPACE may be specified
for Hash partitions
*Action: remove invalid option(s) from the list of physical attributes
of a table partition
*Comment: this error could have resulted from omission of a
terminating (right) parenthesis following the list of
partition descriptions
I am able to use CTAS to create a table with one single partition for all the data and subsequently split it into multiple partitions as required.
Please let me know how I can make your syntax work.
I think you implemented it wrong, but there is an error when I run it against v 12.1.0.2.0 The PARALLEL keyword should come after the right parenthesis following the partition list and why not specify the degree while you're at it:
CREATE TABLE cns.COMM_cns_dummy_data_BK PARTITION BY RANGE (FILE_DT)
(
PARTITION cns_dummy_data_201105 VALUES LESS THAN (201106),
PARTITION cns_dummy_data_201106 VALUES LESS THAN (201107),
PARTITION cns_dummy_data_201107 VALUES LESS THAN (201108),
PARTITION cns_dummy_data_201108 VALUES LESS THAN (201109),
PARTITION cns_dummy_data_201109 VALUES LESS THAN (201110),
PARTITION cns_dummy_data_201110 VALUES LESS THAN (201111),
PARTITION cns_dummy_data_201111 VALUES LESS THAN (201112),
PARTITION cns_dummy_data_201112 VALUES LESS THAN (201201)
)
parallel (degree 4)
AS SELECT * FROM
cns.COMM_cns_dummy_data;
Post a Comment
Note: Only a member of this blog may post a comment.