Re: dba_extents vs dba_segments
From: <genegurevich_at_discover.com>
Date: Thu, 12 Mar 2009 14:45:32 -0500
Message-ID: <OF2A991998.D9802A5C-ON86257577.006C46A8-86257577.006C8CB0_at_discover.com>
Looks like this is an answer.
Date: Thu, 12 Mar 2009 14:45:32 -0500
Message-ID: <OF2A991998.D9802A5C-ON86257577.006C46A8-86257577.006C8CB0_at_discover.com>
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_at_yaho
o.com> To
oracle-l_at_freelists.org,
03/12/2009 06:35 genegurevich_at_discover.com
AM cc
hkchital_at_singnet.com.sg,
jkstill_at_gmail.com, tim_at_evdbt.com,
Please respond to ajaycajay_at_gmail.com
asif_oracle_at_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_at_discover.com <genegurevich_at_discover.com> wrote:
From: genegurevich_at_discover.com <genegurevich_at_discover.com>
Subject: Re: dba_extents vs dba_segments
To: oracle-l_at_freelists.org
Cc: hkchital_at_singnet.com.sg, jkstill_at_gmail.com, tim_at_evdbt.com, ajaycajay_at_gmail.com
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.
--
http://www.freelists.org/webpage/oracle-l
Please consider the environment before printing this email.
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Mar 12 2009 - 14:45:32 CDT