Shulou(Shulou.com)05/31 Report--

How to understand ORACLE MOVE tablespaces, I believe that many inexperienced people do not know what to do, so this paper summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

When you move a table to another table space, the index does not move with it and is invalidated (with the exception of the LOB type). Before making a tablespace transfer, the user of the transferred table must have permission to use the tablespace on the destination tablespace, otherwise an error will be reported: ORA-01950: no privileges on tablespace...

Table move is divided into

1. Normal form move

two。 Partition table move

3.LONG

4.LOB large field type move

5. The move of the index is realized by rebuild.

1. Move ordinary tables and indexes

1. Basic grammar:

A 、 alter table table_name tmove tablespace xxx

B 、 alter index index_name rebuild tablespace xxx

In the ordinary table after move, the statement executes normally in the operation statement that does not use the invalid index, but if the operation statement uses the index (the primary key is regarded as the only index), then the index used in the report is invalid, the statement execution fails, and others, such as foreign keys, non-null constraints, default values, etc., will not fail.

2. The basic syntax for recreating the primary key or index is:

A 、 alter index index_name rebuild

B 、 alter index pk_name rebuild

3. Move quotes rebuild syntax:

A 、 alter index index_name rebuild tablespace tbs_name

B 、 alter index pk_name rebuild tablespace tbs_name

II. Move partitioned tables and indexes

Like regular tables, partitioned table indexes are invalidated, and the only difference is syntax.

1. Basic syntax of partition

Note: if it is a single-level partition, use the keyword PARTITION, and 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)

Such as:

ALTER TABLE PART_ALARM move SUBPARTITION pendant 01 TABLESPACE usersPARALLEL (DEGREE 2)

-- Global index

ALTER INDEX GX1_ PART_ALARM REBUILD tablespace usersPARALLEL (DEGREE 2)

-- partitioned index

ALTER INDEX LX1_ PART_ALARM REBUILD SUBPARTITION pendant 01 TABLESPACE users1PARALLEL (DEGREE 2)

ALTER INDEX LX1_ PART_ALARM REBUILD SUBPARTITION pendant 02 TABLESPACE users2PARALLEL (DEGREE 2)

.

ALTER INDEX LX1_ PART_ALARM REBUILD SUBPARTITION pair0n TABLESPACE usersnPARALLEL (DEGREE 2)

2. Move a partition of the table

ALTER TABLE tab_name move PARTITION partition_name TABLESPACE tbs_name

3. Rebuild the global index

ALTER INDEX global_index REBUILD

Or

ALTER INDEX global_index REBUILD tablespace tbs_name

4. Rebuild the 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

User_segments

III. Move LONG type

You can use the DBMS_REDEFINITION package to provide some convenience, but you haven't used it.

The long type cannot be transmitted over MOVE. Special tips, try not to use the LONG type, very difficult to manage.

1. LONG cannot use insert into. Select... Wait for the mode with select.

Such as

Create table T123 (id int,en long)

Then

Insert into T123 (id,en) select * from T123

To report errors, you can use pl/sql to help resolve them, 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

/

2. Transfer of tables of LONG type fields

1.) the method of the new create table.

A.create A new table is stored in the tablespace that needs to be transferred.

b. Create a new index (use the tablespace clause to specify a new tablespace).

c. Transfer the data here.

2.) Using the method of COPY

Copy from bigboar/bigboar@bigboar_sid insert T123 (id,en) using select id,en from T123

3. 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

4 、 exp/imp

Exp bigboar/bigboar file=a.dat tables=t123

Imp bigboar/bigboar file=a.dat full=y IGNORE = y

A.drop dropped the old watch.

The new b.rename table is the name of the old table.

IV. LOB type

When you create a table with a lob field, oracle automatically creates two separate segment for the lob field, one for the data (segment_type=LOBSEGMENT) and the other for the segment_type=LOBINDEX. By default, they are stored in the tablespace along with the table. When we MOVE the table, the field of LOG type and the index of this field will not follow MOVE. We must do MOVE separately. The syntax is as follows:

Alter table t321 move tablespace users

Alter table T321 move lob (en) store as (tablespace users)

As we all know, when we create a table with a lob field, oracle automatically creates two separate segment for the lob field, one for the data and the other for the index, and they are stored in the table space specified by the corresponding table. However, when we use alter table tb_name move tablespace tbs_name; to do spatial migration of the table, we can only move data other than lob fields, and if we want to move the data of lob-related fields at the same time, we must use the following sentence with special parameter data:

Alter table tb_name move tablespace tbs_name lob (col_lob1,col_lob2) store as (tablesapce tbs_name)

V. examples of practical operations

1. Description

Move the tables on the SOURCE_TABLESPACE tablespace to the DEST_TABLESPACE tablespace, delete the SOURCE_TABLESPACE, free up disk space, build a smaller SOURCE_TABLESPACE tablespace, and move the table back to SOURCE_TABLESPACE.

2. Steps

1.) Check whether the tablespace table field is partitioned, including LOB field, LONG field, etc.

2.) Check what the tablespace contains (including tables, indexes, partitions, etc.)

Select segment_name,segment_type from dba_segments where tablespace_name='SOURCE_TABLESPACE'

3.) Move tables and indexes

Alter table IBSS.TB_CM_MSPARAM_HIST move tablespace DEST_TABLESPACE

Alter table IBSS.TB_CM_SPRESENT_HIST move tablespace DEST_TABLESPACE

Alter table IBSS.TB_CM_MSSRVLTD_HIST move tablespace DEST_TABLESPACE

Alter table IBSS.TB_CM_MSITEM_HIST move tablespace DEST_TABLESPACE

Alter table IBSS.TB_CM_MSOBJECT_HIST move tablespace DEST_TABLESPACE

Alter table IBSS.TB_CM_MSOBJGRP_HIST move tablespace DEST_TABLESPACE

Alter table IBSS.TB_CM_SERVACCT_HIST move tablespace DEST_TABLESPACE

Alter table IBSS.TB_CM_BANKACCT_HIST move tablespace DEST_TABLESPACE

Alter table IBSS.TB_CM_ACCT_HIST move tablespace DEST_TABLESPACE

Alter table IBSS.TB_CM_MSINFO_HIST move tablespace DEST_TABLESPACE

Alter table IBSS.TB_CM_MSITEMR_HIST move tablespace DEST_TABLESPACE

Alter index IBSS.IX_CM_MSINFOUST rebuild tablespace DEST_TABLESPACE

Alter index IBSS.IX_CM_MSINMSINF rebuild tablespace DEST_TABLESPACE

Alter index IBSS.IX_CM_MSIN _ DISC rebuild tablespace DEST_TABLESPACE

Alter index IBSS.IX_CM_MSITEMNG rebuild tablespace DEST_TABLESPACE

Alter index IBSS.IX_CM_MSOBJE ID rebuild tablespace DEST_TABLESPACE

Alter index IBSS.IX_CM_M_MSINFO rebuild tablespace DEST_TABLESPACE

Alter index IBSS.IX_CM_MSOBJET rebuild tablespace DEST_TABLESPACE

Alter index IBSS.IX_CM_MSPRINFO rebuild tablespace DEST_TABLESPACE

4.) Delete tablespace SOURCE_TABLESPACE

DROP TABLESPACE HPMDBS1 INCLUDING CONTENTS and DATAFILES

5.) Recreate the tablespace

CREATE TABLESPACE HPMDBS1 DATAFILE

'/ opt/oracle/oradata1/tbs0101.dbf' SIZE 6192 M AUTOEXTEND OFF

6) Re-move tables and indexes to the original space

Alter table IBSS.TB_CM_MSPARAM_HIST move tablespace SOURCE_TABLESPACE

Alter table IBSS.TB_CM_SPRESENT_HIST move tablespace SOURCE_TABLESPACE

Alter table IBSS.TB_CM_MSSRVLTD_HIST move tablespace SOURCE_TABLESPACE

Alter table IBSS.TB_CM_MSITEM_HIST move tablespace SOURCE_TABLESPACE

Alter table IBSS.TB_CM_MSOBJECT_HIST move tablespace SOURCE_TABLESPACE

Alter table IBSS.TB_CM_MSOBJGRP_HIST move tablespace SOURCE_TABLESPACE

Alter table IBSS.TB_CM_SERVACCT_HIST move tablespace SOURCE_TABLESPACE

Alter table IBSS.TB_CM_BANKACCT_HIST move tablespace SOURCE_TABLESPACE

Alter table IBSS.TB_CM_ACCT_HIST move tablespace SOURCE_TABLESPACE

Alter table IBSS.TB_CM_MSINFO_HIST move tablespace SOURCE_TABLESPACE

Alter table IBSS.TB_CM_MSITEMR_HIST move tablespace SOURCE_TABLESPACE

Alter index IBSS.IX_CM_MSINFOUST rebuild tablespace SOURCE_TABLESPACE

Alter index IBSS.IX_CM_MSINMSINF rebuild tablespace SOURCE_TABLESPACE

Alter index IBSS.IX_CM_MSIN _ DISC rebuild tablespace SOURCE_TABLESPACE

Alter index IBSS.IX_CM_MSITEMNG rebuild tablespace SOURCE_TABLESPACE

Alter index IBSS.IX_CM_MSOBJE ID rebuild tablespace SOURCE_TABLESPACE

Alter index IBSS.IX_CM_M_MSINFO rebuild tablespace SOURCE_TABLESPACE

Alter index IBSS.IX_CM_MSOBJET rebuild tablespace SOURCE_TABLESPACE

Alter index IBSS.IX_CM_MSPRINFO rebuild tablespace SOURCE_TABLESPACE

7.) Check that the original table and index are correct, and check that the tablespace index is invalid.

SELECT index_name,index_type, STATUS,partitioned, table_name

FROM User_Indexes

Where status='UNUSABLE'

6. Commonly used SQL

This method can be used to free up a large amount of disk space occupied by table space, which is troublesome if there are too many data objects. You can use the following SQL for batch processing.

1. Rebuild the spliced sql statement of partition / normal table and index

SELECT 'ALTER INDEX' | | t1.owner | |'. | | segment_name | | 'REBUILD' | |''| |

CASE WHEN t1.segment_type = 'INDEX PARTITION' THEN' PARTITION' | | partition_name

WHEN t1.segment_type = 'INDEX SUBPARTITION' THEN' SUBPARTITION' | | partition_name

ELSE''

END

| | 'tablespace' | |'& DEST_TABLESPACE' | |';'|

FROM dba_segments t1

WHERE tablespace_name='&SOURCE_TABLESPACE' AND segment_type IN ('INDEX','INDEX PARTITION','INDEX SUBPARTITION')

UNION

SELECT 'ALTER TABLE' | | t1.owner | |'. | | segment_name | | 'MOVE' | |''| |

CASE WHEN t1.segment_type = 'TABLE PARTITION' THEN' PARTITION' | | partition_name

WHEN t1.segment_type = 'TABLE SUBPARTITION' THEN' SUBPARTITION' | | partition_name

ELSE''

END

| | 'tablespace' | |'& DEST_TABLESPACE' | |';'|

FROM dba_segments t1

WHERE tablespace_name='&SOURCE_TABLESPACE' AND segment_type IN ('TABLE','TABLE PARTITION')

ORDER BY 1 DESC

2. Transfer LOB field SQL

SELECT 'ALTER TABLE' | | t2.owner | |'. | | t2.table_name | | 'move lob (' | | t1.segment_name | |')'| | 'STORE AS (' | | 'tablespace' | | & end_tablespace | |');'

FROM dba_segments t1,dba_lobs t2

WHERE t1.segment_name=t2.segment_name AND tablespace_name='&source_tablespace' AND segment_type IN ('LOBINDEX','LOBSEGMENT','LOB PARTITION')

After reading the above, have you mastered how to understand ORACLE MOVE tablespaces? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.