topic Re: oracle tablespace in Operating System - HP-UX

oracle tablespace

HPADM — Mon, 09 Dec 2002 15:07:18 GMT

Hi,
I would like to see if a tablespace is data dictionary managed or locally managed. How can I do? Is better to create the data dictionary or locally managed one?
Thank you

Re: oracle tablespace

Ian Lochray — Mon, 09 Dec 2002 16:11:56 GMT

select EXTENT_MANAGEMENT from dba_tablespaces where tablespace_name = 'your_tablepsace_name';

Oracle recommend using locally managed tablespaces at 9i.

Re: oracle tablespace

HPADM — Mon, 09 Dec 2002 16:22:24 GMT

Thank you very much Ian.

Re: oracle tablespace

Peter van Rijn_1 — Tue, 10 Dec 2002 09:07:14 GMT

In 9i "locally managed" is even the default!
Peter

Re: oracle tablespace

T G Manikandan — Sat, 14 Dec 2002 06:10:55 GMT

Locally managed tablespaces are preferred.

There are a lot of advantages in using locally managed tablespaces in the concept of space management.

Like tablespace fragmentation,cohelesing or extent management are seen fine in the locally maanaged tablespace.

//*

Improved concurrency and speed of space operations, as space allocations and deallocations predominantly modify locally managed resources (bitmaps stored in header files) rather than requiring centrally managed resources such as enqueues.

Improved performance, because recursive operations that are sometimes required during dictionary-managed space allocation are eliminated.

Readable standby databases are allowed, as locally managed temporary tablespaces (used for sorts, etc.) are locally managed and thus do not generate any rollback or redo.

Simplified space allocation--when the AUTOALLOCATE clause is specified, appropriate extent size is automatically selected.

Reduced user reliance on the data dictionary because necessary information is stored in file headers and bitmap blocks.
*//

I have attached a document which lists out some advantages.

Thanks

Re: oracle tablespace

T G Manikandan — Sat, 14 Dec 2002 06:11:31 GMT