Re: Used size of a datafile.
- From: Jesper Haure Norrevang <jhn.aida@xxxxxx>
- To: jkstill@xxxxxxxxx
- Date: Sun, 09 Jan 2005 13:12:46 +0100
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@xxxxxxxxx>
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@xxxxxx> 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
> --
> //www.freelists.org/webpage/oracle-l
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
