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
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
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:
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: