Oracle movable tablespace Method

Source database environment:

OS: WINDOWS 7
 
Db version: Oracle 10.2.0.4.0 Note: This migration solution cannot be used in VERSION 10.1.0.1.0.
 
Db user: SCOTT
 
Dba permissions and users are required

Target database environment:
 
OS: WINDOWS 7
 
Db version: 11.2.0.1.0 Note: This migration scheme cannot be used in earlier 11g versions.
 
Dba permissions and users are required

Implementation steps:

1. Check whether the migrated tablespace meets the migration conditions and is a self-contained tablespace.
 
SQL> EXEC SYS. DBMS_TTS.TRANSPORT_SET_CHECK ('sdh _ DATA_TEST01 ', TRUE );
 
PL/SQL procedure successfully completed
 
SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS; -- identify that the self-contained type must be empty.

VIOLATIONS
 
2. The tablespace is in read-only mode.
 
SQL> alter tablespace SDH_DATA_TEST01 read only;
 
Tablespace altered
 
SQL> SELECT status from dba_tablespaces where tablespace_name = 'sdh _ DATA_TEST01 ';
 
STATUS
 
---------
 
READ ONLY

3. transmit the data file corresponding to the table space of SDH_DATA_TEST01 to the remote server. D: \ backup
 
4. Create directory FTP_TEST as 'd: \ BACKUP in the target database ';
 
5. Create a dblink for the target database to connect to the source database.
 
Create public database link DBLINK_133
 
Connect to SCOTT identified by tiger
 
Using '(DESCRIPTION =
 
(ADDRESS_LIST =
 
(ADDRESS = (PROTOCOL = TCP) (HOST = 10.6.246.133) (PORT = 1521 ))
 
)
 
(CONNECT_DATA =
 
(SERVICE_NAME = demo)
 
)
)';
 

6. Execute the data stick import tool in the target database to import the tablespace to the target database.
 
D: \ app \ yanan \ product \ 11.2.0 \ dbhome_2> impdp test/oracle TRANSPORT_DATAFILES = 'd: \ backup \ done'network_link = 'dblink _ 000000' TRANSPORT_TABLESPACES = (SDH_DATA_TEST01) NOLOGFILE = Y
 
 
 
Import: Release 11.2.0.1.0-Production on Thursday October 25 14:51:28 2012
 
 
 
Copyright (c) 1982,200 9, Oracle and/or its affiliates. All rights reserved.
 
 
 
Connect to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production
 
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
Start "TEST". "SYS_IMPORT_TRANSPORTABLE_01": test/******** TRANSPORT_DATAFILES ='
 
D: \ backup \ SDH_DATA_TEST01_01.DBF 'network_link = 'dblink _ 000000' TRANSPORT_TABLESPACE
 
S = (SDH_DATA_TEST01) NOLOGFILE = Y
 
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
 
Processing object type TRANSPORTABLE_EXPORT/TABLE
 
Processing object type TRANSPORTABLE_EXPORT/INDEX
 
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
 
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
 
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
 
The job "TEST". "SYS_IMPORT_TRANSPORTABLE_01" was successfully completed at 14:51:51.
 
7. Modify the tablespace status of the source database to read/write:
 
SQL> alter tablespace SDH_DATA_TEST01 read write;