Ask the Expert

Moving partitions

I want details regarding moving partitions. My scenario is as follows:

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 )
)
);

    Requires Free Membership to View

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.

This was first published in October 2003

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: