{"id":556,"date":"2025-06-12T14:45:11","date_gmt":"2025-06-12T17:45:11","guid":{"rendered":"https:\/\/lemosdba.com.br\/?page_id=556"},"modified":"2025-07-03T21:10:40","modified_gmt":"2025-07-04T00:10:40","slug":"ora_table_info-sql","status":"publish","type":"page","link":"https:\/\/lemosdba.com.br\/en\/ora_table_info-sql\/","title":{"rendered":"ora_table_info.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 lines 400\ncol owner for a20\ncol table_name for a20\ncol tablespace_name for a15\ncol segment_created for a15\ncol last_analyzed for a13\nselect dtab.owner\n      ,dtab.table_name\n      ,dtab.tablespace_name\n      ,dtab.segment_created\n      ,dtab.last_analyzed\n      ,dtab.initial_extent\n      ,dtab.next_extent\n      ,dtab.min_extents\n      ,dtab.max_extents\n      ,num_rows\n      ,dtab.blocks\n      ,dtab.empty_blocks\n      ,dtab.num_freelist_blocks\n      ,dtab.avg_space\n      ,dtab.pct_free\n      ,round((dtab.blocks*dtbs.block_size\/1024\/1024),2) \"TOTAL_BLOCKS_SIZE_MB\"\n      ,round((dtab.num_rows*dtab.avg_row_len\/1024\/1024),2) \"TOTAL_ROWS_SIZE_MB\"\n      ,round(((dtab.blocks*dtbs.block_size\/1024\/1024)-(dtab.num_rows*dtab.avg_row_len\/1024\/1024)),2) \"AVG_FREE_SPACE_MB\"\n      ,(round(((dtab.blocks*dtbs.block_size\/1024\/1024)-(dtab.num_rows*dtab.avg_row_len\/1024\/1024)),2)\/nullif(round(((dtab.blocks*dtbs.block_size\/1024\/1024)),2),0))*100 \"AVG_FREE_SPACE_PCT\"\nfrom dba_tables dtab\n    ,dba_tablespaces dtbs\nwhere dtab.tablespace_name = dtbs.tablespace_name\n  and dtab.owner = upper('&amp;1')\n  and dtab.table_name = upper('&amp;2');\nundefine 1\nundefine 2<\/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\"> lines <\/span><span style=\"color: #B48EAD\">400<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">col <\/span><span style=\"color: #81A1C1\">owner<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">for<\/span><span style=\"color: #D8DEE9FF\"> a20<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">col table_name <\/span><span style=\"color: #81A1C1\">for<\/span><span style=\"color: #D8DEE9FF\"> a20<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">col tablespace_name <\/span><span style=\"color: #81A1C1\">for<\/span><span style=\"color: #D8DEE9FF\"> a15<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">col segment_created <\/span><span style=\"color: #81A1C1\">for<\/span><span style=\"color: #D8DEE9FF\"> a15<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">col last_analyzed <\/span><span style=\"color: #81A1C1\">for<\/span><span style=\"color: #D8DEE9FF\"> a13<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">select<\/span><span style=\"color: #D8DEE9FF\"> dtab.owner<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">      ,dtab.table_name<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">      ,dtab.tablespace_name<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">      ,dtab.segment_created<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">      ,dtab.last_analyzed<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">      ,dtab.initial_extent<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">      ,dtab.next_extent<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">      ,dtab.min_extents<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">      ,dtab.max_extents<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">      ,num_rows<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">      ,dtab.blocks<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">      ,dtab.empty_blocks<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">      ,dtab.num_freelist_blocks<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">      ,dtab.avg_space<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">      ,dtab.pct_free<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">      ,<\/span><span style=\"color: #88C0D0\">round<\/span><span style=\"color: #D8DEE9FF\">((dtab.blocks<\/span><span style=\"color: #81A1C1\">*<\/span><span style=\"color: #D8DEE9FF\">dtbs.block_size<\/span><span style=\"color: #81A1C1\">\/<\/span><span style=\"color: #B48EAD\">1024<\/span><span style=\"color: #81A1C1\">\/<\/span><span style=\"color: #B48EAD\">1024<\/span><span style=\"color: #D8DEE9FF\">),<\/span><span style=\"color: #B48EAD\">2<\/span><span style=\"color: #D8DEE9FF\">) <\/span><span style=\"color: #ECEFF4\">&quot;<\/span><span style=\"color: #A3BE8C\">TOTAL_BLOCKS_SIZE_MB<\/span><span style=\"color: #ECEFF4\">&quot;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">      ,<\/span><span style=\"color: #88C0D0\">round<\/span><span style=\"color: #D8DEE9FF\">((dtab.num_rows<\/span><span style=\"color: #81A1C1\">*<\/span><span style=\"color: #D8DEE9FF\">dtab.avg_row_len<\/span><span style=\"color: #81A1C1\">\/<\/span><span style=\"color: #B48EAD\">1024<\/span><span style=\"color: #81A1C1\">\/<\/span><span style=\"color: #B48EAD\">1024<\/span><span style=\"color: #D8DEE9FF\">),<\/span><span style=\"color: #B48EAD\">2<\/span><span style=\"color: #D8DEE9FF\">) <\/span><span style=\"color: #ECEFF4\">&quot;<\/span><span style=\"color: #A3BE8C\">TOTAL_ROWS_SIZE_MB<\/span><span style=\"color: #ECEFF4\">&quot;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">      ,<\/span><span style=\"color: #88C0D0\">round<\/span><span style=\"color: #D8DEE9FF\">(((dtab.blocks<\/span><span style=\"color: #81A1C1\">*<\/span><span style=\"color: #D8DEE9FF\">dtbs.block_size<\/span><span style=\"color: #81A1C1\">\/<\/span><span style=\"color: #B48EAD\">1024<\/span><span style=\"color: #81A1C1\">\/<\/span><span style=\"color: #B48EAD\">1024<\/span><span style=\"color: #D8DEE9FF\">)<\/span><span style=\"color: #81A1C1\">-<\/span><span style=\"color: #D8DEE9FF\">(dtab.num_rows<\/span><span style=\"color: #81A1C1\">*<\/span><span style=\"color: #D8DEE9FF\">dtab.avg_row_len<\/span><span style=\"color: #81A1C1\">\/<\/span><span style=\"color: #B48EAD\">1024<\/span><span style=\"color: #81A1C1\">\/<\/span><span style=\"color: #B48EAD\">1024<\/span><span style=\"color: #D8DEE9FF\">)),<\/span><span style=\"color: #B48EAD\">2<\/span><span style=\"color: #D8DEE9FF\">) <\/span><span style=\"color: #ECEFF4\">&quot;<\/span><span style=\"color: #A3BE8C\">AVG_FREE_SPACE_MB<\/span><span style=\"color: #ECEFF4\">&quot;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">      ,(<\/span><span style=\"color: #88C0D0\">round<\/span><span style=\"color: #D8DEE9FF\">(((dtab.blocks<\/span><span style=\"color: #81A1C1\">*<\/span><span style=\"color: #D8DEE9FF\">dtbs.block_size<\/span><span style=\"color: #81A1C1\">\/<\/span><span style=\"color: #B48EAD\">1024<\/span><span style=\"color: #81A1C1\">\/<\/span><span style=\"color: #B48EAD\">1024<\/span><span style=\"color: #D8DEE9FF\">)<\/span><span style=\"color: #81A1C1\">-<\/span><span style=\"color: #D8DEE9FF\">(dtab.num_rows<\/span><span style=\"color: #81A1C1\">*<\/span><span style=\"color: #D8DEE9FF\">dtab.avg_row_len<\/span><span style=\"color: #81A1C1\">\/<\/span><span style=\"color: #B48EAD\">1024<\/span><span style=\"color: #81A1C1\">\/<\/span><span style=\"color: #B48EAD\">1024<\/span><span style=\"color: #D8DEE9FF\">)),<\/span><span style=\"color: #B48EAD\">2<\/span><span style=\"color: #D8DEE9FF\">)<\/span><span style=\"color: #81A1C1\">\/<\/span><span style=\"color: #88C0D0\">nullif<\/span><span style=\"color: #D8DEE9FF\">(<\/span><span style=\"color: #88C0D0\">round<\/span><span style=\"color: #D8DEE9FF\">(((dtab.blocks<\/span><span style=\"color: #81A1C1\">*<\/span><span style=\"color: #D8DEE9FF\">dtbs.block_size<\/span><span style=\"color: #81A1C1\">\/<\/span><span style=\"color: #B48EAD\">1024<\/span><span style=\"color: #81A1C1\">\/<\/span><span style=\"color: #B48EAD\">1024<\/span><span style=\"color: #D8DEE9FF\">)),<\/span><span style=\"color: #B48EAD\">2<\/span><span style=\"color: #D8DEE9FF\">),<\/span><span style=\"color: #B48EAD\">0<\/span><span style=\"color: #D8DEE9FF\">))<\/span><span style=\"color: #81A1C1\">*<\/span><span style=\"color: #B48EAD\">100<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #ECEFF4\">&quot;<\/span><span style=\"color: #A3BE8C\">AVG_FREE_SPACE_PCT<\/span><span style=\"color: #ECEFF4\">&quot;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">from<\/span><span style=\"color: #D8DEE9FF\"> dba_tables dtab<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    ,dba_tablespaces dtbs<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">where<\/span><span style=\"color: #D8DEE9FF\"> dtab.tablespace_name <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> dtbs.tablespace_name<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">  <\/span><span style=\"color: #81A1C1\">and<\/span><span style=\"color: #D8DEE9FF\"> dtab.owner <\/span><span style=\"color: #81A1C1\">=<\/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;1<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #D8DEE9FF\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">  <\/span><span style=\"color: #81A1C1\">and<\/span><span style=\"color: #D8DEE9FF\"> dtab.table_name <\/span><span style=\"color: #81A1C1\">=<\/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>\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><\/code><\/pre><\/div>","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-556","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/lemosdba.com.br\/en\/wp-json\/wp\/v2\/pages\/556","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=556"}],"version-history":[{"count":2,"href":"https:\/\/lemosdba.com.br\/en\/wp-json\/wp\/v2\/pages\/556\/revisions"}],"predecessor-version":[{"id":705,"href":"https:\/\/lemosdba.com.br\/en\/wp-json\/wp\/v2\/pages\/556\/revisions\/705"}],"wp:attachment":[{"href":"https:\/\/lemosdba.com.br\/en\/wp-json\/wp\/v2\/media?parent=556"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}