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?
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 (
-- 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.
The brain is a wonderful organ; it starts working the moment you get up in the morning and does not stop until you get into the office.
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, 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 126.96.36.199.1 RDBMS 188.8.131.52.1 DICTIONARY PRODID-5 PORTID-46 ORA-600 1949273
Abstract: ORA-600  ON EXPORT AFTER MOVING A LOB TO ANOTHER TABLESPACE
Closed as a DUP of:
BUG: 1949273 184.108.40.206.0 RDBMS 220.127.116.11.0 DICTIONARY PRODID-5 PORTID-453 ORA-600
Abstract: ORA-600  ON ACCESS TABLE WITH LOB AFTER MOVE TABLE.
Click Here to Expand Forum to Full Width