Re: dba_extents and dba_segments
- From: "Niall Litchfield" <niall.litchfield@xxxxxxxxx>
- To: ric.van.dyke@xxxxxxxxxx
- Date: Thu, 10 May 2007 09:14:08 +0100
That would be the behaviour I'd expect to be honest. A dropped segment is
still a segment, but its extents are available for reuse. Or am I just being
daft here.
Meanwhile a little search through the bug db on metalink reveals bug 5665912
and a whole raft of similar bugs. which may be relevant together with the
procedures
DBMS_SPACE_ADMIN.TABLESPACE_FIX_SEGMENT_EXTBLKS
DBMS_SPACE_ADMIN.TABLESPACE_REBUILD_BITMAPS
both of which are a bit of a surprise to me. There's a reasonable amount of
reading to do, but it rather looks to me as if the space information in
these various views can be inconsistent, or wrong, for a number of reasons.
(migration from previous version, parallel operations to name but two in
addition to the dropped objects issue).
cheers
Niall
On 5/10/07, Ric Van Dyke <ric.van.dyke@xxxxxxxxxx> wrote:
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of genegurevich@xxxxxxxxxxxxxxxxxxxxx
Sent: Wednesday, May 09, 2007 4:34 PM
To: oracle-l@xxxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l
--
Niall Litchfield
Oracle DBA
http://www.orawin.info
Other related posts:
