Oracle 导入具有deffered段属性的元数据

嗨，汤姆，

在使用impdp对架构进行元数据导入时，它占用了太多空间来创建空分区。初始化参数deferred_segment_creation设置为true，分区仍然占用空间。有什么方法可以导入仅包含架构元数据而不使用太多空间的转储文件，或者有什么方法可以在impdp操作时将段的默认空间分配更改为64K，而不是8M。来源和分离都是12.1.0.2

谢谢，
阿米亚。

看起来像是12.1的问题。这是一个缩小的例子

SQL> create table part_tab
  2  ( x char(100),
  3    y int ) segment creation deferred
  4   ;

Table created.

SQL> create or replace directory temp as 'c:\temp';

Directory created.

C:\bin>expdp tables=part_tab directory=temp dumpfile=part.dmp

Export: Release 12.1.0.2.0 - Production on Wed Apr 19 16:58:00 2017

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

Username: mcdonac/*******

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 "MCDONAC"."SYS_EXPORT_TABLE_01":  mcdonac/******** tables=part_tab directory=temp dumpfile=part.dmp
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported "MCDONAC"."PART_TAB"                            0 KB       0 rows
Master table "MCDONAC"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for MCDONAC.SYS_EXPORT_TABLE_01 is:
  C:\TEMP\PART.DMP
Job "MCDONAC"."SYS_EXPORT_TABLE_01" successfully completed at Wed Apr 19 16:58:08 2017 elapsed 0 00:00:02


C:\bin>impdp tables=part_tab directory=temp dumpfile=part.dmp content=metadata_only  sqlfile=part.sql

Import: Release 12.1.0.2.0 - Production on Wed Apr 19 16:58:14 2017

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

Username: mcdonac/********

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 "MCDONAC"."SYS_SQL_FILE_TABLE_01" successfully loaded/unloaded
Starting "MCDONAC"."SYS_SQL_FILE_TABLE_01":  mcdonac/******** tables=part_tab directory=temp dumpfile=part.dmp content=metadata_only sqlfile=part.sql
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "MCDONAC"."SYS_SQL_FILE_TABLE_01" successfully completed at Wed Apr 19 16:58:19 2017 elapsed 0 00:00:01


C:\bin>type c:\temp\part.sql

-- CONNECT MCDONAC
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/TABLE/TABLE
CREATE TABLE "MCDONAC"."PART_TAB"
   (    "X" CHAR(100 BYTE),
        "Y" NUMBER(*,0)
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  TABLESPACE "USERS" ;
-- new object type path: TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
-- new object type path: TABLE_EXPORT/TABLE/STATISTICS/MARKER
复制

到目前为止还不错...带有seg延迟的空表，也创建了带有seg延迟的sql文件。现在让我们重复相同的练习，但是这次我将首先将一些数据加载到表中

SQL> create table part_tab
  2  ( x char(100),
  3    y int ) segment creation deferred
  4   ;

Table created.

SQL> insert /*+ APPEND */ into part_tab
  2  select rownum, rownum
  3  from dual connect by level < 100000;

99999 rows created.

SQL> create or replace directory temp as 'c:\temp';

Directory created.

SQL> host del c:\temp\part.dmp

SQL> host
Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\bin>expdp tables=part_tab directory=temp dumpfile=part.dmp

Export: Release 12.1.0.2.0 - Production on Wed Apr 19 17:00:38 2017

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

Username: mcdonac/******

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 "MCDONAC"."SYS_EXPORT_TABLE_01":  mcdonac/******** tables=part_tab directory=temp dumpfile=part.dmp
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 13 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported "MCDONAC"."PART_TAB"                        10.48 MB   99999 rows
Master table "MCDONAC"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for MCDONAC.SYS_EXPORT_TABLE_01 is:
  C:\TEMP\PART.DMP
Job "MCDONAC"."SYS_EXPORT_TABLE_01" successfully completed at Wed Apr 19 17:00:45 2017 elapsed 0 00:00:02


C:\bin>exit

SQL> host
Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\bin>impdp tables=part_tab directory=temp dumpfile=part.dmp content=metadata_only  sqlfile=part.sql

Import: Release 12.1.0.2.0 - Production on Wed Apr 19 17:00:56 2017

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

Username: mcdonac/******

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 "MCDONAC"."SYS_SQL_FILE_TABLE_01" successfully loaded/unloaded
Starting "MCDONAC"."SYS_SQL_FILE_TABLE_01":  mcdonac/******** tables=part_tab directory=temp dumpfile=part.dmp content=metadata_only sqlfile=part.sql
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "MCDONAC"."SYS_SQL_FILE_TABLE_01" successfully completed at Wed Apr 19 17:01:01 2017 elapsed 0 00:00:01


C:\bin>type c:\temp\part.sql
-- CONNECT MCDONAC
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/TABLE/TABLE
CREATE TABLE "MCDONAC"."PART_TAB"
   (    "X" CHAR(100 BYTE),
        "Y" NUMBER(*,0)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;
-- new object type path: TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
-- new object type path: TABLE_EXPORT/TABLE/STATISTICS/MARKER

复制

请注意，段的创建已经变得立即 (所以无论你在会话级别有什么参数，等等都不会有太大的用处)。

但幸运的是，我们可以通过transform参数来控制它，所以这里是你可以解决它的方法:

SQL> create table part_tab
  2  ( x char(100),
  3    y int ) segment creation deferred
  4  partition by range ( y )
  5  (
  6    partition p0 values less than ( 10000 ),
  7    partition p1 values less than ( 20000 ),
  8    partition p2 values less than ( 30000 ),
  9    partition p3 values less than ( 40000 ),
 10    partition p4 values less than ( 50000 ),
 11    partition p5 values less than ( 60000 ),
 12    partition p6 values less than ( 70000 ),
 13    partition p7 values less than ( 80000 ),
 14    partition p8 values less than ( 90000 ),
 15    partition p9 values less than (100000 )
 16  ) ;

Table created.

SQL>
SQL> insert /*+ APPEND */ into part_tab
  2  select rownum, rownum
  3  from dual connect by level < 100000;

99999 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> create or replace directory temp as 'c:\temp';

Directory created.

SQL>
SQL> host del c:\temp\part.dmp

SQL>
SQL> host
Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\bin>expdp tables=part_tab directory=temp dumpfile=part.dmp

Export: Release 12.1.0.2.0 - Production on Wed Apr 19 17:10:03 2017

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

Username: mcdonac/******

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 "MCDONAC"."SYS_EXPORT_TABLE_01":  mcdonac/******** tables=part_tab directory=temp dumpfile=part.dmp
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 80 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported "MCDONAC"."PART_TAB":"P0"                   1.044 MB    9999 rows
. . exported "MCDONAC"."PART_TAB":"P1"                   1.054 MB   10000 rows
. . exported "MCDONAC"."PART_TAB":"P2"                   1.054 MB   10000 rows
. . exported "MCDONAC"."PART_TAB":"P3"                   1.054 MB   10000 rows
. . exported "MCDONAC"."PART_TAB":"P4"                   1.054 MB   10000 rows
. . exported "MCDONAC"."PART_TAB":"P5"                   1.054 MB   10000 rows
. . exported "MCDONAC"."PART_TAB":"P6"                   1.054 MB   10000 rows
. . exported "MCDONAC"."PART_TAB":"P7"                   1.054 MB   10000 rows
. . exported "MCDONAC"."PART_TAB":"P8"                   1.054 MB   10000 rows
. . exported "MCDONAC"."PART_TAB":"P9"                   1.054 MB   10000 rows
Master table "MCDONAC"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for MCDONAC.SYS_EXPORT_TABLE_01 is:
  C:\TEMP\PART.DMP
Job "MCDONAC"."SYS_EXPORT_TABLE_01" successfully completed at Wed Apr 19 17:10:16 2017 elapsed 0 00:00:05


C:\bin>exit

SQL>
SQL> @drop part_tab

Y1                      Y2
----------------------- -------------------------
TABLE                   cascade constraints purge


Table dropped.

SQL>
SQL> host
Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\bin>impdp tables=part_tab directory=temp dumpfile=part.dmp content=metadata_only transform=SEGMENT_CREATION:N

Import: Release 12.1.0.2.0 - Production on Wed Apr 19 17:10:22 2017

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

Username: mcdonac/******

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 "MCDONAC"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "MCDONAC"."SYS_IMPORT_TABLE_01":  mcdonac/******** tables=part_tab directory=temp dumpfile=part.dmp content=metadata_only transform=SEGMENT_CREATION:N
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "MCDONAC"."SYS_IMPORT_TABLE_01" successfully completed at Wed Apr 19 17:10:29 2017 elapsed 0 00:00:02


C:\bin>exit

SQL>
SQL> col partition_name format a30
SQL> select partition_name, bytes from user_extents;

no rows selected

SQL>
复制