Re: dba_extents and dba_segments

Hi,
   
  When an object is dropped, it is moved to the recyclebin. It still occupies 
space in DBA_SEGMENTS and the extents are de-allocated from DBA_EXTENTS. After 
purging the table the segment space is returned back. If you FLASH BACK the 
table then the extents are reallocated.
   
   
  SQL> select * from v$version;
  BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
  
SQL>  select * from user_recyclebin;
  no rows selected
  SQL> select sum(bytes/1024)/1024 from dba_extents where tablespace_name 
='APPSTS';
  SUM(BYTES/1024)/1024
--------------------
               .4375
  SQL> select sum(bytes/1024)/1024 from dba_segments where tablespace_name  
='APPSTS';
  SUM(BYTES/1024)/1024
--------------------
               .4375
  SQL> create table apps.test as select * from all_objects;
  Table created.
  SQL> create table apps.test2 as select * from all_objects;
  Table created.
  SQL> select sum(bytes/1024)/1024 from dba_extents where tablespace_name 
='APPSTS';
  SUM(BYTES/1024)/1024
--------------------
              2.4375
  SQL> select sum(bytes/1024)/1024 from dba_segments where tablespace_name  
='APPSTS';
  SUM(BYTES/1024)/1024
--------------------
              2.4375
  SQL> drop table apps.test;
  Table dropped.
  SQL> select sum(bytes/1024)/1024 from dba_extents where tablespace_name 
='APPSTS';
  SUM(BYTES/1024)/1024
--------------------
              1.4375
  SQL> select sum(bytes/1024)/1024 from dba_segments where tablespace_name  
='APPSTS';
  SUM(BYTES/1024)/1024
--------------------
              2.4375
  SQL>  select * from user_recyclebin;
  OBJECT_NAME                    ORIGINAL_NAME                    OPERATION
------------------------------ -------------------------------- ---------
TYPE                      TS_NAME                        CREATETIME
------------------------- ------------------------------ -------------------
DROPTIME               DROPSCN PARTITION_NAME                   CAN CAN
------------------- ---------- -------------------------------- --- ---
   RELATED BASE_OBJECT PURGE_OBJECT      SPACE
---------- ----------- ------------ ----------
BIN$OgywoXaTSauN6N21gymPgA==$0 TEST                             DROP
TABLE                     APPSTS                         2007-05-15:14:10:04
2007-05-15:14:10:20     197892                                  YES YES
     10030       10030        10030        128
  
SQL> purge recyclebin;
  Recyclebin purged.
  SQL> select sum(bytes/1024)/1024 from dba_extents where tablespace_name 
='APPSTS';
  SUM(BYTES/1024)/1024
--------------------
              1.4375
  SQL> select sum(bytes/1024)/1024 from dba_segments where tablespace_name  
='APPSTS';
  SUM(BYTES/1024)/1024
--------------------
              1.4375
   
  SQL> drop table test2;

  SQL> select sum(bytes/1024)/1024 from dba_extents where tablespace_name 
='APPSTS';
  SUM(BYTES/1024)/1024
--------------------
               .4375
  SQL> select sum(bytes/1024)/1024 from dba_segments where tablespace_name  
='APPSTS';
  SUM(BYTES/1024)/1024
--------------------
              1.4375

  SQL> flashback table test2 to before drop;
  Flashback complete.
  SQL> select sum(bytes/1024)/1024 from dba_extents where tablespace_name 
='APPSTS';
  SUM(BYTES/1024)/1024
--------------------
              1.4375
  SQL> select sum(bytes/1024)/1024 from dba_segments where tablespace_name  
='APPSTS';
  SUM(BYTES/1024)/1024
--------------------
              1.4375


rjamya <rjamya@xxxxxxxxx> wrote:
  I don't see that ... 

oraclei@xxx> sys yyy

SQL*Plus: Release 10.2.0.3.0 - Production on Thu May 10 06:40:58 2007
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> set timing on
SQL> select sum(bytes/1024)/1024 from dba_extents where tablespace_name = 
'XXXDATA'; 

SUM(BYTES/1024)/1024
--------------------
          966505.375

Elapsed: 00:00:16.52
SQL> select sum(bytes/1024)/1024 from dba_segments where tablespace_name = 
'XXXDATA';

SUM(BYTES/1024)/1024 
--------------------
          966102.188

Elapsed: 00:00:00.27


400mb is missing, but that could very well be dropped objects etc.
rjamya

  On 5/9/07, genegurevich@xxxxxxxxxxxxxxxxxxxxx 
<genegurevich@xxxxxxxxxxxxxxxxxxxxx> wrote:  
SQL> select sum(bytes/1024)/1024 from dba_extents where tablespace_name 
='CCS_X_017';

SUM(BYTES/1024)/1024
--------------------
               22200

  1* select sum(bytes/1024)/1024 from dba_segments where tablespace_name 
='CCS_X_017' 
SQL> /

SUM(BYTES/1024)/1024
--------------------
                2400





       
---------------------------------
Yahoo! oneSearch: Finally,  mobile search that gives answers, not web links.

Other related posts: