DEFERRED_SEGMENT_CREATION and ORA-04031

11g new feature:

segment will be created when first insert statment is issued for table. if DEFERRED_SEGMENT_CREATION is true.

ISSUE:
When partition table is used, it might cause ORA-04031 error.  The PRTMV part of shared pool will take most of shared pool memory.

Solution:
set DEFERRED_SEGMENT_CREATION  to false.


reference:
11.2 Database New Feature Deferred Segment Creation [Video] [ID 887962.1]
http://www.dba-oracle.com/t_shared_pool_scripts.htm
http://blog.tanelpoder.com/2009/06/04/ora-04031-errors-and-monitoring-shared-pool-subpool-memory-utilization-with-sgastatxsql/