14.4 use expdp and impdp to move a tablespace

Determine which platforms can move tablespaces:

SQL> select platform_name from V $ transportable_platform;

Platform_name
----------------------------------------
Solaris [Tm] OE (32-bit)
Solaris [Tm] OE (64-bit)
Microsoft Windows Ia (32-bit)
Linux Ia (32-bit)
Aix-based systems (64-bit)
HP-UX (64-bits)
HP Tru64 UNIX
HP-UX Ia (64-bit)
Linux Ia (64-bit)
HP Open VMS
Microsoft Windows Ia (64-bit)
IBM zseries based Linux
Linux 64-bit for AMD
Apple Mac OS
Microsoft Windows 64-bit for AMD
Solaris operating system (x86)
IBM power based Linux

You have selected 17 rows.

Moving a tablespace has the following restrictions:

• The source database and target database must have the same database character set and national character set. The data dictionary view nls_database_patameters can be queried. DBA users can obtain the database character set (nls_characterset) and national character set (nls_nchar_characterset ).
• The tablespace cannot be moved to the target database with the same tablespace. If a tablespace with the same name exists in the target database before the Oracle database is 10 Gb, The tablespace cannot be moved. However, from Oracle Database 10 Gb, you can use the alter tablespace RENAME command to modify the name of the source data table space or destination database tablespace.
• The system tablespace and the tablespace of the Sys user object cannot be moved.
• If you want to move a tablespace to another operating system, you must set the initialization parameter compatible to 10.0 or above.

To migrate the table space tbs01 in the orcl database to the demo database, perform the following steps:

(1) determine the set of self-contained tablespaces. A set of self-contained tablespaces is a set of associated tablespaces. When moving a tablespace, if there is an association between the two tablespaces, the two tablespaces must be moved simultaneously.

For example, table space A contains the EMP table, while table Space B contains the index ind_emp of table EMP. If you want to move table Space B, you must also move table space, in this case, table space A and B are the set of self-contained table spaces.

Common violations of the rules for a set of self-contained tablespaces are as follows:

• The tablespace set contains the Sys solution object.
• The tablespace set contains the tablespace of the index, but does not contain the tablespace of the base table of the index.
• The tablespace set does not contain all partitions of the partition table.
• The tablespace set contains the tablespace of the table where the table is located, but does not contain the tablespace of its lob column.

SQL> conn system/orcl @ orcl
Connected.
SQL> execute SYS. dbms_tts.transport_set_check ('tbs01', true)

The PL/SQL process is successfully completed.

SQL> select * From SYS. transport_set_violations;

Violations
--------------------------------------------------------------------------------

Index Scott. ind_empno in tablespace tbs01 points to table Scott. EMP in tablespac

E Users

Index Scott. ind_dname in tablespace tbs01 points to table Scott. Dept in tablespa

Ce users

SQL> drop index Scott. ind_empno;

The index has been deleted.

SQL> drop index Scott. ind_dname;

The index has been deleted.

SQL> execute SYS. dbms_tts.transport_set_check ('tbs01', true)

The PL/SQL process is successfully completed.

SQL> select * From SYS. transport_set_violations;

Unselected row

(2) generate the table space set to be moved.

SQL> alter tablespace tbs01 read only;

The tablespace has been changed.

SQL> host expdp system/orcl @ orcl directory = dump_dir dumpfile = transport. dmp Trans
Port_tablespaces = tbs01

Export: Release 10.2.0.1.0-production on Monday, January, 2009 14:04:24

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

Connect to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production
With the partitioning, OLAP and Data Mining options
Start "system". "sys_export_transportable_01": System/********* @ orcl directory = dum
P_dir dumpfile = transport. dmp transport_tablespaces = tbs01
Processing object type transportable_export/plugts_blk
Processing object type transportable_export/table
Processing object type transportable_export/post_instance/plugts_blk
The master table "system". "sys_export_transportable_01" is successfully loaded/uninstalled"
**************************************** **************************************
The dump file set of system. sys_export_transportable_01 is:
D: \ dump \ transport. dmp
The job "system". "sys_export_transportable_01" was successfully completed at 14:04:47.

(3) Transfer the dump file and data file to the target database.

SQL> host Copy D: \ orcl \ tbs01.dbf D: \ demo \ tbs01.dbf
1 file has been copied.

(4) Insert the tablespace to the target database.

SQL> host impdp system/orcl @ demo directory = dump_dir dumpfile = transport. dmp Trans
Port_datafiles = D: \ demo \ tbs01.dbf remap_schema = SCOTT: HR

Import: Release 10.2.0.1.0-production on Monday, January, 2009 14:30:55

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

Connect to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production
With the partitioning, OLAP and Data Mining options
The master table "system". "sys_import_transportable_01" is successfully loaded/uninstalled"
Start "system". "sys_import_transportable_01": System/********* @ demo directory = dum
P_dir dumpfile = transport. dmp transport_datafiles = D: \ demo \ tbs01.dbf remap_schema =
SCOTT: HR
Processing object type transportable_export/plugts_blk
Processing object type transportable_export/table
Processing object type transportable_export/post_instance/plugts_blk
The job "system". "sys_import_transportable_01" was successfully completed at 14:31:05.

SQL> alter tablespace tbs01 read write;

The tablespace has been changed.