Oracle move table or move index ____oracle

The origin of a table space is too large, after the deletion of data due to the end of the file is used, can not resize, intend to all the table space on the object move to a temporary storage table space to do collation.

When you move a table to another table space, the index does not follow together and will fail. (LOB type Exception) table move, we are divided into:
* Normal table move
* Partition Table move
*long,lob a large segment type move to test and explain.

The move of the index, we implement by 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

One: Move normal table, index basic syntax:
ALTER TABLE tab_name move tablespace tbs_name;

The normal table in move, where the statement executes normally in the action statement without the failed index, but if the operation's statement uses the index (primary key as a unique index), then the report uses the

Index invalidation, statement execution failed, other such as foreign key, non-empty constraint, default value, etc. will not be invalidated.

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 a move index, we use the rebuild syntax:
Alter index index_name rebuild tablespace tbs_name;
Alter index PK_NAME rebuild tablespace tbs_name;

Hint: The index that the query table has, you can use the User_indexes view (both index and primary key are found in this view).

Two: Move partition table and index and normal table, index will be invalid, the difference is only syntax.

Partitioning basic syntax: Note that if it is a single-stage partition, use the keyword partition, and if it is a multilevel partition, use subpartition instead of partition. If the partition or partition index is larger, you can use parallel move or Rebuild,parallel (DEGREE 2), such as:
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);

To move a partition of a table:
================
ALTER TABLE tab_name Move PARTITION partition_name tablespace tbs_name;

To rebuild a global index:
================
ALTER INDEX Global_index REBUILD;
ALTER INDEX global_index REBUILD tablespace tbs_name;

To rebuild a local index:
================
ALTER TABLE tab_name MODIFY PARTITION partition_name REBUILD unusable local INDEXES;
ALTER INDEX local_index_name REBUILD PARTITION partition_name tablespace tbs_name;

Tips:
User_part_tables
User_ind_partitions
User_ind_subpartitions
User_lob_partitions
User_lob_subpartitions
User_part_indexes
User_part_lobs can query partition related content, at the same time, partition object, also be segment, so also can in dba_segments Richard to.

Three: Move Long,lob type It is said that the Dbms_redefinition package can provide some convenience and no use.

I:long type
A long type cannot transmit special hints through move, and it is difficult to manage with a long type as far as possible. Reference: http://www.anysql.net/2005/12/long_vs_lob.html
1,long cannot use INSERT INTO ... select ... Wait for the pattern with select. Such as
CREATE TABLE t123 (ID int,en long);
Insert into t123 (id,en) SELECT * from t123, report errors that can be pl/sql to help resolve, such as:
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 a transfer of a table with a long field, you can use:
Create a new table method.
* Create a new table, stored in the table space that needs to be transferred.
* Create a new index (use the TABLESPACE clause to specify a new tablespace).
* Transfer the data. Method one: Using the Copy method:
Copy from Bigboar/bigboar@bigboar_sid Insert t123 (id,en) using a select Id,en from t123; method Two: pl/ SQL (as above) method three: Directly convert long to CLOB type
CREATE TABLE t321 (ID int,en clob) tablespace users;
Insert into t321 (id,en) Select Id,to_lob (en) from t123; Method Four: Exp/imp
Exp Bigboar/bigboar File=a.dat tables=t123
Imp bigboar/bigboar file=a.dat full=y IGNORE =y
* Drop off the old watch.
* Rename the new table is the name of the old table table.

Ii:lob type when you create a table with LOB fields, Oracle automatically establishes two separate segment for the LOB field, one to hold the data (segment_type=lobsegment), and the other to store the

Drop Index (SEGMENT_TYPE=LOBINDEX). By default, they are stored in table spaces with tables. When we move to a table, the Log Type field and the index of the field do not follow move, and you must move it separately, as follows:
ALTER TABLE t321 move tablespace users;
ALTER TABLE t321 move lob (en) store as (tablespace users);