{"id":4905,"date":"2015-06-12T08:27:49","date_gmt":"2015-06-12T06:27:49","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/an-alternative-to-dba_extents-optimized-for-lmt\/"},"modified":"2015-06-12T08:27:49","modified_gmt":"2015-06-12T06:27:49","slug":"an-alternative-to-dba_extents-optimized-for-lmt","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/an-alternative-to-dba_extents-optimized-for-lmt\/","title":{"rendered":"An alternative to DBA_EXTENTS optimized for LMT"},"content":{"rendered":"<h2>By Franck Pachot<\/h2>\n<p>.<br \/>\nThis is a script I have for several years, when tablespaces became locally managed. When we want to know to which segment a block (identified by file id, block id) belongs to, the DBA_EXTENTS view can be very long when you have lot of datafiles and lot of segments. This view using the underlying X$ tables and constrained by hints is faster when queried for one FILE_ID\/BLOCK_ID. I did that in 2006 when having lot of corruptions on several 10TB databases with 5000 datafiles.<\/p>\n<p>Since then, I&#8217;ve used it only a few times, so there is no guarantee that the plan is still optimal in current version, but the approach of starting to filter the segments that are in the same tablespace as the file_id makes it optimal for a search by file_id and block_id.<\/p>\n<h3>The script<\/h3>\n<p>Here is the creation of the DATAFILE_MAP view:<\/p>\n<pre><code>create or replace view datafile_map as\nWITH\n l AS ( \/* LMT extents indexed on ktfbuesegtsn,ktfbuesegfno,ktfbuesegbno *\/\n  SELECT ktfbuesegtsn segtsn,ktfbuesegfno segrfn,ktfbuesegbno segbid, ktfbuefno extrfn, \n         ktfbuebno fstbid,ktfbuebno + ktfbueblks - 1 lstbid,ktfbueblks extblks,ktfbueextno extno \n  FROM sys.x$ktfbue\n ),\n d AS ( \/* DMT extents ts#, segfile#, segblock# *\/\n  SELECT ts# segtsn,segfile# segrfn,segblock# segbid, file# extrfn, \n         block# fstbid,block# + length - 1 lstbid,length extblks, ext# extno \n  FROM sys.uet$\n ),\n s AS ( \/* segment information for the tablespace that contains afn file *\/\n  SELECT \/*+ materialized *\/\n  f1.fenum afn,f1.ferfn rfn,s.ts# segtsn,s.FILE# segrfn,s.BLOCK# segbid ,s.TYPE# segtype,f2.fenum segafn,t.name tsname,blocksize\n  FROM sys.seg$ s, sys.ts$ t, sys.x$kccfe f1,sys.x$kccfe f2  \n  WHERE s.ts#=t.ts# AND t.ts#=f1.fetsn AND s.FILE#=f2.ferfn AND s.ts#=f2.fetsn \n ),\n m AS ( \/* extent mapping for the tablespace that contains afn file *\/\nSELECT \/*+ use_nl(e) ordered *\/ \n s.afn,s.segtsn,s.segrfn,s.segbid,extrfn,fstbid,lstbid,extblks,extno, segtype,s.rfn, tsname,blocksize\n FROM s,l e\n WHERE e.segtsn=s.segtsn AND e.segrfn=s.segrfn AND e.segbid=s.segbid\n UNION ALL\n SELECT \/*+ use_nl(e) ordered *\/  \n s.afn,s.segtsn,s.segrfn,s.segbid,extrfn,fstbid,lstbid,extblks,extno, segtype,s.rfn, tsname,blocksize\n FROM s,d e\n  WHERE e.segtsn=s.segtsn AND e.segrfn=s.segrfn AND e.segbid=s.segbid\n UNION ALL\n SELECT \/*+ use_nl(e) use_nl(t) ordered *\/ \n f.fenum afn,null segtsn,null segrfn,null segbid,f.ferfn extrfn,e.ktfbfebno fstbid,e.ktfbfebno+e.ktfbfeblks-1 lstbid,e.ktfbfeblks extblks,null extno, null segtype,f.ferfn rfn,name tsname,blocksize\n FROM sys.x$kccfe f,sys.x$ktfbfe e,sys.ts$ t\n WHERE t.ts#=f.fetsn and e.ktfbfetsn=f.fetsn and e.ktfbfefno=f.ferfn\n UNION ALL\n SELECT \/*+ use_nl(e) use_nl(t) ordered *\/ \n f.fenum afn,null segtsn,null segrfn,null segbid,f.ferfn extrfn,e.block# fstbid,e.block#+e.length-1 lstbid,e.length extblks,null extno, null segtype,f.ferfn rfn,name tsname,blocksize\n FROM sys.x$kccfe f,sys.fet$ e,sys.ts$ t\n WHERE t.ts#=f.fetsn and e.ts#=f.fetsn and e.file#=f.ferfn\n ),\n o AS (\n  SELECT s.tablespace_id segtsn,s.relative_fno segrfn,s.header_block   segbid,s.segment_type,s.owner,s.segment_name,s.partition_name \n  FROM SYS_DBA_SEGS s \n )\nSELECT \n afn file_id,fstbid block_id,extblks blocks,nvl(segment_type,decode(segtype,null,'free space','type='||segtype)) segment_type,\n owner,segment_name,partition_name,extno extent_id,extblks*blocksize bytes,\n tsname tablespace_name,rfn relative_fno,m.segtsn,m.segrfn,m.segbid\n FROM m,o WHERE extrfn=rfn and m.segtsn=o.segtsn(+) AND m.segrfn=o.segrfn(+) AND m.segbid=o.segbid(+)\nUNION ALL\nSELECT \n file_id+(select to_number(value) from v$parameter WHERE name='db_files') file_id,\n 1 block_id,blocks,'tempfile' segment_type,\n '' owner,file_name segment_name,'' partition_name,0 extent_id,bytes,\n  tablespace_name,relative_fno,0 segtsn,0 segrfn,0 segbid\n FROM dba_temp_files\n;\n<\/code><\/pre>\n<h3>Sample output<\/h3>\n<pre><code>COLUMN   partition_name ON FORMAT   A16\nCOLUMN   segment_name ON FORMAT   A20\nCOLUMN   owner ON FORMAT   A16\nCOLUMN   segment_type ON FORMAT   A16\n\nselect file_id,block_id,blocks,segment_type,owner,segment_name,partition_name from datafile_map \nwhere file_id=1326 and 3782 between block_id and block_id + blocks - 1\nSQL&gt; \/\n\n FILE_ID BLOCK_ID  BLOCKS SEGMENT_TYPE     OWNER            SEGMENT_NAME     PARTITION_NAME\n-------- -------- ------- ---------------- ---------------- ---------------- ----------------\n    1326     3781      32 free space\n\n<\/code><\/pre>\n<p>you identified free space block<\/p>\n<pre><code>select file_id,block_id,blocks,segment_type,owner,segment_name,partition_name from datafile_map \nwhere file_id=1326 and 3982 between block_id and block_id + blocks - 1\nSQL&gt; \/\n\n\n FILE_ID BLOCK_ID  BLOCKS SEGMENT_TYPE     OWNER            SEGMENT_NAME         PARTITION_NAME\n-------- -------- ------- ---------------- ---------------- -------------------- ----------------\n    1326     3981       8 TABLE PARTITION  TESTUSER         AGGR_FACT_DATA       AFL_P_211\n\n<\/code><\/pre>\n<p>you identified a data block<\/p>\n<pre><code>select file_id,block_id,blocks,segment_type,owner,segment_name,partition_name from datafile_map \nwhere file_id=202 and 100 between block_id and block_id + blocks - 1\nSQL&gt; \/\n\n   FILE_ID   BLOCK_ID     BLOCKS SEGMENT_TYPE     OWNER            SEGMENT_NAME         PARTITION_NAME\n---------- ---------- ---------- ---------------- ---------------- -------------------- ---------------\n       202          1       1280 tempfile                          C:O102TEMP02.DBF\n\n<\/code><\/pre>\n<p>you identified a tempfile file_id<\/p>\n<pre><code>select file_id,block_id,blocks,segment_type,owner,segment_name,partition_name from datafile_map \nwhere file_id=1 and block_id between 0 and 100 order by file_id,block_id;\n\n   FILE_ID   BLOCK_ID     BLOCKS SEGMENT_TYPE     OWNER            SEGMENT_NAME         PARTITION_NAME\n---------- ---------- ---------- ---------------- ---------------- -------------------- ---------------\n         1          9          8 ROLLBACK         SYS              SYSTEM\n         1         17          8 ROLLBACK         SYS              SYSTEM\n         1         25          8 CLUSTER          SYS              C_OBJ#\n         1         33          8 CLUSTER          SYS              C_OBJ#\n         1         41          8 CLUSTER          SYS              C_OBJ#\n         1         49          8 INDEX            SYS              I_OBJ#\n         1         57          8 CLUSTER          SYS              C_TS#\n         1         65          8 INDEX            SYS              I_TS#\n         1         73          8 CLUSTER          SYS              C_FILE#_BLOCK#\n         1         81          8 INDEX            SYS              I_FILE#_BLOCK#\n         1         89          8 CLUSTER          SYS              C_USER#\n         1         97          8 INDEX            SYS              I_USER#\n\n<\/code><\/pre>\n<p>you mapped the first segments in system tablespace<\/p>\n<p>Try it on a database with lot of segments and lot of datafiles, and compare with DBA_EXTENTS. Then you will know which one to choose in case of emergency.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>By Franck Pachot . This is a script I have for several years, when tablespaces became locally managed. When we want to know to which segment a block (identified by file id, block id) belongs to, the DBA_EXTENTS view can be very long when you have lot of datafiles and lot of segments. This view [&hellip;]<\/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":[198,59],"tags":[],"type_dbi":[],"class_list":["post-4905","post","type-post","status-publish","format-standard","hentry","category-database-management","category-oracle"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.4) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>An alternative to DBA_EXTENTS optimized for LMT - 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\/an-alternative-to-dba_extents-optimized-for-lmt\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"An alternative to DBA_EXTENTS optimized for LMT\" \/>\n<meta property=\"og:description\" content=\"By Franck Pachot . This is a script I have for several years, when tablespaces became locally managed. When we want to know to which segment a block (identified by file id, block id) belongs to, the DBA_EXTENTS view can be very long when you have lot of datafiles and lot of segments. This view [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/an-alternative-to-dba_extents-optimized-for-lmt\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2015-06-12T06:27:49+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=\"5 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\\\/an-alternative-to-dba_extents-optimized-for-lmt\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/an-alternative-to-dba_extents-optimized-for-lmt\\\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"An alternative to DBA_EXTENTS optimized for LMT\",\"datePublished\":\"2015-06-12T06:27:49+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/an-alternative-to-dba_extents-optimized-for-lmt\\\/\"},\"wordCount\":219,\"commentCount\":0,\"articleSection\":[\"Database management\",\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/an-alternative-to-dba_extents-optimized-for-lmt\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/an-alternative-to-dba_extents-optimized-for-lmt\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/an-alternative-to-dba_extents-optimized-for-lmt\\\/\",\"name\":\"An alternative to DBA_EXTENTS optimized for LMT - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2015-06-12T06:27:49+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/66ab87129f2d357f09971bc7936a77ee\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/an-alternative-to-dba_extents-optimized-for-lmt\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/an-alternative-to-dba_extents-optimized-for-lmt\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/an-alternative-to-dba_extents-optimized-for-lmt\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"An alternative to DBA_EXTENTS optimized for LMT\"}]},{\"@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":"An alternative to DBA_EXTENTS optimized for LMT - 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\/an-alternative-to-dba_extents-optimized-for-lmt\/","og_locale":"en_US","og_type":"article","og_title":"An alternative to DBA_EXTENTS optimized for LMT","og_description":"By Franck Pachot . This is a script I have for several years, when tablespaces became locally managed. When we want to know to which segment a block (identified by file id, block id) belongs to, the DBA_EXTENTS view can be very long when you have lot of datafiles and lot of segments. This view [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/an-alternative-to-dba_extents-optimized-for-lmt\/","og_site_name":"dbi Blog","article_published_time":"2015-06-12T06:27:49+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/an-alternative-to-dba_extents-optimized-for-lmt\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/an-alternative-to-dba_extents-optimized-for-lmt\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"An alternative to DBA_EXTENTS optimized for LMT","datePublished":"2015-06-12T06:27:49+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/an-alternative-to-dba_extents-optimized-for-lmt\/"},"wordCount":219,"commentCount":0,"articleSection":["Database management","Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/an-alternative-to-dba_extents-optimized-for-lmt\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/an-alternative-to-dba_extents-optimized-for-lmt\/","url":"https:\/\/www.dbi-services.com\/blog\/an-alternative-to-dba_extents-optimized-for-lmt\/","name":"An alternative to DBA_EXTENTS optimized for LMT - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2015-06-12T06:27:49+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/an-alternative-to-dba_extents-optimized-for-lmt\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/an-alternative-to-dba_extents-optimized-for-lmt\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/an-alternative-to-dba_extents-optimized-for-lmt\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"An alternative to DBA_EXTENTS optimized for LMT"}]},{"@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\/4905","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=4905"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/4905\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=4905"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=4905"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=4905"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=4905"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}