ORACLE表空间传输

1.查询编码
SQL>SELECT d.PLATFORM_NAME, ENDIAN_FORMAT
      FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
      WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;

2.字符集检查
SQL>select * from nls_database_parameters where parameter='NLS_CHARACTERSET';

3.自包含检查
SQL>EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('abc', TRUE);

SQL>SELECT * FROM TRANSPORT_SET_VIOLATIONS;
   no rows selected

4.修改表空间属性
SQL>alter tablespace abc read only;

5.导出表空间
$expdp system dumpfile=abc.dmp directory=data_pump_dir transport_tablespaces=abc logfile=abc_exp.log

6.查看DATA_PUMP_DIR路径
SQL>SELECT * FROM DBA_DIRECTORIES WHERE DIRECTORY_NAME= 'DATA_PUMP_DIR';
------------------------------------------------------
/u01/app/oracle/product/10.2.0/db_1/rdbms/log/

7.传输表空间
>ftp 192.168.1.2
>cd /u01/app/oracle/product/10.2.0/db_1/rdbms/log/
>get abc.dmp                               #传输导出文件
>cd /u01/app/oracle/oradata/master
>get abc.dbf       #传输表空间
>quit
$mv abc.dmp /u01/app/oracle/product/10.2.0/db_1/rdbms/log/

在源主机修改表空间状态
SQL>alter tablespace abc read write;

8.转换编码
Rman target /
RMAN>convert datafile '/home/oracle/abc.dbf' to platform 'Linux IA (32-bit)' from platform 'AIX-Based Systems (64-bit)' format='/u01/app/oracle/oradata/prod/abc.dbf';

9.导入表空间
$impdp system dumpfile=abc.dmp directory=data_pump_dir transport_datafiles=/u01/app/oracle/oradata/prod/abc.dbf  [remap_schema=user1:user2] logfile=abc_imp.log
//remap:原方案:目标方案,不指定需要两边有相同用户

查询表空间状态
select tablespace_name,status from dba_tablespaces where tablespace_name='ABC';

TABLESPACE_NAME  STATUS
-----------------------
ABC              READ ONLY

修改状态
alter tablespace abc read write;

附:
1)源主机转换编码
RMAN>convert tablespace abc to platform 'Linux IA (32-bit)' format '/u01/app/abc.dbf';

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30077753/viewspace-1409244/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30077753/viewspace-1409244/