Re: dba_extents and dba_segments

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_at_hotsos.com> 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_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
>
>
>
>
>

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info

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