{"id":560,"date":"2025-06-12T15:16:31","date_gmt":"2025-06-12T18:16:31","guid":{"rendered":"https:\/\/lemosdba.com.br\/?page_id=560"},"modified":"2025-07-03T21:16:03","modified_gmt":"2025-07-04T00:16:03","slug":"ora_table_space_usage-sql","status":"publish","type":"page","link":"https:\/\/lemosdba.com.br\/en\/ora_table_space_usage-sql\/","title":{"rendered":"ora_table_space_usage.sql"},"content":{"rendered":"<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#d8dee9ff;--cbp-line-number-width:calc(2 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#2e3440ff\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewbox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#d8dee9ff;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>set verify off\nset serveroutput on size 1000000\n\ndeclare\n  unformatted_blocks number;\n  unformatted_bytes number;\n  fs1_blocks number;\n  fs1_bytes number;\n  fs2_blocks number;\n  fs2_bytes number;\n  fs3_blocks number;\n  fs3_bytes number;\n  fs4_blocks number;\n  fs4_bytes number;\n  full_blocks number;\n  full_bytes number;\nbegin\n  dbms_space.space_usage(upper('&amp;1'),upper('&amp;2'),upper('&amp;3'),unformatted_blocks,unformatted_bytes,fs1_blocks,fs1_bytes,fs2_blocks,fs2_bytes,fs3_blocks,fs3_bytes,fs4_blocks,fs4_bytes,full_blocks,full_bytes);\n  dbms_output.put_line('Number of blocks that are unformatted: '||unformatted_blocks);\n  dbms_output.put_line('Number of blocks having at least 0 to 25% free space: '||fs1_blocks);\n  dbms_output.put_line('Number of blocks having at least 25 to 50% free space: '||fs2_blocks);\n  dbms_output.put_line('Number of blocks having at least 50 to 75% free space: '||fs3_blocks);\n  dbms_output.put_line('Number of blocks having at least 75 to 100% free space: '||fs4_blocks);\n  dbms_output.put_line('Number of full blocks: '||full_blocks);\n  dbms_output.put_line('Total number of bytes full in the segment: '||full_bytes);\nend;\n\/\nundefine 1\nundefine 2\nundefine 3<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewbox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki nord\" style=\"background-color: #2e3440ff\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #81A1C1\">set<\/span><span style=\"color: #D8DEE9FF\"> verify <\/span><span style=\"color: #81A1C1\">off<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">set<\/span><span style=\"color: #D8DEE9FF\"> serveroutput <\/span><span style=\"color: #81A1C1\">on<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">size<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #B48EAD\">1000000<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">declare<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">  unformatted_blocks <\/span><span style=\"color: #81A1C1\">number<\/span><span style=\"color: #D8DEE9FF\">;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">  unformatted_bytes <\/span><span style=\"color: #81A1C1\">number<\/span><span style=\"color: #D8DEE9FF\">;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">  fs1_blocks <\/span><span style=\"color: #81A1C1\">number<\/span><span style=\"color: #D8DEE9FF\">;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">  fs1_bytes <\/span><span style=\"color: #81A1C1\">number<\/span><span style=\"color: #D8DEE9FF\">;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">  fs2_blocks <\/span><span style=\"color: #81A1C1\">number<\/span><span style=\"color: #D8DEE9FF\">;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">  fs2_bytes <\/span><span style=\"color: #81A1C1\">number<\/span><span style=\"color: #D8DEE9FF\">;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">  fs3_blocks <\/span><span style=\"color: #81A1C1\">number<\/span><span style=\"color: #D8DEE9FF\">;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">  fs3_bytes <\/span><span style=\"color: #81A1C1\">number<\/span><span style=\"color: #D8DEE9FF\">;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">  fs4_blocks <\/span><span style=\"color: #81A1C1\">number<\/span><span style=\"color: #D8DEE9FF\">;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">  fs4_bytes <\/span><span style=\"color: #81A1C1\">number<\/span><span style=\"color: #D8DEE9FF\">;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">  full_blocks <\/span><span style=\"color: #81A1C1\">number<\/span><span style=\"color: #D8DEE9FF\">;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">  full_bytes <\/span><span style=\"color: #81A1C1\">number<\/span><span style=\"color: #D8DEE9FF\">;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">begin<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">  dbms_space.space_usage(<\/span><span style=\"color: #88C0D0\">upper<\/span><span style=\"color: #D8DEE9FF\">(<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">&amp;1<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #D8DEE9FF\">),<\/span><span style=\"color: #88C0D0\">upper<\/span><span style=\"color: #D8DEE9FF\">(<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">&amp;2<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #D8DEE9FF\">),<\/span><span style=\"color: #88C0D0\">upper<\/span><span style=\"color: #D8DEE9FF\">(<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">&amp;3<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #D8DEE9FF\">),unformatted_blocks,unformatted_bytes,fs1_blocks,fs1_bytes,fs2_blocks,fs2_bytes,fs3_blocks,fs3_bytes,fs4_blocks,fs4_bytes,full_blocks,full_bytes);<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">  dbms_output.put_line(<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">Number of blocks that are unformatted: <\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #81A1C1\">||<\/span><span style=\"color: #D8DEE9FF\">unformatted_blocks);<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">  dbms_output.put_line(<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">Number of blocks having at least 0 to 25% free space: <\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #81A1C1\">||<\/span><span style=\"color: #D8DEE9FF\">fs1_blocks);<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">  dbms_output.put_line(<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">Number of blocks having at least 25 to 50% free space: <\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #81A1C1\">||<\/span><span style=\"color: #D8DEE9FF\">fs2_blocks);<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">  dbms_output.put_line(<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">Number of blocks having at least 50 to 75% free space: <\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #81A1C1\">||<\/span><span style=\"color: #D8DEE9FF\">fs3_blocks);<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">  dbms_output.put_line(<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">Number of blocks having at least 75 to 100% free space: <\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #81A1C1\">||<\/span><span style=\"color: #D8DEE9FF\">fs4_blocks);<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">  dbms_output.put_line(<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">Number of full blocks: <\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #81A1C1\">||<\/span><span style=\"color: #D8DEE9FF\">full_blocks);<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">  dbms_output.put_line(<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">Total number of bytes full in the segment: <\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #81A1C1\">||<\/span><span style=\"color: #D8DEE9FF\">full_bytes);<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">end<\/span><span style=\"color: #D8DEE9FF\">;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">\/<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">undefine <\/span><span style=\"color: #B48EAD\">1<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">undefine <\/span><span style=\"color: #B48EAD\">2<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">undefine <\/span><span style=\"color: #B48EAD\">3<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p><\/p>","protected":false},"excerpt":{"rendered":"","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","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":"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":"","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-4)","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-4)","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-4)","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":""},"class_list":["post-560","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/lemosdba.com.br\/en\/wp-json\/wp\/v2\/pages\/560","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/lemosdba.com.br\/en\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/lemosdba.com.br\/en\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/lemosdba.com.br\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/lemosdba.com.br\/en\/wp-json\/wp\/v2\/comments?post=560"}],"version-history":[{"count":2,"href":"https:\/\/lemosdba.com.br\/en\/wp-json\/wp\/v2\/pages\/560\/revisions"}],"predecessor-version":[{"id":706,"href":"https:\/\/lemosdba.com.br\/en\/wp-json\/wp\/v2\/pages\/560\/revisions\/706"}],"wp:attachment":[{"href":"https:\/\/lemosdba.com.br\/en\/wp-json\/wp\/v2\/media?parent=560"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}