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:
Post a Comment