Use Shell scripts to view database tablespace usage

Use Shell scripts to view database tablespace usage

Check the table space in the database. You may have a lot of scripts in use. You can also read it and share an example of using shell scripts to check the table space usage.
The script is as follows:
Sqlplus-s $ DB_CONN_STR @ $ SH_DB_SID <EOF
Set echo off heading on underline on;
Column inst_num heading "Inst Num" new_value inst_num format 99999;
Column inst_name heading "Instance" new_value inst_name format a12;
Column db_name heading "DB Name" new_value db_name format a12;
Column dbid heading "DB Id" new_value dbid format 9999999999 just c;

Prompt
Prompt Current Instance
Prompt ~~~~~~~~~~~~~~~~

Select d. dbid
, D. name db_name
, I. instance_number inst_num
, I. instance_name inst_name
From v \ $ database d,
V \ $ instance I;

Set term on feedback off lines 130 pagesize 999 tab off trims on
Column MB format 999,999,999 heading "Total MB"
Column free format 9,999,999 heading "Free MB"
Column used format 99,999,999 heading "Used MB"
Column Largest format 999,999 heading "lrgmb"
Column tablespace_name format a20 heading "Tablespace"
Column status format a3 truncated
Column max_extents format 99999999999 heading "MaxExt"
Col extent_management for a1 trunc head "M"
Col allocation_type for a1 trunc head ""
Col Ext_Size for a4 trunc head "Init"
Column pfree format a3 trunc heading "% Fr"

Break on report
Compute sum of MB on report
Compute sum of free on report
Compute sum of used on report

Select
D. tablespace_name,
Decode (d. status,
'Online', 'oln ',
'Read only', 'r/o ',
D. status) status,
D. extent_management,
Decode (d. allocation_type,
'User ','',
D. allocation_type) allocation_type,
(Case
When initial_extent <1048576
Then lpad (round (initial_extent/1024,0), 3) | 'K'
Else lpad (round (initial_extent/1024/1024, 0), 3) | 'M'
End) Ext_Size,
NVL (a. bytes/1024/1024, 0) MB,
NVL (f. bytes/1024/1024, 0) free,
(NVL (a. bytes/1024/1024, 0)-NVL (f. bytes/1024/1024, 0) used,
NVL (l. large/1024/1024, 0) largest,
D. MAX_EXTENTS,
Lpad (round (f. bytes/a. bytes) * 100,0), 3) pfree,
(Case when round (f. bytes/a. bytes * 100,0)> = 20 then ''else' * 'end) alrt
FROM sys. dba_tablespaces d,
(SELECT tablespace_name, SUM (bytes) bytes
FROM dba_data_files
Group by tablespace_name),
(SELECT tablespace_name, SUM (bytes) bytes
FROM dba_free_space
Group by tablespace_name) f,
(SELECT tablespace_name, MAX (bytes) large
FROM dba_free_space
Group by tablespace_name) l
WHERE d. tablespace_name = a. tablespace_name (+)
AND d. tablespace_name = f. tablespace_name (+)
AND d. tablespace_name = l. tablespace_name (+)
And not (d. extent_management LIKE 'local' AND d. contents LIKE 'temporary ')
UNION ALL
Select
D. tablespace_name,
Decode (d. status,
'Online', 'oln ',
'Read only', 'r/o ',
D. status) status,
D. extent_management,
Decode (d. allocation_type,
'Uniform', 'U ',
'System', 'A ',
'User ','',
D. allocation_type) allocation_type,
(Case
When initial_extent <1048576
Then lpad (round (initial_extent/1024,0), 3) | 'K'
Else lpad (round (initial_extent/1024/1024, 0), 3) | 'M'
End) Ext_Size,
NVL (a. bytes/1024/1024, 0) MB,
(NVL (a. bytes/1024/1024, 0)-NVL (t. bytes/1024/1024, 0) free,
NVL (t. bytes/1024/1024, 0) used,
NVL (l. large/1024/1024, 0) largest,
D. MAX_EXTENTS,
Lpad (round (nvl (a. bytes-t.bytes)/NVL (a. bytes, 0) * 100,100), 0), 3) pfree,
(Case when nvl (round (. bytes-t.bytes)/NVL (. bytes, 0) * 100,0), 100)> = 20 then''else' * 'end) alrt
FROM sys. dba_tablespaces d,
(SELECT tablespace_name, SUM (bytes) bytes
FROM dba_temp_files
Group by tablespace_name order by tablespace_name),
(SELECT tablespace_name, SUM (bytes_used) bytes
FROM v \ $ temp_extent_pool
Group by tablespace_name) t,
(SELECT tablespace_name, MAX (bytes_cached) large
FROM v \ $ temp_extent_pool
Group by tablespace_name order by tablespace_name) l
WHERE d. tablespace_name = a. tablespace_name (+)
AND d. tablespace_name = t. tablespace_name (+)
AND d. tablespace_name = l. tablespace_name (+)
AND d. extent_management LIKE 'local'
AND d. contents LIKE 'temporary'
ORDER by 1
/
Prompt
Exit
EOF

The running result is relatively simple and clear.

This article permanently updates the link address: