Re: dba_extents vs dba_segments
Date: Thu, 12 Mar 2009 04:35:18 -0700 (PDT)
Message-ID: <732733.39445.qm_at_web56603.mail.re3.yahoo.com>
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
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Mar 12 2009 - 06:35:18 CDT