Database Administration (MOSC)

MOSC Banner

Datafile seems empty, but dropping raises "ORA-03262: the file is non-empty"

edited Dec 1, 2010 1:49AM in Database Administration (MOSC) 13 commentsAnswered
 Trying to drop a datafile:
   SQL> alter tablespace prod drop datafile 13;		
		
   alter tablespace prod drop datafile 13
   *
   ERROR at line 1:
   ORA-03262: the file is non-empty    SQL> select * from dba_extents where file_id = 13;

   no rows selected
So it looks empty after all; all blocks should be free space.  A closer look:

   SQL> select * from (select file_id, block_id, blocks, block_id - lag(block_id+blocks) over (order by block_id) as gap		
		
from sys.dba_free_space where file_id=13) where rownum<9;
      FILE_ID   BLOCK_ID     BLOCKS        GAP
   ---------- ---------- ---------- ----------
           13          5          4
           13          9          4          0
           13         13          4          0
           13         17          4          0
           13         21          4          0
           13         25          4          0
           13         29          4          0
           13         33          4          0
   8 rows selected.
   SQL> select * from (select block_id, blocks, block_id+blocks as end, block_id - lag(block_id+blocks) over (order by block_id) as gap

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center