Andrew Fraser DBA

Rebuild table indexes lobs in oracle

Rebuilds are beneficial in some rare circumstances such as after a mass delete operation. The below method is safer and faster than export/import.

Example table:

CREATE TABLE tab1 ( col1 VARCHAR2(1) , col2 CLOB ) TABLESPACE ts1 ;
CREATE INDEX tab1i ON tab1 ( col1 ) TABLESPACE ts1 ;

List objects and tablespaces:

SELECT tablespace_name FROM user_tables WHERE table_name = 'TAB1' ;
SELECT tablespace_name FROM user_lobs WHERE table_name = 'TAB1' ;
SELECT tablespace_name , index_name FROM user_indexes WHERE table_name = 'TAB1' ;

Rebuild:

ALTER TABLE tab1 MOVE ;
ALTER TABLE tab1 MOVE LOB ( col2 ) STORE AS ( TABLESPACE ts1 ) ;
ALTER INDEX tab1i REBUILD ;

Update optimizer stats:

EXEC dbms_scheduler.run_job ( 'GATHER_STATS_JOB' )

For large tables, it may be better to rebuild them online using dbms_redefinition.

H/t Anand.