New Features / Documentos / Soporte Técnico / Servicios / Tilsor
 
 

Locally-Managed Tablespaces

Creation Date: 03-JAN-2000
Last Revision Date: 03-APR-2002


PURPOSE
To describe new options introduced in Oracle8i and Orale9i for the CREATE
TABLESPACE command and provide some examples of locally managed tablespaces.

RELATED DOCUMENTS
Oracle8i Server Concepts
Oracle8i Server SQL Reference
Oracle9i Database Concepts
Oracle9i SQL Reference


The CREATE TABLESPACE command has a new clause introduced in Oracle8i, the
"extent_management_clause", that specifies how the extents of the tablespace
are managed. This clause uses one of the following parameters:

- DICTIONARY:
Specifies that the tablespace is managed using dictionary tables.
This is the default in Oracle8i.

- LOCAL:
Specifies that tablespace is locally managed.
This is the default in Oracle9i.
If COMPATIBLE is set to 9.0.0, then the default extent management for any
new tablespace is locally managed in Oracle9i.
If COMPATIBLE is less than 9.0.0, then the default extent management for
any new tablespace is dictionary managed in Oracle9i.

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 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).

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, with
a 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 only
use this type of allocation.

The storage parameters NEXT, PCTINCREASE, MINEXTENTS, MAXEXTENTS, and DEFAULT
STORAGE are not valid for extents that are managed locally.

Currently you cannot create a locally managed SYSTEM tablespace in 8.1.5.
Refer to [BUG:809225] for more information. This is fixed in 8.1.6 and higher.

Locally managed temporary tablespaces must be of type "temporary" (not
"permanent").

Example:
--------

REM AUTOALLOCATE specifies that the tablespace is system managed.
REM Users cannot specify an extent size.

SQL> CREATE TABLESPACE local_uniform DATAFILE
2 '/u02/app/oracle/product/8.1.5/oradata/V8151/local_u.dbf' SIZE 1M reuse
3 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;
Tablespace created.

SQL> CREATE TABLESPACE local_auto DATAFILE
2 '/u02/app/oracle/product/8.1.5/oradata/V8151/local_auto.dbf' SIZE 1M reuse
3 EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
Tablespace created.

SQL> select TABLESPACE_NAME,
2 INITIAL_EXTENT,
3 NEXT_EXTENT,
4 MIN_EXTENTS,
5 MAX_EXTENTS,
6 MIN_EXTLEN,
7 EXTENT_MANAGEMENT,
8 ALLOCATION_TYPE,
9 PLUGGED_IN
10 from dba_tablespaces
11 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,
2 tablespace_name,
3 INITIAL_EXTENT,
4 NEXT_EXTENT
5 from user_tables
6 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


Now look at the actual size of each allocated extent:


SQL> select SEGMENT_NAME,
2 SEGMENT_TYPE,
3 TABLESPACE_NAME,
4 EXTENT_ID,
5 BYTES,
6 BLOCKS
7 from user_extents
8 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,
2 SEGMENT_TYPE,
3 TABLESPACE_NAME,
4 EXTENT_ID,
5 BYTES,
6 BLOCKS
7 from user_extents
8 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 and
LOCAL_AUTO are 128K (131072 bytes) and 64K (65536 bytes), correspondingly.


Advantages of Locally-Managed Tablespaces:
------------------------------------------

1. Better space management due to:
- uniformed extent sizes;
- reduced data dictionary access.

2. Reduced fragmentation.

3. Better management of temporary space.