Oracle getting started Tutorial: Moving data objects between tablespaces

Move data objects between tablespaces

I. BASIC script

Moving objects between tablespaces in the Oracle database involves multiple types of data objects.
Move Table:

1. Alter TableT_aMoveTablespace tbs_a;

Mobile index:

1. Alter IndexI _a rebuild tablespace tbs_a;

Mobile LOBSEGMENT:

1. Alter TableT_aMoveLob (colume_a) storeAs(Tablespace tbs_a );

According to the actual test, LOBINDEX will automatically move with the corresponding LOBSEGMENT
Move a table at the same time. LOBSEGMENT:

1. Alter TableT_aMoveTablespace tbs_a lob (colume_a) storeAs(Tablespace tbs_a );

If the table has fields of the LONG type, the table cannot be moved according to the above method. Only EXP/IMP can be used to move the table.

Ii. Generate mobile scripts in batches

If a large number of objects need to be moved, it is unrealistic to write a script in one row. You can use the following script to generate a moving script.

-- Generate a LOBSEGMENT moving statement to move the LOBSEGMENT object of USR_A from the tablespace TBS_OLD to TBS_NEW. After the generated statement is executed, the corresponding LOBINDEX object will also be moved to TBS_NEW

1. Select 'Alter table'| Owner |'.'| Table_name |'Move LOB ('| Column_name |') Store as (tablespace TBS_NEW );' FromDba_lobsWhereOwner ='Usr _' AndTablespace_name ='Tbs _ old';

-- To move the table where the LOB segment is located along with the LOB segment to the new tablespace, use the following statement to generate a script

1. Select 'Alter table'| Owner |'.'| Table_name |'Move tablespace TBS_NEW LOB ('| Column_name |') Store as (tablespace TBS_NEW );' FromDba_lobsWhereOwner ='Usr _' AndTablespace_name ='Tbs _ old';

-- Generate scripts for moving USR_A tables and indexes. Sort the scripts to move the tables first, and execute the scripts for moving indexes in sequence; otherwise, moving the index first and then the table will cause the index to become invalid.

1. Select 'Alter'| Segment_type |'Usr_a .'| Segment_name |''| Decode (segment_type,'Table','Move','Index','Rebuilt') |''|'Tablespace TBS_NEW ;' FromDba_segmentsWhereOwner ='Usr _' AndTablespace_name ='Tbs _ old' AndSegment_typeIn('Table','Index')Order BySegment_typeDesc;

-- You can use the following statement to check invalid index information in the dba_indexes data dictionary table.

1. Select*FromDba_indexesWhereStatus ='Unusable';

Iii. query statements that may be used

There are also some statements that may be used to query the data object information in the table space.

-- Count the number of USR_A Data Objects

1. Select Count(*)FromDba_segmentsWhereOwner ='Usr _';

-- View the tablespace name distributed by USR_A's Data Objects

1. Select DistinctTablespace_nameFromDba_segmentsWhereOwner ='Usr _';

-- View the LOB segment and LOB index object of USR_A

1. Select*FromDba_segmentsWhereOwner ='Usr _' AndSegment_typeIn('Lobsegment','Lobindex');

-- Calculate the number of data objects of USR_A by tablespace and segment type

1. SelectTablespace_name, segment_type,Count(*)FromDba_segmentsWhereOwner ='Usr _';
2. Group ByTablespace_name, segment_type;
3. Order ByTablespace_name, segment_type;