Find table name from v$datafile . name colum

When you look at wait events (i.e. with Toad), you see a file# parameter. How can I get more useful information as the table name.

Is it possible to know even the number of records that are read b开发者_如何转开发y that table?

In another forum I found this advice, but it doesn't seem to work.

select segment_name
from dba_extents ext
where ext.file_id = 828
            and 10711 between ext.block_id and ext.block_id + ext.blocks - 1
            and rownum = 1

Let's talk files, blocks, segments and extents.

A segment is a database object that is stored. It may be a table, index, (sub)partition, cluster or LOB. Mostly you'll be interested in tables and indexes.

A segment is made up of extents. If you think of a segment as a book, an extent is a chapter. A segment (generally) starts with at least one extent. When it needs to store more data and it doesn't have room in the existing extents, it adds another extent to the segment.

An extent lives in a datafile. A datafile can have lots of extents each starting at a different point in the file and having a size. You may have one extent of 15 blocks starting in file 1 at block 10.

A wait event should identify the file and block (and row). If your wait event is for file #1 and block 12 you go off to USER_EXTENTS (or DBA_EXTENTS) and look for the extent in file# 1 where 12 is between the starting block location and the starting block location plus the number of blocks. So block 12 would between starting block 10 and end block 25 (start plus size).

Once you've identified the extent, you track it back to its parent segment (USER_SEGMENTS / DBA_SEGMENTS) which will give you the table/index name.

A theoretical SQL is as follows :

select username, sid, serial#, 
       row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#,
       ext.*
from v$session s
     join dba_extents ext on ext.file_id = row_wait_file#
     and row_wait_block# between ext.block_id and ext.block_id + ext.blocks - 1
where username = 'HR'
and status = 'ACTIVE'

For this one I purposefully blocked a session so that it was waiting on a row lock.

828 is a rather large file id. It isn't impossible, but it is unusual. Do a select from DBA_DATA_FILES and see if you have such a file. If not, and you've only got a few files, look at all the objects that match the "10711 between ext.block_id and ext.block_id + ext.blocks - 1" criteria without the file id. You should be able to find a likely candidate from there.

The exception is if the problem was on a temporary segment. Since these get disposed of at the end of the operation, there's no permanent object recorded. In that cases the 'name' of the table/index isn't applicable and you need to tackle any performance issue another way (eg look at the SQL and its explain plan and work out whether it is correct in using lots of temp space).