Locally Managed Tablespaces
A tablespace that can manage extent allocation by itself is called locally managed tablespace. These tablespaces maintain a bitmap in each datafile to keep track of the freed or used status of blocks in that datafile. Each bit in the bitmap corresponds to a block or a group of blocks. When an extent is allocated or freed for reuse, Oracle changes the bitmap values to show the new status of the blocks. These changes do not generate rollback information because they do not update tables in the data dictionary (except for special cases such as tablespace quota information).
NEXT,PCTINCREASE,MINEXTENTS,MAXEXTENTS, andDEFAULT STORAGEare not valid for extents that are managed locally !
Extent Allocation
Locally managed tablespaces can have uniform extent sizes or variable extent sizes that are determined by the system. Any of the options, UNIFORM or AUTOALLOCATE can be mentioned while creating the tablespace. For UNIFORM extents you can specify an extent size. The default size is 1MB. For AUTOALLOCATE extents you can specify the size of the initial extent and Oracle determines the optimal size of the additional extents, with a minimum extent size of 64KB. That is why these are called system-managed extents.
How Extents are Allocated
Oracle looks for free space to allocate to a new extent by first determining a candidate datafile in the tablespace and then searching the datafile's bitmap for the required number of adjacent free blocks. If that datafile does not have enough adjacent free space, Oracle looks in another datafile. When extents are deallocated, Oracle modifies the bitmap in the datafile.
Create A Locally Managed Tablespace
CREATE TABLESPACE users
DATAFILE 'users.dbf'
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 256K;
Advantages
- Local management of extents avoids recursive space management operations, which can occur in dictionary managed tablespaces.
- Local management of extents automatically tracks adjacent free space, eliminating the need to coalesce free extents.
- Reliance on data dictionary is reduced.
Notes
- Temporary tablespaces that manage their extents locally can only use UNIFORM extent allocation.
- For permanent tablespaces the default extent size for system managed extents is 64KB.
- The storage parameters NEXT, PCTINCREASE, MINEXTENTS, MAXEXTENTS, and DEFAULT STORAGE are not valid for locally managed tablespaces.
Change normal tablespaces to locally managed
Check extent management of the tablespaces with following command:
SQL> SELECT tablespace_name,extent_management
FROM dba_tablespaces;TABLESPACE_NAME EXTENT_MAN ------------------------------ ---------- SYSTEM DICTIONARY TEMP LOCAL USERS DICTIONARY TAB DICTIONARY IDX DICTIONARY SYSAUX LOCAL UNDO LOCAL
First change first all dictionary managed tablespaces except tablespace SYSTEM to locally managed with the following procedure:
SQL> execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL ('USERS'); PL/SQL procedure successfully completed.
The tablespace must be kept online and read/write during migration. Note that temporary tablespaces cannot be migrated.
Change SYSTEM tablespaces to locally managed
Before the SYSTEM tablespace can be migrated to locally managed format, you should ensure the following:
Steps to change SYSTEM tablespaces to locally
managed
SQL> shutdown immediate SQL> startup restrictSQL> execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL ('SYSTEM');ERROR at line 1:
ORA-10644: SYSTEM tablespace cannot be default temporary tablespace
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 227
ORA-06512: at line 1SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;SQL> execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL ('SYSTEM');ERROR at line 1:
ORA-10647: Tablespace other than SYSTEM, UNDO, TEMP not
found in read only mode
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 227
ORA-06512: at line 1SQL> select tablespace_name from dba_tablespaces;TABLESPACE_NAME
------------------------------
SYSTEM
TEMP
USERS
TAB
IDX
SYSAUX
UNDOSQL> alter tablespace USERS read only;
SQL> alter tablespace TAB read only;
SQL> alter tablespace IDX read only;SQL> execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL ('SYSTEM');ERROR at line 1:
ORA-10648: Tablespace SYSAUX is not offline
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 227
ORA-06512: at line 1SQL> alter tablespace SYSAUX offline;SQL> execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL ('SYSTEM');
PL/SQL procedure successfully completed.
