Re: selecting the HWM of a table

Hi,

Use DBMS_SPACE.UNUSED_SPACE
here is an example from metalink :

set serveroutput on

declare

  TOTAL_BLOCKS              number;
  TOTAL_BYTES               number;
  UNUSED_BLOCKS             number;
  UNUSED_BYTES              number;
  LAST_USED_EXTENT_FILE_ID  number;
  LAST_USED_EXTENT_BLOCK_ID number;
  LAST_USED_BLOCK           number;

begin
  dbms_space.unused_space('SCOTT','EMP','TABLE',

     TOTAL_BLOCKS, TOTAL_BYTES, UNUSED_BLOCKS, UNUSED_BYTES,
     LAST_USED_EXTENT_FILE_ID, LAST_USED_EXTENT_BLOCK_ID,
     LAST_USED_BLOCK);

    dbms_output.put_line('OBJECT_NAME               = EMP');
    dbms_output.put_line('-----------------------------------');
    dbms_output.put_line('TOTAL_BLOCKS              = '||TOTAL_BLOCKS);
    dbms_output.put_line('TOTAL_BYTES               = '||TOTAL_BYTES);
    dbms_output.put_line('UNUSED_BLOCKS             = '||UNUSED_BLOCKS);
    dbms_output.put_line('UNUSED BYTES              = '||UNUSED_BYTES);
    dbms_output.put_line('LAST_USED_EXTENT_FILE_ID  =
'||LAST_USED_EXTENT_FILE_ID);
    dbms_output.put_line('LAST_USED_EXTENT_BLOCK_ID =
'||LAST_USED_EXTENT_BLOCK_ID);
    dbms_output.put_line('LAST_USED_BLOCK           = '||LAST_USED_BLOCK);

end;
/
The high water mark is the difference between TOTAL_BYTES and UNUSED bytes values.

Ranga Chakravarthi
Oracle Certified Professional DBA

"Harmonie Euterpe" <Euterpe-Emmen_at_hetnet.nl> wrote in message news:3A8991B4.3251F55E_at_hetnet.nl...
> Dear guru's,
>
> is there a way to directly select the current high water mark of a
> (normal) table from the data dictionary or the shared pool?
>
> I don't want to (1) analyze the table and (2) lookup the blocks column
> in dba_tables. I don't want to make a blockdump from the table's segment
> header as well.
>
> Just a plain select must do!
> Any (plsql) tricks are allowed.
>
> Kind Regards,
>
> Herman de Boer.
>
>
>
Received on Tue Feb 13 2001 - 19:09:13 CST