Re: help with space in tablespaces

Hi Chris,

This ought to do the job:

select Blocks+empty_blocks "Allocated blocks", Blocks "Blocks below HWM", a.blocks-b.blocks_w_rows "Empty blocks below HWM" from

   (select blocks, empty_blocks from dba_tables where owner=upper('&1') and table_name=upper('&2')) a,

   (SELECT COUNT (DISTINCT SUBSTR(rowid,1,15)) blocks_w_rows FROM &1..&2) b

Good for Oracle8+ only. Put it in a file, say tabstorage.sql, and execute with

"@tabstorage SCHEMA TABLE"

This give you a report on the table storage. The table MUST be analyzed prior to running this. I leave it as an excercise to modify it to reflect tablespace storage.

Hth,
Ivan Bajon

"blah" <blah_at_blah.com> wrote in message
news:9luttp$t2r2_at_inetbws1.citec.com.au...
> Hi All,
>
> When a data tablespace is nealy full and then say 1million rows are
deleted my
> query will not show the reduction in data it only shows the space left
above
> the high water mark.
>
> Is their a way to show how much space is actually available in the
tablespace
> now that 1 million rows have been deleted?
>
> Thanks,
> Chris
Received on Wed Aug 22 2001 - 06:17:53 CDT