{"id":251,"date":"2013-11-11T23:44:30","date_gmt":"2013-11-11T23:44:30","guid":{"rendered":"http:\/\/tomas.papp.me.uk\/?p=251"},"modified":"2013-11-11T23:44:30","modified_gmt":"2013-11-11T23:44:30","slug":"jop-toto-je-poriadny-tablespace-script","status":"publish","type":"post","link":"https:\/\/tomas.papp.me.uk\/?p=251","title":{"rendered":"jop, toto je poriadny tablespace script.."},"content":{"rendered":"<pre>column dummy noprint\r\ncolumn pct_used format 999.9 heading \"%|Used\"\r\ncolumn name format a19 heading \"Tablespace Name\"\r\ncolumn Kbytes format 999,999,999 heading \"KBytes\"\r\ncolumn used format 999,999,999 heading \"Used\"\r\ncolumn free format 999,999,999 heading \"Free\"\r\ncolumn largest format 999,999,999 heading \"Largest\"\r\ncolumn max_size format 999,999,999 heading \"MaxPoss|Kbytes\"\r\ncolumn pct_max_used format 999.9 heading \"%|Max|Used\"\r\nbreak on report\r\ncompute sum of kbytes on report\r\ncompute sum of free on report\r\ncompute sum of used on report\r\n \r\nselect (select decode(extent_management,'LOCAL','*',' ')\r\n from dba_tablespaces where tablespace_name = b.tablespace_name) ||\r\nnvl(b.tablespace_name,\r\n nvl(a.tablespace_name,'UNKOWN')) name,\r\n kbytes_alloc kbytes,\r\n kbytes_alloc-nvl(kbytes_free,0) used,\r\n nvl(kbytes_free,0) free,\r\n ((kbytes_alloc-nvl(kbytes_free,0))\/\r\n kbytes_alloc)*100 pct_used,\r\n nvl(largest,0) largest,\r\n nvl(kbytes_max,kbytes_alloc) Max_Size,\r\n decode( kbytes_max, 0, 0, (kbytes_alloc\/kbytes_max)*100) pct_max_used\r\nfrom ( select sum(bytes)\/1024 Kbytes_free,\r\n max(bytes)\/1024 largest,\r\n tablespace_name\r\n from sys.dba_free_space\r\n group by tablespace_name ) a,\r\n ( select sum(bytes)\/1024 Kbytes_alloc,\r\n sum(maxbytes)\/1024 Kbytes_max,\r\n tablespace_name\r\n from sys.dba_data_files\r\n group by tablespace_name\r\n union all\r\n select sum(bytes)\/1024 Kbytes_alloc,\r\n sum(maxbytes)\/1024 Kbytes_max,\r\n tablespace_name\r\n from sys.dba_temp_files\r\n group by tablespace_name )b\r\nwhere a.tablespace_name (+) = b.tablespace_name\r\norder by 1\r\n\/\r\n\r\n\r\n\r\n\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>column dummy noprint column pct_used format 999.9 heading &#8220;%|Used&#8221; column name format a19 heading &#8220;Tablespace Name&#8221; column Kbytes format 999,999,999 heading &#8220;KBytes&#8221; column used format 999,999,999 heading &#8220;Used&#8221; column free format 999,999,999 heading &#8220;Free&#8221; column largest format 999,999,999 heading &#8220;Largest&#8221; column max_size format 999,999,999 heading &#8220;MaxPoss|Kbytes&#8221; column pct_max_used format 999.9 heading &#8220;%|Max|Used&#8221; break on report [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-251","post","type-post","status-publish","format-standard","hentry","category-oracle"],"_links":{"self":[{"href":"https:\/\/tomas.papp.me.uk\/index.php?rest_route=\/wp\/v2\/posts\/251","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/tomas.papp.me.uk\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/tomas.papp.me.uk\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/tomas.papp.me.uk\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/tomas.papp.me.uk\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=251"}],"version-history":[{"count":1,"href":"https:\/\/tomas.papp.me.uk\/index.php?rest_route=\/wp\/v2\/posts\/251\/revisions"}],"predecessor-version":[{"id":252,"href":"https:\/\/tomas.papp.me.uk\/index.php?rest_route=\/wp\/v2\/posts\/251\/revisions\/252"}],"wp:attachment":[{"href":"https:\/\/tomas.papp.me.uk\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=251"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/tomas.papp.me.uk\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=251"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/tomas.papp.me.uk\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=251"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}