oracle tablespace

[HPADM]

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

[Ian Lochray]

select EXTENT_MANAGEMENT from dba_tablespaces where tablespace_name = 'your_tablepsace_name';

Oracle recommend using locally managed tablespaces at 9i.

[HPADM]

Thank you very much Ian.

[Peter van Rijn_1]

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

[T G Manikandan]

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]

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