Oracle 11g R2 那 exp imp 陷阱問題(Oracle 11g export using EXP utility missing some tables)

新的工作有機會接觸資料庫的建置,最常用的動作之一就是將標準版的資料庫(Oracle 9i),
Import到新導入的公司去(DB版本不一).
使用的方法是將我們標準版的資料庫Export出來成DMP檔
先Create User後
imp sys/password@instance fromuser=from_schema touser=to_schema file=XX.DMP

但是我第一個接觸的客戶就發生缺一大堆Table(自然物件也會Compile失敗)的問題,本來想說可能是我們標準版的DB問題,後來發現也不見得如此,經查是空的table導不出來,原因是因為空的table沒有分配到segment

Oracle 11g有一個新的機制是為了節省空間的運用,Export時不會Export沒有分配到segment的Table
用以下語法可以調整此設定,不過要在Create Instance之後就下指令解決,不然後面Create的Table都必須透過搬移Table Space才可以解決
SQL> alter system set deferred_segment_creation=false scope=both;
不過最好的方法還是改掉舊方法
使用11g提供的新Export Import工具
Use the new Oracle Data Pump utilities for the export and import:
    expdp user/pass@sid DUMPFILE=test.dmp SCHEMAS=TEST
    impdp user/pass@sid DUMPFILE=test.dmp

原文處理方法如下,很詳細

Oracle 11g export using EXP utility missing some tables

I am much surprised that Oracle mandated their new features hidden without notification to legacy users. One such example would be the new Deferred Segment Creation feature and Oracle Data Pump (EXPDP/IMPDP) utilities of Oracle 11g.The original EXP/IMP utilities are deprecated and no longer supported in 11g, but its still been shipped with 11g for backward compatibility.Happy news is that the EXPDP is twice faster as compared to EXP, and IMPDP is relatively 15 to 45 times faster as compared to IMP.

Problem:

I have recently exported a 11g schema using 11g EXP utility and tried to import into another 11g Instance using 11g IMP utility. But to my surprise, not all the tables got transferred to the destination instance. Upon troubleshooting the export log, found out that only empty tables i.e. tables with NO ROWS (0 rows) did not exported at all. I concluded this to be a bug at first, but later found out to be a side-effect of a new 'Deferred Segment Creation' feature and not a bug.

Cause:

Deferred Segment Creation - 11g New Feature:

Oracle 11g introduced the new default feature "Deferred Segment Creation". In a nutshell, when you create a new table - no segment is created, no initial extent is allocated, no storage is reserved. And this made the export (EXP) in Oracle 11gR2 to skip the empty tables (with no rows).

Solution:

Easier Way:

Use the new Oracle Data Pump utilities for the export and import:

    expdp user/pass@sid DUMPFILE=test.dmp SCHEMAS=TEST

    impdp user/pass@sid DUMPFILE=test.dmp

Harder Way:

Alternatively, you can force the allocation of extents on each empty table using the following command in the Source database to export and re-run the export EXP command, which would export the empty tables as well.

ALTER TABLE <table_name> ALLOCATE EXTENT;

NOTE: For New Objects yet to create, We turn off the functionality before creating any objects using the following command.

ALTER SYSTEM SET DEFERRED_SEGMENT_CREATION=FALSE;