RE: Unable to drop a datafile

  • From: "Kamran Agayev (ICT/SNO)" <itakamran@xxxxxxxxxxxx>
  • To: "Amir.Hameed@xxxxxxxxx" <Amir.Hameed@xxxxxxxxx>, "Mark W. Farnham" <mwf@xxxxxxxx>, "Mark.Bobak@xxxxxxxxxxxx" <Mark.Bobak@xxxxxxxxxxxx>, "'Ryan January'" <rjjanuary@xxxxxxxxxxxxxxxx>
  • Date: Thu, 6 Sep 2012 11:00:39 +0000

And which PL/SQL block Oracle tries to execute? 
Have you raised SR? 

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Hameed, Amir
Sent: Thursday, September 06, 2012 3:52 PM
To: Mark W. Farnham; Mark.Bobak@xxxxxxxxxxxx; 'Ryan January'
Cc: oracle-l@xxxxxxxxxxxxx
Subject: RE: Unable to drop a datafile

I had already checked the SQL trace and found that it was a PL/SQL block that 
Oracle was trying to execute. I have also tried with recycle-bin turned off and 
cleaned but the outcome was the same. In one of my test databases, which is 
also 11.1.0.7, I added a small datafile to an existing tablespace and then 
immediately tried to drop it and got the same error. So, this is most likely a 
bug. This is an Oracle ERP system and the tablespaces are user-managed and not 
truly locally managed.

Thanks
-----Original Message-----
From: Mark W. Farnham [mailto:mwf@xxxxxxxx]
Sent: Wednesday, September 05, 2012 10:19 PM
To: Mark.Bobak@xxxxxxxxxxxx; Hameed, Amir; 'Ryan January'
Cc: oracle-l@xxxxxxxxxxxxx
Subject: RE: Unable to drop a datafile

What Mark said about sql_trace is a really good idea. Also, I wonder whether 
you need to purge your recyclebin or this has anything to do with as of date 
processing.

How much remains undeleted in the entire tablespace? Is copying what you want 
to keep to a new tablespace feasible as a workaround? Depending on the sizes, 
that may take less time than an sr.

Good luck.

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Bobak, Mark
Sent: Wednesday, September 05, 2012 8:27 PM
To: Amir.Hameed@xxxxxxxxx; Ryan January
Cc: oracle-l@xxxxxxxxxxxxx
Subject: RE: Unable to drop a datafile

If you enable sql_trace, it may reveal the recursive sql that is catching that 
error.  but you'll probably need to open an sr.
-Mark




Sent from my Samsung Galaxy NoteT, an AT&T LTE smartphone

"Hameed, Amir" <Amir.Hameed@xxxxxxxxx> wrote:
Thanks Ryan. I have tried it with both absolute path and file_id but the 
outcome was the same.

-----Original Message-----
From: Ryan January [mailto:rjjanuary@xxxxxxxxxxxxxxxx]
Sent: Wednesday, September 05, 2012 5:43 PM
To: Hameed, Amir
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: Unable to drop a datafile

I have seen similar error messages once in a great while where character data 
was found and numeric data was expected.  If I recall correctly you have to 
specify the full path of the datafile when attempting to drop it when not using 
the file number. Did you only supply the file name
('DATAFILE_NAME') in the command or did you give it the full path ( 
'/path/to/DATAFILE_NAME', '+DATA/<db>/DATAFILE/DATAFILE_NAME' , etc...) ?

Thanks,
Ryan

On 09/05/2012 09:34 AM, Hameed, Amir wrote:
> Hi All,
> I am trying to drop a datafile which is empty and contains no extent 
> and
is not the first datafile of the tablespace either. I am getting the following 
error when I execute the drop command:
> alter tablespace <TS_NAME> drop datafile 'DATAFILE_NAME' ; alter 
> tablespace .... drop datafile ....
> *
> ERROR at line 1:
> ORA-00604: error occurred at recursive SQL level 1
> ORA-01426: numeric overflow
>
> The DB version is 11.1.0.7. Has anyone seen this issue and knows how 
> to
resolve it?
>
> Thanks
> Amir
>
> --
> //www.freelists.org/webpage/oracle-l
>
>


----------------------------------------------------------------------------
-----------
This email is intended solely for the use of the addressee and may contain 
information that is confidential, proprietary, or both.
If you receive this email in error please immediately notify the sender and 
delete the email.
----------------------------------------------------------------------------
-----------

--
//www.freelists.org/webpage/oracle-l




--
//www.freelists.org/webpage/oracle-l


--
//www.freelists.org/webpage/oracle-l


--
//www.freelists.org/webpage/oracle-l


Other related posts: