RE: Dicitonary managed tablespaces

From: Nguyen, Long <Long.Nguyen_at_its.csiro.au>
Date: Wed, 3 May 2000 09:56:23 +1000
Message-Id: <10485.104751@fatcity.com>

Hi Mark,

Note 93771.1 is include below for you and others who do not have Metalink access.

  DocID: 	    Note 93771.1 
  Subject:      Locally-Managed Tablespaces in Oracle8i
  Type:         BULLETIN
  Status:       PUBLISHED

                                                     Content Type: TEXT/PLAIN
                                                     Creation Date: 03-JAN-2000
                                                     Last Revision Date: 28-MAR-2000
                                                     Language: USAENG

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

  RELATED DOCUMENTS
    Oracle8i Server Concepts
    Oracle8i Server 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
will be managed. This clause uses one of the following parameters:

DICTIONARY: Specifies that the tablespace is managed using dictionary tables. This is the default.
LOCAL: Specifies that tablespace is locally managed. 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.

For optimal use, the UNIFORM SIZE in the create tablespace storage clause should always be a multiple of the SORT_AREA_SIZE parameter. Oracle will default to the next multiple of the SORT_AREA_SIZE parameter.

UNIFORM SIZE = SORT_AREA_SIZE * n
INITIAL = NEXT = SORT_AREA_SIZE * n

Ex:
If SORT_AREA_SIZE = 64k (default size) and you specify the UNIFORM SIZE in the create tablepspace storage clause to be 32k, Oracle will automatically allocate 64k.

If SORT_AREA_SIZE = 64k
and you specify the UNIFORM SIZE = 72k Oracle will automatically allocate 128k

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, you can specify the size of the initial extent and Oracle determines the optimal size of additional extents, with a minimum extent size of 64 KB. 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.

You cannot create a locally managed SYSTEM tablespace.

Locally managed temporary tablespaces must 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 XMIN_ETLEN 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:

Better space management due to:
- uniformed extent sizes;
- reduced data dictionary access.
Reduced fragmentation.
Better management of temporary space.

-----Original Message-----
From: mleith_at_bradmark.co.uk [mailto:mleith_at_bradmark.co.uk] Sent: Tuesday, May 02, 2000 10:44 PM
To: Multiple recipients of list ORACLE-L Subject: RE: Dicitonary managed tablespaces

Hey Guy's

I actually don't have access to Metalink!! Received on Tue May 02 2000 - 18:56:23 CDT