How to Find High Water Mark in Oracle

Here’s an interesting find on how to find the high watermark in Oracle. It works fine in both the 10.2.0.1 and 11.2.0.1 at my laptop.

Use dbms_space.unused_space to find unused space, whose arguments are:
   segment_owner              IN  VARCHAR2, 
   segment_name               IN  VARCHAR2,
   segment_type               IN  VARCHAR2,
   total_blocks               OUT NUMBER,
   total_bytes                OUT NUMBER,
   unused_blocks              OUT NUMBER,
   unused_bytes               OUT NUMBER,
   last_used_extent_file_id   OUT NUMBER,
   last_used_extent_block_id  OUT NUMBER,
   last_used_block            OUT NUMBER, 
   partition_name             IN  VARCHAR2 DEFAULT NULL);

var tb number
var tby number
var ub number
var uby number
var l1 number
var l2 number
var l3 number
SQL> exec dbms_space.unused_space('R_STAGE1','EB_GL_TRANSACTIONS','TABLE',:tb,:tby,:ub,:uby,:l1,:l2,:l3)

PL/SQL procedure successfully completed.

SQL> print

        TB
----------
   1721600

       TBY
----------
2.8207E+10

        UB
----------
         0

       UBY
----------
         0

        L1
----------
        38

        L2
----------
    129925

        L3
----------
       640
So in this case, HMW is in the last block (ub or unused blocks and uby or unused bytes are 0).
To find where HMW is located physically,
SQL> select header_file, header_block from dba_segments where segment_name = 'EB_GL_TRANSACTIONS';

HEADER_FILE HEADER_BLOCK
----------- ------------
         26         3205
SQL> alter system dump datafile 26 block 3205;

System altered.
In the dump file, we see
  Extent Control Header
  -----------------------------------------------------------------
  Extent Header:: spare1: 0      spare2: 0      #extents: 2690   #blocks: 1721598
                  last map  0x0ac0b405  #maps: 1      offset: 8224
      Highwater::  0x0981fe05  ext#: 2689   blk#: 640    ext size: 640
  #blocks in seg. hdr's freelists: 1406670
  #blocks below: 1721598
  mapblk  0x0ac0b405  offset: 1672
                   Unlocked
     Map Header:: next  0x0ac0b405  #extents: 1017 obj#: 38223  flag: 0x40000000
  Extent Map
  -----------------------------------------------------------------
   0x06800c86  length: 639
   0x08c00005  length: 640
   0x09000005  length: 640
   0x09400005  length: 640
...
   0x0a40b405  length: 640
   0x0a80b405  length: 640

  nfl = 1, nfb = 1 typ = 1 nxf = 0 ccnt = 344324
  SEG LST:: flg: USED   lhd: 0x0bc1016a ltl: 0x0981fe04
End dump data blocks tsn: 28 file#: 26 minblk 3205 maxblk 3205
The address of HMW is 0x0981fe05, or 159514117. Find the location:
SQL> select dbms_utility.data_block_address_file(159514117) file#, dbms_utility.data_block_address_block(159514117) block# from dual;

       FILE#       BLOCK#
------------ ------------
          38       130565
If data is populated up to the highest block (no block at the top that had data completely deleted), we can confirm it this way:
SQL> select * from dba_extents where file_id = 38 and segment_name = 'EB_GL_TRANSACTIONS' order by extent_id;

OWNER                          SEGMENT_NAME                                                              PARTITION_NAME                 SEGMENT_TYPE
------------------------------ --------------------------------------------------------------------------------- ------------------------------ ------------------
TABLESPACE_NAME                 EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
------------------------------ ---------- ---------- ---------- ---------- ---------- ------------
R_STAGE1                       EB_GL_TRANSACTIONS                                                                                       TABLE
R_STAGE_DATA                            4         38          5   10485760        640           38

...

R_STAGE1                       EB_GL_TRANSACTIONS                                                                                       TABLE
R_STAGE_DATA                         2689         38     129925   10485760        640           38
Notice that the starting block of the last extent is 129925. It has 640 blocks so the last block is
129925 + 640 – 1 = 130564. So 130565, where HWM points to, would be the first block beyond the last used block.

		
Advertisements
This entry was posted in database, Oracle, Oracle DBA and tagged , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s