In ORACLE, we can locate a database object through file_id (file#) and block_id (block#). For example, if we have file#=4 block#=266 blocks=8 in the trace file generated in 10046, then I can locate the object through the following two SQL s
SQL 1: This SQL is inefficient and takes a long time to execute.
SELECT OWNER,SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAMEFROM DBA_EXTENTSWHERE FILE_ID =&FILE_IDAND &BLOCK_ID BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1;
SQL 2: This SQL is more efficient (there is no CACHEHINT field in ORACLE 10g)
SELECT OBJD,FILE#,BLOCK#,CLASS#,TS#,CACHEHINT,STATUS,DIRTYFROM V$BHWHERE FILE# = &FILE_IDAND BLOCK# = &BLOCK_ID;SELECT OWNER, OBJECT_NAME FROM DBA_OBJECTS WHERE OBJECT_ID=&OBJECT_ID;
Here's an example to illustrate it. The details are as follows
SQL> COL OWNER FOR A12;SQL> COL SEGMENT_NAME FOR A32;SQL> SELECT OWNER ,2 SEGMENT_NAME ,3 HEADER_FILE ,4 HEADER_BLOCK5 FROM DBA_SEGMENTS6 WHERE OWNER='TEST' AND SEGMENT_NAME='EMPLOYEE';OWNER SEGMENT_NAME HEADER_FILE HEADER_BLOCK------------ -------------------------------- ----------- ------------TEST EMPLOYEE 4 266SQL>SQL> SELECT OWNER,2 SEGMENT_NAME,3 SEGMENT_TYPE,4 TABLESPACE_NAME5 FROM DBA_EXTENTS6 WHERE FILE_ID = 47 AND 266 BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1;OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME------------ -------------------------------- ------------------ -----------------TEST EMPLOYEE TABLE USERSSQL>SQL> SELECT OBJD,2 FILE#,3 BLOCK#,4 CLASS#,5 TS#,6 CACHEHINT,7 STATUS,8 DIRTY9 FROM V$BH10 WHERE FILE# = 411 AND BLOCK# = 266;OBJD FILE# BLOCK# CLASS# TS# CACHEHINT STATUS D---------- ---------- ---------- ---------- ---------- ---------- ---------- -76090 4 266 4 4 15 cr N76090 4 266 4 4 15 cr N76090 4 266 4 4 15 cr NSQL> SELECT OWNER, OBJECT_NAME FROM DBA_OBJECTS WHERE OBJECT_ID=76090;OWNER OBJECT_NAME------------ ------------------------------------------------------------TEST EMPLOYEE
Yesterday, when I was discussing a question about free blocks in the group, when I validated the test, I found a strange phenomenon, using the following SQL to find the largest free block.
SELECT UPPER(F.TABLESPACE_NAME) AS "Table space name",D.TOT_GROOTTE_MB AS "Table space size(M)",D.TOT_GROOTTE_MB - F.TOTAL_BYTES AS "Used space(M)",TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99')AS "Usage ratio",F.TOTAL_BYTES AS "Free space(M)",F.MAX_BYTES AS "Maximum free block(M)"FROM(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTESFROM SYS.DBA_FREE_SPACEGROUP BY TABLESPACE_NAME) F,(SELECT DD.TABLESPACE_NAME,ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MBFROM SYS.DBA_DATA_FILES DDGROUP BY DD.TABLESPACE_NAME) DWHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME;SELECT FILE_ID,BLOCK_ID, BYTES,BLOCKSFROM DBA_FREE_SPACEWHERE TABLESPACE_NAME=&TABLESPACE_NAMEORDER BY BYTES DESC;
Then I found that I could not find the corresponding object using the above two SQL s. As shown in the following screenshot:
After looking up the data, we found that after Oracle Database 10g introduced the recycle bin function, the space in the recycle bin (RECYCLEBIN$) would be calculated as free space and added to the dba_free_space dictionary. In $ORACLE_HOME/rdbms/admin/catspace.sql, you can find the definition of view DBA_FREE_SPACE. The script is as follows:
Definition of DBA_FREE_SPACE in ORACLE 10g:
create or replace view DBA_FREE_SPACE(TABLESPACE_NAME, FILE_ID, BLOCK_ID,BYTES, BLOCKS, RELATIVE_FNO)asselect ts.name, fi.file#, f.block#,f.length * ts.blocksize, f.length, f.file#from sys.ts$ ts, sys.fet$ f, sys.file$ fiwhere ts.ts# = f.ts#and f.ts# = fi.ts#and f.file# = fi.relfile#and ts.bitmapped = 0union allselect /*+ ordered use_nl(f) use_nl(fi) */ts.name, fi.file#, f.ktfbfebno,f.ktfbfeblks * ts.blocksize, f.ktfbfeblks, f.ktfbfefnofrom sys.ts$ ts, sys.x$ktfbfe f, sys.file$ fiwhere ts.ts# = f.ktfbfetsnand f.ktfbfetsn = fi.ts#and f.ktfbfefno = fi.relfile#and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0union allselect /*+ ordered use_nl(u) use_nl(fi) */ts.name, fi.file#, u.ktfbuebno,u.ktfbueblks * ts.blocksize, u.ktfbueblks, u.ktfbuefnofrom sys.recyclebin$ rb, sys.ts$ ts, sys.x$ktfbue u, sys.file$ fiwhere ts.ts# = rb.ts#and rb.ts# = fi.ts#and u.ktfbuefno = fi.relfile#and u.ktfbuesegtsn = rb.ts#and u.ktfbuesegfno = rb.file#and u.ktfbuesegbno = rb.block#and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0union allselect ts.name, fi.file#, u.block#,u.length * ts.blocksize, u.length, u.file#from sys.ts$ ts, sys.uet$ u, sys.file$ fi, sys.recyclebin$ rbwhere ts.ts# = u.ts#and u.ts# = fi.ts#and u.segfile# = fi.relfile#and u.ts# = rb.ts#and u.segfile# = rb.file#and u.segblock# = rb.block#and ts.bitmapped = 0/
Definition of DBA_FREE_SPACE in ORACLE 11g:
create or replace view DBA_FREE_SPACE(TABLESPACE_NAME, FILE_ID, BLOCK_ID,BYTES, BLOCKS, RELATIVE_FNO)asselect ts.name, fi.file#, f.block#,f.length * ts.blocksize, f.length, f.file#from sys.ts$ ts, sys.fet$ f, sys.file$ fiwhere ts.ts# = f.ts#and f.ts# = fi.ts#and f.file# = fi.relfile#and ts.bitmapped = 0union allselect /*+ ordered use_nl(f) use_nl(fi) */ts.name, fi.file#, f.ktfbfebno,f.ktfbfeblks * ts.blocksize, f.ktfbfeblks, f.ktfbfefnofrom sys.ts$ ts, sys.x$ktfbfe f, sys.file$ fiwhere ts.ts# = f.ktfbfetsnand f.ktfbfetsn = fi.ts#and f.ktfbfefno = fi.relfile#and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0union allselect /*+ ordered use_nl(u) use_nl(fi) */ts.name, fi.file#, u.ktfbuebno,u.ktfbueblks * ts.blocksize, u.ktfbueblks, u.ktfbuefnofrom sys.recyclebin$ rb, sys.ts$ ts, sys.x$ktfbue u, sys.file$ fiwhere ts.ts# = rb.ts#and rb.ts# = fi.ts#and u.ktfbuefno = fi.relfile#and u.ktfbuesegtsn = rb.ts#and u.ktfbuesegfno = rb.file#and u.ktfbuesegbno = rb.block#and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0union allselect ts.name, fi.file#, u.block#,u.length * ts.blocksize, u.length, u.file#from sys.ts$ ts, sys.uet$ u, sys.file$ fi, sys.recyclebin$ rbwhere ts.ts# = u.ts#and u.ts# = fi.ts#and u.segfile# = fi.relfile#and u.ts# = rb.ts#and u.segfile# = rb.file#and u.segblock# = rb.block#and ts.bitmapped = 0/
So is it possible that the largest free block found in DBA_FREE_SPACE was an object in the recycle bin? So let's test it.
SQL> show parameter recyclebin;NAME TYPE VALUE------------------------------------ ----------- ------------------------------recyclebin string onSQL> CREATE TABLE ESCMOWNER.TTT2 AS3 SELECT * FROM DBA_OBJECTS;Table created.SQL> COL OWNER FOR A12;SQL> COL SEGMENT_NAME FOR A32;SQL> SELECT OWNER,SEGMENT_NAME, HEADER_FILE, HEADER_BLOCK2 FROM DBA_SEGMENTS3 WHERE OWNER='ESCMOWNER' AND SEGMENT_NAME='TTT' ;OWNER SEGMENT_NAME HEADER_FILE HEADER_BLOCK------------ -------------------------------- ----------- ------------ESCMOWNER TTT 97 113025SQL>SQL> SELECT * FROM X$KTFBFE WHERE KTFBFEFNO=97;ADDR INDX INST_ID KTFBFETSN KTFBFEFNO KTFBFEBNO KTFBFEBLKS---------------- ---------- ---------- ---------- ---------- ---------- ----------00007F57B2388CA0 222 1 9 97 524169 120SQL> DROP TABLE ESCMOWNER.TTT;Table dropped.SQL> COL ORIGINAL_NAME FOR A16;SQL> SELECT OBJ#,OWNER#,ORIGINAL_NAME,FILE#,BLOCK# ,FLAGS,SPACE FROM RECYCLEBIN$;OBJ# OWNER# ORIGINAL_NAME FILE# BLOCK# FLAGS SPACE---------- ---------- ---------------- ---------- ---------- ---------- ----------805429 73 TTT 97 113025 30 896SQL> PURGE DBA_RECYCLEBIN;DBA Recyclebin purged.SQL> SELECT * FROM X$KTFBFE WHERE KTFBFEFNO=97 ;ADDR INDX INST_ID KTFBFETSN KTFBFEFNO KTFBFEBNO KTFBFEBLKS---------------- ---------- ---------- ---------- ---------- ---------- ----------00007F57B2388CA0 222 1 9 97 113025 800007F57B2388CA0 225 1 9 97 524169 120SQL>
As shown above, after emptying the Recycle Bin object, you will find an additional record in X$KTFBFE, KTFBFEFNO and KTFBFEBNO are 97 and 113025, respectively. This value is obviously to delete the FILE_ID (97) and BLOCK_ID (113025) values of the object TTT.
In addition, during the testing process, it was found that not every test result was an additional record in X$KTFBFE, sometimes the record would not change, but the KTFBFEBNO of a record in X$KTFBFE would change, and the change was related to the emptying of the recycling bin. As shown in the following case:
SQL> show parameter recyclebin;NAME TYPE VALUE------------------------------------ ----------- ------------------------------recyclebin string onSQL> CREATE TABLE TEST.TTT2 AS3 SELECT * FROM DBA_OBJECTS;Table created.SQL> COL OWNER FOR A12;SQL> COL SEGMENT_NAME FOR A32;SQL> SELECT OWNER,SEGMENT_NAME, HEADER_FILE, HEADER_BLOCK2 FROM DBA_SEGMENTS3 WHERE OWNER='TEST' AND SEGMENT_NAME='TTT' ;OWNER SEGMENT_NAME HEADER_FILE HEADER_BLOCK------------ -------------------------------- ----------- ------------TEST TTT 5 130SQL> SELECT * FROM X$KTFBFE WHERE KTFBFEFNO=5 ;ADDR INDX INST_ID KTFBFETSN KTFBFEFNO KTFBFEBNO KTFBFEBLKS---------------- ---------- ---------- ---------- ---------- ---------- ----------00002BA829B19558 150 1 6 5 1280 50675200002BA829B19558 151 1 6 5 508032 16256SQL> DROP TABLE TEST.TTT;Table dropped.SQL>SQL> COL ORIGINAL_NAME FOR A16;SQL> SELECT OBJ#,OWNER#,ORIGINAL_NAME,FILE#,BLOCK# ,FLAGS,SPACE FROM RECYCLEBIN$;OBJ# OWNER# ORIGINAL_NAME FILE# BLOCK# FLAGS SPACE---------- ---------- ---------------- ---------- ---------- ---------- ----------82820 85 TTT 5 130 30 1152SQL> SELECT * FROM X$KTFBFE WHERE KTFBFEFNO=5 ;ADDR INDX INST_ID KTFBFETSN KTFBFEFNO KTFBFEBNO KTFBFEBLKS---------------- ---------- ---------- ---------- ---------- ---------- ----------00002BA829B159D8 150 1 6 5 1280 50675200002BA829B159D8 151 1 6 5 508032 16256SQL> PURGE DBA_RECYCLEBIN;DBA Recyclebin purged.SQL> SELECT * FROM X$KTFBFE WHERE KTFBFEFNO=5 ;ADDR INDX INST_ID KTFBFETSN KTFBFEFNO KTFBFEBNO KTFBFEBLKS---------------- ---------- ---------- ---------- ---------- ---------- ----------00002BA829B159D8 150 1 6 5 128 50790400002BA829B159D8 151 1 6 5 508032 16256SQL>
As shown above, after clearing the recycle bin table, you query X$KTFBFE, and you will find that one of the records changes in KTFBFEBNO. Their relationship is
1280 -1152 = 128
So you can see that the value of KTFBFEBNO has changed from 1280 to 128. When you look at DBA_FREE_SPACE, you will see this. So when the recycle bin is emptied, it may be that the database marks the space of the table as an idle block, or that the idle block is merged into other idle blocks.
X$KTFBFE is actually the initials of these words [k]ernel [t]ablespace [f]ile [b]itmapped [f]ree [e]xtents. The most in-depth introduction to this system view is this article. Talking about Oracle dba_free_space If you are interested, you can verify and test it.
Reference material: