{"id":275,"date":"2025-04-03T14:50:04","date_gmt":"2025-04-03T12:50:04","guid":{"rendered":"http:\/\/mariospavlidis.site\/?p=275"},"modified":"2025-07-10T10:46:56","modified_gmt":"2025-07-10T08:46:56","slug":"tablespace-usage","status":"publish","type":"post","link":"http:\/\/130.61.57.200\/index.php\/2025\/04\/03\/tablespace-usage\/","title":{"rendered":"Oracle Tablespace Usage: Analyzing Free, Used, and Actual File Space"},"content":{"rendered":"\n

Monitoring tablespace usage is a critical part of Oracle database administration. As DBAs, we need to keep an eye on how much space is used, how much remains, and how it aligns with what\u2019s physically allocated on disk. We\u2019ll explore a query that gives you a clear view of your tablespace usage, including how much space is actually<\/em> used by segments, the total allocated file space, and the maximum size the tablespace can reach (considering autoextensibility).<\/p>\n\n\n\n

select df.tablespace_name Tablespace,
nvl(totalusedspace,0) Used_MB,
(df.totalspace – nvl(tu.totalusedspace,0)) Free_MB, df.actualspace as Actual_File_Space,
df.totalspace Total_MB,
round(100 * ( (nvl(tu.totalusedspace,0))\/ df.totalspace),2) Pct_Used,
round(100 * ( (df.totalspace – nvl(tu.totalusedspace,0))\/ df.totalspace),2)
Pct_Free
from
(
select tablespace_name,round(sum(decode(autoextensible,’YES’,maxbytes,’NO’,bytes)) \/ 1048576) TotalSpace, round (sum(bytes)\/1048576) ActualSpace
from dba_data_files
group by tablespace_name
) df
,
(
select round(sum(bytes)\/(1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name
) tu
where df.tablespace_name = tu.tablespace_name(+)
order by Pct_Free<\/p>\n\n\n\n

Example Output<\/h2>\n\n\n\n
Tablespace<\/th>Used_MB<\/th>Free_MB<\/th>Actual_File_Space<\/th>Total_MB<\/th>Pct_Used<\/th>Pct_Free<\/th><\/tr><\/thead>
USERS<\/td>512<\/td>488<\/td>600<\/td>1000<\/td>51.20<\/td>48.80<\/td><\/tr>
SYSTEM<\/td>780<\/td>220<\/td>800<\/td>1000<\/td>78.00<\/td>22.00<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

<\/p>\n","protected":false},"excerpt":{"rendered":"

Monitoring tablespace usage is a critical part of Oracle database administration. As DBAs, we need to keep an eye on how much space is used, how much remains, and how it aligns with what\u2019s physically allocated on disk. We\u2019ll explore a query that gives you a clear view of your tablespace usage, including how much […]<\/p>\n","protected":false},"author":1,"featured_media":106,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[72,71],"class_list":["post-275","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-oracle","tag-free","tag-tablespace"],"blocksy_meta":[],"_links":{"self":[{"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/posts\/275","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/comments?post=275"}],"version-history":[{"count":4,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/posts\/275\/revisions"}],"predecessor-version":[{"id":317,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/posts\/275\/revisions\/317"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/media\/106"}],"wp:attachment":[{"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/media?parent=275"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/categories?post=275"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/tags?post=275"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}