{"id":6,"date":"2009-05-14T07:54:08","date_gmt":"2009-05-14T14:54:08","guid":{"rendered":"http:\/\/www.vinodsr.com\/myblog\/?p=6"},"modified":"2009-05-14T08:01:47","modified_gmt":"2009-05-14T15:01:47","slug":"how-to-get-the-tablespace-usage-in-oracle","status":"publish","type":"post","link":"https:\/\/vinodsr.com\/myblog\/2009\/05\/how-to-get-the-tablespace-usage-in-oracle\/","title":{"rendered":"How to get the TableSpace usage in Oracle"},"content":{"rendered":"<p>To get the table space usage in oracle use this query<\/p>\n<blockquote><p>select\u00a0\u00a0\u00a0 a.TABLESPACE_NAME,<br \/>\nROUND(a.BYTES\/1024000) &#8220;Used (MB)&#8221;,<br \/>\nROUND(b.BYTES\/1024000) &#8220;Free (MB)&#8221;,<br \/>\nround(((a.BYTES-b.BYTES)\/a.BYTES)*100,2) &#8220;% USED&#8221;<br \/>\nfrom<br \/>\n(<br \/>\nselect\u00a0 TABLESPACE_NAME,<br \/>\nsum(BYTES) BYTES<br \/>\nfrom\u00a0\u00a0\u00a0 dba_data_files<br \/>\ngroup\u00a0\u00a0 by TABLESPACE_NAME<br \/>\n)<br \/>\na,<br \/>\n(<br \/>\nselect\u00a0 TABLESPACE_NAME,<br \/>\nsum(BYTES) BYTES ,<br \/>\nmax(BYTES) largest<br \/>\nfrom\u00a0\u00a0\u00a0 dba_free_space<br \/>\ngroup\u00a0\u00a0 by TABLESPACE_NAME<br \/>\n)<br \/>\nb<br \/>\nwhere\u00a0\u00a0\u00a0 a.TABLESPACE_NAME=b.TABLESPACE_NAME<br \/>\nand a.TABLESPACE_NAME like &#8216;%&#8217;<br \/>\norder\u00a0\u00a0\u00a0\u00a0\u00a0 by ((a.BYTES-b.BYTES)\/a.BYTES) desc ;<\/p><\/blockquote>\n<p>If You want to list a particular table space\u00a0 replace\u00a0<em><strong> a.TABLESPACE_NAME like &#8216;%&#8217; <\/strong><\/em>with<strong> <em>a.TABLESPACE_NAME like &#8216;MY_TABLE_SPACE&#8217;<\/em><\/strong><\/p>\n<p>To get the temporary tablespace usuage use this query<\/p>\n<blockquote><p>SELECT tablespace_name, SUM(bytes_used), SUM(bytes_free)<br \/>\nFROM V$temp_space_header<br \/>\nGROUP BY tablespace_name;<\/p><\/blockquote>\n","protected":false},"excerpt":{"rendered":"<p>Table Space Usage in Oracle<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[45],"class_list":["post-6","post","type-post","status-publish","format-standard","hentry","category-oracle","tag-oracle"],"aioseo_notices":[],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/vinodsr.com\/myblog\/wp-json\/wp\/v2\/posts\/6","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/vinodsr.com\/myblog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/vinodsr.com\/myblog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/vinodsr.com\/myblog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/vinodsr.com\/myblog\/wp-json\/wp\/v2\/comments?post=6"}],"version-history":[{"count":4,"href":"https:\/\/vinodsr.com\/myblog\/wp-json\/wp\/v2\/posts\/6\/revisions"}],"predecessor-version":[{"id":16,"href":"https:\/\/vinodsr.com\/myblog\/wp-json\/wp\/v2\/posts\/6\/revisions\/16"}],"wp:attachment":[{"href":"https:\/\/vinodsr.com\/myblog\/wp-json\/wp\/v2\/media?parent=6"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/vinodsr.com\/myblog\/wp-json\/wp\/v2\/categories?post=6"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/vinodsr.com\/myblog\/wp-json\/wp\/v2\/tags?post=6"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}