Re: dba_extents and dba

From: Asif Momen <asif_oracle_at_yahoo.com>
Date: Tue, 15 May 2007 04:19:50 -0700 (PDT)
Message-ID: <524548.68865.qm@web56615.mail.re3.yahoo.com>

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_at_gmail.com> wrote:
I don't see that ...

oraclei_at_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_at_discoverfinancial.com <genegurevich_at_discoverfinancial.com> 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.

--
http://www.freelists.org/webpage/oracle-l

Received on Tue May 15 2007 - 06:19:50 CDT

Re: dba_extents and dba_segments