>> 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