Oracle Transport Tablespace

最新推荐文章于 2024-04-19 17:29:33 发布

Flexes

最新推荐文章于 2024-04-19 17:29:33 发布

阅读量947

点赞数

分类专栏： SQL优化文章标签： Oracle PLSQL SQL

SQL优化专栏收录该内容

19 篇文章 0 订阅

订阅专栏

Oracle 10g 表空间迁移

Transport tablespace前需要确认
(1)确认表空间是否和其他表空间有包含关系
SQL>conn trans_us/trans_us
SQL>create table account as select rownum id,username,created from dba_users;(table默认在TRANSPORT_TS表空间里)
SQL>create index account_idx on account(id) tablespace users;
SQL>exec dbms_tts.transport_set_check('TRANSPORT_TS',true,true);
SQL>select * from transport_set_violations;
VIOLATIONS
------------------------------------------------------------------------------------------------------------------------
Index TRANS_US.ACCOUNT_IDX in tablespace USERS points to table TRANS_US.ACCOUNT in tablespace TRANSPORT_TS
说明Table account的index account_idx在USERS表空间里.

(2)字符集确认,最好保证两边字符集完全相同
SQL>select * from nls_database_parameters;

exp,imp恢复
[oracle@oracle transport_ts]$ pwd
/home/oracle/transport_ts
[oracle@oracle transport_ts]$export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
[oracle@oracle transport_ts]$exp userid=\'sys/oracle@hdb.oracle.sannet.net as sysdba\' tablespaces=transport_ts transport_tablespace=y file=transport_ts.dmp

Export: Release 10.2.0.1.0 - Production on Tue Feb 21 16:10:31 2012

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
For tablespace TRANSPORT_TS ...
. exporting cluster definitions
. exporting table definitions
. . exporting table ACCOUNT
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully without warnings.

将导出的元文件和原来的数据文件一起拷贝至目标库的数据文件目录

SQL> alter tablespace transport_ts read only;
[oracle@oracle RDPSDB]$ imp userid=\'/ as sysdba\' fromuser=trans_us touser=trans_us tablespaces=transport_ts transport_tablespace=y file=transport_ts.dmp datafiles='/u01/app/oracle/oradata/RDPSDB/transport_ts.ora'

Import: Release 10.2.0.1.0 - Production on Tue Feb 21 16:59:54 2012

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
About to import transportable tablespace(s) metadata...
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
export client uses WE8ISO8859P1 character set (possible charset conversion)
. importing TRANS_US's objects into TRANS_US
. . importing table "ACCOUNT"
Import terminated successfully without warnings.

SQL> alter tablespace transport_ts read write;

或者expdp,impdp进行恢复
SQL> alter tablespace transport_ts read only;
[oracle@oracle transport_ts]$expdp system/oracle@hdb.oracle.sannet.net TRANSPORT_TABLESPACES=(transport_ts) dumpfile=transport_ts.dmp

Export: Release 10.2.0.1.0 - Production on Tuesday, 21 February, 2012 19:23:55

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/oracle@hdb.oracle.sannet.net TRANSPORT_TABLESPACES=(transport_ts) dumpfile=transport_ts.dmp
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
/u01/app/oracle/product/10.2.0/db_1/rdbms/log/transport_ts.dmp
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 19:24:36

默认dmp文件在DATA_PUMP_DIR这个目录下
SQL>select * from dba_directories;

OWNER      DIRECTORY_NAME                 DIRECTORY_PATH
---------- ------------------------------ -----------------------------------------------------------------------
SYS        DATA_PUMP_DIR                  /u01/app/oracle/product/10.2.0/db_1/rdbms/log/
SYS        XMLDIR                         /u01/app/oracle/product/10.2.0/db_1/demo/schema/order_entry/
SYS        DIR                          /home/oracle
SYS        MEDIA_DIR                    /u01/app/oracle/product/10.2.0/db_1/demo/schema/product_media/
SYS        LOG_FILE_DIR               /u01/app/oracle/product/10.2.0/db_1/demo/schema/log/
SYS        WORK_DIR                     /ade/aime_10.2_lnx_push/oracle/work
SYS        DATA_FILE_DIR                  /u01/app/oracle/product/10.2.0/db_1/demo/schema/sales_history/
SYS        SUBDIR                         /u01/app/oracle/product/10.2.0/db_1/demo/schema/order_entry//2002/Sep
SYS        ADMIN_DIR                  /ade/aime_10.2_lnx_push/oracle/md/admin

将导出的元文件和原来的数据文件一起拷贝至目标库的数据文件目录

[oracle@oracle ~]$export ORACLE_SID=RDPSDB
[oracle@oracle ~]$impdp system/oracle dumpfile=transport_ts.dmp transport_datafiles='/u01/app/oracle/oradata/RDPSDB/transport_ts.ora'

Import: Release 10.2.0.1.0 - Production on Tuesday, 21 February, 2012 19:33:04

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** dumpfile=transport_ts.dmp transport_datafiles=/u01/app/oracle/oradata/hdb/transport_ts.ora
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 19:33:12
SQL>alter tablespace transport_ts read write;

注意:

(1),exp需要使用sys用户;
(2),imp文件建议路径为绝对路径;
(3),注意源库与目标库字符集完全一致;
(4),目标库用户需要额外创建,import导入时需要使用fromuser,touser进行映射;
(5),导出的dmp元文件和ora数据文件均需要拷贝至目标库;
(6),tablespace要进行read only和read write的状态修改.

参考网友笔记
http://blog.csdn.net/xsxxxsxx/article/details/6872282
http://apps.hi.baidu.com/share/detail/19354444