New Columns in the DBA_TABLESPACES view

Oracle8i has several new features in storage management and flexibility in using the tablespaces. These are also reflected in the most commonly used DBA views - DBA_TABLESPACES and DBA_DATA_FILES.

DBA_TABLESPACES shows information about all the tablespaces in the database. 

SQL> desc DBA_TABLESPACES
 Name                   Null?    Type
 ---------------------- -------- -------------
 TABLESPACE_NAME        NOT NULL VARCHAR2(30)
 INITIAL_EXTENT                  NUMBER
 NEXT_EXTENT                     NUMBER
 MIN_EXTENTS            NOT NULL NUMBER
 MAX_EXTENTS                     NUMBER
 PCT_INCREASE                    NUMBER
 MIN_EXTLEN                      NUMBER
 STATUS                          VARCHAR2(9)
 CONTENTS                        VARCHAR2(9)
 LOGGING                         VARCHAR2(9)
 EXTENT_MANAGEMENT               VARCHAR2(10)
 ALLOCATION_TYPE                 VARCHAR2(9)
 PLUGGED_IN                      VARCHAR2(3)

The four columns appearing at the end and MIN_EXTLEN are new additions to Oracle8i. 

MIN_EXTLEN shows the MINIMUM_EXTENT value you used (if any) while creating the tablespace, which is mainly used to reduce fragmentation in a dictionary managed tablespace, where all the extent sizes are assured to be a multiple of MINIMUM_EXTENT.

CONTENTS shows whether the tablespace is TEMPORARY or PERMANENT. TEMPORARY tablespaces are used for improving the sort performance and cannot hold any permanent objects.

LOGGING shows the default LOGGING or NOLOGGING setting for the tablespace.

EXTENT_MANAGEMENT shows if the tablespace is dictionary managed or locally managed. In Oracle7 only dictionary managed tablespaces were available. Locally managed tablespaces control extent allocation and de-allocation information in the data files using bitmaps.

ALLOCATION_TYPE will be USER for the dictionary managed tablespace, and for locally managed tablespace newly created, the value will be SYSTEM (Oracle manages the extent allocation either UNIFORM or AUTOALLOCATE). If the locally managed tablespace was converted from dictionary managed tablespace using the sys.dbms_space_admin.tablespace_migrate_to_local procedure, the extent allocation will be USER, because Oracle does not handle the extent sizes.

PLUGGED_IN column shows if the tablespace was "plugged in" to this database from another database using the transportable tablespace feature.

SQL> select TABLESPACE_NAME, MIN_EXTLEN, EXTENT_MANAGEMENT,
  2         LOGGING, ALLOCATION_TYPE, PLUGGED_IN
  3  from   dba_tablespaces;

TABLESPACE_NAME  MIN_EXTLEN EXTENT_MAN LOGGING   ALLOCATIO PLU
---------------- ---------- ---------- --------- --------- ---
SYSTEM                65536 DICTIONARY LOGGING   USER      NO
TOOLS                 65536 LOCAL      LOGGING   SYSTEM    NO
RBS                       0 DICTIONARY LOGGING   USER      NO
TEMP                 524288 LOCAL      NOLOGGING UNIFORM   NO
USERS                     0 DICTIONARY LOGGING   USER      YES
CRY_DATA              65536 LOCAL      LOGGING   USER      NO
CRY_HISTORY          524288 LOCAL      NOLOGGING UNIFORM   NO

SYSTEM is created when the database was created, and is dictionary managed. RBS is dictionary managed tablespace. TOOLS is locally managed, with the extent sizes managed by Oracle (created with EXTENT MANAGEMENT LOCAL AUTOALLOCATE option). TEMP is created using CREATE TEMPORARY TABLESPACE command. USERS is a tablespace plugged in to this database using the transporatable tablesapce feature. CRY_DATA was originally created as DICTIONARY managed, later converted to LOCAL using DBMS_SPACE_ADMIN package. CRY_HISTORY was created as LOCAL with UNIFORM extent sizes (EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K).

Search BijooS.com Exact Match Search      
Home Oracle DBA TKMCE Alumni H1B Info Guestbook

Biju Thomas is Oracle7.3 OCP, Oracle8 OCP, 
Oracle8i OCP and Oracle9i OCA/OCP Certified DBA

Questions/Comments? Write to webmaster@bijoos.com. The scripts, tips and articles appearing on BijooS.com cannot be reproduced elsewhere without the prior permission from the webmaster.