How to migrate different types of data across tablespaces

Http://blog.csdn.net/passion_wang/article/details/6541369

Oracle 10 GB Data migration across tablespaces

Because some developers do not have in-depth understanding of the Oracle database, they usually specify non-default tablespaces when creating tables, this causes many problems during operations such as exp and imp. Therefore, you need to migrate these tables and related data back to the default tablespace of the current user. the Oracle10g data database provides a move command to migrate such data objects across tablespaces, or to move tables with large binary fields such as blob and clob. However, the move command does not support data tables with long fields. In this case, we can also use the copy command for data migration, which will not be detailed here.

First, we need to find the tablespace name and related information.
Select username, default_tablespace from dba_users where default_tablespace not in ('sysaux ', 'system', 'users ');
Username default_tablespace
------------------------------------------------------------
Ydmm
Ydsk

 
Query the owners of different tablespaces and their data objects.
Select distinct owner from dba_extents where tablespace_name = 'ymmm ';
Owner
---------
Ydmm
Ydsk

Select distinct owner from dba_extents where tablespace_name = 'dsk ';
Owner
---------
Ydsk

From the above we can find that the ydmm tablespace also stores the data of ydsk users. Next we will find the data objects that ydsk users have in the ydmm tablespace, migrate all data objects under the ydsk user to the corresponding default tablespace ydsk.
Select owner, segment_name, partition_name, segment_type from dba_segments where tablespace_name = 'ymmdm' and owner = 'dsk ';

Generally, segment_type mainly includes table, index, table partition, index partition, lobsegment, lobindex, and LOB partition. Next we will migrate data segments of these types.
-- Move Table T1 to the ydsk tablespace
Alter table T1 move tablespace ydsk;
-- Move the index idx_t1 of table T1 to the ydsk tablespace.
Alter index idx_t1 rebuild tablespace ydsk;
-- Move the table T2 to the ydsk tablespace containing blob and clob Fields
Alter table T2 move tablespace ydsk lob (blob1, blob2) store as (tablespace ydsk);/where blob1 and blob2 are BLOB fields contained in table T2, which are similar to clob,
Syntax for Moving Blob and clob Fields
-- Alter table table_name move [tablespace tbs_name] lob (lob_field1, lob_field2) store as (tablespace new_tbs_name );
-- If the lob field is in the Partition Table, add the partition keyword, as shown in figure
-- Alter table table_name move [partition partname] [tablespace tbs_name] lob (field) store as (tablespace new_tbs_name );

Of course, if there are many data objects, we can build SQL scripts for migration.
Based on the Data Segment types listed above, the move command statement for constructing data objects across tablespaces is as follows.
Set heading off;
Set echo off;
Set feedback off;
Set termout on;
Spool/home/Oracle/move_table. SQL;
-- Move a table
Select distinct 'alter table ydsk. '| segment_name | 'move tablespace ydsk;' from dba_extents where segment_type = 'table' and tablespace_name = 'ydmm' and owner = 'ydsk ';
-- Move Index
Select distinct 'alter index ydsk. '| segment_name | 'rebuild tablespace ydsk;' from dba_extents where segment_type = 'index' and tablespace_name = 'ymmdmm' and owner = 'dsk ';
-- If a partition table exists, you need to move the index of the partition table and partition table.
-- Move a partition table
Select distinct 'alter table ydsk. '| segment_name | 'move partition' | partition_name | 'tablespace ydsk; 'From dba_extents where segment_type = 'table partition' and tablespace_name = 'ymmd' and owner = 'dsk ';
-- Move the partition Index
Select distinct 'alter index ydsk. '| segment_name | 'rebuild partition' | partition_name | 'tablespace ydsk; 'From dba_extents where segment_type = 'index partition' and tablespace_name = 'ymmd' and owner = 'dsk ';

Spool off;
Log on to the database as sys/system and run move_table. SQL to migrate data.

As mentioned above, the move command does not support data tables with long fields. How can we migrate such tables? In fact, it is very simple to use a copy command. Let's take a look at the usage and advantages of the Copy command:
The Copy command syntax is as follows:
Copy From user/[email protected] to user/[email protected] Create/append/insert/replace table_name (column_name ,......) Using select * From table_name;
Briefly describe the meanings of each clause in the above Syntax:
From and to statements: describe which database to copy from, from or to point to the current database, which can be ignored, but cannot be ignored at the same time.
Create/append/insert/Replace: four options of the Copy command, the function is to create a table and insert data, insert new data after the records of an existing table, insert data into an empty table, and delete the table. Then, create a new table and insert new data.
Table_name (column_name ,......) : The copied table name and column name. If the column name list is omitted, the column name will be determined based on the query statement below.
Using select * From table_name: Write the query results of the table to the previous table according to the specified method.

The Copy command has the following advantages:
1. Support for the Long TYPE: Both create table as and insert into select do not support the long type. This causes a lot of trouble to process long data, while the method of using stored procedures is relatively troublesome. Using exp and IMP has many restrictions. Using copy is relatively simple and quick.
2. You do not need to create a database chain, as long as the remote database is configured in the local tnsnames. ora, you can directly access the database. Easy to operate. It also supports copy from the local database to the local database.
3. The syntax is flexible. The name of the target table does not match the name in the query statement, and there are multiple data processing methods.

Next we will make a simple example to perform the copy operation. (T3 is a table containing long fields)
Create a table T3 containing long Fields
SQL> Create Table T3 (ID number, default_value long );
Create Table
SQL> Create Table T as select * from T3;
Create Table T as select * from T3
*
Error is located in row 1st:
ORA-00997: Invalid use of long data type

Insert
SQL> Create Table T (ID number, default_value long );
SQL> insert into T select ID, data_default from T3;
Insert into T select ID, data_default from T3
*
Error is located in row 1st:
ORA-00997: Invalid use of long data type
From the above, the common table replication method is not good. Next we will use the copy command to copy
SQL> drop table t;
The table is discarded.
SQL> copy from ydmm/[email protected] To ydsk/[email protected] Create t using select * from T3;
The size of the array to read/combine is 15. (Array size: 15)
It will be submitted upon completion. (The number of copies submitted is 0)
The maximum value is 80. (Length: 80)
Table T has been created.
The row 0 is selected from [email protected].
0 rows are inserted with T.
Row 0 has been submitted to T (located in [email protected]).

The size of each data array read is determined by the set arraysize parameter. The data volume submitted each time is controlled by the copycommit parameter of set. Whether the copy type is checked is controlled by the copytypecheck parameter of set. During copy execution, the long truncation length is controlled by the long parameter of set. If you do not want the long type to be truncated, make sure that the long value exceeds the maximum value of the long type in the table.
SQL> set arraysize 1000
SQL> set copycommit 1000
SQL> set copytypecheck off
SQL> set long 100000
SQL> copy from ydmm/[email protected] To ydsk/[email protected] Create t using select * from T3;
The size of the array to be read/combined is 1000. (The array size is 1000)
It will be submitted after every 1000 arrays are combined. (The submitted duplicate is 1000)
It can be up to 100000. (Length: 100000)
The row 0 is selected from [email protected].
0 rows are inserted with T.
Row 0 has been submitted to T (located in [email protected]).

When using the Copy command, try to operate only your own tables. If you want to perform the replace operation on a table under another user, only the insert and delete permissions of the table do not apply, you must have system permissions for create any table, drop any table, and insert any table.