how to check tablespace size in oracle database

https://youtu.be/1zyDJIpMgkY

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.

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 sctip 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.

From:xyz@gmail.com

To:abc@gmail.com

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.shhttps://youtu.be/1zyDJIpMgkY