Re: HoTo find out how much free space in tablespace??

On Thu, 13 Feb 2003 21:35:02 +0100, Lars Steinmetz wrote:

> Hello NG,
>
> I have an Oracle 9i Server with serveral users and tablespaces. One of these
> tablespaces was full so I deleted some data out of it. How can I find out
> how much space in in the tablespace now? The enterprise Manager only shows
> high water marks!
>

What do you mean you "deleted" some data out of a tablespace? Do you mean you did 'delete from TABLE where SOME_CONDITION'?

Because if so, it's not in the least surprising that Enterprise Manager is "wrong" -because it happens to be perfectly right.

Deletes do not, under any circumstances, cause space to be released. The blocks that used to contain the table data still belong to the table. The table might want to use them again one day, so they are still 'in use', by that table, even if they're completely empty.

That is:

Create table BLAH... (BLAH is tiny)
insert 1 billion records (BLAH is now 4GB big) delete from BLAH; (BLAH is STILL 4GB big)

The only thing that frees space up is DDL. Specifically, truncating a table, dropping a table, moving a table, dropping an index or rebuilding an index. Only those activities actually causes Oracle to say 'that block is not needed, so I'll mark it as free space'

Regards
HJR Received on Thu Feb 13 2003 - 18:08:23 CST