How to migrate Oracle table spaces

The portable tablespace is defined to move data from one db to another. The basic concept of migration is to remove the definition of table and table space from the data dictionary, copy the data files under the table space to the directory of the target server, and then load the table and table space definitions. This approach can move a large amount of data from one db to another, which is a faster and more efficient method. Both tables and indexes can be migrated using this method.

Restrictions on migration:

1. The source and target db must be oracle8i above version;

2. The source and target DB must be on the same hardware and OS platform;

3. The block size of the source and target DB should be the same;

4. There is no table space in target DB with the same name as the table space to migrate in source db;

5. The tablespace to be migrated should be a self-contained tablespace, in which objects in the tablespace cannot refer to objects in other table spaces. For example, a tablespace contains an index in which the table should also be in the table space;

6. Table spaces to migrate if you include partitioned tables, all partitions must be in the tablespace.

Steps to migrate:

1. Check to see if the table space is self-contained:

Execute Sys.dbms_tts.transport_set_check (' tablespace_name1,... ', true);

Select * from Sys.transport_set_violations;

If the "No rows selected" is displayed when the select is finished, the table space is self-contained.

2. Set table space to read-only

Alter tablespace Tablespace_name Read only;

3. Use export to generate meta data to unload a file

Exp file=xx.dmp transport_tablespace=y tablespaces= (tablespace_name,...) constraints=n

4. Query the data file for this table space

Select File_name,tablespace_name from Dba_data_files where tablespace_name= ' tablespace_name ';

5. Copy data files to the appropriate directory in target DB

6. Modify Tablespace to be read-write

Alter tablespace Tablespace_name Read write;

7. Importing with Import

Imp file=xxx.dmp transport_tablespace=y tablespaces=tablespace_name datafiles= ('/path ') fromuser= (' user_name ') Touser=

(' user_name ')

Only the Transport_tablespace and datafiles statements must be provided. If tablespaces or tts_owners are not specified, the values of these keywords are considered to be

Keyword is the same in a piece. If Fromuser and Touser are not specified, Oracle loads these objects into users who have the same user name as those in the source db. A user with the same name must already exist in the target db.

8. Change the tablespace to read/write

Alter tablespace Tablespace_name Read write;

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/