{"id":1,"date":"2006-07-29T00:21:00","date_gmt":"2006-07-29T04:21:00","guid":{"rendered":"http:\/\/www.benh.org\/techie\/2006\/07\/how-do-i-find-usedfree-space-in-a-temporary-tablespace-in-oracle\/"},"modified":"2010-09-02T23:52:36","modified_gmt":"2010-09-03T04:52:36","slug":"find-out-usedfree-space-in-a-temporary-tablespace","status":"publish","type":"post","link":"https:\/\/www.benh.org\/techblog\/2006\/07\/find-out-usedfree-space-in-a-temporary-tablespace\/","title":{"rendered":"Finding free\/used temporary table space in Oracle"},"content":{"rendered":"<div style=\"margin-top: 0px; margin-bottom: 0px;\" class=\"sharethis-inline-share-buttons\" ><\/div><p><!--adsense#ads--><br \/>\nIf you&#8217;re a freak who work around with Oracle as backend, you would face the sitution where the oracle temporary space would be exhausted.<\/p>\n<p>The usage temporary tablespace can&#8217;t be found out exactly using DBA_FREE_SPACE. To find out the true value of temporary table space we may need to use V$TEMP_SPACE_HEADER data dictonary.<\/p>\n<p><code><\/p>\n<pre class=\"brush:sql\">SELECT   tablespace_name, SUM (bytes_used), SUM (bytes_free)\r\n    FROM v$temp_space_header\r\nGROUP BY tablespace_name;<\/pre>\n<p><\/code><\/p>\n<p>If you&#8217;re looking out to find out the usage of other table space we need to use the following script.<\/p>\n<p><code><\/p>\n<pre class=\"brush:sql\">CLEAR\r\nSET HEAD ON\r\nSET VERIFY OFF\r\nSPOOL file\r\nCOL tspace form a25 Heading \"Tablespace\"\r\nCOL tot_ts_size form 99999999999999 Heading \"Size (Mb)\"\r\nCOL free_ts_size form 99999999999999 Heading \"Free (Mb)\"\r\nCOL ts_pct form 9999 Heading \"% Free\"\r\nCOL ts_pct1 form 9999 Heading \"% Used\"\r\nBREAK on report\r\nCOMPUTE sum of free_ts_size on report\r\nCOMPUTE sum of tot_ts_size on report\r\nSELECT                                                            \/* + RULE *\/\r\n         df.tablespace_name tspace, df.BYTES \/ (1024 * 1024) tot_ts_size,\r\n         SUM (fs.BYTES) \/ (1024 * 1024) free_ts_size,\r\n         NVL (ROUND (SUM (fs.BYTES) * 100 \/ df.BYTES), 1) ts_pct,\r\n         ROUND ((df.BYTES - SUM (fs.BYTES)) * 100 \/ df.BYTES) ts_pct1\r\n    FROM dba_free_space fs,\r\n         (SELECT   tablespace_name, SUM (BYTES) BYTES\r\n              FROM dba_data_files\r\n          GROUP BY tablespace_name) df\r\n   WHERE fs.tablespace_name(+) = df.tablespace_name\r\nGROUP BY df.tablespace_name, df.BYTES\r\nUNION ALL\r\nSELECT                                                            \/* + RULE *\/\r\n         df.tablespace_name tspace, fs.BYTES \/ (1024 * 1024) tot_ts_size,\r\n         SUM (df.bytes_free) \/ (1024 * 1024) free_ts_size,\r\n         NVL (ROUND ((SUM (fs.BYTES) - df.bytes_used) * 100 \/ fs.BYTES),\r\n              1\r\n             ) ts_pct,\r\n         ROUND ((SUM (fs.BYTES) - df.bytes_free) * 100 \/ fs.BYTES) ts_pct1\r\n    FROM dba_temp_files fs,\r\n         (SELECT   tablespace_name, bytes_free, bytes_used\r\n              FROM v$temp_space_header\r\n          GROUP BY tablespace_name, bytes_free, bytes_used) df\r\n   WHERE fs.tablespace_name(+) = df.tablespace_name\r\nGROUP BY df.tablespace_name, fs.BYTES, df.bytes_free, df.bytes_used\r\nORDER BY 4 DESC\r\n\/\r\nSPOOL off<\/pre>\n<p><\/code><\/p>\n<p>To find the size of table different technique has to be used.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>If you&#8217;re a freak who work around with Oracle as backend, you would face the sitution where the oracle temporary space would be exhausted. The usage temporary tablespace can&#8217;t be found out exactly using DBA_FREE_SPACE. To find out the true value of temporary table space we may need to use V$TEMP_SPACE_HEADER data dictonary. SELECT tablespace_name, [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","ast-disable-related-posts":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[2],"tags":[63,41,111],"class_list":["post-1","post","type-post","status-publish","format-standard","hentry","category-database","tag-dba","tag-oracle","tag-sql"],"_links":{"self":[{"href":"https:\/\/www.benh.org\/techblog\/wp-json\/wp\/v2\/posts\/1","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.benh.org\/techblog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.benh.org\/techblog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.benh.org\/techblog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.benh.org\/techblog\/wp-json\/wp\/v2\/comments?post=1"}],"version-history":[{"count":5,"href":"https:\/\/www.benh.org\/techblog\/wp-json\/wp\/v2\/posts\/1\/revisions"}],"predecessor-version":[{"id":156,"href":"https:\/\/www.benh.org\/techblog\/wp-json\/wp\/v2\/posts\/1\/revisions\/156"}],"wp:attachment":[{"href":"https:\/\/www.benh.org\/techblog\/wp-json\/wp\/v2\/media?parent=1"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.benh.org\/techblog\/wp-json\/wp\/v2\/categories?post=1"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.benh.org\/techblog\/wp-json\/wp\/v2\/tags?post=1"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}