Move Table, index, lob, long

Move Table, index, lob/long [ZT]

 

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 and LOB large field types move for testing and description.

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. Basic syntax for moving common tables and indexes:
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 used an index (the primary key is used as a unique index ), in this case, the indexes used in the report are 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: You can use the user_indexes view to query the indexes of a table (the indexes and primary keys can be found in this view ).

2. Moving partitioned tables and indexes are the same as normal tables, and indexes will become invalid. The difference is just syntax.

Basic partition Syntax: Note that if it is a single-level partition, use the keyword partition. If it 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;
[Xsb Note: You can use the with update global indexes option to update the global index during partitioning.]

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 can be used to query partition-related content. At the same time, the partition object is also a segment, so it can also be found 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 very difficult to manage the 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 error, which can be solved by PL/SQL, for example:
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: 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 ), the other is used to store the 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 );