Which sizing view to believe?

From: "Herring Dave - dherri" <Dave.Herring@xxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Date: Wed, 8 Feb 2006 17:03:30 -0600
I'm trying to come up with some sizing figures to help predict future
space needs.  Unfortunately, I've found that I get inconsistent results
across DBA_DATA_FILES, DBA_FREE_SPACE, DBA_SEGMENTS, and DBA_EXTENTS.
For two tablespaces, taking DBA_DATA_FILES minus DBA_FREE_SPACE I get
2041.16GB for used space.  DBA_EXTENTS says 2017.98GB and DBA_SEGMENTS
says 1984.18GB (see queries at end of email).  Shouldn't they all match?

 

I saw a previous thread from last year on bug 4142932 where extent
counts may not match in certain situations.  If that's the case, which
views should I use to get the real information?  This is all on Oracle
9.2.0.6.

 

BTW, I did compare DBA_SEGMENTS with DBA_EXTENTS and found 14 segments
whose size was listed as different, accounting for the missing 33.8GB in
DBA_SEGMENTS.

 

SELECT alloc_gb - free_gb used_gb

  FROM (SELECT (SELECT SUM(bytes) / 1073741824

                  FROM dba_data_files 

                 WHERE tablespace_name IN
('ATOMIC_TBS','PUBLISHED_TBS')) alloc_gb

             , (SELECT SUM(bytes) / 1073741824

                  FROM dba_free_space 

                 WHERE tablespace_name IN
('ATOMIC_TBS','PUBLISHED_TBS')) free_gb

          FROM dual);

 

             USED_GB

--------------------

      2041.162109375

 

SELECT SUM(bytes) / 1073741824 gb_from_dba_extents

  FROM dba_extents

 WHERE tablespace_name IN ('ATOMIC_TBS','PUBLISHED_TBS');

 

GB_FROM_DBA_EXTENTS

-------------------

       2017.9765625

 

SELECT SUM(bytes) / 1073741824 gb_from_dba_segments

  FROM dba_segments

 WHERE tablespace_name IN ('ATOMIC_TBS','PUBLISHED_TBS');

 

GB_FROM_DBA_SEGMENTS

--------------------

       1984.17578125

 

 

Dave

-------------------------------------

Dave Herring, DBA

Acxiom Corporation

3333 Finley

Downers Grove, IL 60515

wk: 630.944.4762

<mailto:dherri@xxxxxxxxxx <mailto:dherri@xxxxxxxxxx> >

-------------------------------------

 

*************************************************************************
The information contained in this communication is confidential, is
intended only for the use of the recipient named above, and may be
legally privileged.

If the reader of this message is not the intended recipient, you are 
hereby notified that any dissemination, distribution or copying of this
communication is strictly prohibited.

If you have received this communication in error, please resend this
communication to the sender and delete the original message or any copy
of it from your computer system.

Thank you.
*************************************************************************
Which sizing view to believe?

Other related posts:
