From oracle-l-bounce@freelists.org Sun Jan 9 06:07:59 2005 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id j09C7un04529 for ; Sun, 9 Jan 2005 06:07:56 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id j09C7un04524 for ; Sun, 9 Jan 2005 06:07:56 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 5F69972C534; Sun, 9 Jan 2005 07:14:31 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 03371-53; Sun, 9 Jan 2005 07:14:31 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id E159B72C57B; Sun, 9 Jan 2005 07:14:28 -0500 (EST) Date: Sun, 09 Jan 2005 13:12:46 +0100 From: Jesper Haure Norrevang Subject: Re: Used size of a datafile. To: jkstill@gmail.com Cc: vinodg@bmmi.com.bh, oracle-l@freelists.org Message-id: <7ad777b93c.7b93c7ad77@cbs.dk> MIME-version: 1.0 Content-type: text/plain; charset=us-ascii Content-language: en Content-Transfer-Encoding: 8bit Content-disposition: inline X-Accept-Language: en Priority: normal X-archive-position: 14508 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: jhn.aida@cbs.dk Precedence: normal Reply-To: jhn.aida@cbs.dk X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org 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 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 > 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