cancel
Showing results for 
Search instead for 
Did you mean: 

oracle tablespace

HPADM
Advisor

oracle tablespace

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
5 REPLIES
Ian Lochray
Respected Contributor

Re: oracle tablespace

select EXTENT_MANAGEMENT from dba_tablespaces where tablespace_name = 'your_tablepsace_name';

Oracle recommend using locally managed tablespaces at 9i.
HPADM
Advisor

Re: oracle tablespace

Thank you very much Ian.
Peter van Rijn_1
Occasional Visitor

Re: oracle tablespace

In 9i "locally managed" is even the default!
Peter
T G Manikandan
Honored Contributor

Re: oracle tablespace

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
T G Manikandan
Honored Contributor

Re: oracle tablespace

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