ORA-14020: this physical attribute may not be specified for a table partition (#Oracle Partitions)

Issue:

While I was Range Partitioning a table on 2 columns (Year) and (Month), I was stuck with the below error,

ORA-14020: this physical attribute may not be specified for a table partition

 

Cause:

Could be because of a formatting issue

 

Resolution:

Noticed that a “,” was missing from the query. This error could be seen if a “,” or “Parenthesis” “( )” is missed.

===

Partitioning query:

create table <….
….
….
>
PARTITION BY RANGE (year,month)
(PARTITION W_CONTACT_M2M_2015_P0 VALUES LESS THAN (2015,1),
PARTITION W_CONTACT_M2M_2015_p1 VALUES LESS THAN (2015,4),
PARTITION W_CONTACT_M2M_2015_p2 VALUES LESS THAN (2015,7),
PARTITION W_CONTACT_M2M_2015_p3 VALUES LESS THAN (2015,10),
PARTITION W_CONTACT_M2M_2015_p4 VALUES LESS THAN (2016,1),
PARTITION W_CONTACT_M2M_2016_p1 VALUES LESS THAN (2016,4),
PARTITION W_CONTACT_M2M_2016_p2 VALUES LESS THAN (2016,7),
PARTITION W_CONTACT_M2M_2016_p3 VALUES LESS THAN (2016,10),
PARTITION W_CONTACT_M2M_2016_p4 VALUES LESS THAN (2017,1),
PARTITION W_CONTACT_M2M_2017_p1 VALUES LESS THAN (2017,4),
PARTITION W_CONTACT_M2M_2017_p2 VALUES LESS THAN (2017,7),
PARTITION W_CONTACT_M2M_2017_p3 VALUES LESS THAN (2017,10),
PARTITION W_CONTACT_M2M_2017_p4 VALUES LESS THAN (2018,1),
PARTITION W_CONTACT_M2M_2018_p1 VALUES LESS THAN (2018,4),
PARTITION W_CONTACT_M2M_2018_p2 VALUES LESS THAN (2018,7),
PARTITION W_CONTACT_M2M_2018_p3 VALUES LESS THAN (2018,10),
PARTITION W_CONTACT_M2M_2018_p4 VALUES LESS THAN (2019,1),
PARTITION W_CONTACT_M2M_2019_p1 VALUES LESS THAN (2019,4),
PARTITION W_CONTACT_M2M_2019_p2 VALUES LESS THAN (2019,7),
PARTITION W_CONTACT_M2M_2019_p3 VALUES LESS THAN (2019,10),
PARTITION W_CONTACT_M2M_2019_p4 VALUES LESS THAN (2020,1),
PARTITION W_CONTACT_M2M_2020_p1 VALUES LESS THAN (2020,4),
PARTITION W_CONTACT_M2M_2020_p2 VALUES LESS THAN (2020,7),
PARTITION W_CONTACT_M2M_2020_p3 VALUES LESS THAN (2020,10),
PARTITION W_CONTACT_M2M_2020_p4 VALUES LESS THAN (2021,1),
PARTITION W_CONTACT_M2M_20_Max VALUES LESS THAN (maxvalue,1)
COMPRESS FOR QUERY HIGH TABLESPACE “PARTITIONED_SPACE”) ;

Advertisements
This entry was posted in Oracle Database. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s