Saturday, September 17, 2011

Table not present in datapump export 11G R2 - Deferred Segment Creation

After upgrading database to 11G R2 developers keep complaining that some tables doesn’t come in the export file.They noticed it when they imported this and found that some objects were not in a compiled state.When they tried to compile, they came to know that tables was not imported. There is no error in the export / import log, but some tables are not exported.
BTW, they used imp /exp utilities.
The tables which were not exported were empty- which means they had zero rows in it.

Understand Deferred Segment Creation

Beginning with Oracle Database 11g Release 2, when you create heap-organized tables in a locally managed tablespace, the database defers table segment creation until the first row is inserted. In addition, segment creation is deferred for any LOB columns of the table, any indexes created implicitly as part of table creation, and any indexes subsequently explicitly created on the table.This means that,no segments are allocated until a row is inserted to these table.You can verify this by querying user_tables

SQL> SELECT table_name, segment_created FROM user_tables where rownum < 4;
The advantages of this space allocation method are the following:
  1. It saves a significant amount of disk space in applications that create hundreds or thousands of tables upon installation, many of which might never be populated
  2. It reduces application installation time. There is a small performance penalty when the first row is inserted, because the new segment must be created at that time. 
Here is how you can solve this problem
  1. Set the parameter DEFERRED_SEGMENT_CREATION initialization parameter to FALSE, which is TRUE by default
  2. ALTER table MOVE for the tables which are returned from the above query
  3. INSERT a record to these tables and then rollback


Post a Comment


ORA-BLOG. Copyright 2008 All Rights Reserved Revolution Two Church theme by Brian Gardner Converted into Blogger Template by Bloganol dot com