Looks like this is an answer.
Here is my initial test
SQL> select sum(bytes/1024)/1024 from dba_segments where tablespace_name =
'PCM_D_01';
SUM(BYTES/1024)/1024
--------------------
696300
SQL> select sum(bytes/1024)/1024 from dba_extents where tablespace_name =
'PCM_D_01';
SUM(BYTES/1024)/1024
--------------------
536900
Now I exclude the recyclebin objects
SQL> select sum(bytes/1024)/1024 from dba_segments where tablespace_name =
'PCM_D_01' and segment_name not like 'BIN$%';
SUM(BYTES/1024)/1024
--------------------
536900
SQL> ch /segments/extents/g
1* select sum(bytes/1024)/1024 from dba_extents where tablespace_name =
'PCM_D_01' and segment_name not like 'BIN$%'
SQL> /
SUM(BYTES/1024)/1024
--------------------
536900
thank you very much for your help. Also thanks to everyone who responded to
my Emails - Mark, Tim, Jared, Ajay
(hope I did not miss anyone)
Gene Gurevich
Asif Momen
<asif_oracle@yaho
o.com> To
oracle-l@xxxxxxxxxxxxx,
03/12/2009 06:35 genegurevich@xxxxxxxxxxxx
AM cc
hkchital@xxxxxxxxxxxxxx,
jkstill@xxxxxxxxx, tim@xxxxxxxxx,
Please respond to ajaycajay@xxxxxxxxx
asif_oracle@yahoo Subject
.com Re: dba_extents vs dba_segments
Hi Gene,
Well, the discrepancy between the views is due to Oracle 10g's new feature of
Recyclebin.
Some time back, I have blogged on this issue, you may read it here:
http://momendba.blogspot.com/2007/12/inconsistency-between-dbadatafiles.html
Lets reproduce the same:
SQL> conn / as sysdba
Connected.
SQL> select sum(bytes)/1024/1024 from dba_segments where tablespace_name =
'TEST';
SUM(BYTES)/1024/1024
--------------------
31.1875
1 row selected.
SQL> select sum(bytes)/1024/1024 from dba_extents where tablespace_name =
'TEST';
SUM(BYTES)/1024/1024
--------------------
31.1875
1 row selected.
Currently, DBA_SEGMENTS & DBA_EXTENTS report the same amount of usage
"31.1875"
I connect to a TEST schema and create a table and later drop it:
SQL> conn test/test
Connected.
SQL> create table big_table as select * from all_objects;
Table created.
SQL> insert into big_table select * from big_table;
32617 rows created.
SQL> insert into big_table select * from big_table;
65234 rows created.
SQL> commit;
Commit complete.
SQL> drop table big_table;
Table dropped.
Connect as SYS (well, you may also query using USER_*) and run the previous
queries again:
SQL> conn / as sysdba
Connected.
SQL> select sum(bytes)/1024/1024 from dba_segments where tablespace_name =
'TEST';
SUM(BYTES)/1024/1024
--------------------
46.1875
1 row selected.
SQL> select sum(bytes)/1024/1024 from dba_extents where tablespace_name =
'TEST';
SUM(BYTES)/1024/1024
--------------------
31.1875
1 row selected.
Voila, DBA_SEGMENTS reports a higher space usage. Ok, connect as TEST and
check whats
there in your recyclebin:
SQL> conn test/test
Connected.
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------
-------------------
BIG_TABLE BIN$+Ml+aeo2R8eXlKWaSyTBvw==$0 TABLE
2009-03-12:14:18:03
SQL> purge recyclebin;
Recyclebin purged.
SQL> show recyclebin
SQL>
Hmm, Oracle has moved the dropped table into user's recyclebin. We can throw
it out using
the PURGE command.
Check the space usage again:
SQL> conn /as sysdba
Connected.
SQL> select sum(bytes)/1024/1024 from dba_segments where tablespace_name =
'TEST';
SUM(BYTES)/1024/1024
--------------------
31.1875
1 row selected.
SQL> select sum(bytes)/1024/1024 from dba_extents where tablespace_name =
'TEST';
SUM(BYTES)/1024/1024
--------------------
31.1875
1 row selected.
SQL>
Yep, the space reported is correct. :)
Wondering, Oracle Support took a week and hasn't replied yet.
Regards
Asif Momen
http://momendba.blogspot.com
--- On Wed, 3/11/09, genegurevich@xxxxxxxxxxxx <genegurevich@xxxxxxxxxxxx>
wrote:
From: genegurevich@xxxxxxxxxxxx <genegurevich@xxxxxxxxxxxx>
Subject: Re: dba_extents vs dba_segments
To: oracle-l@xxxxxxxxxxxxx
Cc: hkchital@xxxxxxxxxxxxxx, jkstill@xxxxxxxxx, tim@xxxxxxxxx,
ajaycajay@xxxxxxxxx
Date: Wednesday, March 11, 2009, 1:28 PM
Hi everybody:
Thank you for your responses. I will take a look at the documents that
you
referred me to.
I was under impression that others have surely stumbled upon this and
therefore decided not to put any examples
into my Email. However, here is what I see:
SQL> select sum(bytes/1024)/1024 from dba_Segments where tablespace_name=
'PCM_D_01';
SUM(BYTES/1024)/1024
--------------------
696300
SQL> ch /Segments/extents
1* select sum(bytes/1024)/1024 from dba_extents where tablespace_name=
'PCM_D_01'
SQL> /
SUM(BYTES/1024)/1024
--------------------
471100
As you see the difference is quite significant and the only change in my
SQL was going from dba_segments to
dba_extents. The tablespace is a LMTS with uniform extens. The max number
of extents in a single segment is
441.
thank you
Gene Gurevich
Please consider the environment before printing this
email.
--
//www.freelists.org/webpage/oracle-l
Please consider the environment before printing this email.
--
//www.freelists.org/webpage/oracle-l
