Table sorting: Move Table or index summary

Table sorting: Move Table or index summary

The reason is that a tablespace is too large. After the data is deleted, it cannot be resize because the end of the file is used. We plan to move all the objects in the tablespace to a temporary storage tablespace for sorting.

Writing procedure uses utl_file to automatically generate sorting scripts ......

When you move a table to another tablespace, the index will not be moved together and will become invalid. (Lob type exceptions)
Table move, which is divided:
* Move a common table
* Move a partition table
* Long, lob large field type move
To test and describe.

Index move, which is implemented through rebuild

SQL> select * from V $ version;

Oracle9i Enterprise Edition Release 9.2.0.6.0-64bit Production
PL/SQL release 9.2.0.6.0-Production
Core 9.2.0.6.0 Production
TNS for Solaris: Version 9.2.0.6.0-Production
Nlsrtl version 9.2.0.6.0-Production

I. Move common tables and Indexes
Basic Syntax:
Alter table tab_name move tablespace tbs_name;

In normal tables that have been moved, the statement runs normally without any invalid index. However, if the statement uses an index (the primary key is used as a unique index), the report uses

The index is invalid, and the statement execution fails. Other statements, such as foreign keys, non-null constraints, and default values, are not invalid.

We need to recreate the primary key or index. The basic syntax is:
Alter index index_name rebuild;
Alter index pk_name rebuild;

If we need to move the index, use the rebuild Syntax:
Alter index index_name rebuild tablespace tbs_name;
Alter index pk_name rebuild tablespace tbs_name;

Tip:
To query the indexes of a table, you can use the user_indexes view (both the index and primary key can be found in this view ).

 

2. Move the partition table and Index
Like a normal table, indexes will become invalid. The difference is just syntax.

Basic partition Syntax:
Note: If the partition is a single-level partition, use the keyword partition. If the partition is a multi-level partition, use subpartition instead of partition.
If the partition or partition index is large, you can use parallel move or rebuild, parallel (degree 2 );
For example:
Alter table part_alarmtextdata move subpartition alarmtextdata_050910_atd01 tablespace users parallel (degree 2 );
Alter index global_alarmtextdata rebuild tablespace users parallel (degree 2 );
Alter index local_alarmtextdata rebuild subpartition alarmtextdata_050910_atd01 tablespace users parallel (degree 2 );

Move a partition of a table:
======================
Alter table tab_name move partition partition_name tablespace tbs_name;

Re-create global index:
======================
Alter index global_index rebuild;
Or
Alter index global_index rebuild tablespace tbs_name;

Rebuilding a local index:
======================
Alter table tab_name modify partition partition_name rebuild unusable local indexes;
Or
Alter index local_index_name rebuild partition partition_name tablespace tbs_name;

Tip:
User_part_tables
User_ind_partitions
User_ind_subpartitions
User_lob_partitions
User_lob_subpartitions
User_part_indexes
User_part_lobs
You can query partition-related content. At the same time, the partition object is also a segment, so you can also find it in dba_segments.

Iii. Move long and LOB type
It is said that the dbms_redefinition package provides some convenience and has never been used.

I: Long TYPE
The long type cannot be transmitted through move.
It is especially difficult to manage long type.
Reference: http://www.anysql.net/2005/12/long_vs_lob.html
1. Long cannot use the insert into... select... and other select modes.
For example
Create Table t123 (ID int, en long );
Then
Insert into t123 (ID, en) Select * From t123;
Report errors, which can be solved by PL/SQL, such:
Declare
Cursor cur_t123 is select * From t123;
Use_t123 cur_t123 % rowtype;
Begin

Open cur_t123;
Loop
Fetch cur_t123 into use_t123;
Exit when cur_t123 % notfound;

Insert into t123 (ID, en) values (use_t123.id, use_t123.en );

End loop;
Close cur_t123;

End;
/

For tables with long fields, you can use:
Create a new table.
* Create a new table, which is stored in the tablespace to be transferred.
* Create an index (use the tablespace clause to specify a new tablespace ).
* Transfer Data
Method 1: use the copy method:
Copy From bigboar/bigboar @ bigboar_sid insert t123 (ID, en) using select ID, en from t123;
Method 2: PL/SQL (as shown above)
Method 3: convert long to clob type directly.
Create Table T321 (ID int, en clob) tablespace users;
Insert into T321 (ID, en) Select ID, to_lob (en) from t123;
Method 4: exp/imp
Exp bigboar/bigboar file = A. dat tables = t123
IMP bigboar/bigboar file = A. dat full = y ignore = y
* Drop the old table.
* The New rename table is the name of the old table.

 

II: Lob type
When creating a table containing the lob field, Oracle automatically creates two separate segments for the lob field, one for storing data (segment_type = lobsegment), and the other for storing

Index (segment_type = lobindex ). By default, they are stored in the tablespace with the table.
When we move a table, the index of the log type field and the field will not be moved. We must move it separately. The syntax is as follows:
Alter table T321 move tablespace users;
Alter table T321 move lob (en) store as (tablespace users );