Re: dba_extents vs dba_segments

From: Asif Momen <asif_oracle_at_yahoo.com>
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

Original text of this message