Re: finding free space below the high watermark

Possibly the best approach is to analyze the table, then check columns in user_tables:

blocks number of blocks below high water

avg_row_len averages space used by a row

                        (I think you have to add 5 for overheads
                        when using analyze

num_rows       number of rows in table

Assume that a block has about 100 bytes reserved for headers, ITL lists, etc. Then compare BLOCKS

    (avg_row_len+5) * num_rows *
    (blocksize / (blocksize - 100))

The calculation will tell you approximately how many blocks you would need if you rebuilt the table with PCTFREE = 0, so can be used as a guideline on how much space is free.

You could probably get a reasonale idea by analyzing a small fraction of the table rather than doing a compute.

-- 
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


Next public appearances:
 March 2004 Hotsos Symposium - The Burden of Proof
 March 2004 Charlotte NC OUG - CBO Tutorial
 April 2004 Iceland


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html


Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK___February
____UK___June


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


"Brian Anderson" <anderb_at_iname.com> wrote in message
news:259a9565.0402061341.a23c87a_at_posting.google.com...


> I have a system with 2 tables,  a document and a document detail

> table.  Each row in the document table many have as many as serveral

> thousand corresponding rows in the document detail table.  At one

> point we had some many child rows that we moved those rows to another

> database.

>

> We have gone through a data cleanup project and we have removed 120

> million rows from the child table,  I am trying to find a way to

> calculate how much free space I have below the high water mark so the

> I can move those archived rows back into the main database.  However,

> I can't not found a way to see how much space is available under the

> highwater mark.  The database must have a way to know where new data

> can be put. I can work with blocks or Extents I just need a way to get

> the information.  Does anyone know of a way to get this information

> from a locally managed tablespace.  The is only one table in this

> tablespace so a way to find out how much unused space is in a given

> table will work too.

>

> Brian