Oracle getting started Tutorial: Moving data objects between tablespaces

Source: Internet
Author: User

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;

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.