How to check tablespace size in oracle database

by

Introduction: –

In this post we will cover how to check tablespace size in oracle database. Using dba_data_files and dba_free_space to check tablespace size. Follow the below steps to monitory the oracle database utilization.

How to check tablespace size in oracle database

Here I will explain how to check tablespace size in oracle. Every DBA checking the tablespace size on daily basis to maintain the free space in oracle database.

how to check tablespace size in oracle database
 
 

To check tablespace size in oracle database use below scripts.


List all Tablespace size:-

 
SELECT/* + RULE */  df.tablespace_name “Tablespace“,
       df.bytes / (1024 * 1024) “Size (MB)“,
SUM(fs.bytes) / (1024 * 1024) “Free (MB)“,
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) “% Free”,
Round((df.bytes – SUM(fs.bytes)) * 100 / df.bytes) “% Used”
FROM dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+)=df.tablespace_name
GROUP BY df.tablespace_name,df.bytes
order by 5 desc;
 
 

To Check tablespace size with 85% utilization.

 
 
 
SELECT
SUBSTR (a.tablespace_name, 1, 16) “TABLESPACE”,
SUM
(b.BYTES)
* COUNT (DISTINCT b.file_id)
/ COUNT (b.file_id)
/ 1024
/ 1024 “TOTAL_SIZE(MB)”,
( SUM (b.BYTES)
* COUNT (DISTINCT b.file_id)
/ COUNT (b.file_id)
/ 1024
/ 1024
)
– (ROUND (SUM (c.BYTES) / 1024 / 1024 / COUNT (DISTINCT b.file_id)))
“USED_SIZE(MB)”,
ROUND
(SUM (c.BYTES) / 1024 / 1024 / COUNT (DISTINCT b.file_id)
) “FREE_SIZE(MB)”,
MAX
(a.max_extents) “LARGEST_EXTENTS”,
TO_CHAR
( 100
– ( SUM (c.blocks)
* 100
* COUNT (b.file_id)
/ (SUM (b.blocks) * COUNT (DISTINCT b.file_id))
)
/ COUNT (DISTINCT b.file_id),
‘999.99’)
||
‘%’ “USED(%)” ,
TO_CHAR
( ( SUM (c.blocks)
* 100
* COUNT (b.file_id)
/ (SUM (b.blocks) * COUNT (DISTINCT b.file_id))
)
/ COUNT (DISTINCT b.file_id),
‘999.99’
)
||
‘%’ “FREE(%)”
FROM dba_tablespaces a, dba_data_files b, dba_free_space c
WHERE a.tablespace_name = b.tablespace_name
AND a.tablespace_name = c.tablespace_name
GROUP
BY a.tablespace_name
HAVING
( 100 -( SUM (c.blocks)
* 100
* COUNT (b.file_id)
/ (SUM (b.blocks) * COUNT (DISTINCT b.file_id))
)
/ COUNT (DISTINCT b.file_id))>=85 —-here to give condtion
ORDER
BY 6 DESC;
 
As per our requirement we can schedule above query through crontab to pull out tablespace report on daily basis to monitor database.  We can fetch tablespace utilization in html format and send it on email.
 
 

Steps to prepare shell script for tablespace report for alert on email.

 

1. Prepare the shell sctip as tablespace_report.sh.

 
 
To monitor the tablespace size in oracle database on regular basis.
 
Copy the below as tablespace_report.sh
 
ORACLE_SID=PROD; export ORACLE_SID
. /oracle/home/oracle/ora10g/PROD_erp.env
/oracle/home/oracle/ora10g/bin/sqlplus -S -M “HTML ON TABLE ‘BORDER=”8″‘” toad/glprdtoad @/oracle/home/oracle/dba_scripts/cron_scripts/sql/tablespace.sql
 
cat /oracle/home/oracle/dba_scripts/cron_scripts/shell/mailheader.log TABLESPACE_REPORT.html | mailx -t;
 
 
 

2.Prepare the sql script as tablespace.sql.

 
 
Just copy the below scripts as tablespace.sql
 
 
 
set markup html on spool on entmap off
 
SET SERVEROUTPUT ON SIZE 1000000;
SET LINESIZE 9999;
set pagesize 400;
COLUMN TABLESPACE HEADING ‘TABLESPACE’ ENTMAP OFF
COLUMN TOTAL_SIZE(MB) HEADING ‘TOTAL_SIZE(MB)’
COLUMN USED_SIZE(MB)  HEADING ‘USED_SIZE(MB)’
COLUMN LARGEST_EXTENTS HEADING ‘LARGEST_EXTENTS’
COLUMN FREE_SIZE(MB) HEADING ‘FREE_SIZE(MB)’
COLUMN USED_USAGE HEADING ‘USED(%)’
COLUMN FREE_USAGE HEADING ‘FREE(%)’
 
SPOOL TABLESPACE_REPORT.html
 
prompt <i>Here is the tablespace report &_DATE</i>
 
SELECT
SUBSTR (a.tablespace_name, 1, 16) “TABLESPACE”,
SUM
(b.BYTES)
* COUNT (DISTINCT b.file_id)
/ COUNT (b.file_id)
/ 1024
/ 1024 “TOTAL_SIZE(MB)”,
( SUM (b.BYTES)
* COUNT (DISTINCT b.file_id)
/ COUNT (b.file_id)
/ 1024
/ 1024
)
– (ROUND (SUM (c.BYTES) / 1024 / 1024 / COUNT (DISTINCT b.file_id)))
“USED_SIZE(MB)”,
ROUND
(SUM (c.BYTES) / 1024 / 1024 / COUNT (DISTINCT b.file_id)
) “FREE_SIZE(MB)”,
MAX
(a.max_extents) “LARGEST_EXTENTS”,
TO_CHAR
( 100
– ( SUM (c.blocks)
* 100
* COUNT (b.file_id)
/ (SUM (b.blocks) * COUNT (DISTINCT b.file_id))
)
/ COUNT (DISTINCT b.file_id),
‘999.99’)
||
‘%’ “USED(%)” ,
TO_CHAR
( ( SUM (c.blocks)
* 100
* COUNT (b.file_id)
/ (SUM (b.blocks) * COUNT (DISTINCT b.file_id))
)
/ COUNT (DISTINCT b.file_id),
‘999.99’
)
||
‘%’ “FREE(%)”
FROM dba_tablespaces a, dba_data_files b, dba_free_space c
WHERE a.tablespace_name = b.tablespace_name
AND a.tablespace_name = c.tablespace_name
GROUP
BY a.tablespace_name
HAVING
( 100 -( SUM (c.blocks)
* 100
* COUNT (b.file_id)
/ (SUM (b.blocks) * COUNT (DISTINCT b.file_id))
)
/ COUNT (DISTINCT b.file_id))>=85 —-here to give condtion
ORDER
BY 6 DESC;
 
SPOOL OFF
set markup html off
exit;
 
 

3.Prepare the file mailheader.log.

 
Simply copy the below to make mailheader.log to fetch the email.
 
Cc:
Subject:Tablespace Report in PROD > 85%.
Content-Type: text/html
 
In last need to schedule the schell script ‘tablespace_report.sh’ for daily tablespace utilization.
 
Run ‘crontab –e’ to schedule it on oracle database server.
 

 

30 04 * * *  /oracle/home/oracle/dba_scripts/cron_scripts/shell/tablespace_report.sh
 
 
 

1 thought on “How to check tablespace size in oracle database”

Leave a Comment