Oracle Staging table space and index ____oracle

In the day-to-day process of dealing with Oracle databases, you may encounter a situation in which a non-SYS or system segment is built in the system tablespace. For a long time, as the volume of data surges, the database will become more and more slow, the author in the actual work encountered this problem. A number of users of the table all built the system table space, really sigh at that time to create a database of the big, so on that transfer table space to do a summary, and share with you.

In fact, like transfer table space and index of the statement on the Internet, I will take every step I do and the problems encountered to write it.

First, the table space is transferred, and the statement is as follows:

ALTER TABLE user move tablespace newspace;

Use: SELECT ' ALTER TABLE ' | | table_name| | ' Move tablespace newspace; ' From User_tables where owner= ' Scott '; spell out the statements that transfer the user's table space, not to mention, as we all know.

The statement is then copied out of execution.

Then, transfer the index with the following statement:

Alter index Idx_user rebuild tablespace newspace;

Use: SELECT ' Alter index ' | | index_name| | ' rebuild tablespace newspace; ' From user_indexes where owner= ' Scott ';

In fact, for the index is best and the table is not in the same table space, so that the database and later query efficiency better. In addition, for the index, it is best to do a periodic refactoring, before refactoring, it is best to first analyze the index which is not often used, which is often used, and for a table how to build the index more appropriate.

Execute the concatenation of the SQL statement, but found that some of the statements did not execute successfully. Checking for unsuccessful statements suddenly found an index: Dmj-dwx-pk_1, cannot be transferred, repeated attempts are not possible. It was later found that the index could not contain the '-' field, or Oracle thought it was an option, which was not allowed. Unfortunately, this index is also a primary key index (Oracle defaults to the primary key index), helpless, deleted the reconstruction. Disabling or removing a primary key must be used in conjunction with the ALTER TABLE statement as follows:

ALTER TABLE policies DROP PRIMARY KEY;
Or
ALTER TABLE policies DISABLE PRIMARY KEY;
Increases the primary key ALTER TABLE DMJ_DWX ADD constraint Dmj_dwx_pk_1 primary key (Bzh, P);

It was later found that some indexes were unable to transfer tablespace, so the table looking for this index found a BLOB type field in the table, using ALTER index IDX_USER rebuild tablespace newspace; This belongs to the LOB type index transfer.

In Oracle, adding LOB columns adds two more distinctly system-named segment objects, the Lobsegment and Lobindex, the LOB segment and the LOB index segment, respectively.

The LOB segment (lobsegment) corresponds to the data stored on the LOB column of the datasheet. In Oracle's LOB type data columns, there are two save location structures. One is In-row storage, where the LOB data for each row is saved in the data block with the data of the other columns in rows. The LOB column values in this case are smaller. And the other is Out-of-row storage, when the LOB object is large and cannot be saved in a block of data, it can be stored in a separate lobsegment. While Out-of-row storage, the LOB column in the data row holds only a pointer reference to the corresponding position of the lobsegment.
The LOB index segment (LOBINDEX) is the index that Oracle enforces for each LOB type column, and is used primarily for the accelerated operation of LOB type data retrieval. Lobindex is associated with LOB columns, and an error is made if a delete operation is forced.

When the table T is transferred to another table space, the LOB segment and the corresponding Lobindex segment do not change. If you need to move lob/lobindex, you need extra separate operations. Lobindex cannot be reconstructed directly by using rebuild.

Using ALTER TABLE XXX move lob (XX) store as xxx (tablespace xxx), command, we can adjust the location of the LOB column storage. Note: xx in LOB (XX) is the column name of the LOB type in the table.

The following tests can be done to see the modification process:

-----Find the lobindex,lobsegment of the table first

Select Table_name,column_name,segment_name,index_name from user_lobs where table_name = ' CLDE_XCJS ';

Results: Clde_xcjs GRAPH sys_lob0000009622c00003$$ sys_il0000009622c00003$$
------Modify table spaces for LOB types in a table
ALTER TABLE CLDE_XCJS move lob (GRAPH) store as sys_lob0000009622c00003$$ (tablespace idata);;
----View Changes
Select Segment_name, Segment_type, tablespace_name from user_segments
where segment_name = ' sys_lob0000009622c00003$$ ' or segment_name = ' Clde_xcjs ' or
segment_name = ' sys_il0000009622c00003$$ ';

You can check the field before the change of the table space and then modify and then check, contrast.