Transport Tablespace Set(三) ----transport single tablespace

源端字符集与endian查看：

SQL> select userenv('language') from dual;

USERENV('LANGUAGE')
----------------------------------------------------
SIMPLIFIED CHINESE_CHINA.ZHS16GBK

SQL> SELECT d.PLATFORM_NAME, ENDIAN_FORMAT
  2       FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
  3       WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;

PLATFORM_NAME                  ENDIAN_FORMAT
------------------------------ --------------
Microsoft Windows IA (32-bit)  Little

在目标端字符集与endian查看：

SQL> select userenv('language') from dual;

USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.ZHS16GBK

SELECT d.PLATFORM_NAME, ENDIAN_FORMAT
     FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
  3       WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;

PLATFORM_NAME                  ENDIAN_FORMAT
------------------------------ --------------
Linux 64-bit for AMD           Little

从上面信息可看到字条集均为ZHS16GBK,endian类型都是Little，符合transpor tablespace set的要求。

 

下面内容在源端操作：

========================================================================
SQL> create tablespace keke datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\keke_01.dbf' size 10M au
toextend on;

表空间已创建。

SQL> create user keke identified by oracle default tablespace keke;

用户已创建。

SQL> grant connect,resource,dba to keke;

授权成功。

SQL> conn keke/oracle
已连接。

SQL> create table keke as select * from dba_objects;

表已创建。

SQL> commit;

提交完成。

SQL> CONN /AS SYSDBA
已连接。
SQL> exec dbms_tts.transport_set_check('KEKE',TRUE);

PL/SQL 过程已成功完成。

SQL> select * from transport_set_violations;

未选定行

SQL> select tablespace_name,status from dba_tablespaces where tablespace_name='KEKE';

TABLESPACE_NAME                STATUS
------------------------------ ---------
KEKE                           ONLINE

已选择10行。

SQL>

SQL> alter tablespace keke read only;

表空间已更改。
C:\Documents and Settings\Administrator>expdp system/oracle directory=mydump dumpfile=keke.dmp logfi
le=keke.log transport_tablespaces=KEKE

Export: Release 10.2.0.1.0 - Production on 星期一, 09 9月, 2013 13:28:18

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_EXPORT_TRANSPORTABLE_01":  system/******** directory=mydump dumpfile=keke.dmp log
file=keke.log transport_tablespaces=KEKE
处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK
处理对象类型 TRANSPORTABLE_EXPORT/TABLE
处理对象类型 TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
已成功加载/卸载了主表 "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01"
******************************************************************************
SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 的转储文件集为:
  C:\MYDMP\KEKE.DMP
作业 "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" 已于 13:28:33 成功完成

===========================================================================

下面内容在目标端操作：

===========================================================================
SQL> create user keke identified by keke;

User created.

[oracle@node1 ~]$ impdp system/oracle dumpfile=KEKE.DMP directory=dmp transport_datafiles=/test/mydump/KEKE_01.DBF

Import: Release 10.2.0.1.0 - 64bit Production on Monday, 09 September, 2013 13:38:39

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit 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=KEKE.DMP directory=dmp transport_datafiles=/test/mydump/KEKE_01.DBF
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 13:39:12

SQL> select tablespace_name,status from dba_tablespaces where tablespace_name='KEKE';

TABLESPACE_NAME                STATUS
------------------------------ ---------
KEKE                           READ ONLY
SQL> alter tablespace keke read write;

Tablespace altered.

SQL> select tablespace_name,status from dba_tablespaces where tablespace_name='KEKE';

TABLESPACE_NAME                STATUS
------------------------------ ---------
KEKE                           ONLINE

7 rows selected.

SQL> alter user keke identified by oracle default tablespace keke; #####由于权限及密码保存在oracle数据字典里，所以这些信息需要更新。

User altered.

SQL> conn keke/oracle
Connected.
SQL> select count(*) from keke;

  COUNT(*)
----------
     49781

==============================================================================

end