Batch operation records of moving table and rebuild index

Batch operation records of moving table and rebuild index

Batch move tables in the oldtablespace tablespace to newtablespace. query and execute the query results.

Select 'alter table' | table_name | 'move tablespace newtablespace'

From user_all_tables

Where table_space = 'oldtablespace ';

Rebuild indexes in the oldtablespace tablespace to newtablespace in batches. query and execute the query results.

Select 'alter Index' | index_name | 'rebuild tablespace newtablespace ;'
From user_indexes
Where tablespace_name = 'oldtablespace ';

When the table with the lob field is moved, the lob field needs to be moved separately.

Alter table AUDIT_RECORD move lob (lobrow1) store as (TABLESPACE newtablespace );

Alter table AUDIT_RECORD move lob (lobrow2) store as (TABLESPACE newtablespace );
 

Alter table AUDIT_RECORD move tablespace newtablespace;

 

Or
Alter table test2 MOVE

TABLESPACE users

LOB (lobrow1) store as lobsegment

(TABLESPACE newtablespace );

In addition, when executing imp for exp/IMP Migration with a lob field, you need to create and import the tablespace where the original lob field is located, and then consider moving it to other tablespaces.

You can use the remap_tablespace parameter to import data to the specified tablespace.

This article permanently updates the link address: