We are using oracle 10g. with our code, Currently Oracle partitions are size the same way, each partition is using 10MB for data and 12MB for indexes (with the 6 default indexes); even of very few records are written in the partition.We create partitions in advance as a part of nightly job with 10 minutes duration.Can some intelligence can be added where based on statistics we can decide the size of partition dynamically? Lot of space is getting wasted because of this reason.

Hi Sri,

>> Can some intelligence can be added where based on statistics we can decide the size of partition dynamically?

Sure, if you interrogate the data and know the size in advance of the nightly job, you can create the partition using a PL/SQL program, adjusting the partition size according to the data size . . . .

Later, when you upgrade, I think that deferred segment creation works of partitions in 11.2.02, whereby a partition is only created when the first row is added:

http://www.dba-oracle.com/t_oracle_deferre...nt_creation.htm