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