Oracle movable tablespace Method

Source database environment: OS: WINDOWS7DBVERSION: Oracle10.2.0.4.0 Note: Version 10.1.0.1.0 cannot use this migration solution DBUSER: SCOTT requires dba permission

Source database environment: OS: WINDOWS 7 DB VERSION: Oracle 10.2.0.4.0 Note: This migration solution DB USER: SCOTT requires dba permission.

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;