1.1 数据泵新特性测试
1.1.1 数据导出工具expdp差异
The available keywords and their descriptions follow. Default values are listed within square brackets.
ABORT_STEP
Stop the job after it is initialized or at the indicated object.
Valid values are -1 or N where N is zero or greater.
N corresponds to the object's process order number in the master table.
ACCESS_METHOD
Instructs Export to use a particular method to unload data.
Valid keyword values are: [AUTOMATIC], DIRECT_PATH and EXTERNAL_TABLE.
COMPRESSION_ALGORITHM ----压缩算法
Specify the compression algorithm that should be used.
Valid keyword values are: [BASIC], LOW, MEDIUM and HIGH.
ENCRYPTION_PWD_PROMPT
Specifies whether to prompt for the encryption password [NO].
Terminal echo will be suppressed while standard input is read.
KEEP_MASTER
Retain the master table after an export job that completes successfully [NO].
LOGTIME
Specifies that messages displayed during export operations be timestamped.
Valid keyword values are: ALL, [NONE], LOGFILE and STATUS.
METRICS
Report additional job information to the export log file [NO].
Identifies one or more views to be exported as tables.
For example, VIEWS_AS_TABLES=HR.EMP_DETAILS_VIEW.
START_JOB
Start or resume current job.
Valid keyword values are: SKIP_CURRENT.
1.1.2 视图转换成表
SQL> show con_name
CON_NAME
------------------------------
PDBA
SQL> show user
USER is "SCOTT"
SQL> select table_name from user_tables;
TABLE_NAME
--------------------
SALGRADE
BONUS
EMP
DEPT
SQL> create view v_emp as select * from emp;
View created.
SQL> select object_name,object_type from user_objects where object_type not like 'INDEX';
OBJECT_NAME OBJECT_TYPE
------------------------------ -----------------------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
V_EMP VIEW
测试中我们将上面的V_EMP转换成V_emp_TAB:
l 导出是将试图转换成表：
[oracle@DBA12C03 dump]$ expdp scott/scott@pdba dumpfile=view_to_table_02.dmp logfile=view_to_table_02.log views_as_tables=v_emp directory=expdp
Export: Release 12.1.0.2.0 - Production on Wed Jan 14 16:36:43 2015
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/********@pdba dumpfile=view_to_table_02.dmp logfile=view_to_table_02.log views_as_tables=v_emp directory=expdp
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
Total estimation using BLOCKS method: 16 KB
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
. . exported "SCOTT"."V_EMP" 8.781 KB 14 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
/dump/view_to_table_02.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Wed Jan 14 16:36:52 2015 elapsed 0 00:00:08
l 导入转换出来的表
如果还是本地导入，则在导入的时候一定要注意需要将本地的视图删除，否则会报错如下：
[oracle@DBA12C03 dump]$ impdp scott/scott@pdba dumpfile=view_to_table_02.dmp logfile=imp_view_to_table_02.log directory=expdp
Import: Release 12.1.0.2.0 - Production on Wed Jan 14 16:39:42 2015
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "SCOTT"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_FULL_01": scott/********@pdba dumpfile=view_to_table_02.dmp logfile=imp_view_to_table_02.log directory=expdp
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
ORA-39325: TABLE_EXISTS_ACTION cannot be applied to "SCOTT"."V_EMP".
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
Job "SCOTT"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Wed Jan 14 16:39:44 2015 elapsed 0 00:00:01
即使在导入时使用了table_exists_action同样出错，同上一样。
删除视图开始导入：
SQL> drop view v_emp;
View dropped.
[oracle@DBA12C03 dump]$ impdp scott/scott@pdba dumpfile=view_to_table_02.dmp logfile=imp_view_to_table_02.log directory=expdp
Import: Release 12.1.0.2.0 - Production on Wed Jan 14 16:41:46 2015
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "SCOTT"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_FULL_01": scott/********@pdba dumpfile=view_to_table_02.dmp logfile=imp_view_to_table_02.log directory=expdp
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
. . imported "SCOTT"."V_EMP" 8.781 KB 14 rows
Job "SCOTT"."SYS_IMPORT_FULL_01" successfully completed at Wed Jan 14 16:41:48 2015 elapsed 0 00:00:01
OBJECT_NAME OBJECT_TYPE
------------------------------ -----------------------
V_EMP TABLE
SALGRADE TABLE
BONUS TABLE
EMP TABLE
DEPT TABLE
关于导出视图成为表还有其他方式：
expdp scott/scott@pdba dumpfile=view_to_table_03.dmp logfile=view_to_table_03.log views_as_tables=emp_v:v_guijian_tab directory=expdp
Export: Release 12.1.0.2.0 - Production on Wed Jan 14 16:45:23 2015
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/********@pdba dumpfile=view_to_table_03.dmp logfile=view_to_table_03.log views_as_tables=emp_v:v_guijian_tab directory=expdp
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
Total estimation using BLOCKS method: 16 KB
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
. . exported "SCOTT"."EMP_V" 8.789 KB 14 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
/dump/view_to_table_03.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Wed Jan 14 16:45:31 2015 elapsed 0 00:00:07
