Locally managed tablespaces - autoallocate vs. uniform

  • From: "Schauss, R. Peter (IT Solutions)" <peter.schauss@xxxxxxx>
  • To: "Oracle L" <oracle-l@xxxxxxxxxxxxx>
  • Date: Mon, 27 Jul 2009 15:46:16 -0500

I may have an "opportunity" to reload a 140 gb database from an export
as a result of a downgrade from Enterprise Edition to Standard Edition
which my management is contemplating.  My intention, when I reload is to
change from dictionary managed to locally managed tablespaces.  As a
result of decisions that my predecessors or the consultants who advised
them made, the database currently uses dictionary managed tablespaces in
which each tablespace contains a variety of extent sizes.  The result is
that I do not have a clear pattern to follow if I specify uniform extent
sizes when I recreate the tablespaces.

If I could use the autoallocate option, it would simplify my task
considerably.  Is there any disadvantage to doing so?

To add more confusion, the way that the application was built resulted
in a significant number of tables which are not being used and contain
no rows.  Since these tables were not placed in separate tablespaces, if
I specify a large extent sizes, the empty tables could result in a
considerable amount of wasted space.

Will the autoallocate option minimize the wasted space from the empty

Peter Schauss

Other related posts: