How to Resolve ORA-22868: table with LOBs contains segments in different tablespaces

ORA-22868

Saw error ORA-22868 when I tried to drop a tablespace cascadingly.

SQL> drop tablespace ERP_TBS01 including contents and datafiles cascade constraints;
drop tablespace ERP_TBS01 including contents and datafiles cascade constraints
*
ERROR at line 1:
ORA-22868: table with LOBs contains segments in different tablespaces

ORA-22868 means that the tablespace you want to drop contains at least one large object (LOB) segment which table is in another existing tablespace. Oracle cannot handle such ambiguous situation.

Solutions

There're several possible ways to solve ORA-22868.

1. Move LOB back to Table's Tablespace

This goal is to move the LOB back to the tablespace where table is in. But before doing that, you have to know which LOB and where to move.

Here I compose statements that can directly use the result to move LOBs.

SQL> select 'alter table ' || l.owner || '.' || l.table_name || ' move lob(' || l.column_name || ') store as (tablespace ' || t.tablespace_name || ');' stmt from dba_lobs l, dba_tables t where l.owner = t.owner and l.table_name = t.table_name and l.tablespace_name <> t.tablespace_name and l.tablespace_name = 'ERP_TBS01';

STMT
--------------------------------------------------------------------------------
alter table PM.PRINT_MEDIA move lob(AD_PHOTO) store as (tablespace EXAMPLE);

In the above statement, we identify LOBs which are in different tablespace from the table's.

Then we execute the result.

SQL> alter table PM.PRINT_MEDIA move lob(AD_PHOTO) store as (tablespace EXAMPLE);

Table altered.

For more moving LOB techniques, you may refer to: How to Move LOB Column to Another Tablespace.

We can try to drop the tablespace now.

SQL> drop tablespace ERP_TBS01 including contents and datafiles cascade constraints;

Tablespace dropped.

2. Drop this Tablespace Later

If you just want to empty the database by dropping all tablespaces, you can leave such tablespace to the last to drop.