Re: Info on used tablespace

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: 2000/06/10
Message-ID: <960645509.24652.0.pluto.d4ee154e@news.demon.nl>#1/1

"Rob Zwartjes" <rzwartje_at_robz.HOSTNAME> wrote in message news:slrn8k4dts.4kq.rzwartje_at_rob.home.nl...
> Hello there,
>
> I was busy inserting data in my default user tablespace(10Mb) when I got
 the
> message that no more data could be inserted. So I made a new tablespace of
> 750 Mb ( don't start laughing I think everybody started small ). I know
> oracle warns when thinks go nasty. See the first line :). To prevent this
> happening again, I want to check how much space is used at the current
 moment.
> Reading through the pdf files I found the following script:
> select tablespace_name "Tablespace",file_id
> count(*) "pieces",
> max(blocks) "maximum",
> min(blocks) "minimum",
> avg(blocks) "average",
> sum(blocks) "total"
> from sys.dba_free_space
> where tablespace_name=<name>
> group by tablespace_name,file_id;
> When running this script I got the following message:
> no rows selected
> For <name> I enterd the tablespace name in wich I got from the script:
> select file_name,bytes,tablespace_name
> from sys.dba_data_files;
>
> I logged in oracle as system
> manager and as normal user, both with the same result. The tablespace does
> exist because I am working with it :).
>
> Can anybody help me here.
>
> Thanks in advance,
> Rob
>
>

1 the second script is going to provide all tablespaces, as every tablespace has at least one datafile.
2 When the query returns no results it simply means this tablespace doesn't have any free room, the view contains the unallocated extents only. The following scripts should provide you with the real overview.

select t.tablespace_name

       , df.total
       , s.allocated, nvl(f.free, 0)

from dba_tablespaces t
, (select sum(bytes) total

   from dba_data_files
   group by tablespace_name) df
, (select sum(bytes) allocated

   from dba_segments s
   group by tablespace_name) s
, (select sum(bytes) free

   from dba_free_space
   group by tablespace_name) f
where df.tablespace_name = t.tablespace_name

    and s.tablespace_name = df.tablespace_name    and f.tablespace_name(+) = t.tablespace_name /

This info is all in bytes, as the blocksize tends to deviate from database to database.

Hth,

Sybrand Bakker, Oracle DBA Received on Sat Jun 10 2000 - 00:00:00 CDT