Oracle Locally Managed Tablespace

Locally managed tablespace track all extent information in the tablespace itself by using bitmaps, resulting in the following benefits:

Want To Get DBA Training From Experts? Enroll Now For Free Demo On Oracle DBA Training.

• Fast, concurrent space operations. Space allocations and deallocations modify locally managed resources (bitmaps stored in header files).
• Enhanced performance
• Readable standby databases are allowed, because locally managed temporary tablespaces do not generate any undo or redo.
• Space allocation is simplified, because when the autoallocate clause is specified, the database automatically selects the appropriate extent size.
• User reliance on the data dictionary is reduced, because the necessary information is stored in file headers and bitmap blocks.
• Coalescing free extents is unnecessary for locally managed tablespaces.

All tablespaces, including the system tablespace, can be locally managed. The dbms_space_admin package provides maintenance procedures for locally managed tablespaces.

Creating a Locally Managed Tablespace:

Create a locally managed tablespace by specifying local in the extent management clause of the create tablespace statement. You can have the database manage extents for you automatically with the autoallocate clause (the default), or you can specify that the tablespace is managed with uniform extents of a specific size (uniform).

Autoallocate is also a good choice if it is not important for you to have a lot of control over space allocation and deallocation, because it simplifies tablespace management. Some space may be wasted with this setting, but the benefit of having oracle database manage your space most likely outweighs this drawback.

When you do not explicitly specify the type of extent management, oracle database determines extent management as follows:

• If the create tablespace statement omits the default storage clause, then the database creates a locally managed autoallocated tablespace.
• If the create tablespace statement includes a default storage clause, then the database considers the following:
• If you specified the minimum extent clause, the database evaluates whether the values of minimum extent, initial, and next are equal and the value of pctincrease is 0. If so, the database creates a locally managed uniform tablespace with extent size = initial. If the minimum extent, initial, and next parameters are not equal, or if pctincrease is not 0, the database ignores any extent storage parameters you may specify and creates a locally managed, autoallocated tablespace.
• If you did not specify minimum extent clause, the database evaluates only whether the storage values of initial and next are equal and pctincrease is 0. If so, the tablespace is locally managed and uniform. Otherwise, the tablespace is locally managed and autoallocated.

[Related Article: Control File in Oracle]

The following statement creates a locally managed tablespace named lmtbsb and specifies autoallocate:

Sql> create tablespace lmtbsb datafile ‘/u02/oracle/data/lmtbsb01.dbf’ size 50m

Extent management local autoallocate;

Autoallocate causes the tablespace to be system managed with a minimum extent size of 64k.

The alternative to autoallocate is uniform. Which specifies that the tablespace is managed with extents of uniform size. You can specify that size in the size clause of uniform. If you omit size, then the default size is 1m.

Sql> create tablespace lmtbsb datafile ‘/u02/oracle/data/lmtbsb01.dbf’ size 50m

Extent management local uniform size 128k;

You cannot specify the default storage clause, minimum extent, or temporary when you explicitly specify extent management local. If you want to create a temporary locally managed tablespace, use the create temporary tablespace statement.

[Related Article: Oracle DBA Interview Questions]

Specifying segment space management in locally managed tablespaces:

In a locally managed tablespace, there are two methods that oracle database can use to manage segment space: automatic and manual. Manual segment space management uses linked lists called “freelists” to manage free space in the segment, while automatic segment space management uses bitmaps. Automatic segment space management is the more efficient method, and is the default for all new permanent, locally managed tablespaces.

Automatic segment space management delivers better space utilization than manual segment space management. It is also self-tuning, in that it scales with increasing number of users or instances. In addition, for many standard workloads, application performance with automatic segment space management is better than the performance of a well-tuned application using manual segment space management.

The following statement creates tablespace lmtbsb with automatic segment space management:

[Related Article: Oracle Rename Tablespace]

Sql> create tablespace lmtbsb datafile ‘/u02/oracle/data/lmtbsb01.dbf’ size 50m
Extent management local
Segment space management auto;

The segment space management manual clause disables automatic segment space management.
The segment space management that you specify at tablespace creation time applies to all segments subsequently created in the tablespace. You cannot change the segment space management mode of a tablespace.

• If you set extent management to local uniform, then you must ensure that each extent contains at least 5 database blocks.
• If you set extent management to local auto allocate, and if the database block size is 16k or greater, then oracle manages segment space by creating extents with a minimum size of 5 blocks rounded up to 64k.

Explore Oracle DBA Sample Resumes! Download & Edit, Get Noticed by Top Employers! Download Now!

Altering a locally managed tablespace:

You cannot alter a locally managed tablespace to a locally managed temporary tablespace, nor can you change its method of segment space management. Coalescing free extents is unnecessary for locally managed tablespaces. However, you can use the alter tablespace statement on locally managed tablespaces for some operations, including the following:

Related Articles:

Bigfile Tablespace in Oracle

Oracle DBA Tutorial