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].
VIEWS_AS_TABLES
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