How can I move a LOB segment from one tablespace to another? When I try to move one LOB index, for instance, I get the error message ORA-22864.
Can any merciful soul save me from this darkness?
Marc
Printable View
How can I move a LOB segment from one tablespace to another? When I try to move one LOB index, for instance, I get the error message ORA-22864.
Can any merciful soul save me from this darkness?
Marc
In theory, the code for moving the LOB SEGMENT (and
its index simultaneously) should be:
alter table tabX move lob (lob_col_name)
store as (
tablespace new_tablespace
-- any other lob storage clauses.
)
;
In practice when I did this on an 8.1.5 database
that the LOBs became unavailable, with Oracle
error ORA-22922: Non-existent LOB value.
The only way I got round this is by dropping the index and recreating.
alter table table_name move lob(column_name) store as lobsegment(tablespace lob);
will be the syntax to move lobs but I would advise don't do this operation unless you are in release 8173 or greater as there is a bug associated with moving lobs which leads to ora 600[25012], I moved lobs in 5 instances in some tables one day and all the instances encountered this bug and I(oracle support advice) had to recreate all the tables.
Here's the bug description:
BUG: 1952172 8.1.7.1.1 RDBMS 8.1.7.1.1 DICTIONARY PRODID-5 PORTID-46 ORA-600 1949273
Abstract: ORA-600 [25012] ON EXPORT AFTER MOVING A LOB TO ANOTHER TABLESPACE
Closed as a DUP of:
BUG: 1949273 8.1.6.3.0 RDBMS 8.1.6.3.0 DICTIONARY PRODID-5 PORTID-453 ORA-600
Abstract: ORA-600 [25012] ON ACCESS TABLE WITH LOB AFTER MOVE TABLE.