[Transfer tablespace] use the EXPDP/IMPDP tool's transfer tablespace to complete data migration

[Transfer tablespace] use the EXPDP/IMPDP tool's transfer tablespace to complete data migration

This article demonstrates how to use the EXPDP/IMPDP tool to completely simulate the process of transferring tablespaces for your reference.
Task Description: transfers the data in the tbs_sec tablespace of the sec user on the secdb1 instance to the secooler user on the secdb2 instance.

1. secdb1 instance Environment preparation
1) Create the tablespace to be operated
Sys @ secdb1> create tablespace tbs_sec datafile '/u01/app/Oracle/oradata/secdb1/dfile/tbs_sec_01.dbf' size 5 m autoextend on;

Tablespace created.

2) re-create an sec user and authorize
Sys @ secdb1> drop user sec cascade;

User dropped.

Sys @ secdb1> create user sec identified by sec default tablespace tbs_sec;

User created.

Sys @ secdb1> grant connect, resource to sec;

Grant succeeded.

3) create a table and initialize a record under the sec user.
Sys @ secdb1> conn sec/sec
Connected.
Sec @ secdb1> create table t (x int );

Table created.

Sec @ secdb1> insert into t values (1 );

1 row created.

Sec @ secdb1> commit;

Commit complete.

Sec @ secdb1> select * from t;

X
----------
1

2. Check whether the table space in tbs_sec is "self-contained"
Sys @ secdb1> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK ('tbs _ sec ', true );

PL/SQL procedure successfully completed.

Sys @ secdb1> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

No rows selected

OK, no problem.

3. Adjust the tablespace to the read-only status.
Sys @ secdb1> alter tablespace tbs_sec read only;

Tablespace altered.

4. Use the EXPDP tool to export table space element data
Sys @ secdb1> create directory dir_home as '/home/oracle ';

Directory created.

Sys @ secdb1> grant read, write on directory dir_home to public;

Grant succeeded.

[Oracle @ seclinux ~] $ Expdp system/oracle1 directory = dir_home dumpfile = tbs_sec.dmp transport_tablespaces = tbs_sec transport_full_check = y

Export: Release 10.2.0.1.0-Production on Saturday, 23 July, 2010 22:23:21

Copyright (c) 2003,200 5, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production
With the Partitioning, OLAP and Data Mining options
Starting "SYSTEM". "SYS_EXPORT_TRANSPORTABLE_01": system/******** directory = dir_home dumpfile = tbs_sec.dmp transport_tablespaces = tbs_sec transport_full_check = y
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM". "SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
**************************************** **************************************
Dump file set for SYSTEM. SYS_EXPORT_TRANSPORTABLE_01 is:
/Home/oracle/tbs_sec.dmp
Job "SYSTEM". "SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 22:23:39

5. copy the data file corresponding to the table space of tbs_sec to the directory corresponding to the secdb2 instance.
[Oracle @ seclinux ~] $ Cp/u01/app/oracle/oradata/secdb1/dfile/tbs_sec_01.dbf/u01/app/oracle/oradata/secdb2/tbs_sec_01.dbf

6. Use the IMPDP tool on the secdb2 instance to import the tablespace.
Sys @ secdb2> create user secooler identified by secooler;

User created.

Sys @ secdb2> grant connect, resource to secooler;

Grant succeeded.

Sys @ secdb2> create directory dir_home as '/home/oracle ';

Directory created.

Sys @ secdb2> grant read, write on directory dir_home to public;

Grant succeeded.

[Oracle @ seclinux ~] $ Impdp system/oracle1 DUMPFILE = tbs_sec.dmp DIRECTORY = dir_home TRANSPORT_DATAFILES =/u01/app/oracle/oradata/secdb2/tbs_sec_01.dbf REMAP_SCHEMA = (sec: secooler)

Import: Release 10.2.0.1.0-Production on Saturday, 23 July, 2010 22:34:48

Copyright (c) 2003,200 5, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production
With the Partitioning, OLAP and Data Mining options
Master table "SYSTEM". "SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM ". "plugin": system/******** DUMPFILE = tbs_sec.dmp DIRECTORY = dir_home TRANSPORT_DATAFILES =/u01/app/oracle/oradata/secdb2/tbs_sec_01.dbf REMAP_SCHEMA = (sec: secooler)
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM". "SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 22:34:52

7. verify the data import effect on the secdb2 instance
Sys @ secdb2> conn secooler/secooler
Connected.
Secooler @ secdb2> select * from cat;

TABLE_NAME TABLE_TYPE
-----------------------------------------
T TABLE

Secooler @ secdb2> select * from t;

X
----------
1

Sys @ secdb2> select owner, tablespace_name from dba_tables where wner = 'secoler' and table_name = 'T ';

OWNER TABLESPACE_NAME
------------------------------------------------------------
SECOOLER TBS_SEC

OK. The mission is complete.

8. Finally, remember to set the tbs_sec tablespace in the secdb1 instance to read/write mode.
Sys @ secdb1> alter tablespace tbs_sec read write;

Tablespace altered.

9. Summary
You can use the EXPDP/IMPDP to transfer tablespaces for convenient and efficient data migration.
For more information about how to use the EXP/IMP tool to transfer tablespaces, see the following article.
[Experiment] efficient data migration using table space transfer technology ()