Home » SQL & PL/SQL » SQL & PL/SQL » Ora-00604 while dropping user. (Oracle Database 11g Release 11.1.0.7.0 - 64bit Production)
Ora-00604 while dropping user. [message #425220] Thu, 08 October 2009 01:57 Go to next message
ali_sakar
Messages: 22
Registered: February 2009
Location: Mumbai
Junior Member
Dear All,

I want to drop a user but the drop command is failing with Ora-00604.

SQL> drop user tlel cascade;
drop user tlel cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-12083: must use DROP MATERIALIZED VIEW to drop "TLEL"."EMP_DETAILS"

I created a script and dropped all the objects in that user. But still its showing me this error. I have a Materialized View in that user which is giving me issues. Kindly help its urgent.

Ali.

Report message to a moderator

Re: Ora-00604 while dropping user. [message #425224 is a reply to message #425220] Thu, 08 October 2009 02:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
So drop the mview first.

Regards
Michel

Report message to a moderator

Re: Ora-00604 while dropping user. [message #425226 is a reply to message #425224] Thu, 08 October 2009 02:36 Go to previous messageGo to next message
ali_sakar
Messages: 22
Registered: February 2009
Location: Mumbai
Junior Member
Already tried that. Below is the o/p.

SQL> drop MATERIALIZED VIEW tlel.EMP_DETAILS;
drop MATERIALIZED VIEW tlel.EMP_DETAILS
*
ERROR at line 1:
ORA-12003: materialized view "TLEL"."EMP_DETAILS" does not exist

Report message to a moderator

Re: Ora-00604 while dropping user. [message #425228 is a reply to message #425226] Thu, 08 October 2009 02:50 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
I think, you should restart the database.

regards,
Delna

Report message to a moderator

Re: Ora-00604 while dropping user. [message #425235 is a reply to message #425220] Thu, 08 October 2009 03:38 Go to previous messageGo to next message
ali_sakar
Messages: 22
Registered: February 2009
Location: Mumbai
Junior Member
Restarting also did not work. Still getting the same error. Any other soln.? Its very urgent pls.

Ali.

Report message to a moderator

Re: Ora-00604 while dropping user. [message #425239 is a reply to message #425235] Thu, 08 October 2009 04:03 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What does this return:
SELECT owner,object_name,object_type
FROM all_objects
WHERE object_name = 'EMP_DETAILS';

Report message to a moderator

Re: Ora-00604 while dropping user. [message #425254 is a reply to message #425220] Thu, 08 October 2009 04:54 Go to previous messageGo to next message
ali_sakar
Messages: 22
Registered: February 2009
Location: Mumbai
Junior Member
refer to the uploaded file for the o/p of the select
  • Attachment: emp_det.csv
    (Size: 5.55KB, Downloaded 860 times)

Report message to a moderator

Re: Ora-00604 while dropping user. [message #425256 is a reply to message #425254] Thu, 08 October 2009 05:21 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Weird - it looks like the MVIEW has gone for that user, but has left the table behind.
Connect as the affected user, run this and post the results:
DROP TABLE emp_details;
DROP MATERIALIZED VIEW emp_details;

Report message to a moderator

Re: Ora-00604 while dropping user. [message #425263 is a reply to message #425220] Thu, 08 October 2009 05:49 Go to previous messageGo to next message
ali_sakar
Messages: 22
Registered: February 2009
Location: Mumbai
Junior Member
Connected to:
Oracle Database 11g Release 11.1.0.7.0 - 64bit Production

SQL> DROP TABLE emp_details;
DROP TABLE emp_details
*
ERROR at line 1:
ORA-12083: must use DROP MATERIALIZED VIEW to drop "TLEL"."EMP_DETAILS"


SQL> DROP MATERIALIZED VIEW emp_details;
DROP MATERIALIZED VIEW emp_details
*
ERROR at line 1:
ORA-12003: materialized view "TLEL"."EMP_DETAILS" does not exist


SQL>

Report message to a moderator

Re: Ora-00604 while dropping user. [message #425336 is a reply to message #425220] Thu, 08 October 2009 11:49 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
I believe this is a known bug. I have seen cases where materialized view metadata can be half updated by oracle when you try to drop it, depending upon the type of materialized view and options. My experience was on an 8i database. Thus the reason for the strange behavior. Part of Oracle thinks the MVIEW still exists, while other parts think it does not.

I cannot recall if there was a fix. I believe I figured something but to my shagrin I cannot remember.

Submit a TAR.

Also, tell us what you know about the materialized view. I seem to recall the problems I had were related to specific options used.

ah...it is coming back to me now... Was this view created using the ON PREBUILT TABLE clause? This is where I had this problem. Try dropping the underlying table and or unregistering the MVIEW. There are ways to register a MVIEW manually and unregister it too (at least that is what I recall).

Kevin

Report message to a moderator

Re: Ora-00604 while dropping user. [message #425393 is a reply to message #425220] Thu, 08 October 2009 23:51 Go to previous messageGo to next message
ali_sakar
Messages: 22
Registered: February 2009
Location: Mumbai
Junior Member
Thanx for the reply Kevin... but i havent used the prebuilt table clause.
I have used ON COMMIT REFRESH clause and I have already dropped all the underlying tables and objects using a script. Now there are no objects existing in that schema.

Once before this had happened and I used some DBMS_AQ package and it got solved then but now I m not able to recollect what steps I followed then...

And how do you register/unregister mviews?

Ali.

Report message to a moderator

Re: Ora-00604 while dropping user. [message #425477 is a reply to message #425220] Fri, 09 October 2009 05:08 Go to previous message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Yeah, so this sounds like that bug to me.

As I recall there DBMS_MVIEW had stuff.

dbms_mview

registier_mview

unregister_mview

I am not at all certain this will fix the problem, so in the end you may be calling Oracle support.

Good luck, Kevin

Report message to a moderator

Previous Topic: plsql
Next Topic: How to export packages???
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Jul 14 20:52:50 CDT 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.