Locally managed tablespaces

Locally managed tablespaces have some part of the tablespace set aside for a bitmap.
* AUTOALLOCATE specifies that the tablespace is system managed. Users cannot specify an extent size.
* UNIFORM specifies that the tablespace is managed with uniform extents of SIZE bytes. The default SIZE is 1 megabyte. NOTE: If you do not specify either AUTOALLOCATE or UNIFORM with the LOCAL parameter, then AUTOALLOCATE is the default. Locally Managed Tablespaces---------------------------
A tablespace that manages its own extents maintains a bitmap in each datafile to keep track of the free or used status of blocks in that datafile. Each bit in the bitmap corresponds to a group of blocks. When an extent is allocated or freed for reuse, Oracle changes the bitmap values to show the newstatus of the blocks. These changes do not generate rollback information because they do not update tables (like sys.uet$, sys.fet$) in the data dictionary (except for special cases such as tablespace quota information).
Local management of extents automatically tracks adjacent free space, eliminating the need to coalesce free extents. The sizes of extents that are managed locally can be determined automatically by the system. Alternatively,all extents can have the same size in a locally managed tablespace. A tablespace that manages its extents locally can have either uniform extent sizes or variable extent sizes that are determined automatically by the system.When you create the tablespace, the UNIFORM or AUTOALLOCATE (system managed) option specifies the type of allocation.
For system managed extents, Oracle determines the optimal size of extents, witha minimum extent size of 64K. This is the default for permanent tablespaces.
For uniform extents, you can specify an extent size or use the default size,which is 1 MB. Temporary tablespaces that manage their extents locally can onlyuse this type of allocation.
In locally managed tablespaces, Oracle uses object's storage parameters INITIAL,NEXT, PCTINCREASE, and MINEXTENTS in conjunction with the size of extents specified for the tablespace to determine the object's first extent. For example, in a uniform locally managed tablespace with 2M extents, if you specify (in CREATE TABLE, etc. )INITIAL value of 1M, NEXT 2M, and MINEXTENTS 4, then Oracle initially creates four 2M extents - to provide for 1M+2M+2M+2M=7M of initial size of the object.
One cannot create a locally managed SYSTEM tablespace in 8i.This is possible with in 9.2.0.X, where SYSTEM tablespace is created by DBCA as locally managed by default.With a locally managed SYSTEM tablespace, the rest of the tablespaces in such database have to be locally managed as well.
Locally managed temporary tablespaces can not be of type "permanent".
Examples (1)------------
REM AUTOALLOCATE specifies that the tablespace is system managed. REM Users cannot specify an extent size.
SQL> CREATE TABLESPACE local_uniform DATAFILE 2 'local_u.dbf' SIZE 1M reuse 3 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K; Tablespace created.
SQL> CREATE TABLESPACE local_auto DATAFILE 2 'local_auto.dbf' SIZE 1M reuse 3 EXTENT MANAGEMENT LOCAL AUTOALLOCATE; Tablespace created.
SQL> select TABLESPACE_NAME, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS, 2 MIN_EXTLEN, EXTENT_MANAGEMENT, ALLOCATION_TYPE, PLUGGED_IN 3 from dba_tablespaces 4 where tablespace_name like 'LOCAL%';
TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS MIN_EXTLEN EXTENT_MAN ALLOCATION PLUGG --------------- -------------- ------------ ----------- ---------------- ----- ---------- ---------- ----- LOCAL_AUTO 65,536 1 2,147,483,645 65,536 LOCAL SYSTEM NO LOCAL_UNIFORM 131,072 131,072 1 2,147,483,645 131,072 LOCAL UNIFORM NO
Now let us create 2 tables in the local tablespaces:
SQL> CREATE TABLE local_table1 ( 2 DEPTNO NUMBER(3) NOT NULL, 3 DNAME VARCHAR2(14), 4 LOC VARCHAR2(13)) 5 storage (initial 10k next 10k) 6 tablespace local_uniform; Table created.
SQL> CREATE TABLE local_table2 ( 2 DEPTNO NUMBER(3) NOT NULL, 3 DNAME VARCHAR2(14), 4 LOC VARCHAR2(13)) 5 storage (initial 10k next 10k) 6 tablespace local_auto; Table created.
Look at the INITIAL and NEXT extents in the USER_TABLES:
SQL> select table_name, tablespace_name, INITIAL_EXTENT, NEXT_EXTENT 2 from user_tables where table_name like 'LOCAL%';
TABLE_NAME TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT ------------------------------ --------------- -------------- ------------ LOCAL_TABLE1 LOCAL_UNIFORM 10,240 131,072 LOCAL_TABLE2 LOCAL_AUTO 10,240
Check the actual size of each allocated extent:
SQL> select SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, EXTENT_ID, BYTES, BLOCKS 2 from user_extents where segment_name like 'LOCAL%';
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID BYTES BLOCKS --------------- --------------- --------------- --------- ------------ ------ LOCAL_TABLE1 TABLE LOCAL_UNIFORM 0 131,072 64 LOCAL_TABLE2 TABLE LOCAL_AUTO 0 65,536 32
Let us allocate another extent for each table:
SQL> alter table local_table1 allocate extent; Table altered.
SQL> alter table local_table2 allocate extent; Table altered.
SQL> select SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, EXTENT_ID, BYTES, BLOCKS 2 from user_extents where segment_name like 'LOCAL%';
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID BYTES BLOCKS --------------- --------------- --------------- --------- ------------ ------ LOCAL_TABLE1 TABLE LOCAL_UNIFORM 0 131,072 64 LOCAL_TABLE1 TABLE LOCAL_UNIFORM 1 131,072 64 LOCAL_TABLE2 TABLE LOCAL_AUTO 0 65,536 32 LOCAL_TABLE2 TABLE LOCAL_AUTO 1 65,536 32
The db_block_size in this example is 2048 (2K).
Thus, the extent sizes for tables created in tablespaces LOCAL_UNIFORM andLOCAL_AUTO are 128K (131072 bytes) and 64K (65536 bytes), correspondingly.
In the next Example, note the effect of object's INITIAL, NEXT, PCTINCREASE, and MINEXTENTS storage parameters when creating tables.
Example (2)-----------
CREATE TABLESPACE lmt_1 DATAFILE 'lmt_1.dbf'size 20m EXTENT MANAGEMENT LOCAL uniform size 2m;
Tablespace created.
create table lmt_test1 (a number)STORAGE (initial 1m next 2m minextents 4) tablespace lmt_1;
Table created.
select EXTENT_ID, BYTES from dba_extents where SEGMENT_NAME='LMT_TEST1';
EXTENT_ID BYTES---------- ---------- 0 2097152 1 2097152 2 2097152 3 2097152
create table lmt_test2 (a number)STORAGE (INITIAL 1m next 2m minextents 4 pctincrease 50) tablespace lmt_1;
Table created.
select EXTENT_ID, BYTES from dba_extents where SEGMENT_NAME='LMT_TEST2';
EXTENT_ID BYTES---------- ---------- 0 2097152 1 2097152 2 2097152 3 2097152 4 2097152 5 2097152
create table lmt_test3 (a number)STORAGE (INITIAL 1m next 2m minextents 4 pctincrease 90) tablespace lmt_1;
Table created.
select EXTENT_ID, BYTES from dba_extents where SEGMENT_NAME='LMT_TEST3';
EXTENT_ID BYTES---------- ---------- 0 2097152 1 2097152 2 2097152 3 2097152 4 2097152 5 2097152 6 2097152 7 2097152
As we could see, tables' storage parameters are taken in consideration wheninitially calculating the size of each table (in terms of number of allocated ts extents). After that, each allocated extent will be the tablespace's extent size, or multiple extents - if table extent is allocated via"alter table allocate extent (size XXX)".
Advantages of Locally Managed Tablespaces:------------------------------------------
1. Better space management due to: - uniform extent sizes; - reduced data dictionary access.
2. Reduced tablespace fragmentation.
3. Better management of temporary space.