【12C】資料泵新特性（DISABLE_ARCHIVE_LOGGING+VIEWS_AS_TABLES匯出檢視+LOGTIME）

lhrbest發表於2016-12-16
HiveView
【12C】12c資料泵新特性測試（關閉日誌DISABLE_ARCHIVE_LOGGING+VIEWS_AS_TABLES選項匯出檢視）

 

真題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演示了這一功能：

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

“TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y”表示表和索引都關閉日誌。

有關該新特性需要注意以下幾點：

①　在匯入完成後，表和索引的狀態（包括LOGGING狀態和索引是否有效的狀態）都會恢復到匯入之前的狀態。

②　在使用該引數匯入資料檔案後，如果相應的datafile被restored和recovered，那麼接下來的涉及到目標表的查詢會報ORA-01578和ORA-26040的壞塊錯誤。例如：

例如：

SQL> select * from test_nologging;

 

ORA-01578: ORACLE data block corrupted (file # 11, block # 84)

ORA-01110: data file 4: '/oradata/users.dbf'

ORA-26040: Data block was loaded using the NOLOGGING option

因此，在使用該引數匯入資料檔案後，需要立馬對相關的資料檔案做RMAN備份。

 

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

以下SQL演示了這一功能：

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

l   impdp system/lhr DIRECTORY=data_pump_dir DUMPFILE=expdp_vw.dmp LOGFILE=impdp_vw.log remap_schema=lhr:lhr01

l   impdp lhr/lhr DIRECTORY=data_pump_dir DUMPFILE=expdp_vw.dmp LOGFILE=impdp_vw.log remap_schema=lhr:lhr1  VIEWS_AS_TABLES=lhr.my_view

l   impdp lhr/lhr DIRECTORY=data_pump_dir DUMPFILE=expdp_vw.dmp LOGFILE=impdp_vw.log remap_schema=lhr:lhr1  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;

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

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

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

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

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

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

 

& 說明：

有關12c資料泵新特性的更多內容可以參考我的BLOG：http://blog.itpub.net/26736162/viewspace-2130830/

有關ORA-01578和ORA-26040--NOLOGGING操作引起的壞塊的更多內容可以參考我的BLOG：http://blog.itpub.net/26736162/viewspace-2152783/

 

 


     

   

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

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.



1.2 將檢視轉換為表然後匯出（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查詢選項獲得更好的效能。


實驗 



語法：
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





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

In this Document

Goal
Solution
  Audit all expdp/impdp operations with unified auditing
  Export one or more views as tables
  Silent encryption password for expdp job
  Transportable feature
References


APPLIES TO:

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.

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

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




impdp中的DISABLE_ARCHIVE_LOGGING引數測試

在oracle 12c版本中引入了impdp中的TRANSFORM中的DISABLE_ARCHIVE_LOGGING值，可以實現在匯入的時候使用nologging處理從而減少日誌量也增加速度,但是在force logging情況下該引數無效
建立測試表

[oracle@localhost ~]$ sqlplus xff/oracle@localhost/pdb
 
SQL*Plus: Release 12.2.0.1.0 Production on Fri Apr 7 10:20:45 2017
 
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
 
SQL> create table t_xifenfei as select * from dba_objects;
 
Table created.
 
SQL> insert into t_xifenfei select * from t_xifenfei;
 
217838 rows created.
 
SQL> /
 
435676 rows created.
 
SQL> /
 
871352 rows created.
 
SQL> /
 
1742704 rows created.
 
SQL> commit;
 
Commit complete.
 
SQL> select count(*) from t_xifenfei;
 
  COUNT(*)
----------
   3485408

匯出測試表

[oracle@localhost ~]$ expdp xff/oracle@localhost/pdb dumpfile=t_xifenfei.dmp tables=t_xifenfei REUSE_DUMPFILES=yes
 
Export: Release 12.2.0.1.0 - Production on Fri Apr 7 11:55:01 2017
 
Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
 
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "XFF"."SYS_EXPORT_TABLE_01":  xff/********@localhost/pdb dumpfile=t_xifenfei.dmp tables=t_xifenfei REUSE_DUMPFILES=yes
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "XFF"."T_XIFENFEI"                          460.6 MB 3485408 rows
Master table "XFF"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for XFF.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/admin/xffdb/dpdump/4A93528C587D82CEE055000000000001/t_xifenfei.dmp
Job "XFF"."SYS_EXPORT_TABLE_01" successfully completed at Fri Apr 7 11:55:59 2017 elapsed 0 00:00:58

歸檔模式下不使用DISABLE_ARCHIVE_LOGGING匯入

[oracle@localhost rdbms]$ sqlplus / as sysdba
 
SQL*Plus: Release 12.2.0.1.0 Production on Sat Apr 8 02:43:23 2017
 
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
 
SQL> select force_logging from v$database;
 
FORCE_LOGGING
---------------------------------------
NO
 
SQL> alter system switch logfile;
 
System altered.
 
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     14
Next log sequence to archive   16
Current log sequence           16
 
[oracle@localhost ~]$  impdp xff/oracle@localhost/pdb dumpfile=t_xifenfei.dmp
 
Import: Release 12.2.0.1.0 - Production on Sat Apr 8 02:46:05 2017
 
Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
 
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "XFF"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "XFF"."SYS_IMPORT_FULL_01":  xff/********@localhost/pdb dumpfile=t_xifenfei.dmp
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "XFF"."T_XIFENFEI"                          460.6 MB 3485408 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "XFF"."SYS_IMPORT_FULL_01" successfully completed at Sat Apr 8 02:47:08 2017 elapsed 0 00:01:02
 
[oracle@localhost ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 12.2.0.1.0 Production on Sat Apr 8 02:47:30 2017
 
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
 
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     17
Next log sequence to archive   19
Current log sequence           19

這裡可以看出來,匯入過程使用時間為1分鐘多,匯入過程日誌切換 了3次

歸檔模式下使用DISABLE_ARCHIVE_LOGGING匯入

[oracle@localhost ~]$ sqlplus xff/oracle@localhost/pdb
 
SQL*Plus: Release 12.2.0.1.0 Production on Sat Apr 8 02:49:23 2017
 
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
 
Last Successful login time: Sat Apr 08 2017 02:46:05 -04:00
 
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
 
SQL> drop table t_xifenfei purge;
 
Table dropped.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@localhost ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 12.2.0.1.0 Production on Sat Apr 8 02:50:00 2017
 
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
 
SQL> alter system switch logfile;
 
System altered.
 
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     18
Next log sequence to archive   20
Current log sequence           20
 
 
[oracle@localhost ~]$  impdp xff/oracle@localhost/pdb dumpfile=t_xifenfei.dmp TRANSFORM=DISABLE_ARCHIVE_LOGGING:y
 
Import: Release 12.2.0.1.0 - Production on Sat Apr 8 02:54:49 2017
 
Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
 
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "XFF"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "XFF"."SYS_IMPORT_FULL_01":  xff/********@localhost/pdb dumpfile=t_xifenfei.dmp TRANSFORM=DISABLE_ARCHIVE_LOGGING:y
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "XFF"."T_XIFENFEI"                          460.6 MB 3485408 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "XFF"."SYS_IMPORT_FULL_01" successfully completed at Sat Apr 8 02:55:00 2017 elapsed 0 00:00:10
[oracle@localhost ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 12.2.0.1.0 Production on Sat Apr 8 02:55:45 2017
 
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
 
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     18
Next log sequence to archive   20
Current log sequence           20

這裡可以看出來當使用了DISABLE_ARCHIVE_LOGGING為Y之後匯入日誌沒有發生切換,匯入時間僅為10s.

非歸檔模式下不使用DISABLE_ARCHIVE_LOGGING匯入

SQL> alter system switch logfile;
 
System altered.
 
SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     19
Current log sequence           21
SQL> drop table xff.t_xifenfei purge;
 
Table dropped.
 
[oracle@localhost ~]$  impdp xff/oracle@localhost/pdb dumpfile=t_xifenfei.dmp
 
Import: Release 12.2.0.1.0 - Production on Sat Apr 8 03:22:42 2017
 
Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
 
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "XFF"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "XFF"."SYS_IMPORT_FULL_01":  xff/********@localhost/pdb dumpfile=t_xifenfei.dmp
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "XFF"."T_XIFENFEI"                          460.6 MB 3485408 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "XFF"."SYS_IMPORT_FULL_01" successfully completed at Sat Apr 8 03:23:17 2017 elapsed 0 00:00:27
 
[oracle@localhost ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 12.2.0.1.0 Production on Sat Apr 8 03:23:49 2017
 
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
 
SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     19
Current log sequence           21

這裡測試在非歸檔模式下不設定DISABLE_ARCHIVE_LOGGING，日誌量增加不明顯，匯入時間變為為27秒.

非歸檔模式下使用DISABLE_ARCHIVE_LOGGING匯入

[oracle@localhost ~]$ sqlplus xff/oracle@localhost/pdb
 
SQL*Plus: Release 12.2.0.1.0 Production on Sat Apr 8 03:24:10 2017
 
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
 
Last Successful login time: Sat Apr 08 2017 03:22:43 -04:00
 
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
 
SQL> drop table t_xifenfei purge;
 
Table dropped.
 
SQL> conn / as sysdba
Connected.
SQL> alter system switch logfile;
 
System altered.
 
SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     20
Current log sequence           22
 
[oracle@localhost ~]$  impdp xff/oracle@localhost/pdb dumpfile=t_xifenfei.dmp TRANSFORM=DISABLE_ARCHIVE_LOGGING:y
 
Import: Release 12.2.0.1.0 - Production on Sat Apr 8 03:25:51 2017
 
Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
 
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "XFF"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "XFF"."SYS_IMPORT_FULL_01":  xff/********@localhost/pdb dumpfile=t_xifenfei.dmp TRANSFORM=DISABLE_ARCHIVE_LOGGING:y
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "XFF"."T_XIFENFEI"                          460.6 MB 3485408 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "XFF"."SYS_IMPORT_FULL_01" successfully completed at Sat Apr 8 03:26:01 2017 elapsed 0 00:00:10
[oracle@localhost ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 12.2.0.1.0 Production on Sat Apr 8 03:26:37 2017
 
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
 
SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     20
Current log sequence           22

這裡可以看出來在非歸檔模式下使用DISABLE_ARCHIVE_LOGGING匯入時間為10s,日誌量也沒有明顯增加。

在force logging在非歸檔情況下使用不DISABLE_ARCHIVE_LOGGING引數

[oracle@localhost ~]$ sqlplus xff/oracle@localhost/pdb
 
SQL*Plus: Release 12.2.0.1.0 Production on Sat Apr 8 10:07:07 2017
 
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
 
Last Successful login time: Sat Apr 08 2017 03:29:36 -04:00
 
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
 
SQL> drop table t_xifenfei purge;
 
Table dropped.
SQL> alter system switch logfile;
 
System altered.
 
SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     22
Current log sequence           24
SQL> select force_logging from v$database;
 
FORCE_LOGGING
---------------------------------------
YES
 
[oracle@localhost ~]$ impdp xff/oracle@localhost/pdb dumpfile=t_xifenfei.dmp TRANSFORM=DISABLE_ARCHIVE_LOGGING:y
 
Import: Release 12.2.0.1.0 - Production on Sat Apr 8 10:10:39 2017
 
Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
 
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "XFF"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "XFF"."SYS_IMPORT_FULL_01":  xff/********@localhost/pdb dumpfile=t_xifenfei.dmp TRANSFORM=DISABLE_ARCHIVE_LOGGING:y
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "XFF"."T_XIFENFEI"                          460.6 MB 3485408 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "XFF"."SYS_IMPORT_FULL_01" successfully completed at Sat Apr 8 10:11:02 2017 elapsed 0 00:00:21
 
[oracle@localhost ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 12.2.0.1.0 Production on Sat Apr 8 10:11:17 2017
 
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
 
SQL> archive log lsit;
SP2-0718: illegal ARCHIVE LOG option
SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     22
Current log sequence           24

這可以可以看出來在非歸檔情況下force logging無明顯增加日誌量和匯入時間

在force logging在歸檔情況下使用DISABLE_ARCHIVE_LOGGING引數

SQL> drop table xff.t_xifenfei purge;
 
Table dropped.
 
SQL> alter system switch logfile;
 
System altered.
 
SQL> select force_logging from v$database;
 
FORCE_LOGGING
---------------------------------------
YES
 
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     23
Next log sequence to archive   25
Current log sequence           25
 
[oracle@localhost ~]$ impdp xff/oracle@localhost/pdb dumpfile=t_xifenfei.dmp TRANSFORM=DISABLE_ARCHIVE_LOGGING:y
 
Import: Release 12.2.0.1.0 - Production on Sat Apr 8 10:33:28 2017
 
Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
 
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "XFF"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "XFF"."SYS_IMPORT_FULL_01":  xff/********@localhost/pdb dumpfile=t_xifenfei.dmp TRANSFORM=DISABLE_ARCHIVE_LOGGING:y
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "XFF"."T_XIFENFEI"                          460.6 MB 3485408 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "XFF"."SYS_IMPORT_FULL_01" successfully completed at Sat Apr 8 10:34:50 2017 elapsed 0 00:01:15
[oracle@localhost ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 12.2.0.1.0 Production on Sat Apr 8 10:35:09 2017
 
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
 
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     26
Next log sequence to archive   28
Current log sequence           28

這裡可以看出來在force logging情況下，設定DISABLE_ARCHIVE_LOGGING引數不生效
從上述測試在不管是非歸檔還是歸檔情況下使用DISABLE_ARCHIVE_LOGGING都會減小匯入時間，減少歸檔量，但是需要注意如果資料庫是force logging情況下，DISABLE_ARCHIVE_LOGGING引數會無效。





12c 資料泵匯入時禁止生成日誌的引數disable_archive_logging


早就希望資料泵匯入時能夠有nologging的功能，省去很多歸檔日誌的消耗。從Oracle Database 12c開始終於有了該功能，就是使用引數DISABLE_ARCHIVE_LOGGING。從字面上就能看出它的用處吧，禁用歸檔日誌！

在執行匯入操作時，物件的記錄日誌屬性會被設定為NO，匯入操作結束後日志屬性還原。

1，採用壓縮方式匯出hr使用者資料
[oracle@snow ~]$ expdp dp/dp directory=dp_dir dumpfile=hrc.dmp logfile=hrc.log schemas=hr compression=all

對比一下壓縮前後的差距，不到一半的空間。
[oracle@snow ~]$ ll
total 64024
-rw-r--r--. 1 oracle oinstall 945 Feb 8 18:33 12cpfile.ora
-rw-r-----. 1 oracle oinstall 176128 Feb 9 16:09 20150226_1.dmp
-rw-r-----. 1 oracle oinstall 1549 Feb 9 16:09 20150226_1.log
-rw-r-----. 1 oracle oinstall 237568 Feb 9 16:00 20150226.dmp
-rw-r--r--. 1 oracle oinstall 1613 Feb 8 20:00 createdb.sql
drwxr-xr-x. 7 oracle oinstall 4096 Jun 10 2013 database
-rw-r-----. 1 oracle oinstall 1618 Feb 9 16:00 export.log
-rw-r--r--. 1 oracle oinstall 154 Feb 9 16:09 flashback.par
-rw-r-----. 1 oracle oinstall 64253952 Feb 9 09:56 full.dmp
-rw-r-----. 1 oracle oinstall 29419 Feb 9 10:04 full.log
-rw-r-----. 1 oracle oinstall 241664 Feb 9 16:43 hrc.dmp  <壓縮資料
-rw-r-----. 1 oracle oinstall 2568 Feb 9 16:43 hrc.log
-rw-r-----. 1 oracle oinstall 577536 Feb 9 15:22 hr.dmp   <非壓縮資料
-rw-r-----. 1 oracle oinstall 2549 Feb 9 15:22 hr.log
-rw-r-----. 1 oracle oinstall 1721 Feb 9 15:32 import.log
-rw-r--r--. 1 oracle oinstall 88 Feb 9 14:43 pro.par
[oracle@snow ~]$
[oracle@snow ~]$
[oracle@snow ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Mon Feb 9 16:47:39 2015

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SYS@ora12c >drop user hr cascade;

User dropped.

SYS@ora12c >exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@snow ~]$

匯入資料時開啟transform=disable_archive_logging:Y 避免生成歸檔日誌
[oracle@snow ~]$ impdp dp/dp directory=dp_dir dumpfile=hrc.dmp \
> transform=disable_archive_logging:Y

Import: Release 12.1.0.1.0 - Production on Mon Feb 9 16:49:16 2015

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "DP"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "DP"."SYS_IMPORT_FULL_01": dp/******** directory=dp_dir dumpfile=hrc.dmp transform=disable_archive_logging:Y
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "HR"."COUNTRIES" 5.218 KB 25 rows
. . imported "HR"."DEPARTMENTS" 5.437 KB 27 rows
. . imported "HR"."EMPLOYEES" 8.773 KB 107 rows
. . imported "HR"."JOBS" 5.445 KB 19 rows
. . imported "HR"."JOB_HISTORY" 5.304 KB 10 rows
. . imported "HR"."LOCATIONS" 6.046 KB 23 rows
. . imported "HR"."REGIONS" 4.851 KB 4 rows
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
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/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "DP"."SYS_IMPORT_FULL_01" successfully completed at Mon Feb 9 16:49:29 2015 elapsed 0 00:00:11

盼望已久的功能終於來了，盼著哪天有12c資料庫的匯入工作真正嘗試一次。

 

Oracle12c中資料泵新特性之功能增強(expdp, impdp)



Oracle的資料泵功能在10g中被引進。本文對資料泵在12c中的增強做一個概覽。

1.   禁用日誌選項(DISABLE_ARCHIVE_LOGGING)

Impdp的TRANSFORM引數已經擴充套件為包括DISABLE_ARCHIVE_LOGGING選項。該選項的預設值為 "N"，不會影響日誌行為。將該選項設定為"Y"，這將會使表和索引在匯入前將日指屬性設定為NOLOGGING，從而匯入期間減少相關日誌的產生，匯入後再將日誌屬性重置為LOGGING。

TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y

透過附上物件型別，可以把影響限定為特定物件型別(TABLE or INDEX)。

TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y:TABLE

 

TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y:INDEX

如下例所示。

$ impdp system/Password1@pdb1directory=test_dir dumpfile=emp.dmp logfile=impdp_emp.log \

    remap_schema=scott:test transform=disable_archive_logging:y

如果資料庫執行在FORCE LOGGING模式下，DISABLE_ARCHIVE_LOGGING選項將沒什麼影響。

2.   LOGTIME引數

LOGTIME引數決定時間戳是否將包括在expdp和impdp功能的輸出資訊中。 

LOGTIME=[NONE | STATUS |LOGFILE | ALL]

可用值如下。

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

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

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

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

如下例所示。

$ expdp scott/tiger@pdb1 tables=empdirectory=test_dir dumpfile=emp.dmp logfile=expdp_emp.loglogtime=all

 

Export: Release 12.1.0.1.0 - Production onWed Nov 20 22:11:57 2013

 

Copyright (c) 1982, 2013, Oracle and/or itsaffiliates.  All rights reserved.

 

Connected to: Oracle Database 12c EnterpriseEdition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, Oracle Label Security,OLAP, Advanced Analytics

and Real Application Testing options

20-NOV-13 22:12:09.312: Starting"SCOTT"."SYS_EXPORT_TABLE_01":  scott/********@pdb1 tables=empdirectory=test_dir dumpfile=emp.dmp logfile=expdp_emp.log logtime=all

20-NOV-13 22:12:13.602: Estimate in progressusing BLOCKS method...

20-NOV-13 22:12:17.797: Processing objecttype TABLE_EXPORT/TABLE/TABLE_DATA

20-NOV-13 22:12:18.145: Total estimationusing BLOCKS method: 64 KB

20-NOV-13 22:12:30.583: Processing objecttype TABLE_EXPORT/TABLE/TABLE

20-NOV-13 22:12:33.649: Processing objecttype TABLE_EXPORT/TABLE/INDEX/INDEX

20-NOV-13 22:12:37.744: Processing objecttype TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

20-NOV-13 22:12:38.065: Processing objecttype TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

20-NOV-13 22:12:38.723: Processing objecttype TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

20-NOV-13 22:12:41.052: Processing objecttype TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

20-NOV-13 22:12:41.337: Processing objecttype TABLE_EXPORT/TABLE/STATISTICS/MARKER

20-NOV-13 22:13:38.255: . . exported"SCOTT"."EMP"                                8.75 KB      14 rows

20-NOV-13 22:13:40.483: Master table"SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

20-NOV-13 22:13:40.507:******************************************************************************

20-NOV-13 22:13:40.518: Dump file set forSCOTT.SYS_EXPORT_TABLE_01 is:

20-NOV-13 22:13:40.545:   /home/oracle/emp.dmp

20-NOV-13 22:13:40.677: Job"SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at WedNov 20 22:13:40 2013 elapsed 0 00:01:36

 

$

3.   像表一樣匯出檢視

VIEWS_AS_TABLES引數允許把檢視當成表匯出。表結構匹配檢視列，檢視查詢結果對應表資料。

VIEWS_AS_TABLES=[schema_name.]view_name[:table_name],...

 

如下例所示。

CONN scott/tiger@pdb1

CREATE VIEW emp_v AS

 SELECT * FROM emp;

用VIEWS_AS_TABLES引數匯出檢視。

$ expdp scott/tiger views_as_tables=scott.emp_vdirectory=test_dir dumpfile=emp_v.dmp logfile=expdp_emp_v.log

Expdp預設會建立一個臨時表來作為檢視的複製，但不包含資料，只是為匯出提供一個後設資料源。此外，也可以確定一個結構合適的表來代替。但這也許只有在只讀庫上才有意義。

4.   匯入時改變表壓縮

TRANSFORM引數的TABLE_COMPRESSION_CLAUSE子句允許表匯入過程中動態改變表的壓縮特性。

TRANSFORM=TABLE_COMPRESSION_CLAUSE:[NONE |compression_clause]

TABLE_COMPRESSION_CLAUSE引數的可選值如下。

1)     NONE ：漏掉表壓縮子句，表繼承表空間的壓縮屬性。

2)     NOCOMPRESS：禁用表壓縮。

3)     COMPRESS：開啟基本表壓縮。

4)     ROW STORE COMPRESSBASIC ：和COMPRESS一樣。

5)     ROW STORE COMPRESSADVANCED ：開啟高階壓縮，也被稱為OLTP壓縮。

6)     COLUMN STORECOMPRESS FOR QUERY ：Exadata和ZFS儲存配置中可用的混合列壓縮(HCC)。

7)     COLUMN STORECOMPRESS FOR ARCHIVE ：Exadata和ZFS儲存配置中可用的混合列壓縮。

包括空格的壓縮子句需要用單引號或雙引號括起來。

如下例所示。

$ impdp system/Password1@pdb1directory=test_dir dumpfile=emp.dmp logfile=impdp_emp.log \

    remap_schema=scott:test transform=table_compression_clause:compress

5.   匯入時改變表的LOB儲存

TRANFORM引數的LOB_STORAGE子句使得在進行非可傳輸匯入操作時改變表的壓縮特性。

TRANSFORM=LOB_STORAGE:[SECUREFILE | BASICFILE| DEFAULT | NO_CHANGE]

LOB_STORAGE子句可以為如下值。

1)     SECUREFILE ：LOB資料儲存為SecureFiles。

2)     BASICFILE ：LOB資料儲存為BasicFiles。

3)     DEFAULT ：LOB資料的儲存由資料庫自行決定。

4)     NO_CHANGE ：LOB資料的儲存和原來的物件一樣。

舉例如下。

$ impdp system/Password1@pdb1directory=test_dir dumpfile=lob_table.dmp logfile=impdp_lob_table.log \

     transform=lob_storage:securefile

6.   匯出檔案壓縮選項

作為高階壓縮選項的一部分，你可以確定COMPRESSION_ALGORITHM引數以確定匯出檔案的壓縮級別。這和先前討論的表壓縮沒關係。

COMPRESSION_ALGORITHM=[BASIC | LOW | MEDIUM |HIGH]

可用值如下。

1)     BASIC ：和先前版本的壓縮演算法一樣。提供很好的壓縮，但對效能影響不大。

2)     LOW ：用於降低CPU消耗比壓縮率更重要的場景。

3)     MEDIUM ：推薦使用的選項。和BASIC特性類似，但用了不同的演算法。

4)     HIGH：最大限度壓縮，但會消耗更多CPU。

舉例如下。

$ expdp scott/tiger tables=empdirectory=test_dir dumpfile=emp.dmp logfile=expdp_emp.log \

       compression=all compression_algorithm=medium

7.   多宿主選項支援(CDB和PDB)

Oracle12c引進了多宿主選項，允許多個可插拔資料庫(PDBs)存在於一個容器資料庫(CDB)。將資料泵用於PDB和用於非CDB資料庫沒太大差別。

從11.2.0.2庫以FULL選項匯出的資料可以匯入一個新PDB中，這和之前的完全匯入一樣，但會有些限制。

8.   審計命令

透過建立審計策略，可以對Oracle 12c的資料泵作業進行審計。

CREATE AUDIT POLICY policy_name

 ACTIONS COMPONENT=DATAPUMP [EXPORT | IMPORT | ALL];

當該策略用於使用者時，使用者的資料泵作業資訊將被審計。如下策略會審計所有的資料泵操作。該策略被用於使用者SCOTT。

CONN / AS SYSDBA

CREATE AUDIT POLICY aud_dp_plcy ACTIONSCOMPONENT=DATAPUMP ALL;

AUDIT POLICY aud_dp_plcy BY scott;

執行如下資料泵命令。

$ expdp scott/tiger tables=empdirectory=test_dir dumpfile=emp.dmp logfile=expdp_emp.log

檢查審計資訊時，會發現該資料泵作業已被審計。

-- Flush audit information to disk.

EXECDBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;

 

SET LINESIZE 200

COLUMN event_timestamp FORMAT A30

COLUMN dp_text_parameters1 FORMAT A30

COLUMN dp_boolean_parameters1 FORMAT A30

 

SELECT event_timestamp,

      dp_text_parameters1,

      dp_boolean_parameters1

FROM  unified_audit_trail

WHERE audit_type = 'Datapump';

 

EVENT_TIMESTAMP               DP_TEXT_PARAMETERS1          DP_BOOLEAN_PARAMETERS1

------------------------------------------------------------ ------------------------------

14-DEC-13 09.47.40.098637 PM   MASTER TABLE:  "SCOTT"."SYS_EX MASTER_ONLY:FALSE, DATA_ONLY:

                              PORT_TABLE_01" , JOB_TYPE: EXP  FALSE, METADATA_ONLY: FALSE,

                              ORT, METADATA_JOB_MODE: TABLE_DUMPFILE_PRESENT: TRUE, JOB_RE

                              EXPORT, JOB VERSION: 12.1.0.0. STARTED:FALSE

                              0, ACCESS METHOD: AUTOMATIC, D

                              ATA OPTIONS: 0, DUMPER DIRECTO

                              RY: NULL REMOTE LINK: NULL, T

                              ABLE EXISTS: NULL, PARTITION O

                              PTIONS: NONE

 

SQL>

9.   加密口令增強

先前的版本中，資料泵加密口令需要在命令列輸入ENCRYPTION_PASSWORD引數，這樣很容易偷窺口令。

Oracle 12c中，ENCRYPTION_PWD_PROMPT引數可以加密但不要求在命令列輸入口令，而是執行時提示使用者輸入口令，且命令不回顯。

ENCRYPTION_PWD_PROMPT=[YES | NO]

舉例如下。

$ expdp scott/tiger tables=empdirectory=test_dir dumpfile=emp.dmp logfile=expdp_emp.log \

       encryption_pwd_prompt=yes

 

Export: Release 12.1.0.1.0 - Production onSat Dec 14 21:09:11 2013

 

Copyright (c) 1982, 2013, Oracle and/or itsaffiliates.  All rights reserved.

 

Connected to: Oracle Database 12c EnterpriseEdition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, OLAP, AdvancedAnalytics and Real Application Testing options

 

Encryption Password:

Starting"SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** tables=emp directory=test_dir

dumpfile=emp.dmp logfile=expdp_emp.logencryption_pwd_prompt=yes

Estimate in progress using BLOCKS method...

Processing object typeTABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 KB

Processing object typeTABLE_EXPORT/TABLE/TABLE

Processing object typeTABLE_EXPORT/TABLE/INDEX/INDEX

Processing object typeTABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object typeTABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object typeTABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object typeTABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object typeTABLE_EXPORT/TABLE/STATISTICS/MARKER

Processing object typeTABLE_EXPORT/TABLE/POST_TABLE_ACTION

. . exported"SCOTT"."EMP"                               8.765 KB      14 rows

Master table"SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SCOTT.SYS_EXPORT_TABLE_01is:

 /tmp/emp.dmp

Job"SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at SatDec 14 21:09:55 2013 elapsed 0 00:00:41

 

$

10.        可傳輸資料庫

現在，TRANSPORTABLE選項可以和 FULL選項一起使用來傳輸整個資料庫。

$ expdp system/Password1 full=Ytransportable=always version=12 directory=TEMP_DIR \

  dumpfile=orcl.dmp logfile=expdporcl.log

該方法也可以被用來升級資料庫。

11.        其他增強

1)     資料泵支援擴充套件資料型別，但VERSION引數需要設定為12.1之後的版本。

2)     有域索引的LOB列現在可以充分裡用直接路徑載入的優勢。

在Oracle Database 12c中加入了一些DataPump Expdp/Impdp的新特性，當然包括對CDB的支援，此外還有部分特性。

 

例如DISABLE_ARCHIVE_LOGGING/RECOVERY_LOGGING 減少impdp匯入時 TABLE/INDEX產生的redo，注意這僅僅是減少不是禁絕。

 

基本語法如下：

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

 

  • 注意 即便你用DISABLE_ARCHIVE_LOGGING:Y 也不代表能完全不產生redo
  • 對於 FORCE LOGGING的資料庫 DISABLE_ARCHIVE_LOGGING:Y無效

 

具體使用：

Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 – 64bit Beta
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 17
Current log sequence 19

 
oracle@localhost:~$ expdp system/oracle dumpfile=temp:ogg_maclean.dmp schemas=ogg_maclean

Export: Release 12.1.0.0.2 – Beta on Sun Apr 28 05:14:00 2013

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 – 64bit Beta
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting “SYSTEM”.”SYS_EXPORT_SCHEMA_02″: system/******** dumpfile=temp:ogg_maclean.dmp schemas=ogg_maclean
Estimate in progress using BLOCKS method…
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 30 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
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/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
. . exported “OGG_MACLEAN”.”MACLEAN_PRESS1″ 2.298 MB 84000 rows
. . exported “OGG_MACLEAN”.”MACLEAN_PRESS10″ 2.298 MB 84000 rows
. . exported “OGG_MACLEAN”.”MACLEAN_PRESS2″ 2.298 MB 84000 rows
. . exported “OGG_MACLEAN”.”MACLEAN_PRESS3″ 2.298 MB 84000 rows
. . exported “OGG_MACLEAN”.”MACLEAN_PRESS4″ 2.298 MB 84000 rows
. . exported “OGG_MACLEAN”.”MACLEAN_PRESS5″ 2.298 MB 84000 rows
. . exported “OGG_MACLEAN”.”MACLEAN_PRESS6″ 2.298 MB 84000 rows
. . exported “OGG_MACLEAN”.”MACLEAN_PRESS7″ 2.298 MB 84000 rows
. . exported “OGG_MACLEAN”.”MACLEAN_PRESS8″ 2.298 MB 84000 rows
. . exported “OGG_MACLEAN”.”MACLEAN_PRESS9″ 2.298 MB 84000 rows
Master table “SYSTEM”.”SYS_EXPORT_SCHEMA_02″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_02 is:
/tmp/ogg_maclean.dmp
Job “SYSTEM”.”SYS_EXPORT_SCHEMA_02″ successfully completed at Sun Apr 28 05:15:01 2013 elapsed 0 00:00:57

oracle@localhost:~$ ls -lh /tmp/ogg_maclean.dmp
-rw-r—– 1 oracle oinstall 24M Apr 28 05:15 /tmp/ogg_maclean.dmp

 

 
oracle@localhost:~$ impdp system/oracle dumpfile=temp:ogg_maclean.dmp remap_schema=ogg_maclean:ogg_maclean1

Import: Release 12.1.0.0.2 – Beta on Sun Apr 28 05:18:18 2013

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 – 64bit Beta
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table “SYSTEM”.”SYS_IMPORT_FULL_01″ successfully loaded/unloaded
Starting “SYSTEM”.”SYS_IMPORT_FULL_01″: system/******** dumpfile=temp:ogg_maclean.dmp remap_schema=ogg_maclean:ogg_maclean1
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported “OGG_MACLEAN1”.”MACLEAN_PRESS1″ 2.298 MB 84000 rows
. . imported “OGG_MACLEAN1”.”MACLEAN_PRESS10″ 2.298 MB 84000 rows
. . imported “OGG_MACLEAN1”.”MACLEAN_PRESS2″ 2.298 MB 84000 rows
. . imported “OGG_MACLEAN1”.”MACLEAN_PRESS3″ 2.298 MB 84000 rows
. . imported “OGG_MACLEAN1”.”MACLEAN_PRESS4″ 2.298 MB 84000 rows
. . imported “OGG_MACLEAN1”.”MACLEAN_PRESS5″ 2.298 MB 84000 rows
. . imported “OGG_MACLEAN1”.”MACLEAN_PRESS6″ 2.298 MB 84000 rows
. . imported “OGG_MACLEAN1”.”MACLEAN_PRESS7″ 2.298 MB 84000 rows
. . imported “OGG_MACLEAN1”.”MACLEAN_PRESS8″ 2.298 MB 84000 rows
. . imported “OGG_MACLEAN1”.”MACLEAN_PRESS9″ 2.298 MB 84000 rows
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
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/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job “SYSTEM”.”SYS_IMPORT_FULL_01″ successfully completed at Sun Apr 28 05:18:31 2013 elapsed 0 00:00:10

 
DISABLE_ARCHIVE_LOGGING
oracle@localhost:~$ impdp system/oracle dumpfile=temp:ogg_maclean.dmp remap_schema=ogg_maclean:ogg_maclean2 TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y

Import: Release 12.1.0.0.2 – Beta on Sun Apr 28 05:21:45 2013

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 – 64bit Beta
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table “SYSTEM”.”SYS_IMPORT_FULL_01″ successfully loaded/unloaded
Starting “SYSTEM”.”SYS_IMPORT_FULL_01″: system/******** dumpfile=temp:ogg_maclean.dmp remap_schema=ogg_maclean:ogg_maclean2 TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported “OGG_MACLEAN2”.”MACLEAN_PRESS1″ 2.298 MB 84000 rows
. . imported “OGG_MACLEAN2”.”MACLEAN_PRESS10″ 2.298 MB 84000 rows
. . imported “OGG_MACLEAN2”.”MACLEAN_PRESS2″ 2.298 MB 84000 rows
. . imported “OGG_MACLEAN2”.”MACLEAN_PRESS3″ 2.298 MB 84000 rows
. . imported “OGG_MACLEAN2”.”MACLEAN_PRESS4″ 2.298 MB 84000 rows
. . imported “OGG_MACLEAN2”.”MACLEAN_PRESS5″ 2.298 MB 84000 rows
. . imported “OGG_MACLEAN2”.”MACLEAN_PRESS6″ 2.298 MB 84000 rows
. . imported “OGG_MACLEAN2”.”MACLEAN_PRESS7″ 2.298 MB 84000 rows
. . imported “OGG_MACLEAN2”.”MACLEAN_PRESS8″ 2.298 MB 84000 rows
. . imported “OGG_MACLEAN2”.”MACLEAN_PRESS9″ 2.298 MB 84000 rows
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
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/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job “SYSTEM”.”SYS_IMPORT_FULL_01″ successfully completed at Sun Apr 28 05:21:56 2013 elapsed 0 00:00:09

 

如同匯出表那樣匯出檢視資料

 Exporting Views as Tables會匯出 表的定義和檢視資料，而不僅僅是檢視定義。以及其依賴的物件，例如約束和授權

 

SQL> create view cnt as select count(*) c1 from MACLEAN_PRESS1;

View created.

oracle@localhost:~$ expdp system/oracle dumpfile=temp:view.dmp views_as_tables=ogg_maclean.cnt

Export: Release 12.1.0.0.2 – Beta on Sun Apr 28 05:52:49 2013

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 – 64bit Beta
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting “SYSTEM”.”SYS_EXPORT_TABLE_01″: system/******** dumpfile=temp:view.dmp views_as_tables=ogg_maclean.cnt 
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 “OGG_MACLEAN”.”CNT” 5.046 KB 1 rows
Master table “SYSTEM”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/tmp/view.dmp
Job “SYSTEM”.”SYS_EXPORT_TABLE_01″ successfully completed at Sun Apr 28 05:53:01 2013 elapsed 0 00:00:10















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筆試面寶典》讀者群小麥苗的微店

   小麥苗的微信公眾號      小麥苗的DBA寶典QQ群2     《DBA筆試面試寶典》讀者群       小麥苗的微店

.............................................................................................................................................

【12C】資料泵新特性（DISABLE_ARCHIVE_LOGGING+VIEWS_AS_TABLES匯出檢視+LOGTIME）
 DBA筆試面試講解群
 《DBA寶典》讀者群 歡迎與我聯絡



來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2130830/，如需轉載，請註明出處，否則將追究法律責任。

相關文章