Locally managed tablespaces - autoallocate vs. uniform

From: Schauss, R. Peter (IT Solutions) <"Schauss,>
Date: Mon, 27 Jul 2009 15:46:16 -0500
Message-ID: <CD9150D80CFCFB42BC73C40791C1E01904F00705_at_XMBIL112.northgrum.com>

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

Thanks,
Peter Schauss

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Jul 27 2009 - 15:46:16 CDT