Search Oracle Blogs

List of Blogs maintained by Paweł Barut.


Tuesday, January 02, 2007

ORA-03297 on empty datafile

Today I've noticed strange behavior of Oracle 10gR2 (10.2.0.3) while shrinking datafile. I had empty datafile (no objects) and i couldn't resize it. I was getting ORA-03297: file contains used data beyond requested RESIZE value while DBA_EXTENTS show no rows for that file. I've discovered that there are objects in Recycle Bin that were located on that file. After purging recycle bin file was shirked successfully.
Let me make an example:
SQL> create tablespace tb_test
  2  datafile 'c:\temp\tb_test.dbf' size 100 K autoextend on;
Tablespace created.
SQL> select count(*) from dba_extents where tablespace_name='TB_TEST';
  COUNT(*)                                                                      
----------                                                                      
         0                                                                      
SQL> select bytes from v$datafile where name = 'C:\TEMP\TB_TEST.DBF';
     BYTES                                                                      
----------                                                                      
    106496                                                                      
SQL> create table t_big tablespace tb_test as
  2  select a.* from all_objects a, dba_users for_cartesian;
Table created.
SQL> select count(*) from dba_extents where tablespace_name='TB_TEST';
  COUNT(*)                                                                      
----------                                                                      
        34                                                                      
SQL> select bytes from v$datafile where name = 'C:\TEMP\TB_TEST.DBF';
     BYTES                                                                      
----------                                                                      
  19963904
Now we have datafile quite big. Let's drop table and shrink file:
SQL> drop table t_big;
Table dropped.
SQL> select count(*) from dba_extents where tablespace_name='TB_TEST';
  COUNT(*)                                                                      
----------                                                                      
         0                                                                      
SQL> alter database datafile 'c:\temp\tb_test.dbf' resize 100 K;
alter database datafile 'c:\temp\tb_test.dbf' resize 100 K
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value 
So. even there is nothing reported in DBA_EXTENT, file cannot be shinked. Let's purge recyclebin and try again:
SQL> purge recyclebin;
Recyclebin purged.
SQL> alter database datafile 'c:\temp\tb_test.dbf' resize 100 K;
Database altered.
Now works fine! Don't forget to clear after tests :)
SQL> drop tablespace tb_test including contents and datafiles;
Tablespace dropped.


Cheers, Paweł

2 comments:

Hasnath said...

Hi

Thank you very much for your explanation.

Shweta
bd.shweta@gmail.com

Alex said...

Thank you.Good one

 

Copyright © Paweł Barut
Printing from DOS to USB Printer