I want details regarding moving partitions. My scenario is as follows:
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
I've a table which has a number and a BLOB column, both of which are NOT NULL type. This table is composite partitioned using range and hash on the same column. Each partition is subpartitioned into two. I'm using Oracle Version 9.0.2.
Now if I try to move the partitions to a different tablespace, I get an Oracle error saying that ERROR: ORA_14257 cannot move a partition other than a range or hash partition. Now is there any other way to move the partition to another tablespace? Here is the code:
CREATE TABLE temp1 ( col1 INTEGER NOT NULL , col2 BLOB NOT NULL ) TABLESPACE space LOB (col2) STORE AS ( DISABLE STORAGE IN ROW PCTVERSION 10 NOCACHE ) NOPARALLEL NOCACHE PARTITION BY RANGE (col1) SUBPARTITION BY HASH (col1) ( PARTITION p1 VALUES LESS THAN (10) TABLESPACE space LOB (col2) STORE AS ( TABLESPACE space PCTVERSION 10 NOCACHE ) ( SUBPARTITION sp1 TABLESPACE space LOB (col2) STORE AS ( TABLESPACE space ) ,SUBPARTITION sp2 TABLESPACE space LOB (col2) STORE AS ( TABLESPACE space ) ) ,PARTITION p2 VALUES LESS THAN (20) TABLESPACE space LOB (col2) STORE AS ( TABLESPACE space PCTVERSION 10 NOCACHE ) ( SUBPARTITION sp3 TABLESPACE space LOB (col2) STORE AS ( TABLESPACE space ) ,SUBPARTITION sp4 TABLESPACE space LOB (col2) STORE AS ( TABLESPACE space ) ) );
Since your partition is a composite partition, you cannot move it with the ALTER TABLE MOVE command. The ORA-14257 error says that the partition must be a range or hash partition, not both. So you'll have to go through a little more work to get this moved. You can do one of a few things.
1. You can export the partition with the EXP utility. Then drop the partition and recreate it in the new tablespace. Then import the partition's data with the IMP utility.
2. You can copy the partition's data into a temporary table with the CREATE TABLE AS SELECT command. Then, drop and recreate the partition in the new tablespace. Then move the data back into the partition with the INSERT SELECT command.
Dig Deeper on Oracle database design and architecture
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.