In this article we will discuss about the space monitoring related views in ORACLE database.
DBA_TABLESPACES
DBA_TABLESPACES is a database Space monitoring view that describes all the tablespaces in the database.
| Column | Datatype | Description |
| TABLESPACE_NAME | VARCHAR2(30) | Name of the tablespace |
| BLOCK_SIZE | NUMBER | Tablespace block size |
| INITIAL_EXTENT | NUMBER | Default initial extent size |
| NEXT_EXTENT | NUMBER | Default incremental extent size |
| MIN_EXTENTS | NUMBER | Default minimum number of extents |
| MAX_EXTENTS | NUMBER | Default maximum number of extents |
| PCT_INCREASE | NUMBER | Default percent increase for extent size |
| MIN_EXTLEN | NUMBER | Minimum extent size for this tablespace |
| STATUS | VARCHAR2(9) | Tablespace status: |
| ONLINE | ||
| OFFLINE | ||
| READ ONLY | ||
| CONTENTS | VARCHAR2(9) | Tablespace contents: |
| UNDO | ||
| PERMANENT | ||
| TEMPORARY | ||
| LOGGING | VARCHAR2(9) | Default logging attribute: |
| LOGGING | ||
| NOLOGGING | ||
| FORCE_LOGGING | VARCHAR2(3) | Indicates whether the tablespace is under force logging mode (YES) or not (NO) |
| EXTENT_MANAGEMENT | VARCHAR2(10) | Indicates whether the extents in the tablespace are dictionary managed (DICTIONARY) or locally managed (LOCAL) |
| ALLOCATION_TYPE | VARCHAR2(9) | Type of extent allocation in effect for the tablespace: |
| SYSTEM | ||
| UNIFORM | ||
| USER | ||
| PLUGGED_IN | VARCHAR2(3) | Indicates whether the tablespace is plugged in (YES) or not (NO) |
| SEGMENT_SPACE_MANAGEMENT | VARCHAR2(6) | Indicates whether the free and used segment space in the tablespace is managed using free lists (MANUAL) or bitmaps (AUTO) |
| DEF_TAB_COMPRESSION | VARCHAR2(8) | Indicates whether default table compression is enabled (ENABLED) or not (DISABLED) |
| Note: Enabling default table compression indicates that all tables in the tablespace will be created with table compression enabled unless otherwise specified. | ||
| RETENTION | VARCHAR2(11) | Undo tablespace retention: |
| GUARANTEE – Tablespace is an undo tablespace with RETENTION specified as GUARANTEE | ||
| A RETENTION value of GUARANTEE indicates that unexpired undo in all undo segments in the undo tablespace should be retained even if it means that forward going operations that need to generate undo in those segments fail. | ||
| NOGUARANTEE – Tablespace is an undo tablespace with RETENTION specified as NOGUARANTEE | ||
| NOT APPLY – Tablespace is not an undo tablespace | ||
| BIGFILE | VARCHAR2(3) | Indicates whether the tablespace is a bigfile tablespace (YES) or a smallfile tablespace (NO) |
For example, we can use below query to display the details of tabespaces:
SQL> SELECT TABLESPACE_NAME,BLOCK_SIZE,
2 contents,extent_management from dba_tablespaces;
DBA_SEGMENTS
DBA_SEGMENTS view describes the storage allocated for all segments in the database, i.e. it will describe the physical” data segment, like a data file associated with a tablespace.
| Column | Datatype | Description |
| OWNER | VARCHAR2(30) | Username of the segment owner |
| SEGMENT_NAME | VARCHAR2(81) | Name, if any, of the segment |
| PARTITION_NAME | VARCHAR2(30) | Object Partition Name (Set to NULL for non-partitioned objects) |
| SEGMENT_TYPE | VARCHAR2(18) | Type of segment: INDEX PARTITION, TABLE PARTITION, TABLE, CLUSTER, INDEX, ROLLBACK, DEFERRED ROLLBACK, TEMPORARY, CACHE, LOBSEGMENT and LOBINDEX |
| TABLESPACE_NAME | VARCHAR2(30) | Name of the tablespace containing the segment |
| HEADER_FILE | NUMBER | ID of the file containing the segment header |
| HEADER_BLOCK | NUMBER | ID of the block containing the segment header |
| BYTES | NUMBER | Size, in bytes, of the segment |
| BLOCKS | NUMBER | Size, in Oracle blocks, of the segment |
| EXTENTS | NUMBER | Number of extents allocated to the segment |
| INITIAL_EXTENT | NUMBER | Size in bytes requested for the initial extent of the segment at create time. (Oracle rounds the extent size to multiples of 5 blocks if the requested size is greater than 5 blocks.) |
| NEXT_EXTENT | NUMBER | Size in bytes of the next extent to be allocated to the segment |
| MIN_EXTENTS | NUMBER | Minimum number of extents allowed in the segment |
| MAX_EXTENTS | NUMBER | Maximum number of extents allowed in the segment |
| PCT_INCREASE | NUMBER | Percent by which to increase the size of the next extent to be allocated |
| FREELISTS | NUMBER | Number of process freelists allocated to this segment |
| FREELIST_GROUPS | NUMBER | Number of freelist groups allocated to this segment |
| RELATIVE_FNO | NUMBER | Relative file number of the segment header |
| BUFFER_POOL | VARCHAR2(7) | Default buffer pool for the object |
Below is the SQL command which we can use it for space monitoring.
SQL> select TABLESPACE_NAME,count(*) NUM_OBJECTS,
2 sum(bytes/1024/1024/1024),sum(blocks),sum(extends) from dba_segments
3 group by rollup (TABLESPACE_NAME);Output will be as shown below:
DBA_FREE_SPACE
DBA_FREE_SPACE is a useful space monitoring view that describes the free extents in all tablespaces in the database
| Column | Datatype | Description |
| TABLESPACE_NAME | VARCHAR2(30) | Name of the tablespace containing the extent |
| FILE_ID | NUMBER | File identifier number of the file containing the extent |
| BLOCK_ID | NUMBER | Starting block number of the extent |
| BYTES | NUMBER | Size of the extent (in bytes) |
| BLOCKS | NUMBER | Size of the extent (in Oracle blocks) |
| RELATIVE_FNO | NUMBER | Relative file number of the file containing the extent |
Query:
Select TABLESPACE_NAME,sum(bytes/1024/1024/1024) from dba_free_space
2 group by TABLESPACE_NAME;Output of the query will be displayed as below
