Oracle tablespace usage monitoring

You can use the following methods to conveniently find the SQL statement used to monitor the Oracle tablespace usage:

A test database is found to ensure that only one user is connected. Use TOAD to view the table space usage. Refresh the share pool and then the table space usage, you can view the executed SQL statement in the share pool as follows:

Select ts. TABLESPACE_NAME tablespace name,
TS. STATUS,
TS. CONTENTS,
TS. EXTENT_MANAGEMENT,
SIZE_INFO.MEGS_ALLOC,
SIZE_INFO.MEGS_FREE,
SIZE_INFO.MEGS_USED,
SIZE_INFO.PCT_FREE,
SIZE_INFO.PCT_USED,
Round (SIZE_INFO.MEGS_USED * 100/SIZE_INFO.MAX) used_of_max, --- add by myself
SIZE_INFO.MAX
FROM (select a. TABLESPACE_NAME,
ROUND (A. BYTES_ALLOC/1024/1024) MEGS_ALLOC,
ROUND (NVL (B. BYTES_FREE, 0)/1024/1024) MEGS_FREE,
ROUND (A. BYTES_ALLOC-NVL (B. BYTES_FREE, 0)/1024/1024) MEGS_USED,
ROUND (NVL (B. BYTES_FREE, 0)/A. BYTES_ALLOC) * 100) PCT_FREE,
100-ROUND (NVL (B. BYTES_FREE, 0)/A. BYTES_ALLOC) * 100) PCT_USED,
ROUND (A. MAXBYTES/1048576) MAX
FROM (select f. TABLESPACE_NAME,
SUM (F. BYTES) BYTES_ALLOC,
SUM (DECODE (F. AUTOEXTENSIBLE, 'yes', F. MAXBYTES, 'No', F. BYTES) MAXBYTES
FROM DBA_DATA_FILES F
Group by TABLESPACE_NAME),
(Select f. TABLESPACE_NAME, SUM (F. BYTES) BYTES_FREE
FROM DBA_FREE_SPACE F
Group by TABLESPACE_NAME) B
Where a. TABLESPACE_NAME = B. TABLESPACE_NAME (+)
UNION ALL
Select h. TABLESPACE_NAME,
ROUND (SUM (H. BYTES_FREE + H. BYTES_USED)/1048576) MEGS_ALLOC,
ROUND (SUM (H. BYTES_FREE + H. BYTES_USED )-
NVL (P. BYTES_USED, 0)/1048576) MEGS_FREE,
ROUND (SUM (NVL (P. BYTES_USED, 0)/1048576) MEGS_USED,
ROUND (SUM (H. BYTES_FREE + H. BYTES_USED )-
NVL (P. BYTES_USED, 0 ))/
SUM (H. BYTES_USED + H. BYTES_FREE) * 100) PCT_FREE,
100-ROUND (SUM (H. BYTES_FREE + H. BYTES_USED )-
NVL (P. BYTES_USED, 0 ))/
SUM (H. BYTES_USED + H. BYTES_FREE) * 100) PCT_USED,
ROUND (SUM (F. MAXBYTES)/1048576) MAX
From sys. V _ $ TEMP_SPACE_HEADER H,
SYS. V _ $ TEMP_EXTENT_POOL P,
DBA_TEMP_FILES F
Where p. FILE_ID (+) = H. FILE_ID
And p. TABLESPACE_NAME (+) = H. TABLESPACE_NAME
And f. FILE_ID = H. FILE_ID
And f. TABLESPACE_NAME = H. TABLESPACE_NAME
Group by h. TABLESPACE_NAME) SIZE_INFO,
SYS. DBA_TABLESPACES TS

Where ts. TABLESPACE_NAME = SIZE_INFO.TABLESPACE_NAME

The preceding figure shows the monitoring of temporary tablespace. If you only need to monitor permanent tablespace, you can simply rewrite it:

Set LINESIZE 100

Col TABLESPACE_NAME format A20

Select *
From
(
Select a. TABLESPACE_NAME,
ROUND (A. BYTES_ALLOC/1024/1024) MEGS_ALLOC,
ROUND (A. BYTES_ALLOC-NVL (B. BYTES_FREE, 0)/1024/1024) MEGS_USED,
ROUND (A. BYTES_ALLOC-NVL (B. BYTES_FREE, 0) * 100/A. MAXBYTES) used_of_max,
ROUND (A. MAXBYTES-A. BYTES_ALLOC + NVL (B. BYTES_FREE, 0)/1048576) free_of_max,
ROUND (A. MAXBYTES/1048576) MAX
FROM (select f. TABLESPACE_NAME,
SUM (F. BYTES) BYTES_ALLOC,
SUM (DECODE (F. AUTOEXTENSIBLE, 'yes', F. MAXBYTES, 'No', F. BYTES) MAXBYTES
FROM DBA_DATA_FILES F
Group by TABLESPACE_NAME),
(Select f. TABLESPACE_NAME, SUM (F. BYTES) BYTES_FREE
FROM DBA_FREE_SPACE F
Group by TABLESPACE_NAME) B
Where a. TABLESPACE_NAME = B. TABLESPACE_NAME (+)
) Size_info
Where size_info.used_of_max> 80;

You only need to check used_of_max and free_of_max for the monitoring content, including the percentage of used space in the maximum tablespace and the remaining scalable tablespace size. (The preceding script is used to monitor the tablespaces whose tablespace usage exceeds 80% .)

Oracle Undo image data exploration

Oracle ROLLBACK and Undo)

The Undo tablespace cannot be opened because it is damaged.

How to handle Undo tablespace failures

Oracle Undo tablespace reconstruction and restoration