{"id":491,"date":"2025-06-12T13:45:18","date_gmt":"2025-06-12T16:45:18","guid":{"rendered":"https:\/\/lemosdba.com.br\/?page_id=491"},"modified":"2025-07-03T21:10:02","modified_gmt":"2025-07-04T00:10:02","slug":"ora_datafile_free_info-sql","status":"publish","type":"page","link":"https:\/\/lemosdba.com.br\/en\/ora_datafile_free_info-sql\/","title":{"rendered":"ora_datafile_free_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 300\ncol file_name for a120\nselect dfs.tablespace_name\n      ,dfs.file_id\n      ,dfs.relative_fno      \n      ,ddf.file_name\n      ,sum(dfs.bytes) as \"FREE_BYTES\"\n      ,sum(dfs.bytes\/1024\/1024) as \"FREE_MB\"\n      ,sum(dfs.blocks) \"BLOCKS_QTY\"\n      ,dts.block_size\nfrom dba_free_space dfs\n    ,dba_data_files ddf\n    ,dba_tablespaces dts\nwhere dfs.tablespace_name = ddf.tablespace_name\n  and dfs.file_id = ddf.file_id\n  and dfs.relative_fno = ddf.relative_fno\n  and dfs.tablespace_name = upper('&amp;1')\n  and dts.tablespace_name = ddf.tablespace_name\ngroup by dfs.tablespace_name\n        ,dfs.file_id\n        ,dfs.relative_fno\n        ,ddf.file_name\n        ,dts.block_size\norder by dfs.tablespace_name\n        ,dfs.file_id;\nundefine 1<\/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\">300<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">col file_name <\/span><span style=\"color: #81A1C1\">for<\/span><span style=\"color: #D8DEE9FF\"> a120<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">select<\/span><span style=\"color: #D8DEE9FF\"> dfs.tablespace_name<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">      ,dfs.file_id<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">      ,dfs.relative_fno      <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">      ,ddf.file_name<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">      ,<\/span><span style=\"color: #88C0D0\">sum<\/span><span style=\"color: #D8DEE9FF\">(dfs.bytes) <\/span><span style=\"color: #81A1C1\">as<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #ECEFF4\">&quot;<\/span><span style=\"color: #A3BE8C\">FREE_BYTES<\/span><span style=\"color: #ECEFF4\">&quot;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">      ,<\/span><span style=\"color: #88C0D0\">sum<\/span><span style=\"color: #D8DEE9FF\">(dfs.bytes<\/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\">as<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #ECEFF4\">&quot;<\/span><span style=\"color: #A3BE8C\">FREE_MB<\/span><span style=\"color: #ECEFF4\">&quot;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">      ,<\/span><span style=\"color: #88C0D0\">sum<\/span><span style=\"color: #D8DEE9FF\">(dfs.blocks) <\/span><span style=\"color: #ECEFF4\">&quot;<\/span><span style=\"color: #A3BE8C\">BLOCKS_QTY<\/span><span style=\"color: #ECEFF4\">&quot;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">      ,dts.block_size<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">from<\/span><span style=\"color: #D8DEE9FF\"> dba_free_space dfs<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    ,dba_data_files ddf<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    ,dba_tablespaces dts<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">where<\/span><span style=\"color: #D8DEE9FF\"> dfs.tablespace_name <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> ddf.tablespace_name<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">  <\/span><span style=\"color: #81A1C1\">and<\/span><span style=\"color: #D8DEE9FF\"> dfs.file_id <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> ddf.file_id<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">  <\/span><span style=\"color: #81A1C1\">and<\/span><span style=\"color: #D8DEE9FF\"> dfs.relative_fno <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> ddf.relative_fno<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">  <\/span><span style=\"color: #81A1C1\">and<\/span><span style=\"color: #D8DEE9FF\"> dfs.tablespace_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;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\"> dts.tablespace_name <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> ddf.tablespace_name<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">group by<\/span><span style=\"color: #D8DEE9FF\"> dfs.tablespace_name<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        ,dfs.file_id<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        ,dfs.relative_fno<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        ,ddf.file_name<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        ,dts.block_size<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">order by<\/span><span style=\"color: #D8DEE9FF\"> dfs.tablespace_name<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        ,dfs.file_id;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">undefine <\/span><span style=\"color: #B48EAD\">1<\/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-491","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/lemosdba.com.br\/en\/wp-json\/wp\/v2\/pages\/491","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=491"}],"version-history":[{"count":4,"href":"https:\/\/lemosdba.com.br\/en\/wp-json\/wp\/v2\/pages\/491\/revisions"}],"predecessor-version":[{"id":703,"href":"https:\/\/lemosdba.com.br\/en\/wp-json\/wp\/v2\/pages\/491\/revisions\/703"}],"wp:attachment":[{"href":"https:\/\/lemosdba.com.br\/en\/wp-json\/wp\/v2\/media?parent=491"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}