linux平台表空间传至windows平台 表空间：rman_ts
---------------------------------------------------------------------------------------------
--------------------以下为linux平台下操作----------------------------------------------------
--查看表空间
SQL> select TABLESPACE_NAME from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS
TEST_TBS
BFTBS
RMAN_TS
TEMP01
8 rows selected.
--查看表空间状态
SQL> select TABLESPACE_NAME,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
USERS ONLINE
TEST_TBS ONLINE
BFTBS ONLINE
RMAN_TS ONLINE
TEMP01 ONLINE
8 rows selected.
--表空间大小20M
SQL> set linesize 300
SQL> col % for a20
SQL> col tsname for a15
SQL> SELECT UPPER(F.TABLESPACE_NAME) "tsname",
2 D.TOT_GROOTTE_MB "total(M)",
3 D.TOT_GROOTTE_MB - F.TOTAL_BYTES "used(M)",
4 TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,
5 2),
6 '990.99') "%",
7 F.TOTAL_BYTES "free(M)",
8 F.MAX_BYTES "max block(M)"
9 FROM (SELECT TABLESPACE_NAME,
10 ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
11 ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
12 FROM SYS.DBA_FREE_SPACE
13 GROUP BY TABLESPACE_NAME) F,
14 (SELECT DD.TABLESPACE_NAME,
15 ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
16 FROM SYS.DBA_DATA_FILES DD
17 GROUP BY DD.TABLESPACE_NAME) D
18 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
19 ORDER BY 4 DESC;
tsname total(M) used(M) % free(M) max block(M)
--------------- ---------- ---------- -------------------- ---------- ------------
SYSTEM 325 239.81 73.79 85.19 84.94
RMAN_TS 20 8.31 41.55 11.69 11.69
SYSAUX 325 77.19 23.75 247.81 247.81
TEST_TBS 20 .75 3.75 19.25 19.19
UNDOTBS1 200 5.5 2.75 194.5 187.94
BFTBS 5 .12 2.40 4.88 4.88
USERS 25 .06 0.24 24.94 24.94
7 rows selected.
--查看RMAN_TS表空间数据文件存放位置
SQL> select FILE_NAME,TABLESPACE_NAME from dba_data_files where TABLESPACE_NAME = 'RMAN_TS';
FILE_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
/home/oracle/oradata/test/rman_ts.dbf
RMAN_TS
SQL> set line 300
SQL> /
FILE_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
/home/oracle/oradata/test/rman_ts.dbf
RMAN_TS
--检查自包含表空间，检查待传输表空间中对象，是否引用了其他未被传输表空间中的对象
SQL>
SQL> exec dbms_tts.transport_set_check('RMAN_TS',TRUE);
PL/SQL procedure successfully completed.
--返回0说明上述检查满足自包含条件
SQL> select * from transport_set_violations;
no rows selected
--将待传输的表空间置为只读
SQL> alter tablespace RMAN_TS read only;
Tablespace altered.
SQL> !
[oracle@zhaomn ~]$ pwd
/home/oracle
[oracle@zhaomn ~]$ ls
admin EXT_CASE1_2941.bad flash_recovery_area oradata product sqlnet.log zhaomn_tables_exp.log
Desktop EXT_CASE1_2941.log ldr_case1.ctl oraInventory sp.sql zhaomn_tables.dmp
[oracle@zhaomn ~]$ exit
exit
--设置目录
SQL> create directory dump_file_dir as '/home/oracle';
Directory created.
--执行导出
[oracle@zhaomn ~]$ expdp system/system dumpfile=tbs_rman_ts.dmp directory=dump_file_dir transport_tablespaces=rman_ts nologfile=y
Export: Release 10.2.0.1.0 - Production on Friday, 23 July, 2010 21:34:57
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
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** dumpfile=tbs_rman_ts.dmp directory=dump_file_dir transport_tablespaces=rman_ts 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/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
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:
/home/oracle/tbs_rman_ts.dmp
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 21:35:22
--将生成的tbs_rman_ts.dmp和表空间的数据文件/home/oracle/oradata/test/rman_ts.dbf传到windows相应的目录中
--tbs_rman_ts.dmp文件放的位置和创建的目录对应上即可，如：create directory dump_file_dir as 'd:\';
--表空间数据文件放在windows下oracle数据文件的路径下，如：E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORACLE\rman_ts.dbf
-----------------------------下面操作在windows平台下进行----------------------------------------------------
--创建rman_ts表空间对应的用户，授权
create user rman
identified by "rman"
default tablespace TEST_TBS
temporary tablespace TEMP
profile DEFAULT;
grant connect to rman;
grant import full database to rman;
grant dba to rman;
--执行导入
C:\Documents and Settings\Administrator>impdp system/system dumpfile=tbs_rman_ts
.dmp directory=dump_file_dir nologfile=y transport_datafiles=E:\ORACLE\PRODUCT\1
0.2.0\ORADATA\ORACLE\rman_ts.dbf
Import: Release 10.2.0.1.0 - Production on 星期五, 23 7月, 2010 17:38:47
Copyright (c) 2003, 2005, Oracle. All rights reserved.
连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
已成功加载/卸载了主表 "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01"
启动 "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** dumpfile=tbs_rman_
ts.dmp directory=dump_file_dir nologfile=y transport_datafiles=E:\ORACLE\PRODUCT
\10.2.0\ORADATA\ORACLE\rman_ts.dbf
处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK
处理对象类型 TRANSPORTABLE_EXPORT/TABLE
处理对象类型 TRANSPORTABLE_EXPORT/INDEX
处理对象类型 TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
处理对象类型 TRANSPORTABLE_EXPORT/INDEX_STATISTICS
处理对象类型 TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
处理对象类型 TRANSPORTABLE_EXPORT/TABLE_STATISTICS
处理对象类型 TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
作业 "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" 已于 17:39:01 成功完成
--------------------以下为linux平台下操作----------------------------------------------------
--将原表空间rman_ts置为读写
alter tablespace RMAN_TS read write;
------------------后记-----------------------------------------------------------------------
传输表空间需要至少拷贝两类文件：生成的dmp文件；数据文件
在导入的时候也可能导至另一个用户下，如：
impdp system/system dumpfile=tbs_rman_ts.dmp directory=dump_file_dir nologfile=y transport_datafiles=E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORACLE\rman_ts.dbf remap_schema=(rman:scott)
--此行代码，未经测试
