Thursday, December 05, 2013

Unsolved Mystery: The datafile that wouldn't shrink


I had a problem the other day in reclaiming disk space on one of my Oracle development databases (10gR2).

Some background:


Made a tablespace delme with datafile delme.dbf.

Made a table mytable (which is very big) in tablespace delme, to use for just a little while, then it was to be dropped.

Done with table so: drop table mytable;

The problem:


Realizing I should have used "purge" in the DROP TABLE statement, I sigh and say OK, let's just run the Tom Kyte maxshrink script...There's absolutely nothing I want in this tablespace.

Crap, it's still using the space:

SQL> alter database datafile '/opt/oracle/oradata01/delme.dbf' resize 1m
  2  ;
alter database datafile '/opt/oracle/oradata01/delme.dbf' resize 1m
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value


SQL> alter database datafile '/opt/oracle/oradata01/delme.dbf' resize 5m;
alter database datafile '/opt/oracle/oradata01/delme.dbf' resize 5m
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value


SQL> alter database datafile '/opt/oracle/oradata01/delme.dbf' resize 100m;
alter database datafile '/opt/oracle/oradata01/delme.dbf' resize 100m
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value


Hmmm....maybe the issue is the table wound up in the recycle bin. if I don't use "purge" the table gets put in the recycle bin. So let's find it there:
SQL> select object_name, original_name, type from recyclebin;

no rows selected


Oh...uh...ok...now what? Let's check if any objects might actually exist in the datafile:

select *
from (
select owner, segment_name, segment_type, block_id
from dba_extents
where file_id = ( select file_id
  from dba_data_files
where file_name = '/opt/oracle/oradata01/delme.dbf' )
  order by block_id desc
)
  2    3    4    5    6    7    8    9   10  ;

no rows selected

Hmm...

So now I'll just try dropping it, I guess. Here's how much space it took up before:

oracle@frd-db01:~/product/10.2/db_1> ll /opt/oracle/oradata01/delme.dbf
-rw-r----- 1 oracle oinstall 38722871296 2013-12-03 05:31 /opt/oracle/oradata01/delme.dbf

and now we drop tablespace:
SQL> drop tablespace delme including contents and datafiles;

Tablespace dropped.

oracle@frd-db01:~/product/10.2/db_1> df -h | grep /opt/oracle/oradata01
                      100G   56G   45G  56% /opt/oracle/oradata01


And yay, I have space again, but I just wish I had gotten to the bottom of the mystery. Unfortunately, I simply don't have the time for that right now. Does anyone in my vast readership know what might have happened? Comments are welcome!



No comments: