Oracle 12C 数据泵新特性(DISABLE_ARCHIVE_LOGGING+VIEWS_AS_TABLES导出视图+LOGTIME)

0    94    1

Tags:

👉 本文共约10169个字,系统预计阅读时间或需39分钟。

在Oracle 12c中,在数据泵(expdp)方面有哪些增强的新特性?

Oracle 12c的数据泵新增了很多的新特性,分别如下所示:

在Data Pump中引入了新的TRANSFORM的选项DISABLE_ARCHIVE_LOGGING

(1)在Data Pump中引入了新的TRANSFORM的选项DISABLE_ARCHIVE_LOGGING,这对于表和索引在导入期间提供了关闭Redo日志生成的灵活性。当为TRANSFORM选项指定了DISABLE_ARCHIVE_LOGGING:Y值,那么在整个导入期间,表和索引的Redo日志就会处于关闭状态,仅生成少量的日志。这一功能在导入大型表时缓解了压力,并且减少了过度的Redo产生,从而加快了导入。这一属性对表和索引都适用。不管是在非归档还是归档情况下使用DISABLE_ARCHIVE_LOGGING都会减小导入时间,减少归档量。但是需要注意的是,如果数据库处于FORCE LOGGING模式,那么DISABLE_ARCHIVE_LOGGING参数会无效。

以下SQL演示了这一功能:

“TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y”表示表和索引都关闭日志。

有关该新特性需要注意以下几点:

① 在导入完成后,表和索引的状态(包括LOGGING状态和索引是否有效的状态)都会恢复到导入之前的状态。

② 在使用该参数导入数据文件后,如果相应的datafile被restored和recovered,那么接下来的涉及到目标表的查询会报ORA-01578和ORA-26040的坏块错误。例如:

例如:

因此,在使用该参数导入数据文件后,需要立马对相关的数据文件做RMAN备份。

使用VIEWS_AS_TABLES选项可以让数据泵将视图转换为表然后导出

(2)使用VIEWS_AS_TABLES选项可以让数据泵将视图转换为表然后导出。需要注意的是,导出到dmp文件后,视图的定义已经自动转换为表的定义了。在执行导入操作后,会以表的形式存在

以下SQL演示了这一功能:

表数据准备:

LOGTIME参数

(3)LOGTIME参数决定时间戳是否将包括在expdp和impdp功能的输出信息中。LOGTIME的可用值如下所示:

① NONE:默认值,指示输出中不包括时间戳,输出和之前的版本相似。

② STATUS:时间戳包括在控制台输出中,但不会在日志文件中出现。

③ LOGFILE:时间戳出现在日志文件中,但不会输出到控制台。

④ ALL:时间戳出现在日志文件和控制台输出中。

关于Oracle 12c的数据泵还有很多的新特性,例如压缩、加密、审计等,这里不再详细介绍,读者可参考作者的博客或阅读相关的官方文档进行学习。

1.1 关闭redo日志的生成(TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y)

Data Pump中引入了新的TRANSFORM选项,这对于对象在导入期间提供了关闭重做生成的灵活性。当为TRANSFORM选项指定了DISABLE_ARCHIVE_LOGGING值,那么在整个导入期间,重做生成就会处于关闭状态。这一功能在导入大型表时缓解了压力,并且减少了过度的redo产生,从而加快了导入。这一属性还可应用到表以及索引。

以下案例演示了这一功能:

impdp directory=dpump dumpfile=abcd.dmp TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y logfile=abcd.log

l impdp directory=dpump dumpfile=abcd.dmp TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y logfile=abcd.log

l impdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp SCHEMAS=hr TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y --表和索引都关闭日志

l impdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp SCHEMAS=hr TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y:TABLE --只有表关闭日志

l impdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp SCHEMAS=hr TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y TRANSFORM=DISABLE_ARCHIVE_LOGGING:N:INDEX

l impdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp SCHEMAS=hr TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y:INDEX

Enables you to alter object creation DDL for objects being imported.

Syntax and Description

TRANSFORM=transform_name:value[:object_type]

The transform_name specifies the name of the transform.The possible options are as follows,in alphabetical order:

?DISABLE_ARCHIVE_LOGGING:[Y|N]

If set to Y,then the logging attributes for the specified object types(TABLE and/or INDEX)are disabled before the data is imported.If set to N(the default),then archive logging is not disabled during import.After the data has been loaded,the logging attributes for the objects are restored to their original settings.If no object type is specified,then the DISABLE_ARCHIVE_LOGGING behavior is applied to both TABLE and INDEX object types.This transform works for both file mode imports and network mode imports.It does not apply to transportable tablespace imports.

Note:

If the database is in FORCE LOGGING mode,then the DISABLE_ARCHIVE_LOGGING option will not disable logging when indexes and tables are created.

将视图转换为表然后导出(VIEWS_AS_TABLES选项)

这是Data Pump中另外一个改进。有了VIEWS_AS_TABLES 选项,你就可以将视图数据载入表中。

以下案例演示了如何在导出过程中将视图数据载入到表中:

expdp directory=dpump views_as_tables=my_view:my_table dumpfile=abcd.dmp logfile=abcd.log

1.2.1 自己实验

expdp VIEWS_AS_TABLES选项可以将视图看做表并将其数据导出。

expdp system/lhr DIRECTORY=data_pump_dir DUMPFILE=expdp_vw.dmp LOGFILE=expdp_vw.log VIEWS_AS_TABLES=lhr.my_view

表数据准备:

create table lhr.my_tab1 (nr number, txt varchar2(10));

insert into lhr.my_tab1 values (1,'Line 1');

insert into lhr.my_tab1 values (2,'Line 2');

create table lhr.my_tab2 (nr number, col2 number, col3 varchar2(10));

insert into lhr.my_tab2 values (1,1,'c3_1');

insert into lhr.my_tab2 values (2,2,'c3_2');

commit;

create view lhr.my_view (nr, txt, col3) as

select t1.nr, t1.txt, t2.col3

from lhr.my_tab1 t1, lhr.my_tab2 t2

where t1.nr=t2.nr;

开始导出:

C:\Users\xiaomaimiao>expdp system/lhr DIRECTORY=data_pump_dir DUMPFILE=expdp_vw.dmp LOGFILE=expdp_vw.log VIEWS_AS_TABLES=lhr.my_view

Export: Release 12.1.0.2.0 - Production on 星期五 12月 16 16:31:49 2016

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

连接到: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

启动 "SYSTEM"."SYS_EXPORT_TABLE_01": system/**** DIRECTORY=data_pump_dir DUMPFILE=expdp_vw.dmp LOGFILE=expdp_vw.log VIEWS_AS_TABLES=lhr.my_view

正在使用 BLOCKS 方法进行估计...

处理对象类型 TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA

使用 BLOCKS 方法的总估计: 16 KB

处理对象类型 TABLE_EXPORT/VIEWS_AS_TABLES/TABLE

. . 导出了 "LHR"."MY_VIEW" 5.929 KB 2 行

已成功加载/卸载了主表 "SYSTEM"."SYS_EXPORT_TABLE_01"


SYSTEM.SYS_EXPORT_TABLE_01 的转储文件集为:

E:\APP\ORACLE\ADMIN\LHRDB12C\DPDUMP\EXPDP_VW.DMP

作业 "SYSTEM"."SYS_EXPORT_TABLE_01" 已于 星期五 12月 16 16:32:36 2016 elapsed 0 00:00:31 成功完成

查看其DDL语句:

C:\Users\xiaomaimiao>impdp system/lhr DIRECTORY=data_pump_dir DUMPFILE=expdp_vw.dmp LOGFILE=impdp_vw.log sqlfile=a.txt

Import: Release 12.1.0.2.0 - Production on 星期五 12月 16 16:35:14 2016

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

连接到: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

已成功加载/卸载了主表 "SYSTEM"."SYS_SQL_FILE_FULL_01"

启动 "SYSTEM"."SYS_SQL_FILE_FULL_01": system/**** DIRECTORY=data_pump_dir DUMPFILE=expdp_vw.dmp LOGFILE=impdp_vw.log sqlfile=a.txt

处理对象类型 TABLE_EXPORT/VIEWS_AS_TABLES/TABLE

作业 "SYSTEM"."SYS_SQL_FILE_FULL_01" 已于 星期五 12月 16 16:35:26 2016 elapsed 0 00:00:10 成功完成

DDL语句内容:

-- CONNECT SYSTEM

ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';

-- new object type path: TABLE_EXPORT/VIEWS_AS_TABLES/TABLE

CREATE TABLE "LHR"."MY_VIEW"

( "NR" NUMBER,

"TXT" VARCHAR2(10 BYTE),

"COL3" VARCHAR2(10 BYTE)

) SEGMENT CREATION DEFERRED

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

NOCOMPRESS LOGGING

TABLESPACE "USERS" ;

进行导入:

C:\Users\xiaomaimiao>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on 星期五 12月 16 16:37:03 2016

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

连接到:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> create user lhr01 identified by lhr;

用户已创建。

SQL> grant dba to lhr01;

授权成功。

SQL> exit

从 Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options 断开

C:\Users\xiaomaimiao>impdp system/lhr DIRECTORY=data_pump_dir DUMPFILE=expdp_vw.dmp LOGFILE=impdp_vw.log remap_schema=lhr:lhr01

Import: Release 12.1.0.2.0 - Production on 星期五 12月 16 16:39:49 2016

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

连接到: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

已成功加载/卸载了主表 "SYSTEM"."SYS_IMPORT_FULL_02"

启动 "SYSTEM"."SYS_IMPORT_FULL_02": system/**** DIRECTORY=data_pump_dir DUMPFILE=expdp_vw.dmp LOGFILE=impdp_vw.log remap_schema=lhr:lhr01

处理对象类型 TABLE_EXPORT/VIEWS_AS_TABLES/TABLE

处理对象类型 TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA

. . 导入了 "LHR01"."MY_VIEW" 5.929 KB 2 行

作业 "SYSTEM"."SYS_IMPORT_FULL_02" 已于 星期五 12月 16 16:39:57 2016 elapsed 0 00:00:06 成功完成

数据泵EXPDP/IMPDP在12C版本的新特性-VIEWS_AS_TABLES

VIEWS_AS_TABLES

默认值:无默认值

提示:
该参数会使用非加密的格式导出视图的数据到非加密的表。如果正在导出敏感数据,oracle强烈建议使用加密的方式导出并导出到加密的表空间。可以使REMAP_TABLESPACE参数更换导入的表空间。

目的:
指定一个或多个视图以表的形式导出。?
语法和说明?:
VIEWS_AS_TABLES=[schema_name.]view_name[:table_name], ...

数据泵以表的形式从视图中导出相同的列和数据,也会导出依赖于视图的对象,比如:授权和约束。但是不会导出依赖于表的对象。
该参数可以单独使用也可以和TABLES参数一起使用(可以在一个job中同时导出表和视图),如果单独使用,数据泵以导出表的方式导出视图。
语法元素的定义如下:?
schema_name: 指定的schema名称,如未指定,以当前用户执行导出。
view_name: 要导出为一个表的视图的名称。视图必须存在,并且它必须是关系视图,并且只有scalar, non-LOB 列。如果指定一个无效或不存在的视图,会跳过并返回一条错误消息。
table_name: 要作为导出视图的元数据源的表的名称。默认情况下数据泵会自动为视图创建一个临时"模板表",和视图有相同列和数据类型。如果数据库是只读的则此默认创建模板表将失败。在这种情况下,您可以指定表的名称。
表必须和视图在一个schema下。它必须是一个非分区的关系表(堆表)。它不能是嵌套表。?

如果导出作业包含多个视图与显式指定的模板表,模板表都必须不同。
如下:(在这两个视图使用同一个模板表),其中一个视图是跳过:?
expdp scott/tiger directory=dpump_dir dumpfile=a.dmp views_as_tables=v1:emp,v2:emp
?报告跳过的对象并返回错误消息。?
?完成导出操作后自动删除模板表。可以执行以下查询以查看(名字以?KU$VAT开头):
SQL> SELECT * FROM user_tab_comments WHERE table_name LIKE 'KU$VAT%';
TABLE_NAME TABLE_TYPE


COMMENTS
-----------------------------------------------------
KU$VAT_63629 TABLE
Data Pump metadata template table for view SCOTT.EMPV
?限制?
?该参数不能与TRANSPORTABLE=ALWAYS参数一起使用。?
?使用该参数创建的表不包含任何隐藏的列,以指定创建的表是原视图的一部分。?
?该参数不支持LONG数据类型。
?示例?
?下面的示例导出视图的内容到一个名为的转储文件。?scott.view1scott1.dmp
> expdp scott/tiger views_as_tables=view1 directory=data_pump_dir dumpfile=scott1.dmp
dmp文件将包含一个名为view1的表。?该表所有的行从视图view1获取。



Oracle 12c 新特性 --- Oracle Data Pump Export View As a Table

概念

There is a new expdp command-line option for Oracle Data Pump Export that allows the user to indicate that a view should be exported as a table. This means that, instead of exporting the view definition, Oracle Data Pump exports a table definition and then unloads all data from the view. At import time, Oracle Data Pump creates a table using the table definition in the dump file and then inserts the data unloaded from the view into the table. The PL/SQL DBMS_DATAPUMP package has a similar option.

This feature allows greater flexibility in what a DBA can export. A view gives the DBA greater capability than the current WHERE parameter to specify a subset of the database to be unloaded. In a network mode import, exporting the contents of a view can achieve much better performance than using the impdp QUERY option.

对于Oracle数据泵导出,有一个新的expdp命令行选项,允许用户指出应该将视图导出为表。这意味着,Oracle数据泵不是导出视图定义,而是导出一个表定义,然后从视图中卸载所有数据。在导入时,Oracle数据泵在转储文件中使用表定义创建一个表,然后将从视图中卸载的数据插入到表中。PL / SQL DBMS_DATAPUMP包有类似的选项。

这个特性使DBA能够导出的内容更加灵活。视图赋予DBA更大的能力,而不是当前的WHERE参数指定要卸载的数据库的子集。在网络模式导入中,导出视图的内容可以比使用impdp查询选项获得更好的性能。

实验

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.

本人提供Oracle、MySQL、PG等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!

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

12c DataPump EXPORT (EXPDP) Enhancements (文档 ID 2171666.1)

Oracle Database - Enterprise Edition - Version 12.1.0.1 to 12.1.0.2 [Release 12.1]
Information in this document applies to any platform.

What are the 12c new features for DataPump Export (EXPDP)?

Audit all expdp/impdp operations with unified auditing

Example:
CREATE AUDIT POLICY ACTIONS COMPONENT=DATAPUMP { EXPORT | IMPORT | ALL };

Keep policy
AUDIT POLICY BY SYSTEM;

You can query the UNIFIED_AUDIT_TRAIL data dictionary view to find Oracle Data Pump audited events:

SELECT DP_TEXT_PARAMETERS1, DP_BOOLEAN_PARAMETERS1 FROM UNIFIED_AUDIT_TRAIL WHERE AUDIT_TYPE = 'DATAPUMP';

DP_TEXT_PARAMETERS1 DP_BOOLEAN_PARAMETERS1


MASTER TABLE: SCOTT.SYS_EXPORT_TABLE_01, MASTER_ONLY: FALSE,
JOB_TYPE: EXPORT, DATA_ONLY: FALSE,
METADATA_JOB_MODE: TABLE_EXPORT, METADATA_ONLY: FALSE,
JOB VERSION: 12.1.0.0, DUMPFILE_PRESENT: TRUE,
ACCESS METHOD: DIRECT_PATH, JOB_RESTARTED: FALSE
DATA OPTIONS: 0,
DUMPER DIRECTORY: NULL
REMOTE LINK: NULL,
TABLE EXISTS: NULL,
PARTITION OPTIONS: NONE

Export one or more views as tables

The new VIEWS_AS_TABLES parameter allows you to export one or more views as tables. Data Pump exports a table with the same columns as the view and with row data fetched from the view.
Data Pump also exports objects dependent on the view, such as grants and constraints. Dependent objects that do not apply to tables (for example, grants of the UNDER object privilege) are not exported.
The VIEWS_AS_TABLES parameter can be used by itself or along with the TABLES parameter.

Example:-
> expdp scott/tiger DIRECTORY=dpump1 DUMPFILE=scott1.dmp VIEWS_AS_TABLES=empview tables=emp

Silent encryption password for expdp job

You can now specify silently a password during expdp runtime. When ENCRYPTION_PWD_PROMPT=YES on the command line, DataPump will prompt you for the encryption password, rather than you entering it
on the command line with the ENCRYPTION_PASSWORD parameter. The advantage to doing this is that the encryption password is not echoed to the screen when it is entered at the prompt.
Whereas, when it is entered on the command line using the ENCRYPTION_PASSWORD parameter, it appears in plain text.
The password will not be visible by commands like ps or will not be stored in scripts. If you specify an encryption password on the export operation, you must also supply it on the import operation.

Example:-
expdp scott/tiger DIRECTORY=dpump1 DUMPFILE=export.dmp ENCRYPTION_PWD_PROMPT=Y

Transportable feature

The transportable option specifies whether the transportable option should be used during a table mode export (specified with the TABLES parameter) or a full mode export (specified with the FULL parameter).

A. Full Transportable Export/Import (Full Database)

Example:-
\1) Make the tablespaces read only

\2) Export the database:
expdp user_name full=y dumpfile=expdat.dmp directory=data_pump_dir transportable=always logfile=export.log

\3) Check the export log, to determine the datafiles which should be copied to target system.
Example:-


Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
/u01/app/oracle/admin/mydb/dpdump/expdat.dmp


Datafiles required for transportable tablespace SALES:
/u01/app/oracle/oradata/mydb/sales01.dbf
Datafiles required for transportable tablespace CUSTOMERS:
/u01/app/oracle/oradata/mydb/cust01.dbf
Datafiles required for transportable tablespace EMPLOYEES:
/u01/app/oracle/oradata/mydb/emp01.dbf

\4) Check the endian conversion if required

SELECT d.PLATFORM_NAME, ENDIAN_FORMAT
FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;

\5) Copy the datafile and the dump file to target environment

\6) Import the database
impdp scott/tiger FULL=y DUMPFILE=fullexp.dmp DIRECTORY=dpump1 TRANSPORT_DATAFILES='/u01/app/oracle/oradata/db1211/users01.dbf' LOGFILE=import.log

B. Transportable Export/Import (Tables/Partitions)

Example: -
\1) Mark the datafiles that are associated with the table as read only:
SQL>ALTER TABLESPACE sales_prt_tbs READ ONLY;

\2) Export using expdp, tables:

> expdp user_name dumpfile=sales_prt.dmp directory=data_pump_dir transportable=always logfile=exp.log tables=sh.sales_prt:sales_q1_2000,sh.sales_prt:sales_q2_2000

\3) Check the log for the datafiles to be copied.

Datafiles required for transportable tablespace SALES_PRT_TBS:
/u01/app/oracle/oradata/sourcedb/sales_prt.dbf
Job SYSTEM.SYS_EXPORT_TABLE_01 successfully completed at 11:32:13

\4) Copy the datafile(s) and the dump file to target environment

\5) Import the dump:
> impdp user_name dumpfile=sales_prt.dmp directory=data_pump_dir logfile=imp.log transport_datafiles='/u01/app/oracle/oradata/targetdb/sales_prt.dbf' tables=sh.sales_prt:sales_q1_2000,sh.sales_prt:sales_q2_2000

Note: If you want to export an entire tablespace in transportable mode, then use the TRANSPORT_TABLESPACES parameter.

REFERENCES

NOTE:2171674.1 - 12c DataPump IMPORT (IMPDP) Enhancements

标签:

头像

小麦苗

学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注

3 × 5 =

 

嘿,我是小麦,需要帮助随时找我哦
  • 18509239930
  • 个人微信

  • 麦老师QQ聊天
  • 个人邮箱
  • 点击加入QQ群
  • 个人微店

  • 回到顶部
返回顶部