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.

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-l
Received on Thu Mar 12 2009 - 14:45:32 CDT

Original text of this message