Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> 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