Move data objects between tablespaces
I. BASIC script
Moving objects between tablespaces in the Oracle database involves multiple types of data objects.
Move Table:
- Alter TableT_aMoveTablespace tbs_a;
Mobile index:
- Alter IndexI _a rebuild tablespace tbs_a;
Mobile LOBSEGMENT:
- 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:
- 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
- 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
- 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.
- 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.
- 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
- Select Count(*)FromDba_segmentsWhereOwner ='Usr _';
-- View the tablespace name distributed by USR_A's Data Objects
- Select DistinctTablespace_nameFromDba_segmentsWhereOwner ='Usr _';
-- View the LOB segment and LOB index object of USR_A
- Select*FromDba_segmentsWhereOwner ='Usr _' AndSegment_typeIn('Lobsegment','Lobindex');
-- Calculate the number of data objects of USR_A by tablespace and segment type
- SelectTablespace_name, segment_type,Count(*)FromDba_segmentsWhereOwner ='Usr _';
- Group ByTablespace_name, segment_type;
- Order ByTablespace_name, segment_type;