[expdp]一道lz0-042题的验证

lz0-042中有一道题：
62. User SCOTT wants to export his objects using Oracle Data Pump and executes the following command:
$ expdp scott/tiger
directory = EXPORT_DIR
dumpfile = scott.dmp
include = table
include = view) "like '%DEPARTMENTS%'"
content = DATA_ONLY
Which task would the command accomplish?
A) Oracle Data Pump would export only the data of all of the tables and views.
B) Oracle Data Pump would export all of the table structures along with data and all the views.
C) Oracle Data Pump would export the table data and the view definitions where the view name contains a string named DEPARTMENTS.
D) Oracle Data Pump would export the table data and the view definitions with data where view name contains a string named DEPARTMENTS.
E) Oracle Data Pump would export all of the table structures and the view definitions with data where view name contains a string named DEPARTMENTS.

题目答案是C，导出表中的数据以及包含DEPARTMENTS的视图定义。

做了一个实验，觉得答案有待商榷。

以test_user做实验。
先看一下test_user的数据库对象：
test_user$orcl@localhost.localdomain SQL> select object_name,object_type from user_objects;

OBJECT_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
OBJECT_TYPE
---------------------------------------------------------
PK_TEST
INDEX

TEST_T
TABLE

TEST_VW
VIEW

按照题目中的参数进行导出：
[oracle@localhost log]$ pwd
/u01/app/oracle/product/10.2.0/db_1/rdbms/log
[oracle@localhost log]$ ls
par
[oracle@localhost log]$ cat par
userid=test_user/test_user
directory=data_pump_dir
dumpfile=data_includes.dmp
content=data_only
include=table
include=view:"like 'TEST%'"
[oracle@localhost log]$
[oracle@localhost log]$ expdp parfile=par

Export: Release 10.2.0.1.0 - Production on 星期日, 14 11月, 2010 20:17:19

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 "TEST_USER"."SYS_EXPORT_SCHEMA_01":  parfile=par
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
. . exported "TEST_USER"."TEST_T"                        5.257 KB       2 rows
ORA-39168: Object path VIEW was not found.
Master table "TEST_USER"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST_USER.SYS_EXPORT_SCHEMA_01 is:
  /u01/app/oracle/product/10.2.0/db_1/rdbms/log/data_includes.dmp
Job "TEST_USER"."SYS_EXPORT_SCHEMA_01" completed with 1 error(s) at 20:17:29

[oracle@localhost log]$ ls
data_includes.dmp  export.log  par
[oracle@localhost log]$

导出的过程中产生的一个错误ora-39186,视图定义应该没有导出。

去掉content=data_only参数，再次尝试导出：
[oracle@localhost log]$ cat par
userid=test_user/test_user
directory=data_pump_dir
dumpfile=includes.dmp
include=table
include=view:"like 'TEST%'"
[oracle@localhost log]$ expdp parfile=par

Export: Release 10.2.0.1.0 - Production on 星期日, 14 11月, 2010 20:22:50

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 "TEST_USER"."SYS_EXPORT_SCHEMA_01":  parfile=par
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/VIEW/VIEW
. . exported "TEST_USER"."TEST_T"                        5.257 KB       2 rows
Master table "TEST_USER"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST_USER.SYS_EXPORT_SCHEMA_01 is:
  /u01/app/oracle/product/10.2.0/db_1/rdbms/log/includes.dmp
Job "TEST_USER"."SYS_EXPORT_SCHEMA_01" successfully completed at 20:23:08

[oracle@localhost log]$ ls -lt
total 312
-rw-r----- 1 oracle oinstall 155648 Nov 14 20:23 includes.dmp
-rw-r--r-- 1 oracle oinstall   1242 Nov 14 20:23 export.log
-rw-r--r-- 1 oracle oinstall    115 Nov 14 20:22 par
-rw-r----- 1 oracle oinstall 131072 Nov 14 20:17 data_includes.dmp
[oracle@localhost log]$

这次导出成功。以SQLFILE导入方式查看导出DMP文件中的DDL:
[oracle@localhost log]$ impdp test_user/test_user directory=data_pump_dir dumpfile=includes.dmp sqlfile=includes.sql

Import: Release 10.2.0.1.0 - Production on 星期日, 14 11月, 2010 20:25:23

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
Master table "TEST_USER"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "TEST_USER"."SYS_SQL_FILE_FULL_01":  test_user/******** directory=data_pump_dir dumpfile=includes.dmp sqlfile=includes.sql
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Job "TEST_USER"."SYS_SQL_FILE_FULL_01" successfully completed at 20:25:29

[oracle@localhost log]$ cat includes.sql
-- CONNECT TEST_USER
-- new object type path is: SCHEMA_EXPORT/TABLE/TABLE
CREATE TABLE "TEST_USER"."TEST_T"
   (    "COL_X" VARCHAR2(10),
        "COL_N" NUMBER(*,0)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" ;
 
-- new object type path is: SCHEMA_EXPORT/TABLE/INDEX/INDEX
CREATE UNIQUE INDEX "TEST_USER"."PK_TEST" ON "TEST_USER"."TEST_T" ("COL_X")
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" PARALLEL 1 ;

  ALTER INDEX "TEST_USER"."PK_TEST" NOPARALLEL;
 
-- new object type path is: SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
ALTER TABLE "TEST_USER"."TEST_T" ADD CONSTRAINT "PK_TEST" PRIMARY KEY ("COL_X")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"  ENABLE;
 
ALTER TABLE "TEST_USER"."TEST_T" ADD CONSTRAINT "CHK_TEST" CHECK (col_n > 0) ENABLE;
 
-- new object type path is: SCHEMA_EXPORT/VIEW/VIEW
CREATE  FORCE VIEW "TEST_USER"."TEST_VW" ("Nation", "Age") AS
  select col_x as "Nation",extract(year from sysdate)-col_n as "Age" from test_t;
 
[oracle@localhost log]$

可以看到，这次导出了table和包含TEST字符串的view。

实验结论
把上面两个实验对比可以发现，data_only似乎不应该与include=view同时使用，与include=table似乎还说得通，虽然有点多此一举。

expdp使用各种参数各种取值，其结果变幻无穷，让人琢磨不透，需要大量的实验和透彻的分析才能理清一二。

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

转载于:http://blog.itpub.net/24131851/viewspace-678073/