Articles Comments

Oracle DBA & All IT » New Feature, ORA-Error » 11gR2 export file จะไม่เอา table ที่ไม่มี data มาด้วย (deferred segment creation)

11gR2 export file จะไม่เอา table ที่ไม่มี data มาด้วย (deferred segment creation)

วันก่อนไปเจอคำถามนี้ใน narisa มาค่ะ เห็นว่าเคยเจอมาเหมือนกัน หาแทบตาย -_-'
แล้วเห็นมีคนเจอเหมือนกันก็เลยเอามารวบรวมไว้ให้ค่ะ ใครสงสัยเรื่องไหนสามารถฝากไว้ได้ที่ forum ค่ะ แล้วเด๋วจะทะยอยหาคำตอบมาให้ค่ะ หวังว่าจะเป็นประโยชน์กับเพื่อนๆนะคะ

 

มันเป็น new freature บน 11gR2 ค่ะ
parameter ชื่อ DEFERRED_SEGMENT_CREATION default จะเป็น TRUE
มันจะเป็นการ save space กรณีที่มีการ create table แต่ยังไม่มี ข้อมูลมันจะยังไม่มีการสร้าง segment ขึ้นค่ะ

ถ้าต้องการให้ export ได้ปกติเหมือน 10g ก้ต้องไป alter parameter ดังนี้ค่ะ

Solution:
alter system set deferred_segment_creation=false;

แล้ว table ที่สร้างหลังจากที่เรา alter แม้จะมี 0 rows ก้จะออกมาด้วยค่ะ

สำหรับ table ที่ถูกสร้างไปก่อนแล้วก้ให้ทำการ gen script ออกมาแล้วรันแล้วลอง export อีกครั้งค่ะ น่าจะได้ออกมาค่ะ

<generate script for change segment_created and try to export again>

select 'alter table '||owner||'.'||table_name||' move tablespace &1 ;'
from dba_tables where SEGMENT_CREATED='NO'
and owner ='&2';

 

Example Solution For This case:

If you still use the good old export utility, you probably have noticed that empty tables are not exported by the exp utility.

You can test it by simply creating dummy table (don’t insert any data into it) and then try to export the table.
>sqlplus user/pass

SQL*Plus: Release 11.2.0.1.0 Production on Fri Apr 9 15:48:54 2010

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options

SQL> create table foo(id number);

Table created.

SQL>exit

>exp file=foo.dmp log=goo.dmp tables=foo userid=user/pass

Export: Release 11.2.0.1.0 – Production on Fri Apr 9 15:48:00 2010

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
Export done in WE8MSWIN1252 character set and UTF8 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path …
EXP-00011: USER.FOO does not exist
Export terminated successfully with warnings.

Well, I am pretty sure that the table exists – even without any data in it.

The reason for this behavior is 11.2 new feature ‘Deferred Segment Creation‘ – the creation of a table sent is deferred until the first row is inserted.

As a result, empty tables are not listed in dba_segments and are not exported by exp utility.

This feature is controlled by the database parameter deferred_segment_creation. It has default value of TRUE. If you set it by FALSE, any newly created table AFTER the change will be exported – nevertheless if it is empty or not.

>sqlplus system/pass

SQL*Plus: Release 11.1.0.7.0 – Production on Fri Apr 9 15:59:02 2010

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options

SQL> alter system set deferred_segment_creation=false;

System altered.

SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options

>sqlplus user/pass

SQL*Plus: Release 11.1.0.7.0 – Production on Fri Apr 9 15:59:20 2010

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options

SQL> create table foo2(id number);

Table created.

SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options

>exp file=foo2.dmp log=foo2.log tables=foo2 userid=user/pass

Export: Release 11.1.0.7.0 – Production on Fri Apr 9 15:59:54 2010

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
Export done in WE8MSWIN1252 character set and UTF8 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path …
. . exporting table                           FOO2          0 rows exported
Export terminated successfully without warnings.

—————————————————————————————————————–

Note: Deferred Segment Creation option is not supported in 11gR2 Standard Edition

.. Preview : 14164

Written by

บอกเล่าสิ่งที่พบเจอมาในการทำงาน ประสบการณ์การทำงานด้าน DBA ถ่ายทอดกันด้วยภาษาง่ายๆ บ้านๆ " ทุกอย่างไม่อยาก แต่... แค่ตั้งใจไม่พอ ต้องลงมือทำ และทำ GoodLuck " Fanpage: www.facebook.com/DBAor .. "Oracle Database Consultant " ..

Filed under: New Feature, ORA-Error · Tags: , , ,

  • At last, smoenoe comes up with the “right” answer!

  • Hi there, nice blog – just wanted to acknowledge that with a comment

  • อั้น

    แจ๋วเลยครับ ผมติดเรื่องนี้พอดีเลย
     
    ขอบคุณมาก ๆ ๆ ๆ ๆ ๆ