dba_segments
Name Type
-------------------- ------------
OWNER VARCHAR2(30)
SEGMENT_NAME VARCHAR2(81)
PARTITION_NAME VARCHAR2(30)
SEGMENT_TYPE VARCHAR2(18)
TABLESPACE_NAME VARCHAR2(30)
HEADER_FILE NUMBER
HEADER_BLOCK NUMBER
BYTES NUMBER
BLOCKS NUMBER
EXTENTS NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NUMBER
MAX_EXTENTS NUMBER
PCT_INCREASE NUMBER
FREELISTS NUMBER
FREELIST_GROUPS NUMBER
RELATIVE_FNO NUMBER
BUFFER_POOL VARCHAR2(7)
DBA_SEGMENTS describes the storage allocated for all segments in the database. This information has many uses, one of which is to find an objects size:
col segment_name format a20
select segment_name
, bytes "SIZE_BYTES"
, ceil(bytes / 1024 / 1024) "SIZE_MB"
from dba_segments
where segment_name like '&obj_name'
/
You can also use it to list all of the objects in a tablespace:
set pages 999
col owner format a15
col segment_name format a40
col segment_type format a20
select owner
, segment_name
, segment_type
from dba_segments
where lower(tablespace_name) like lower('%&tablespace%')
order by owner, segment_name
/
Or to see which if any objects are approaching their maximum extent allocation (maxextents):
col segment_name format a40
select owner
, segment_type
, segment_name
, max_extents - extents as "spare"
, max_extents
from dba_segments
where owner not in ('SYS','SYSTEM')
and (max_extents - extents) < 10
order by 4
/
For more useful DBA queries click here.
| 