Use of Oracle tablespace migration Transport_tablespace ____oracle

Problem Description:

If a table space has more than one user, then we will be in the migration, if exported by the user import, it would be a troublesome work, this time we can through the Transport_tablespace parameters of the table space migration to achieve a one-time migration of the entire table space. The following is an experiment to explain the entire implementation process and related considerations.

Experiment steps:

1, create the table space:

sql> Create tablespace Test datafile ' +data ' size 10m autoextend on next 1m maxsize 20m;

2. Create User:

Sql> create user test identified by test default tablespace test temporary tablespace temp quota 10m on test;

3, Grant the right:
Sql> Grant Connect,resource to test;

4, under the test user to build the table:
Sql> Conn Test/test
Sql> CREATE TABLE Test (I number) tablespace test;
Sql> begin
2 for I in 1..10000 loop
3 INSERT INTO test values (i);
4 End Loop;
5 commit;
6 end;
7/

Pl/sql procedure successfully completed

5. Set the test table space to read only:
Sql> Conn/as SYSDBA
sql> alter TABLESPACE test Read only;
Sql> col name for A40
Sql> Set Linesize 200
Sql> Select name,file#,checkpoint_change#,status,enabled from V$datafile where the like '%only% ' is enabled;

NAME file# checkpoint_change# STATUS ENABLED
---------------------------------------- ---------- ------------------ ------- ----------
+data/rac/datafile/test.478.945596467 6 2950036 ONLINE READ only

6. Use SYS user to export table space (must be SYS user)

Sql> host exp transport_tablespace=y tablespaces=test file=/u01/oracle/test.dmp

Export:release 11.2.0.4.0-production on Fri June 2 07:40:41 2017

Copyright (c) 1982, Oracle and/or its affiliates. All rights reserved.

Username:sys
Password:

Exp-00056:oracle Error 28009 encountered
Ora-28009:connection as SYS should be as SYSDBA or sysoper
Username:sys as Sysdba
Password:

Connected to:oracle Database 11g Enterprise Edition release 11.2.0.4.0-64bit Production
with the partitioning, R EAL application clusters, Automatic Storage Management, OLAP,
Data Mining and real application Tes
Export do in Us7ascii character set and Al16utf16 NCHAR character Set
Server uses ZHS16GBK character set (Possible CharSet Conversi ON)
Note:table data (rows) is not is exported
about to export transportable tablespace metadata ...
for tablespace test ...
. Exporting cluster definitions
. exporting table Definitions
... exporting table    &nbs p;                          TEST
. Exporting referential integrity constraints
. Exporting triggers
. End transportable tablespace metadata export
Export terminated successfully without.

7. After the export is finished, reset the table space to read and write:
sql> alter tablespace Test read write;

8, copy data files: From the ASM inside copy data files to the file system has the following two ways:

8.1, the use of Asmcmd:
Asmcmd> CD Data/rac/datafile
asmcmd> CP test.478.945596467/u01/grid/test.dbf
Copying +data/rac/datafile/test.478.945596467->/u01/grid/test.dbf

8.2. Use Rman:
rman> copy datafile ' +data/rac/datafile/test.478.945596467 ' to '/home/oracle/test.dbf ';

9, we need to copy the exported meta file and the original data file to the target library data file directory:
[Root@rac1 ~]# scp/u01/grid/test.dbf 192.168.56.20:/u01/oracle/app/oradata/rac/test.dbf
[Oracle@rac1 ~]$ scp/u01/oracle/test.dmp 192.168.56.20:/u01/oracle/test.dmp

10. Target-side import recovery

10.1. Create test user

Sql> create user test identified by test;

10.2. Perform table space Import
[ROOT@RACDG ~]# chmod 777/u01/oracle/app/oradata/rac/test.dbf
[ROOT@RACDG ~]# Su-oracle
[ORACLE@RACDG ~]$ imp userid=\ ' sys/oracle as Sysdba\ ' Transport_tablespace=y file=/u01/oracle/test.dmp Test datafiles= '/u01/oracle/app/oradata/rac/test.dbf ';

Import:release 11.2.0.4.0-production on Fri June 2 10:34:34 2017

Copyright (c) 1982, Oracle and/or its affiliates. All rights reserved.

Connected to:oracle Database 11g Enterprise Edition release 11.2.0.4.0-64bit Production
With the partitioning, OLAP, Data Mining and real application testing options

Export file created by export:v11.02.00 via conventional path
About to import transportable tablespace (s) metadata ...
Import done in Us7ascii character set and Al16utf16 NCHAR character set
Import server uses ZHS16GBK character set (possible charset conversion)
. Importing test ' s objects into SYSTEM
. . Importing table "TEST"
Import terminated successfully without warnings.

Note: If this table space has only a single user, then the import is relatively flexible, we can import it to another user:

[ORACLE@RACDG ~]$ imp userid=\ ' sys/oracle as Sysdba\ ' Transport_tablespace=y file=/u01/oracle/test.dmp Touser=scott tablespaces=test datafiles= '/u01/oracle/app/oradata/rac/test.dbf '

11, Query Import Results

Sql> Select COUNT (*) from test.test;

COUNT (*)
----------
10000

Sql> select Tablespace_name,file_name from Dba_data_files;

Tablespace_name file_name
------------------------------ ---------------------------------------------
Users/u01/oracle/app/oradata/rac/users01.dbf
Undotbs1/u01/oracle/app/oradata/rac/undotbs01.dbf
Sysaux/u01/oracle/app/oradata/rac/sysaux01.dbf
System/u01/oracle/app/oradata/rac/system01.dbf
Test/u01/oracle/app/oradata/rac/test.dbf

12, modify test user's default table space for test

sql> ALTER USER test default tablespace test;

User altered.

Sql> Select Username,default_tablespace from dba_users where username = ' TEST ';

USERNAME Default_tablespace
------------------------------       ------------------------------
Test test

13, set the table space to read and write

sql> alter tablespace Test read write;

At this point, the table space migration has been completed.

Precautions :

(1), Exp needs to use the SYS user;
(2), Attention Source Library and Target library character set are identical;
(3), the target library users need to create in advance, including the original library in the table space of all users;
(4) If the tablespace has only one user and needs to be imported to other users, IMP needs to be mapped using fromuser,touser when importing;
(5), the exported DMP and Ora data files need to be copied to the target library;
(6), tablespace to do the state modification of Read only and read write;
(7), the export of imported data seems to contain no process, triggers, views and other objects, only table data;
(8), after the import is complete, you need to modify the user's default table space.