Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 940
  • Last Modified:

Datafile deletion

Hello All,

I am getting "ORA-00060: deadlock detected while waiting for resource" while deleting a datafile.

when i checked in dba_data_files and V$datafile the ONLINE_STATUS/STATUS is in RECOVER mode.

I do not need this datafile so i tried to drop using the following command ..

ALTER TABLESPACE DATAP_LARGE DROP DATAFILE 'D:\ORACLE\ORADATA\DATAP\DATAP_LARGE_12.DBF';

I checked the Trace file geenrated for the deadlock, the Session which is in question is the session where i ran the command (SQLPLUS).

Can anybody throw some light on this.

Thanks

Rahul
0
Max4rDBA
Asked:
Max4rDBA
  • 4
  • 3
1 Solution
 
slightwv (䄆 Netminder) Commented:
Did you issue the offline drop command first?

ALTER DATABASE DATAFILE 'D:\ORACLE\ORADATA\DATAP\DATAP_LARGE_12.DBF' OFFLINE DROP;
0
 
Max4rDBAAuthor Commented:
Yes Slightwv , i have executed the offline drop command and then i executed the "ALTER TABLESPACE DATAP_LARGE DROP DATAFILE 'D:\ORACLE\ORADATA\DATAP\DATAP_LARGE_12.DBF'; " where i got the deadlock error.

0
 
slightwv (䄆 Netminder) Commented:
Check Metalink for some causes:

Unable to Drop a Datafile From the Tablespace Using Alter Tablespace Command [ID 1050261.1]
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
Max4rDBAAuthor Commented:
Thanks Slightvw, I saw the metalink just before migrating the tablespace i wanted to know if i put the database in the MOUNT stage and then execute the command will the drop datafile work.

Thanks

Rahul
0
 
slightwv (䄆 Netminder) Commented:
>> i wanted to know if i put the database in the MOUNT stage and then execute the command will the drop datafile work.

I have no idea.  If you are able to shutdown the database it's worth a try.
0
 
Max4rDBAAuthor Commented:
Deleted datafile after dropping the tablespace
0
 
Max4rDBAAuthor Commented:
Found own answer
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now