Which sizing view to believe?

From: Herring Dave - dherri <Dave.Herring_at_acxiom.com>
Date: Wed, 8 Feb 2006 17:03:30 -0600
Message-ID: <7ED53A68952D3B4C9540B4EFA5C76E36016D9484@CWYMSX04.Corp.Acxiom.net>

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_at_acxiom.com <mailto:dherri_at_acxiom.com> >

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.

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

Received on Wed Feb 08 2006 - 17:03:30 CST