high water mark??
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: high water mark??

  1. #1
    Join Date
    Oct 2000
    Posts
    144

    Question

    I want to reduce the size of the tablespace. Before I do that, I want to see
    the high water mark of the tablespace. What is the object name that I can query
    to find the high water mark?

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    Tablespaces don't have a HWM. What exactly are you trying to do?
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Nov 2000
    Posts
    224
    You could see the HWM for all the tables in tha Tablespace.
    Sum this for all the tables.

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    you can see a sort of HWM for datafiles



    rem
    rem Remarks: minimal size of a datafile is 2 Oracle blocks
    rem resizing should always be a multiple of Oracle blocks
    rem
    rem Requirements: select on sys.dba_data_files
    rem select on sys.dba_free_space
    rem select on sys.v_$parameter
    rem
    rem --------------------------------------------------------------------

    prompt Calculation of HighwaterMark of datafiles
    prompt ---------------------------------------------------------------------------------------------------------

    set feed off
    set serveroutput on
    execute dbms_output.enable(2000000);

    declare
    cursor c_dbfile is
    select tablespace_name
    ,file_name
    ,file_id
    ,bytes
    from sys.dba_data_files
    where status !='INVALID'
    order by tablespace_name,file_id;

    cursor c_space(v_file_id in number) is
    select block_id,blocks
    from sys.dba_free_space
    where file_id=v_file_id
    order by block_id desc;

    blocksize binary_integer;
    filesize number;
    extsize number;

    begin

    /* get the blocksize of the database, needed to calculate the startaddress */

    select value
    into blocksize
    from v$parameter
    where name = 'db_block_size';

    /* retrieve all datafiles */

    for c_rec1 in c_dbfile
    loop
    filesize := c_rec1.bytes;
    <>
    for c_rec2 in c_space(c_rec1.file_id)
    loop
    extsize := ((c_rec2.block_id - 1)*blocksize + c_rec2.blocks*blocksize);
    if extsize = filesize
    then
    filesize := (c_rec2.block_id - 1)*blocksize;
    else
    /* in order to shrink the free space must be uptil end of file */
    exit outer;
    end if;
    end loop outer;
    if filesize = c_rec1.bytes
    then
    dbms_output.put_line('Tablespace: '
    ||' '||c_rec1.tablespace_name||' Datafile: '||c_rec1.file_name);
    dbms_output.put_line('Can not be resized, no free space at end of file.')
    ;
    dbms_output.put_line('.');
    else
    if filesize < 2*blocksize
    then
    dbms_output.put_line('Tablespace: '
    ||' '||c_rec1.tablespace_name||' Datafile: '||c_rec1.file_name);
    dbms_output.put_line('Can be resized uptil: '||2*blocksize
    ||' Bytes, Actual size: '||c_rec1.bytes||' Bytes');
    dbms_output.put_line('.');
    else
    dbms_output.put_line('Tablespace: '
    ||' '||c_rec1.tablespace_name||' Datafile: '||c_rec1.file_name);
    dbms_output.put_line('Can be resized uptil: '||filesize
    ||' Bytes, Actual size: '||c_rec1.bytes);
    dbms_output.put_line('.');
    end if;
    end if;
    end loop;
    end;
    /

    set feed on
    prompt


    dunno if this is what you are looking for

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width