Oracle transfer tablespace

Oracle's transfer tablespace refers to the ability to copy all data files in one or more tablespaces of a database to another database server, and load the tablespace as the target database through import.

The biggest advantage of using this method is that it is faster, and the time required is similar to copying data files, which is much faster than executing EXP/IMP import and export.

Transfer tablespace restrictions:

The source and target databases must use the same character set and country character set (corresponding to the NLS_CHARACTERSET and NLS_NCHAR_CHARACTERSET values in the nls_database_parameters view respectively)

To transfer data to other operating systems, compatible must be set to 10.0 or above.

Oracle provides the process dbms_tts.transport_set_check to check whether a tablespace set is self-contained. There are three parameters in this process. The first is the list of tablespace names, which are separated by commas. The second parameter indicates whether to check integrity constraints, the third parameter indicates whether the object in the set is referenced outside the set while checking whether the object in the set is referenced outside the set.

After the process is completed, query the view transport_set_violations to view the check results. If it is null, it indicates that the table space is self-contained and can be transferred.

Lab:

-- Create test table space and test users on the source end
SQL> create tablespace t_tbs datafile 'G: \ oracle \ product \ 10.2.0 \ oradata \ orcl \ t_tbs01.dbf'
2 size 10 M;
 
The tablespace has been created.
 
SQL> create user test identified by test;
 
The user has been created.
 
SQL> grant connect, resource to test;
 
Authorization successful.
 
SQL> conn test/test @ orcl
Connected.
SQL> create table t_t (id int) tablespace t_tbs;
 
The table has been created.
 
SQL> insert into t_t select level from dual connect by level <100;
 
99 rows have been created.
 
SQL> commit;
 
Submitted.
 
-- 1. Source check self-contained
SQL> show user
USER is "SYS"
SQL> execute dbms_tts.transport_set_check ('t_tbs ', true );
 
The PL/SQL process is successfully completed.
 
SQL> select * from transport_set_violations;
 
Unselected row
 
 
-- 2. The source tablespace is read-only at the beginning of the export.
SQL> alter tablespace t_tbs read only;
 
The tablespace has been changed.
 
-- 3. Execute export. sysdba is required.
SQL> ho exp 'sys/oracle @ orcl as sysdba 'transport_tablespace = y tablespaces = t_tbs file = d: t_tbs.dmp
 
Export: Release 10.2.0.1.0-Production on Thursday August 30 22:16:12 2012
 
Copyright (c) 1982,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 ZHS16GBK and AL16UTF16 NCHAR character sets have been exported.
Note: Table data (rows) will not be exported)
The metadata of the deletable tablespace will be exported soon...
For the tablespace T_TBS...
. Exporting cluster Definition
. Exporting table definition...
... Exporting table T_T...
. The reference integrity constraints are being exported.
. Exporting trigger...
. End exporting the deletable tablespace metadata
The export is successfully terminated without a warning.

1 2 Next Page