OraFAQ Forum: SQL & PL/SQL » Ora-00604 while dropping user.

Home » SQL & PL/SQL » SQL & PL/SQL » Ora-00604 while dropping user. (Oracle Database 11g Release 11.1.0.7.0 - 64bit Production)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Ora-00604 while dropping user. [message #425220]

Thu, 08 October 2009 01:57

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

Michel Cadot
Messages: 68757
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

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

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

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

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

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 847 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

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

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

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

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

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed May 21 16:36:04 CDT 2025