[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Datafile deletion

Posted on 2010-11-18
7
Medium Priority
?
936 Views
Last Modified: 2012-05-10
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
Comment
Question by:Max4rDBA
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34164798
Did you issue the offline drop command first?

ALTER DATABASE DATAFILE 'D:\ORACLE\ORADATA\DATAP\DATAP_LARGE_12.DBF' OFFLINE DROP;
0
 

Author Comment

by:Max4rDBA
ID: 34166327
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34166556
Check Metalink for some causes:

Unable to Drop a Datafile From the Tablespace Using Alter Tablespace Command [ID 1050261.1]
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:Max4rDBA
ID: 34166626
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34167097
>> 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
 

Accepted Solution

by:
Max4rDBA earned 0 total points
ID: 34420978
Deleted datafile after dropping the tablespace
0
 

Author Closing Comment

by:Max4rDBA
ID: 34434243
Found own answer
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Via a live example, show how to take different types of Oracle backups using RMAN.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question