真題1、在Oracle 12c中，在資料泵（expdp）方面有哪些增強的新特性？

答案：Oracle 12c的資料泵新增了很多的新特性，分別如下所示：

（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備份。

（2）使用VIEWS_AS_TABLES選項可以讓資料泵將檢視轉換為表然後匯出。需要注意的是，匯出到dmp檔案後，檢視的定義已經自動轉換為表的定義了。在執行匯入操作後，會以表的形式存在。

以下SQL演示了這一功能：

表資料準備：

（3）LOGTIME引數決定時間戳是否將包括在expdp和impdp功能的輸出資訊中。LOGTIME的可用值如下所示：

①　NONE：預設值，指示輸出中不包括時間戳，輸出和之前的版本相似。

②　STATUS：時間戳包括在控制檯輸出中，但不會在日誌檔案中出現。

③　LOGFILE：時間戳出現在日誌檔案中，但不會輸出到控制檯。

④　ALL：時間戳出現在日誌檔案和控制檯輸出中。

關於Oracle 12c的資料泵還有很多的新特性，例如壓縮、加密、審計等，這裡不再詳細介紹，讀者可參考作者的部落格或閱讀相關的官方文件進行學習。

Data Pump 版本有了不少有用的改進，例如在匯出時將檢視轉換為表，以及在匯入時關閉日誌記錄等。

1.1 關閉redo日誌的生成（TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y）

Data Pump中引入了新的TRANSFORM選項，這對於物件在匯入期間提供了關閉重做生成的靈活性。當為TRANSFORM選項指定了DISABLE_ARCHIVE_LOGGING值，那麼在整個匯入期間，重做生成就會處於關閉狀態。這一功能在匯入大型表時緩解了壓力，並且減少了過度的redo產生，從而加快了匯入。這一屬性還可應用到表以及索引。

以下案例演示了這一功能：

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.

1.2 將檢視轉換為表然後匯出（VIEWS_AS_TABLES選項）

這是Data Pump中另外一個改進。有了VIEWS_AS_TABLES 選項，你就可以將檢視資料載入表中。

以下案例演示瞭如何在匯出過程中將檢視資料載入到表中：

1.2.1      自己實驗

expdp VIEWS_AS_TABLES選項可以將檢視看做表並將其資料匯出。

表資料準備：

開始匯出：

檢視其DDL語句：

DDL語句內容：

進行匯入：

資料泵EXPDP/IMPDP在12C版本的新特性-VIEWS_AS_TABLES

語法：
VIEWS_AS_TABLES=[schema_name.]view_name,...
The VIEWS_AS_TABLES parameter can be used by itself or along with the TABLES parameter. If either is used, Data Pump performs a table-mode import.
The syntax elements are defined as follows:
schema_name--The name of the schema in which the view resides. If a schema name is not supplied, it defaults to the user performing the import.
view_name--The name of the view to be imported as a table.

1)建立檢視
SQL> alter session set container=pdbcndba;

Session altered.

SQL> conn test/test@pdbcndba
Connected.

SQL> create or replace view vw_test as select OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID from leo2;

View created.

SQL> select count(*) from vw_test;

  COUNT(*)
----------
     90936

SQL> select OBJECT_TYPE from all_objects where OWNER='TEST' and OBJECT_NAME ='VW_TEST';

OBJECT_TYPE
-----------------------
VIEW

2）匯出檢視
[oracle@host1 ~]$ expdp test/test@pdbcndba views_as_tables=test.vw_test directory=dpump_dir1 dumpfile=vw_test.dmp logfile=vw_test.log 

Export: Release 12.1.0.2.0 - Production on Fri Aug 4 17:33:43 2017

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 "TEST"."SYS_EXPORT_TABLE_01":  test/********@pdbcndba views_as_tables=test.vw_test directory=dpump_dir1 dumpfile=vw_test.dmp logfile=vw_test.log 
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 "TEST"."VW_TEST"                            3.520 MB   90936 rows
Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_TABLE_01 is:
  /backup/vw_test.dmp
Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at Fri Aug 4 17:33:45 2017 elapsed 0 00:00:01

可以看到檢視和資料一併匯出。
轉儲檔案將包含一個名為vw_test的表，其中的行是從檢視中提取的。
3) 刪除檢視，並將匯出的檢視vw_test.dmp檔案匯入資料中
SQL> drop view vw_test;                                                                

View dropped.

[oracle@host1 ~]$ impdp test/test@pdbcndba VIEWS_AS_TABLES=vw_test directory=dpump_dir1 dumpfile=vw_test.dmp logfile=vw_test2.log 

Import: Release 12.1.0.2.0 - Production on Fri Aug 4 17:34:30 2017

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 "TEST"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "TEST"."SYS_IMPORT_TABLE_01":  test/********@pdbcndba VIEWS_AS_TABLES=vw_test directory=dpump_dir1 dumpfile=vw_test.dmp logfile=vw_test2.log 
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
. . imported "TEST"."VW_TEST"                            3.520 MB   90936 rows
Job "TEST"."SYS_IMPORT_TABLE_01" successfully completed at Fri Aug 4 17:34:31 2017 elapsed 0 00:00:01
4）檢視物件型別變為表，驗證了View As a Table新特性
SQL> select OBJECT_TYPE from all_objects where OWNER='TEST' and OBJECT_NAME ='VW_TEST';

OBJECT_TYPE
-----------------------
TABLE

SQL> select count(*) from vw_test;

  COUNT(*)
----------
     90936

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

In this Document

APPLIES TO:

GOAL

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

SOLUTION

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

impdp中的DISABLE_ARCHIVE_LOGGING引數測試

About Me

............................................................................................................................................. ● 本文作者：小麥苗，部分內容整理自網路，若有侵權請聯絡小麥苗刪除 ● 本文在itpub（http://blog.itpub.net/26736162/abstract/1/）、部落格園（http://www.cnblogs.com/lhrbest）和個人微信公眾號（xiaomaimiaolhr）上有同步更新 ● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/ ● 本文部落格園地址：http://www.cnblogs.com/lhrbest ● 本文pdf版、個人簡介及小麥苗雲盤地址：http://blog.itpub.net/26736162/viewspace-1624453/ ● 資料庫筆試面試題庫及解答：http://blog.itpub.net/26736162/viewspace-2134706/ ● DBA寶典今日頭條號地址： ............................................................................................................................................. ● QQ群號：230161599（滿）、618766405 ● 微信群：可加我微信，我拉大家進群，非誠勿擾 ● 聯絡我請加QQ好友（646634621），註明新增緣由 ● 於 2018-04-01 06:00 ~ 2018-04-31 24:00 在魔都完成 ● 最新修改時間：2018-04-01 06:00 ~ 2018-04-31 24:00 ● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解 ● 版權所有，歡迎分享本文，轉載請保留出處 ............................................................................................................................................. ● 小麥苗的微店： ● 小麥苗出版的資料庫類叢書：http://blog.itpub.net/26736162/viewspace-2142121/ ● 小麥苗OCP、OCM、高可用網路班：http://blog.itpub.net/26736162/viewspace-2148098/ ............................................................................................................................................. 使用微信客戶端掃描下面的二維碼來關注小麥苗的微信公眾號（xiaomaimiaolhr）及QQ群（DBA寶典），學習最實用的資料庫技術。    小麥苗的微信公眾號      小麥苗的DBA寶典QQ群2     《DBA筆試面試寶典》讀者群       小麥苗的微店 .............................................................................................................................................