Oracle 10: dropping tablespace would not delete the datafiles
Hello!
I have a Problem dropping tablespaces to free space in the filesystem. In my environment its a Windows OS, the Metalink-search delivered Unix as well. The Problem occurs in an Oracle 10gR2 environment up to 10.2.0.4 and first tests with 11g seem to show, that the Problem persists.
I found Information on Metalink: Metalink BUG-IDs: 6153791, 6625888, 4941639 und Doc-IDs: 356708.1, 112218.1 the solution there is: restart the database.
Is there another way of dropping the tablespaces? Is it possible to stop the ADDM analysing unneeded data?
The "drop tablespace including contents and datafiles" statments returns without error. The DBA has no chance to find out any error, except parsing the alertlog! I tried to offline the tablespace and wait for some time but this does not work as well.
Even when you offline the tablespace before dropping you have the Problem.
Thank you for your Help!
Michael_K
APPENDIX 1: Statements:
The statements below reproduced the error.
create tablespace trullala datafile 'c:\oracle\oradata\db\mydatafile.dbf' size 1000M;
create table trullala (name varchar2(4000)) tablespace trullala;
insert into trullala (name) values ('name1');
insert into trullala (name) values ('name2');
insert into trullala (name) values ('name3');
insert into trullala (name) values ('name4');
commit;
execute dbms_job.gather_table_stats(ownname=>'SYSTEM',tabname=>'TRULLALA');
drop tablespace trullala including contents and datafiles cascade constraints;
APPENDIX 2: Alertlog
The alertlog shows: (retyped)
[...]
<DATE>
Completed: create tablespace trullala ...
<DATE>
drop tablespace trullala ...
<DATE>
Warning: Cannot delete file ...Trullala.dbf
<DATE>
Errors in file <tracefile>
ORA-01265 Unable to delete data <file>
ORA-27056: could not delete file
OSD-04024: Unable to delete file
O/S-Error: (OS 32) <German errormessage>: The process could not delete file it is locked by another process
Completed: drop tablespace trullalla ...
<DATE>
drop tablespace trullala ...
<DATE>
ORA-959 signalled during: drop tablespace trullala ...
[...]