CODEWITHSUNDEEP
oracle-databaseoracle12cpartitioningpartition
sromit
sromit

Reputation: 1112

Exchange Partition giving -Table or index is not partitioned. Invalid syntax

I have 2 schemas TBCAM and AR_TBCAM. There is a table called BKP_COST_EVENT in TBCAM which I have partitioned and I have moved the partition data into another simple table in AR_TBCAM schema called BKP_COST_EVENT_P2016. Now the data has moved to AR_TBCAM schema via this query

ALTER TABLE BKP_COST_EVENT EXCHANGE PARTITION P2016 WITH TABLE AR_TBCAM.BKP_COST_EVENT_P2016 INCLUDING INDEXES WITHOUT VALIDATION;

Now I want to bring the partition data back into the original table BKP_COST_EVENT. But when I run this query standing on AR_TBCAM 

ALTER TABLE BKP_COST_EVENT_P2016 EXCHANGE PARTITION P2016 WITH TABLE TBCAM.BKP_COST_EVENT INCLUDING INDEXES WITHOUT VALIDATION;

It is giving this error: 

Error starting at line : 1 in command -
ALTER TABLE BKP_COST_EVENT_P2016 EXCHANGE PARTITION P2016 WITH TABLE TBCAM.BKP_COST_EVENT INCLUDING INDEXES WITHOUT VALIDATION
Error report -
ORA-14501: object is not partitioned
14501. 00000 -  "object is not partitioned"
*Cause:    Table or index is not partitioned.  Invalid syntax.
*Action:   Retry the command with correct syntax.

Can anyone suggest what am I doing wrong? Or how to bring/restore the data back to my TBCAM schema table BKP_COST_EVENT. I have not dropped the partition p2016 in the original BKP_COST_EVENT

Upvotes: 1

Views: 4260

Answers (1)

djanoti
djanoti

Reputation: 303

In exchange partition syntax first table should be the one which is partitioned, second should be unpartitioned one. So, your first command was correct but 2nd command is wrong. If you are bringing back data to same table's partition run the same command again.

ALTER TABLE BKP_COST_EVENT EXCHANGE PARTITION P2016 WITH TABLE AR_TBCAM.BKP_COST_EVENT_P2016 INCLUDING INDEXES WITHOUT VALIDATION;

Also, if there are no indexes to be moved, its better not to use including indexes clause.

Upvotes: 1

Related Questions