Transport Tablespace Set(三)，createtablespace

Transport Tablespace Set(三)，createtablespace

源端字元集與endian查看：
SQL> select userenv(‘language’) from dual;
USERENV(‘LANGUAGE’)

SIMPLIFIED CHINESE_CHINA.ZHS16GBK
SQL> SELECT d.PLATFORM_NAME, ENDIAN_FORMAT
2 FROM VTRANSPORTABLEPLATFORMtp,VDATABASE 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 VTRANSPORTABLEPLATFORMtp,VDATABASE 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 autoextend 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