RE: dba_extents and dba_segments

From: Ric Van Dyke <ric.van.dyke_at_hotsos.com>
Date: Thu, 10 May 2007 00:06:09 -0500
Message-ID: <C970F08BBE1E164AA8063E01502A71CFAE1D87@WIN02.hotsos.com>

On my little test database the issues seems to be that dba_segments shows segments in the recycle bin (BIN$ segment names) but dba_extents doesn't. This little test just shows for table type segments which were the one segment type with the most difference in my database.  

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options  

SQL> select segment_name, sum(bytes) from dba_segments

  2 where segment_type='TABLE' group by segment_name

  3 minus

  4 select segment_name, sum(bytes) from dba_extents

  5 where segment_type='TABLE' group by segment_name

  6 /  

SEGMENT_NAME
SUM(BYTES)

---

• ---------------

BIN$/3CMRcOqT02P1xIJLb19Ng==$0
65536

BIN$/Xwbg4BFT1y9TIkhXUZd5Q==$0
65536

BIN$2sKa4lyTSE6apkmjIa7x8w==$0
65536

BIN$6RYZ9onvR523E6fhLcyeGg==$0
65536

BIN$ATwCYw1hQY660ges9Ryvqg==$0
65536  

<more rows here>    

---

Ric Van Dyke

Hotsos Enterprises

---

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of genegurevich_at_discoverfinancial.com
Sent: Wednesday, May 09, 2007 4:34 PM
To: oracle-l_at_freelists.org
Subject: dba_extents and dba_segments  

Hi all:  

I am very confused by the following 2 SQLs which I have ran against oracle

10.2.0.3. I have been under impression

that dba_Extents is a rollup of the dba_extents and therefore these two

SQLs should return the same results.

This however does not seem to be the case. Does anyone know what is the

reason for this?  

SQL> select sum(bytes/1024)/1024 from dba_extents where tablespace_name
=

'CCS_X_017';     SUM(BYTES/1024)/1024

---

               22200  

  1* select sum(bytes/1024)/1024 from dba_segments where tablespace_name
=

'CCS_X_017' SQL> /   SUM(BYTES/1024)/1024

---

                2400

 

thank you  

Gene Gurevich    

--

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

 

 


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

Received on Thu May 10 2007 - 00:06:09 CDT