Re: Used size of a datafile.

Thanks Jared,

I think you have a good point here.
I don't like when ad hoc SQL made by
the DBA shows up as the most resource
consuming statements in the database.
I admit that my statement do not perform well.

It reminds me, that the sum of bytes
in DBA_FREE_SPACE and DBA_EXTENTS for
a tablespace do not match the actual
size of the datafiles. The difference is: - 1 block file header,
- 1 block HEADER of bitmap used with locally   managed tablespaces (LMT).
- 6 blocks (may be more?) of LMT-bitmaps.

The difference can be seen in
DBA_DATA_FILES as the difference between USER_BYTES and BYTES.

Regards
Jesper Haure Norrevang

• Original Message ----- From: Jared Still <jkstill_at_gmail.com> Date: Saturday, January 8, 2005 8:09 pm Subject: Re: Used size of a datafile.

> You may consider using DBA_FREE_SPACE rather than DBA_EXTENTS.
>
> If there are a lot of objects in your database, it may be
> (possibly much) less
> expensive to query DBA_FREE_SPACE.
>
> On Sat, 08 Jan 2005 11:15:32 +0100, Jesper Haure Norrevang
> <jhn.aida_at_cbs.dk> wrote:
> > Vinod,
> >
> > select
> > f.tablespace_name,
> > f.file_name,
> > sum(e.bytes) / 1024 / 1024 MB
> > from dba_extents e, dba_data_files f
> > where e.file_id = f.file_id
> > group by f.tablespace_name, f.file_name
> > order by f.tablespace_name, f.file_name;
> >
>
>
> --
> Jared Still
> Certifiable Oracle DBA and Part Time Perl Evangelist
> --
> http://www.freelists.org/webpage/oracle-l
>

--
http://www.freelists.org/webpage/oracle-l