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
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; |
- 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
- 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.
- Set the parameter DEFERRED_SEGMENT_CREATION initialization parameter to FALSE, which is TRUE by default
- ALTER table MOVE for the tables which are returned from the above query
- INSERT a record to these tables and then rollback
0 comments:
Post a Comment