Managing Tablespaces : Locally Managed Tablespaces

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

1)Fast, concurrent space operation.

2)Space allocations and deallocations modify locally managed resources (bitmaps stored in header files).
3)Enhanced performance Readable standby databases are allowed, because locally managed temporary tablespaces do not generate any undo or redo.
4)Space allocation is simplified, because when the AUTOALLOCATE clause is specified, the database automatically selects the appropriate extent size.
5)User reliance on the data dictionary is reduced, because the necessary information is stored in file headers and bitmap blocks.
6)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. This is the default for new permanent tablespaces, but you must specify the EXTENT MANAGEMENT LOCAL clause if you want to specify either the AUTOALLOCATE clause or the UNIFORM clause. 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).

If you expect the tablespace to contain objects of varying sizes requiring many extents with different extent sizes, then AUTOALLOCATE is the best choice.
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.

If you want exact control over unused space, and you can predict exactly the space to be allocated for an object or objects and the number and size of extents, then UNIFORM is a good choice. This setting ensures that you will never have unusable space in your tablespace.  

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.

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

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.
The following example creates a tablespace with uniform 128K extents. (In a database with 2K blocks, each extent would be equivalent to 64 database blocks). Each 128K extent is represented by a bit in the extent bitmap for this file.

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.

Note:
When you allocate a datafile for a locally managed tablespace, you should allow space
for metadata used for space management (the extent bitmap or space header segment) which are part of user space. For example, if specify the UNIFORM clause in the extent management clause but you omit the SIZE parameter, then the default extent size is 1MB. In that case, the size specified for the datafile must be larger (at least one block plus space for the bitmap) than 1MB.

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 an Oracle Real Application Clusters environment, automatic segment space management allows for a dynamic affinity of space to 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. 
 

Although automatic segment space management is the default for all new permanent, locally managed tablespaces, you can explicitly enable it with the 

SEGMENT SPACE MANAGEMENT AUTO clause.

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

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.

Notes:

· 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 AUTOALLOCATE, 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.

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:
1) Adding a datafile. For example:
ALTER TABLESPACE lmtbsb
ADD DATAFILE '/u02/oracle/data/lmtbsb02.dbf' SIZE 1M;
2)Altering tablespace availability (ONLINE/OFFLINE).
3) Making a tablespace read-only or read/write.
4) Renaming a datafile, or enabling or disabling the autoextension of the size of a datafile in the tablespace.