Today, wheat seedlings share with you the new feature test of 12c data pump (turn off the log DISABLE_ARCHIVE_LOGGING+VIEWS_AS_TABLES option to export the view).

12c new characteristic test of data pump (DISABLE_ARCHIVE_LOGGING+VIEWS_AS_TABLES option to export the view)

The Data Pump version has many useful improvements, such as converting views to tables during export and turning off logging during import.

1.1 turn off the generation of redo logs (TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y)

A new TRANSFORM option is introduced in the Data Pump, which provides the flexibility to turn off redo generation for objects during import. When disable is specified for the TRANSFORM option_ ARCHIVE_ If the value is logging, redo generation will be turned off during the whole import period. This function eases the pressure when importing large tables and reduces excessive redo generation, thus speeding up the import. This attribute can also be applied to tables and indexes.

The following example demonstrates this function:

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

1.2 convert views to tables and export them (VIEWS_AS_TABLES option)

This is another improvement in Data Pump. With views_ AS_ With the tables option, you can load the view data into the table.

The following example demonstrates how to load view data into a table during export:

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

1.2.1 self experiment

expdp VIEWS_ AS_ The tables option can treat the view as a table and export its data.

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

Table data preparation:

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;

Start export:

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 Friday, December 16:31:49 2016
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
connection 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
 start-up "SYSTEM"."SYS_EXPORT_TABLE_01":  system/******** DIRECTORY=data_pump_dir DUMPFILE=expdp_vw.dmp LOGFILE=expdp_vw.log VIEWS_AS_TABLES=lhr.my_view
 In use BLOCKS Method for estimation...
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
 use BLOCKS Total estimation of method: 16 KB
 Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
. . Exported "LHR"."MY_VIEW"                             5.929 KB       2 that 's ok
 Successfully loaded/Main table unloaded "SYSTEM"."SYS_EXPORT_TABLE_01"
******************************************************************************
SYSTEM.SYS_EXPORT_TABLE_01 The dump file set for is:
  E:\APP\ORACLE\ADMIN\LHRDB12C\DPDUMP\EXPDP_VW.DMP
 task "SYSTEM"."SYS_EXPORT_TABLE_01" On Friday, December 16:32:36 2016 elapsed 0 00:00:31 Successfully completed
 View its DDL sentence:
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 Friday, December 16:35:14 2016
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
connection 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
 Successfully loaded/Main table unloaded "SYSTEM"."SYS_SQL_FILE_FULL_01"
start-up "SYSTEM"."SYS_SQL_FILE_FULL_01":  system/******** DIRECTORY=data_pump_dir DUMPFILE=expdp_vw.dmp LOGFILE=impdp_vw.log sqlfile=a.txt
 Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
 task "SYSTEM"."SYS_SQL_FILE_FULL_01" On Friday, December 16:35:26 2016 elapsed 0 00:00:10 Successfully completed
DDL Statement content:
-- 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" ;
Import:
C:\Users\xiaomaimiao>sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Friday, December 16:37:03 2016
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
connection 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
SQL> create user lhr01 identified by lhr;
User created.
SQL> grant dba to lhr01;
Authorization succeeded.
SQL> exit
 from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options to break off
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 Friday, December 16:39:49 2016
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
connection 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
 Successfully loaded/Main table unloaded "SYSTEM"."SYS_IMPORT_FULL_02"
start-up "SYSTEM"."SYS_IMPORT_FULL_02":  system/******** DIRECTORY=data_pump_dir DUMPFILE=expdp_vw.dmp LOGFILE=impdp_vw.log remap_schema=lhr:lhr01
 Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
 Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
. . Imported "LHR01"."MY_VIEW"                           5.929 KB       2 that 's ok
 task "SYSTEM"."SYS_IMPORT_FULL_02" On Friday, 16 December:39:57 2016 elapsed 0 00:00:06 Successfully completed

Note: there are too many articles in this article, and the official account has 2W word limit. http://blog.itpub.net/26736162/viewspace-2136339/ Or go to the cloud disk of wheat seedlings to download the pdf version. Thank you for your understanding.

DB written interview history connection

About Me: Wheat Seedling

● the author of this paper: wheat seedling, only focusing on the technology of database, paying more attention to the application of technology

● author blog address: http://blog.itpub.net/26736162/abstract/1/

● all rights reserved. Welcome to share this article. Please keep the source for reprint