Ask Maclean

【Oracle Database 12c新特性】 12c DataPump Expdp/Impdp新特性

在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

 

Posted

in

by

mac

Tags:

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *