Oracle11gR2导入导出实战之使用Datapump进行表空间传输
表空间检查
[oracle@localhost database]$ ps -ef|grep smon
oracle 8981 1 0 11:52 ? 00:00:01 ora_smon_orcl
oracle 10794 1 0 16:24 ? 00:00:00 ora_smon_orcl2
oracle 10836 10391 0 16:25 pts/0 00:00:00 grep smon
[oracle@localhost database]$
[oracle@localhost database]$ export ORACLE_SID=orcl
[oracle@localhost database]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Apr 3 16:28:57 2018
Copyright (c) 1982, 2013, Oracle. 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
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/home/oracle/app/oradata/orcl/users01.dbf
/home/oracle/app/oradata/orcl/undotbs01.dbf
/home/oracle/app/oradata/orcl/sysaux01.dbf
/home/oracle/app/oradata/orcl/system01.dbf
SQL> create tablespace test_new datafile '/home/oracle/app/oradata/orcl/test_new01.dbf' size 20m;
SQL> create user test_tts identified by oracle default tablespace test_new;
User created.
SQL> grant connect ,resource to test_tts;
Grant succeeded.
SQL> conn test_tts/oracle;
Connected.
SQL>
SQL> create table test_tts as select * from all_objects where rownum <5000;
Table created.
SQL> create table test_tts2 as select * from all_objects where rownum < 10000;
Table created.
SQL> create table test_tts3(id number ,name varchar2(30));
Table created.
SQL> alter table test_tts3 modify(id primary key);
Table altered.
SQL> insert into test_tts3 select object_id,object_name from all_objects where rownum <20;
19 rows created.
SQL> col table_name format a30;
SQL> select * from cat;
TABLE_NAME TABLE_TYPE
------------------------------ -----------
TEST_TTS TABLE
TEST_TTS2 TABLE
TEST_TTS3 TABLE
SQL> conn / as sysdba
Connected.
SQL>
SQL> exec dbms_tts.transport_set_check('TEST_NEW',TRUE);
PL/SQL procedure successfully completed.
SQL> SELECT * FROM transport_set_violations;
no rows selected
设置exp客户端字符集,否则会导致字符集转换造成imp失败
[oracle@localhost ~]$ export NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK"
exp出表空间字典表
[oracle@localhost ~]$ exp \'sys/oracle as sysdba\' file=test_new.dmp transport_tablespace= y tablespaces=test_new log=test_new_tts.log
Export: Release 11.2.0.4.0 - Production on 16:42:31 2018
Copyright (c) 1982, 2011, 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 done in ZHS16GBK character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
For tablespace TEST_NEW ...
. exporting cluster definitions
. exporting table definitions
. . exporting table TEST_TTS
. . exporting table TEST_TTS2
. . exporting table TEST_TTS3
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully without warnings.
拷贝表空间文件
[oracle@localhost ~]$ export ORACLE_SID=orcl2
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/home/oracle/app/oradata/orcl2/users01.dbf
/home/oracle/app/oradata/orcl2/undotbs01.dbf
/home/oracle/app/oradata/orcl2/sysaux01.dbf
/home/oracle/app/oradata/orcl2/system01.dbf
[oracle@localhost ~]$ cp /home/oracle/app/oradata/orcl/test_new01.dbf /home/oracle/app/oradata/orcl2/
imp导入表空间
[oracle@localhost ~]$export ORACLE_SID=orcl2
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
orcl2
SQL> create user test_tts identified by oracle;
User created.
SQL> grant connect ,resource to test_tts;
Grant succeeded.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost ~]$ imp \'sys/oracle as sysdba\' file=test_new.dmp transport_tablespace=y tablespaces=test_new datafiles=/home/oracle/app/oradata/orcl2/test_new01.dbf
Import: Release 11.2.0.4.0 - Production on 17:07:11 2018
Copyright (c) 1982, 2011, 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 ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing SYS's objects into SYS
. importing SYS's objects into SYS
. importing TEST_TTS's objects into TEST_TTS
. . importing table "TEST_TTS"
. . importing table "TEST_TTS2"
. . importing table "TEST_TTS3"
. importing SYS's objects into SYS
Import terminated successfully without warnings.
验证数据
SQL> conn test_tts/oracle
Connected.
SQL> select * from cat;
TABLE_NAME TABLE_TYPE
------------------------------ -----------
TEST_TTS TABLE
TEST_TTS2 TABLE
TEST_TTS3 TABLE
SQL> select count(*) from TEST_TTS3;
COUNT(*)
----------
19
SQL> conn / as sysdba
Connected.
SQL> SELECT tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
TEST_NEW READ ONLY
6 rows selected.
SQL> alter tablespace TEST_NEW read write;
Tablespace altered.
