TTS, not used for schema exp/imp

有句话叫“有所为，有所不为”， TTS(Transportable Tablespace)也一样。在尝试用TTS来把一个schema下的objects转移到另外一个数据库的时候，发现这是行不通的，因为TTS不是用来干这个事情的！因为TTS不会包含package等object. 

 

简单做下测试....

首先在源数据库ＯＲＣＬ上创建一个表空间ＴＥＳＴ，一个用户ＴＥＳＴ，　然后给ＴＥＳＴ创建一张表ＴＥＳＴ，表上有个INDEX, TRIGGER, 另外还有个package...

C:\Documents and Settings\yufa>sqlplus /@orcl as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Fri Dec 16 09:42:39 2011

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> drop user test cascade;

User dropped.

SQL> alter tablespace test read write;

Tablespace altered.

SQL> create user test identified by a
  2  default tablespace test;

User created.

SQL> grant connect, resource to test;

Grant succeeded.

SQL> conn test/a@orcl
Connected.
SQL> create table test(id int);

Table created.

SQL> create index i_test on test(id);

Index created.

SQL> insert into test values(100);

1 row created.

SQL> insert into test values(1000);

1 row created.

SQL> commit;

Commit complete.

SQL> create or replace package pack_test as
  2  end pack_test;
  3  /

Package created.

SQL> create or replace trigger tri_test
  2  before update on test
  3  for each row
  4  begin
  5   null;
  6  end;
  7  /

Trigger created.

SQL> conn sys/sys@orcl as sysdba
Connected.
SQL> alter tablespace test read only;

Tablespace altered.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64
bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

现在用EXPDP进行TTS的导出...

C:\Documents and Settings\yufa>expdp 'sys/sys@orcl as sysdba' dumpfile=test.dmp
logfile=test.log directory=RO_DIR transport_tablespaces=TEST

Export: Release 11.2.0.2.0 - Production on Fri Dec 16 09:45:57 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit
Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01":  "sys/********@orcl AS SYSDBA" dum
pfile=test.dmp logfile=test.log directory=RO_DIR transport_tablespaces=TEST
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TRIGGER
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TRANSPORTABLE_01 is:
  D:\RO_DIR\TEST.DMP
******************************************************************************
Datafiles required for transportable tablespace TEST:
  D:\ORACLE\ORADATA\ORCL\TEST01.DBF
Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 09:46:51

其实从EXPDP这个输出Log 就可以看出TRANSPORT_EXPORT中只包含了TABLE/INDEX/TRIGGER，没有包含PACKAGE的。

 

关于EXPDP导出的metadata可以包含哪些，可以查询表DATABASE_EXPORT_OBJECTS/SCHEMA_EXPORT_OBJECTS/TABLE_EXPORT_OBJECTS  

但是很奇怪的是没有TRANSPORT_EXPORT_OBJECTS!

 

现在尝试在目标数据库TEST上进行导入操作....

注意在此之前需要先创建用户TEST，不然会报错的

C:\Documents and Settings\yufa>impdp 'sys/sys@test as sysdba' dumpfile=test.dmp
logfile=test_imp.log directory=ro_dir transport_datafiles=D:\oracle\oradata\TEST
\test01.dbf

Import: Release 11.2.0.2.0 - Production on Fri Dec 16 09:50:10 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit
Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01":  "sys/********@test AS SYSDBA" dum
pfile=test.dmp logfile=test_imp.log directory=ro_dir transport_datafiles=D:\orac
le\oradata\TEST\test01.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TRIGGER
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 09:50:16

现在验证下导入了哪些东西...

 

C:\Documents and Settings\yufa>sqlplus test/a@test

SQL*Plus: Release 11.2.0.2.0 Production on Fri Dec 16 09:51:47 2011

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show user
USER is "TEST"


SQL> select count(*) from user_objects;

  COUNT(*)
----------
         3

SQL> select object_name from user_objects;

OBJECT_NAME
--------------------------------------------------------------------------------

TEST
I_TEST
TRI_TEST

SQL> select * from test;

        ID
----------
       100
      1000

SQL> exit

很显然，package PACK_TEST并没有包含在内，其他都在。

 

再做另外一个测试，假如目标数据库TEST上的用户TEST已经创建了表TEST，那会怎么样呢？

C:\Documents and Settings\yufa>sqlplus sys/sys@test as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Fri Dec 16 09:54:20 2011

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> drop tablespace test including contents and datafiles;

Tablespace dropped.

SQL> conn test/a@test
Connected.
SQL> select count(*) from user_objects;

  COUNT(*)
----------
         0

SQL> create table test(id int);

Table created.

SQL> insert into test values(1);

1 row created.

SQL> commit;

Commit complete.

SQL>


C:\Documents and Settings\yufa>impdp 'sys/sys@test as sysdba' dumpfile=test.dmp
logfile=test_imp.log directory=ro_dir transport_datafiles=D:\oracle\oradata\TEST
\test01.dbf

Import: Release 11.2.0.2.0 - Production on Fri Dec 16 09:56:19 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit
Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01":  "sys/********@test AS SYSDBA" dum
pfile=test.dmp logfile=test_imp.log directory=ro_dir transport_datafiles=D:\orac
le\oradata\TEST\test01.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
ORA-39151: Table "TEST"."TEST" exists. All dependent metadata and data will be s
kipped due to table_exists_action of skip
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TRIGGER
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" completed with 1 error(s) at 09:56:25



SQL> conn test/a@test
Connected.

SQL> select count(*) from user_objects;

  COUNT(*)
----------
         1

SQL> select * from test;

        ID
----------
         1

SQL>

结果发现什么都没有导入！不过从IMPDP的错误信息看到貌似可以设置参数table_exists_action使得导入能够成功。

查询下这个参数的含义...

TABLE_EXISTS_ACTION
Action to take if imported object already exists.
Valid keywords are: APPEND, REPLACE, [SKIP] and TRUNCATE.

 

貌似我设置成APPEND应该会把数据都导进来，结果如何呢....

 

C:\Documents and Settings\yufa>impdp 'sys/sys@test as sysdba' dumpfile=test.dmp
logfile=test_imp.log directory=ro_dir transport_datafiles=D:\oracle\oradata\TEST
\test01.dbf TABLE_EXISTS_ACTION=APPEND

Import: Release 11.2.0.2.0 - Production on Fri Dec 16 10:00:17 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit
Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39005: inconsistent arguments
ORA-39032: function TABLE_EXISTS_ACTION is not supported in TRANSPORTABLE jobs

居然这个参数在transportable模式下不能使用！真是崩溃！ 

所以在进行TTS导入的时候，目标schema下面不能有跟源schema相同的表存在！ 

 

最后关于TTS还有一些需要注意的地方....

 

(1) 源数据库和目标数据库的字符集应该是相同或者兼容的,否则会报错，如下....

C:\Documents and Settings\yufa>impdp 'sys/sys@blc as sysdba' dumpfile=test.dmp l
ogfile=test_imp.log directory=ro_dir transport_datafiles=D:\oracle\oradata\BLC\t
est01.dbf

Import: Release 11.2.0.2.0 - Production on Fri Dec 16 10:08:16 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit
Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01":  "sys/********@blc AS SYSDBA" dump
file=test.dmp logfile=test_imp.log directory=ro_dir transport_datafiles=D:\oracl
e\oradata\BLC\test01.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
ORA-39123: Data Pump transportable tablespace job aborted
ORA-29345: cannot plug a tablespace into a database using an incompatible charac
ter set

Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" stopped due to fatal error at 10:08:21

(2)　表空间导入之后的状态还是read only的，需要改回read write状态....

SQL> select status, tablespace_name from dba_tablespaces;

STATUS    TABLESPACE_NAME
--------- ------------------------------
READ ONLY TEST

 

(3) 进行TTS导出前，将相关的表空间置成read only 模式， 导出完了之后，再重新改成read write状态

 

(4) TTS的表空间是self-contained， 可以通过如下语句进行验证...

SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('TEST');
SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

(5) 从11g开始允许交叉平台的表空间传输，它可以把一个数据库从一个平台被移植到另一个平台。由于不同的操作系统平台数据格式是不一样的，因此，在传输时必须进行转换。

 各平台的格式可以通过如下语句查询得到...

    SQL> SELECT * FROM V$TRANSPORTABLE_PLATFORM;