{"id":975,"date":"2024-12-27T22:49:26","date_gmt":"2024-12-27T22:49:26","guid":{"rendered":"https:\/\/w3buddy.com\/?p=975"},"modified":"2026-01-15T13:17:32","modified_gmt":"2026-01-15T07:47:32","slug":"monitoring-oracle-database-growth-and-space-utilization","status":"publish","type":"post","link":"https:\/\/w3buddy.com\/blog\/monitoring-oracle-database-growth-and-space-utilization\/","title":{"rendered":"Monitoring Oracle Database Growth and Space Utilization"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">This SQL query helps monitor the growth and space utilization of an Oracle database by calculating the total size, used space, free space, and growth over a day and week. It provides key metrics such as percentage usage and growth rates, offering insights into the database&#8217;s capacity and expansion trends.<\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">SET LINESIZE 200\nSET PAGESIZE 200\nCOL \u201cDatabase Size\u201d FORMAT a13\nCOL \u201cUsed Space\u201d FORMAT a11\nCOL \u201cUsed in %\u201d FORMAT a11\nCOL \u201cFree in %\u201d FORMAT a11\nCOL \u201cDatabase Name\u201d FORMAT a13\nCOL \u201cFree Space\u201d FORMAT a12\nCOL \u201cGrowth DAY\u201d FORMAT a11\nCOL \u201cGrowth WEEK\u201d FORMAT a12\nCOL \u201cGrowth DAY in %\u201d FORMAT a16\nCOL \u201cGrowth WEEK in %\u201d FORMAT a16\nSELECT\n(select min(creation_time) from v$datafile) \u201cCreate Time\u201d,\n(select name from v$database) \u201cDatabase Name\u201d,\nROUND((SUM(USED.BYTES) \/ 1024 \/ 1024 ),2) || \u2018 MB\u2019 \u201cDatabase Size\u201d,\nROUND((SUM(USED.BYTES) \/ 1024 \/ 1024 ) \u2013 ROUND(FREE.P \/ 1024 \/ 1024 ),2) || \u2018 MB\u2019 \u201cUsed Space\u201d,\nROUND(((SUM(USED.BYTES) \/ 1024 \/ 1024 ) \u2013 (FREE.P \/ 1024 \/ 1024 )) \/ ROUND(SUM(USED.BYTES) \/ 1024 \/ 1024 ,2)*100,2) || \u2018% MB\u2019 \u201cUsed in %\u201d,\nROUND((FREE.P \/ 1024 \/ 1024 ),2) || \u2018 MB\u2019 \u201cFree Space\u201d,\nROUND(((SUM(USED.BYTES) \/ 1024 \/ 1024 ) \u2013 ((SUM(USED.BYTES) \/ 1024 \/ 1024 ) \u2013 ROUND(FREE.P \/ 1024 \/ 1024 )))\/ROUND(SUM(USED.BYTES) \/ 1024 \/ 1024,2 )*100,2) || \u2018% MB\u2019 \u201cFree in %\u201d,\nROUND(((SUM(USED.BYTES) \/ 1024 \/ 1024 ) \u2013 (FREE.P \/ 1024 \/ 1024 ))\/(select sysdate-min(creation_time) from v$datafile),2) || \u2018 MB\u2019 \u201cGrowth DAY\u201d,\nROUND(((SUM(USED.BYTES) \/ 1024 \/ 1024 ) \u2013 (FREE.P \/ 1024 \/ 1024 ))\/(select sysdate-min(creation_time) from v$datafile)\/ROUND((SUM(USED.BYTES) \/ 1024 \/ 1024 ),2)*100,3) || \u2018% MB\u2019 \u201cGrowth DAY in %\u201d,\nROUND(((SUM(USED.BYTES) \/ 1024 \/ 1024 ) \u2013 (FREE.P \/ 1024 \/ 1024 ))\/(select sysdate-min(creation_time) from v$datafile)*7,2) || \u2018 MB\u2019 \u201cGrowth WEEK\u201d,\nROUND((((SUM(USED.BYTES) \/ 1024 \/ 1024 ) \u2013 (FREE.P \/ 1024 \/ 1024 ))\/(select sysdate-min(creation_time) from v$datafile)\/ROUND((SUM(USED.BYTES) \/ 1024 \/ 1024 ),2)*100)*7,3) || \u2018% MB\u2019 \u201cGrowth WEEK in %\u201d\nFROM    (SELECT BYTES FROM V$DATAFILE\nUNION ALL\nSELECT BYTES FROM V$TEMPFILE\nUNION ALL\nSELECT BYTES FROM V$LOG) USED,\n(SELECT SUM(BYTES) AS P FROM DBA_FREE_SPACE) FREE\nGROUP BY FREE.P;<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Sample Output:<\/h2>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">Create Time        |Database Name|Database Size|Used Space |Used in %  |Free Space  |Free in %  |Growth DAY |Growth DAY in % |Growth WEEK |Growth WEEK in %\n-------------------|-------------|-------------|-----------|-----------|------------|-----------|-----------|----------------|------------|----------------\n30\/05\/2019 03:10:09|ORCL         |2628 MB      |2409 MB    |91.68% MB  |218.75 MB   |8.33% MB   |1.18 MB    |.045% MB        |8.27 MB     |.315% MB<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>This SQL query helps monitor the growth and space utilization of an Oracle database by calculating the total size, used space, free space, and growth over a day and week. It provides key metrics such as percentage usage and growth rates, offering insights into the database&#8217;s capacity and expansion trends. Sample Output:<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"googlesitekit_rrm_CAowu461DA:productID":"","footnotes":""},"categories":[1225],"tags":[],"class_list":["post-975","post","type-post","status-publish","format-standard","hentry","category-database"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/975","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/comments?post=975"}],"version-history":[{"count":1,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/975\/revisions"}],"predecessor-version":[{"id":976,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/975\/revisions\/976"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=975"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=975"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/tags?post=975"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}