{"id":11516,"date":"2018-08-07T19:06:07","date_gmt":"2018-08-07T17:06:07","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/how-much-free-space-can-be-reclaimed-from-a-segment\/"},"modified":"2018-08-07T19:06:07","modified_gmt":"2018-08-07T17:06:07","slug":"how-much-free-space-can-be-reclaimed-from-a-segment","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/how-much-free-space-can-be-reclaimed-from-a-segment\/","title":{"rendered":"How much free space can be reclaimed from a segment?"},"content":{"rendered":"

By Franck Pachot<\/h2>\n

.
\nYou have the feeling that your table takes more blocks than it should? Here are the queries I use to quickly check the free space. The idea is to call DBMS_SPACE.SPACE_USAGE and infer the minimum space from the percentages. For example, a block in FS3 (defined as having at least 50 to 75% free space) is supposed to have at least 50% of free space. Of course it can have more, but you don’t know.<\/p>\n

Here is some PL\/SQL to do so:<\/p>\n

\nset serveroutput on\ndeclare\n unf number; unfb number; fs1 number; fs1b number; fs2 number; fs2b number; fs3 number; fs3b number; fs4 number; fs4b number; full number; fullb number; \nbegin\n for i in (select * from (select * from dba_segments where segment_subtype='ASSM' and segment_type in (\n  'TABLE','TABLE PARTITION','TABLE SUBPARTITION','CLUSTER','LOB','LOB PARTITION','LOB SUBPARTITION'\n ) order by bytes desc) where 10>=rownum)\n loop\n  begin\n   dbms_space.space_usage(i.owner,i.segment_name,i.segment_type,unf,unfb,fs1,fs1b,fs2,fs2b,fs3,fs3b,fs4,fs4b,full,fullb,partition_name=>i.partition_name);\n   dbms_output.put_line(to_char((unfb+fs1b+fs2b*0.25+fs3b*0.5+fs4b*0.75)\/1024\/1024\/1024,'999G999D999')||' GB free in '||i.segment_type||' \"'||i.owner||'\".\"'||i.segment_name||'\" partition \"'||i.partition_name||'\"');\n  exception\n   when others then dbms_output.put_line(i.segment_type||' \"'||i.owner||'\".\"'||i.segment_name||'\" partition \"'||i.partition_name||'\": '||sqlerrm);\n  end; \n end loop;\nend;\n\/\n<\/code><\/pre>\n
The output looks like:<\/p>\n
\n        .001 GB free in INDEX \"DEMO\".\"ACCOUNT_PK\" partition \"\"\n        .001 GB free in TABLE \"APEX_040200\".\"WWV_FLOW_PAGE_PLUGS\" partition \"\"\n        .009 GB free in TABLE \"SCOTT\".\"DEMO\" partition \"\"\n        .000 GB free in TABLE \"APEX_040200\".\"WWV_FLOW_STEP_ITEMS\" partition \"\"\n        .003 GB free in INDEX \"SYS\".\"WRH$_SYSMETRIC_HISTORY_INDEX\" partition \"\"\n        .000 GB free in TABLE \"MDSYS\".\"SDO_CS_SRS\" partition \"\"\n        .002 GB free in INDEX \"SYS\".\"I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST\" partition \"\"\n        .006 GB free in TABLE \"SYS\".\"WRH$_SYSMETRIC_HISTORY\" partition \"\"\n        .002 GB free in TABLE \"SYS\".\"WRH$_SQL_PLAN\" partition \"\"\n<\/code><\/pre>\n
If you are in 12c, an inline function in the query might come handy:<\/p>\n
\nwith function freebytes(segment_owner varchar2, segment_name varchar2, segment_type varchar2,partition_name varchar2) return number as\n unf number; unfb number; fs1 number; fs1b number; fs2 number; fs2b number; fs3 number; fs3b number; fs4 number; fs4b number; full number; fullb number; \nbegin\n dbms_space.space_usage(segment_owner,segment_name,segment_type,unf,unfb,fs1,fs1b,fs2,fs2b,fs3,fs3b,fs4,fs4b,full,fullb,partition_name=>partition_name);\n return unfb+fs1b+fs2b*0.25+fs3b*0.5+fs4b*0.75;\nend;\nselect round(freebytes(owner,segment_name,segment_type,partition_name)\/1024\/1024\/1024,3) free_GB,segment_type,owner,segment_name,partition_name\nfrom dba_segments  where segment_subtype='ASSM' and segment_type in (\n  'TABLE','TABLE PARTITION','TABLE SUBPARTITION','CLUSTER','LOB','LOB PARTITION','LOB SUBPARTITION'\n) order by bytes desc fetch first 10 rows only\n\/\n<\/code><\/pre>\n
The result looks like:<\/p>\n
\nFREE_GB SEGMENT_TYPE   OWNER         SEGMENT_NAME                     PARTITION_NAME\n------- ------------   -----         ------------                     --------------\n      0 TABLE          DEMO          ACCOUNTS\n  0.001 INDEX          DEMO          ACCOUNT_PK\n  0.001 TABLE          APEX_040200   WWV_FLOW_PAGE_PLUGS\n  0.009 TABLE          SCOTT         DEMO\n  0.003 INDEX          SYS           WRH$_SYSMETRIC_HISTORY_INDEX\n      0 TABLE          APEX_040200   WWV_FLOW_STEP_ITEMS\n  0.002 INDEX          SYS           I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST\n      0 TABLE          MDSYS         SDO_CS_SRS\n  0.006 TABLE          SYS           WRH$_SYSMETRIC_HISTORY\n  0.002 TABLE          SYS           WRH$_SQL_PLAN\n<\/code><\/pre>\n
Future evolution will be published on GitHub:
\nhttps:\/\/raw.githubusercontent.com\/FranckPachot\/scripts\/master\/administration\/segment_free_space_plsql.sql<\/a>
\nhttps:\/\/raw.githubusercontent.com\/FranckPachot\/scripts\/master\/administration\/segment_free_space_sql.sql<\/a><\/p>\n
Note that having free space does not mean that you have to shrink or reorg. Try to understand what happened to your data before, and whether this space will be reused soon.<\/p>\n
Update 8-AUG-2018<\/h3>\n
In the initial post I added all segment types accepted by the dbms_space documentation but finally removed ‘INDEX’,’INDEX PARTITION’,’INDEX SUBPARTITION’ because the meaning of the output is completely different. See Jonathan Lewis note about it: https:\/\/jonathanlewis.wordpress.com\/2013\/12\/17\/dbms_space_usage\/<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
By Franck Pachot . You have the feeling that your table takes more blocks than it should? Here are the queries I use to quickly check the free space. The idea is to call DBMS_SPACE.SPACE_USAGE and infer the minimum space from the percentages. For example, a block in FS3 (defined as having at least 50 […]<\/p>\n","protected":false},"author":27,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[59],"tags":[96,1419,1420,1421],"type_dbi":[],"class_list":["post-11516","post","type-post","status-publish","format-standard","hentry","category-oracle","tag-oracle","tag-reorg","tag-segment","tag-shrink"],"acf":[],"yoast_head":"\nHow much free space can be reclaimed from a segment? - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/how-much-free-space-can-be-reclaimed-from-a-segment\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How much free space can be reclaimed from a segment?\" \/>\n<meta property=\"og:description\" content=\"By Franck Pachot . You have the feeling that your table takes more blocks than it should? Here are the queries I use to quickly check the free space. The idea is to call DBMS_SPACE.SPACE_USAGE and infer the minimum space from the percentages. For example, a block in FS3 (defined as having at least 50 […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/how-much-free-space-can-be-reclaimed-from-a-segment\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2018-08-07T17:06:07+00:00\" \/>\n<meta name=\"author\" content=\"Oracle Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Oracle Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/how-much-free-space-can-be-reclaimed-from-a-segment\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/how-much-free-space-can-be-reclaimed-from-a-segment\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"How much free space can be reclaimed from a segment?\",\"datePublished\":\"2018-08-07T17:06:07+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/how-much-free-space-can-be-reclaimed-from-a-segment\/\"},\"wordCount\":231,\"commentCount\":0,\"keywords\":[\"Oracle\",\"reorg\",\"segment\",\"shrink\"],\"articleSection\":[\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/how-much-free-space-can-be-reclaimed-from-a-segment\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/how-much-free-space-can-be-reclaimed-from-a-segment\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/how-much-free-space-can-be-reclaimed-from-a-segment\/\",\"name\":\"How much free space can be reclaimed from a segment? - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2018-08-07T17:06:07+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/how-much-free-space-can-be-reclaimed-from-a-segment\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/how-much-free-space-can-be-reclaimed-from-a-segment\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/how-much-free-space-can-be-reclaimed-from-a-segment\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How much free space can be reclaimed from a segment?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\",\"name\":\"Oracle Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"caption\":\"Oracle Team\"},\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"How much free space can be reclaimed from a segment? - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/how-much-free-space-can-be-reclaimed-from-a-segment\/","og_locale":"en_US","og_type":"article","og_title":"How much free space can be reclaimed from a segment?","og_description":"By Franck Pachot . You have the feeling that your table takes more blocks than it should? Here are the queries I use to quickly check the free space. The idea is to call DBMS_SPACE.SPACE_USAGE and infer the minimum space from the percentages. For example, a block in FS3 (defined as having at least 50 […]","og_url":"https:\/\/www.dbi-services.com\/blog\/how-much-free-space-can-be-reclaimed-from-a-segment\/","og_site_name":"dbi Blog","article_published_time":"2018-08-07T17:06:07+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/how-much-free-space-can-be-reclaimed-from-a-segment\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/how-much-free-space-can-be-reclaimed-from-a-segment\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"How much free space can be reclaimed from a segment?","datePublished":"2018-08-07T17:06:07+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/how-much-free-space-can-be-reclaimed-from-a-segment\/"},"wordCount":231,"commentCount":0,"keywords":["Oracle","reorg","segment","shrink"],"articleSection":["Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/how-much-free-space-can-be-reclaimed-from-a-segment\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/how-much-free-space-can-be-reclaimed-from-a-segment\/","url":"https:\/\/www.dbi-services.com\/blog\/how-much-free-space-can-be-reclaimed-from-a-segment\/","name":"How much free space can be reclaimed from a segment? - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2018-08-07T17:06:07+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/how-much-free-space-can-be-reclaimed-from-a-segment\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/how-much-free-space-can-be-reclaimed-from-a-segment\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/how-much-free-space-can-be-reclaimed-from-a-segment\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"How much free space can be reclaimed from a segment?"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee","name":"Oracle Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","caption":"Oracle Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/11516","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/27"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=11516"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/11516\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=11516"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=11516"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=11516"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=11516"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}