Home » RDBMS Server » Server Administration » V$BH & how to get the block number of the empty block?
V$BH & how to get the block number of the empty block? [message #549513] Sat, 31 March 2012 10:59 Go to next message
lonion
Messages: 97
Registered: August 2011
Location: shenzhen,China
Member

①SQL> SELECT OBJECT_ID FROM DBA_OBJECTS WHERE OBJECT_NAME='T2012';
OBJECT_ID                                                
---------                                           
 57082     


②SQL> SELECT HEADER_BLOCK,BLOCKS FROM DBA_SEGMENTS WHERE SEGMENT_NAME = 'T2012';
HEADER_BLOCK   BLOCKS
-------------  --------
683              8

③SQL> SELECT DBMS_ROWID.rowid_block_number(ROWID)USED_BLOCK_NUMBER FROM SCOTT.T2012;
USED_BLOCK_NUMBER                                                
----------------
684      

④SQL> SHUTDOWN IMMEDIATE;

⑤SQL> STARTUP;

⑥SQL> SELECT BLOCK#,CLASS# FROM V$BH WHERE OBJD = '57082';
no data found

⑦SQL> SELECT * FROM SCOTT.T2012;
ID
-----
1

⑧SQL> SELECT BLOCK#,CLASS# FROM V$BH WHERE OBJD='57082';
BLOCK#    CLASS#
-------   ----------
686        1
684        1
687        1
685        1
688        1
683        4

⑨SQL> SELECT EMPTY_BLOCKS FROM DBA_TABLES WHERE TABLE_NAME='T2012';
EMPTY_BLOCKS
------------
3



QUESTIONS ONE:
in the ⑧ step,why block#685,block#686,block#687,block#688 in the buffer cache after i query data from scott.T2012?

QUESTIONS TWO:
in the ⑨ step,what's the block number of the empty block?just like DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID).
Re: V$BH & how to get the block number of the empty block? [message #549515 is a reply to message #549513] Sat, 31 March 2012 11:15 Go to previous messageGo to next message
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
Senior Member
The high water mark of a segment is pushed up in units of 5 blocks. I cannot give a reference for this, but I read it in a reputable source (somewhere in the docs? Ask Tom??) many years ago, and you have just proved this: your query scanned the segment, up to the HWM. The empty blocks will be the next consecutive block numbers: an extent is adjacent blocks.
Re: V$BH & how to get the block number of the empty block? [message #549517 is a reply to message #549515] Sat, 31 March 2012 12:35 Go to previous message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Note that the step numbers are unreadable for us and appears as square so I answer to the questions without knowing the number you pointed to, next time please use figures from 1 to 9 (ascii 49 to 57).

Q1. When you read via (full) table scan operation Oracle reads several blocks in a shot and not just one block this is why you have 5 data blocks in your cache. As John says each time the HWM increases it increases by 5 blocks (unless there is not 5 blocks till the end of the extend; this change happened between 7.<something> and 7.<something>+1).

Q2. Use dbms_space_admin to know the number of the empty block(s).

Note that the mechanism is different if you use ASSM or MSSM (Automatic/Manual Segment Space Management).

Regards
Michel
Previous Topic: error while droping user
Next Topic: Oracle server shutdown
Goto Forum:
  


Current Time: Thu Apr 18 23:00:58 CDT 2024