Re: how to figure out how much disk space is being used?

From: Mark D Powell <mark.powell_at_eds.com>
Date: 25 Oct 2001 06:00:10 -0700
Message-ID: <178d2795.0110250500.2efb92d6@posting.google.com>

allanwtham_at_yahoo.com (godmann) wrote in message news:<95cd51c.0110241850.5c471cd1_at_posting.google.com>...
> Hi there,
>
> To find out how many disk space taken for a particular tablespace you can
> do a
>
> select sum(bytes)
> from dba_segments
> where tablespace_name='XYZ'
>
> Likewise, for a table or any object, do a
>
> select sum(bytes)
> from dba_segments
> where segment_name ='TABLE_NAME'
>
>
> Allan W. Tham
> DBA
Allan the description of what your first query does is incorrect as it tells you how much space within a tablespace has been allocated to objects within the tablespace and not how much space the tablespace has been allocated. You can find that by performing a union of the bytes for a tablespace from dba_extents and dba_freespace or better yet just use dba_data_files:

UT1> @temp
UT1> set echo on
UT1> set timing on
UT1> select sum(DiskAlloc)
  2  from (
  3        select sum(bytes) / 1048576 as DiskAlloc
  4        from   dba_extents
  5        where  tablespace_name = 'USR'
  6        union
  7        select sum(bytes) / 1048576
  8        from   dba_free_space
  9        where  tablespace_name = 'USR'
 10       )

11 /

SUM(DISKALLOC)

4.99609375

Elapsed: 00:00:16.44
UT1> select sum(bytes) / 1048576 as DiskAlloc   2 from dba_data_files
  3 where tablespace_name = 'USR'
  4 /

DISKALLOC

Elapsed: 00:00:00.10
UT1> select sum(bytes) / 1048576 as ObjAlloc   2 from dba_segments
  3 where tablespace_name = 'USR'
  4 /

OBJALLOC

.03125

Elapsed: 00:00:11.42

So my 5M file has only around 32K allocated in it.

Mark D Powell --

Received on Thu Oct 25 2001 - 08:00:10 CDT