Oracle7 Server Administrator's Guide

Contents Index Home Previous Next

Displaying Information About Schema Objects

The data dictionary provides many views about the schema objects described in chapters 10-16. The following list summarizes the views associated with schema objects:

The following data dictionary views contain information about the segments of a database:

The following data dictionary views contain information about a database's extents:

Oracle Packages

Table 16 - 2 describes packages that are supplied with Oracle to either allow PL/SQL access to some SQL features, or to extend the functionality of the database.

Procedure Description
dbms_space.unused_space Returns information about unused space in an object (table, index, or cluster).
dbms_space.free_blocks Returns information about free blocks in an object (table, index, or cluster).
dbms_session.free_unused_ user_memory Procedure for reclaiming unused memory after performing operations requiring large amounts of memory (where large>100K). This procedure should only be used in cases where memory is at a premium.
dbms_system.set_sql_trace_in_session Enables sql_trace in the session identified by serial number and SID (these values are located in v$session).
Table 16 - 2. Supplied Packages: Additional Functionality

The following examples demonstrate ways to display miscellaneous schema objects.

Example 1 Displaying Schema Objects By Type

The following query lists all of the objects owned by the user issuing the query:

SELECT object_name, object_type FROM user_objects;
OBJECT_NAME               OBJECT_TYPE
------------------------- -------------------
EMP_DEPT                  CLUSTER
EMP                       TABLE
DEPT                      TABLE
EMP_DEPT_INDEX            INDEX
PUBLIC_EMP                SYNONYM
EMP_MGR                   VIEW

Example 2 Displaying Column Information

Column information, such as name, datatype, length, precision, scale, and default data values can be listed using one of the views ending with the _COLUMNS suffix. For example, the following query lists all of the default column values for the EMP and DEPT tables:

SELECT table_name, column_name, data_default
   FROM user_tab_columns
   WHERE table_name = 'DEPT' OR table_name = 'EMP';
TABLE_NAME  COLUMN_NAME     DATA_DEFAULT
----------  --------------- --------------------
DEPT        DEPTNO
DEPT        DNAME
DEPT        LOC             'NEW YORK'
EMP         EMPNO
EMP         ENAME
EMP         JOB
EMP         MGR
EMP         HIREDATE        SYSDATE
EMP         SAL
EMP         COMM
EMP         DEPTNO

Notice that not all columns have user-specified defaults. These columns automatically have NULL as the default.

Example 3 Displaying Dependencies of Views and Synonyms

When you create a view or a synonym, the view or synonym is based on its underlying base object. The ALL/USER/DBA_DEPENDENCIES data dictionary views can be used to reveal the dependencies for a view and the ALL/USER/DBA_SYNONYMS data dictionary views can be used to list the base object of a synonym. For example, the following query lists the base objects for the synonyms created by the user JWARD:

SELECT table_owner, table_name, synonym_name
   FROM sys.dba_synonyms
   WHERE owner = 'JWARD';
TABLE_OWNER             TABLE_NAME   SYNONYM_NAME
----------------------- ------------ -----------------
SCOTT                   DEPT         DEPT
SCOTT                   EMP          EMP

Example 4 Displaying General Segment Information

The following query returns the name of each rollback segment, the tablespace that contains each, and the size of each rollback segment:

SELECT segment_name, tablespace_name, bytes, blocks, extents
   FROM sys.dba_segments
   WHERE segment_type = 'ROLLBACK';
SEGMENT_NAME TABLESPACE_NAME      BYTES     BLOCKS    EXTENTS
------------ --------------- ---------- ---------- ----------
RS1          SYSTEM               20480         10          2
RS2          TS1                  40960         20          3
SYSTEM       SYSTEM              184320         90          3

Example 5 Displaying General Extent Information

General information about the currently allocated extents in a database is stored in the DBA_EXTENTS data dictionary view. For example, the following query identifies the extents associated with rollback segments and the size of each of those extents:

SELECT segment_name, bytes, blocks
   FROM sys.dba_extents
   WHERE segment_type = 'ROLLBACK';
SEGMENT_NAME         BYTES     BLOCKS
--------------- ---------- ----------
RS1                  10240          5
RS1                  10240          5
SYSTEM               51200         25
SYSTEM               51200         25
SYSTEM               51200         25

Notice that the RS1 rollback segment is comprised of two extents, both 10K, while the SYSTEM rollback segment is comprised of three equally sized extents of 50K.

Example 6 Displaying the Free Space (Extents) of a Database

Information about the free extents (extents not allocated to any segment) in a database is stored in the DBA_FREE_SPACE data dictionary view. For example, the following query reveals the amount of free space available via free extents in each tablespace:

SELECT tablespace_name, file_id, bytes, blocks
   FROM sys.dba_free_space;
TABLESPACE_NAME         FILE_ID      BYTES     BLOCKS
-------------------- ---------- ---------- ----------
SYSTEM                        1    8120320       3965
SYSTEM                        1      10240          5
TS1                           2   10432512       5094

Example 7 Displaying Segments that Cannot Allocate Additional Extents

You can also use DBA_FREE_SPACE, in combination with the views DBA_SEGMENTS, DBA_TABLES, DBA_CLUSTERS, DBA_INDEXES, and DBA_ROLLBACK_SEGS, to determine if any other segment is unable to allocate additional extents for data dictionary objects only.

A segment may not be allocated to an extent for any of the following reasons:

Note: While the STORAGE clause value for MAXEXTENTS can be UNLIMITED, data dictionary tables cannot have MAXEXTENTS greater than the allowed block maximum. Thus, data dictionary tables cannot be converted to unlimited format.

The following query returns the names, owners, and tablespaces of all segments that fit any of the above criteria:

SELECT seg.owner, seg.segment_name,
   seg.segment_type, seg.tablespace_name,
   DECODE(seg.segment_type,
      'TABLE', t.next_extent,
      'CLUSTER', c.next_extent,
      'INDEX', i.next_extent,
      'ROLLBACK', r.next_extent)
FROM sys.dba_segments seg,
   sys.dba_tables t,
   sys.dba_clusters c,
   sys.dba_indexes i,
   sys.dba_rollback_segs r
WHERE ((seg.segment_type = 'TABLE'
   AND seg.segment_name = t.table_name
   AND seg.owner = t.owner
   AND NOT EXISTS (SELECT tablespace_name
      FROM dba_free_space free
      WHERE free.tablespace_name = t.tablespace_name
      AND free.bytes >= t.next_extent))
OR (seg.segment_type = 'CLUSTER'
   AND seg.segment_name = c.cluster_name
   AND seg.owner = c.owner
   AND NOT EXISTS (SELECT tablespace_name
      FROM dba_free_space free
      WHERE free.tablespace_name = c.tablespace_name
      AND free.bytes >= c.next_extent))
OR (seg.segment_type = 'INDEX'
   AND seg.segment_name = i.index_name
   AND seg.owner = i.owner
   AND NOT EXISTS (SELECT tablespace_name
      FROM dba_free_space free
      WHERE free.tablespace_name = i.tablespace_name
      AND free.bytes >= i.next_extent))
OR     (seg.segment_type = 'ROLLBACK'
   AND seg.segment_name = r.segment_name
   AND seg.owner = r.owner
   AND NOT EXISTS (SELECT tablespace_name
      FROM dba_free_space free
      WHERE free.tablespace_name = r.tablespace_name
     AND free.bytes >= r.next_extent)))
OR seg.extents = seg.max_extents OR seg.extents = data_block_size;

Note: When you use this query, replace data_block_size with the data block size for your system.

Once you have identified a segment that cannot allocate additional extents, you can solve the problem in either of two ways, depending on its cause:


Contents Index Home Previous Next