Error Occurred At Recursive Sql Level 1 Drop User
Contents |
17, 2011 - 11:28 am UTC Category: Database – Version: 8.1.7 Latest Followup You Asked Dear Tom, I just simply can't drop one user in sqlplus. SQL> drop user a; drop user a * ERROR at line 1: ORA-00604: error occurred at recursive java.sql.sqlexception: ora-00604: error occurred at recursive sql level 1 SQL ldwvel 1 ORA-00942: table or view does not exist ORA-06512: at line 7 I also
Error Occurred At Recursive Sql Level 1 No Statement Parsed
checked there is no table or other objects created by a user. Can you solve the problem for me? Thanks, and we said...
Error Occurred At Recursive Sql Level 1 Ora-01000 Maximum Open Cursors Exceeded
hmm, ldwvel? hate cut and pastes that are not really cut and pastes. makes me suspicious :) Anyway, it appears you have a "DROP" trigger and on line 7, it is doing something "bad", something that is not
Ora-00604: Error Occurred At Recursive Sql Level 1 Ora-01003: No Statement Parsed
working. ops$tkyte@ORA920> create or replace trigger drop_trigger 2 before drop on database 3 declare 4 n number; 5 begin 6 execute immediate 'select count(*) from not_a_table' into n; 7 end; 8 / Trigger created. ops$tkyte@ORA920> ops$tkyte@ORA920> create user a identified by a; User created. ops$tkyte@ORA920> drop user a; drop user a * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-00942: table or view does not exist ORA-06512: at line 4 ops$tkyte@ORA920> ora-00604 error occurred at recursive sql level 1 ora-00942 table or view does not exist drop trigger drop_trigger; Trigger dropped. ops$tkyte@ORA920> drop user a; User dropped. So, look in DBA_TRIGGERS for a triggering_event = 'DROP' trigger and then fix it. Reviews Write a Review Trigger while dropping user July 07, 2008 - 11:58 am UTC Reviewer: Muhammad Khan from Islandia, NY USA Hi, That was a very good solution for 9.2 but here I'm having the same problem in Oracle 10.1.0.4 SQL> drop user khamu02; drop user khamu02 * ERROR at line 1: ORA-04098: trigger 'SYS.SDO_TOPO_DROP_FTBL' is invalid and failed re-validation How can I fix the code of SDO_TOPO_DROP_FTBL trigger here? Thanks, Khan Followup July 07, 2008 - 12:14 pm UTC what did you do in order to make this happen, what caused the trigger to go invalid, what did you change. show errors trigger SDO_TOPO_DROP_FTBL what does that show you (as sys of course) Trigger while dropping user July 07, 2008 - 12:41 pm UTC Reviewer: Muhammad Khan from Islandia, NY USA Hi Tom, Thanks for your reply. I really didnt do anything as far as the trigger is concerned. This is a new database. The catalog scripts (catalog.sql and catproc.sql) are already run. Now I'm trying to drop the user. Here is what I get when I try to recompile the trigger: SQL> alter trigger SYS.SDO_TOPO_DROP_FTBL compile; Warning: Trigger altered with compilation errors. SQL> show err Errors for TRIGGER SYS.SDO_TOPO_DROP_FTBL: LINE/CO
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring ora-00604 error occurred at recursive sql level 1 ora-04031 developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question ora-00604 error occurred at recursive sql level 1 ora-01450 maximum key length (3215) exceeded x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; ora-00604 error occurred at recursive sql level 1 sqlldr it only takes a minute: Sign up Not able to delete user in Oracle DB up vote 0 down vote favorite I am getting the error while deleting user from oracle db. The error message is not very helpful to https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:13273199418308 diagnose this issue. Please let me know if you are aware of this issue. SQL> drop user sshil_lrg_2 cascade; drop user sshil_lrg_2 cascade * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-00942: table or view does not exist ORA-06512: at line 36 oracle11g share|improve this question edited Jan 28 '14 at 12:31 asked Jan 28 '14 at 12:16 user2032118 85214 This seems relavant: community.oracle.com/message/9507589#9507589 (trigger getting in the way) –selalerer Jan 28 http://stackoverflow.com/questions/21405461/not-able-to-delete-user-in-oracle-db '14 at 12:32 add a comment| 2 Answers 2 active oldest votes up vote 0 down vote The error message ORA-00942: table or view does not exist indicates that some software tries to execute something like drop table I_DO_NOT_EXIST. However, you are not dropping a table but a user. Looking better at the error message, you will also see ORA-00604 and line 36. This is indication that your original SQL statement caused other SQL (ORA-00604) and PL/SQL (line 36) to be triggered. Probably you have a database trigger that executes on DDL statements. Check the contents of user_triggers of the user sys and disable/debug these trigger. Writing a good trigger that fires on DDL is non-trivial, since you need to ensure that you don't lock yourself out ever. share|improve this answer answered Jan 30 '14 at 16:15 Guido Leenders 2,0561726 @user2032118 hi did this help solve your problem? If not please add what was not successful to your question. –Guido Leenders Jan 31 '14 at 6:03 The OP used a DROP USER {name} CASCADE command. This attempts to delete all of the user's objects. The error is caused by a missing table(s) associated with the Oracle instance. This is (IMHO) an Oracle bug since this can be reproduced with a plain-vanilla install and no (operator) triggers created. See: community.oracle.com/thread/1048567?tstart=0 –Darrell Teague Oct 26 '14 at 6:03 add a comment| up vote 0 d
thought of sharing them. It was a custom schema, it had few AQ objects as well. At first I tried to remove https://troubleshooot.wordpress.com/2011/10/11/drop-user-cascade-problems/ queues objects before trying drop user cascade using below steps. exec dbms_aqadm.stop_queue(queue_name https://coderanch.com/t/81039/Oracle-OAS/error-droping-user-oracle => 'XXU.
This Site Careers Other all forums Forum: Oracle/OAS getting error while droping the user in oracle. Perumalla LakshmanaRao Greenhorn Posts: 9 posted 10 years ago Hi, I have created some new users in the database. I want to drop the user that i have created earlier.But i am getting the following error. SQL> drop user cms_common cascade; drop user cms_common cascade * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-00942: table or view does not exist Can you pls help me out for this problem as soon as possible. sachin K Kansal Greenhorn Posts: 9 posted 10 years ago There should be an "after servererror" trigger on the database and it is also committing in it. use the following query to find that trigger name. select owner, trigger_name from dba_triggers where trigger_type = 'AFTER EVENT'; What you can do is, either drop that trigger or you can make it as an Autonomus Transaction. For all this you should have the required previliges or a DBA role assigned. Sachin Kumar Kansal DBA (Oracle), Database Migration Consultant Perumalla LakshmanaRao Greenhorn Posts: 9 posted 10 years ago Hi Sachin, Whatever query that you have provided,we tried with that one and we got the following rows for that query SQL> conn system/******@fiu; Connected. SQL> select owner, trigger_name 2 from dba_triggers 3 where trigger_type = 'AFTER EVENT'; OWNER TRIGGER_NAME ------------------------------ ------------------------------ SYS AURORA$SERVER$STARTUP SYS AW_DROP_TRG SYS CDC_CREATE_CTABLE_AFTER SYS SYS_LOGON MDSYS SDO_DROP_USER After that i tried to drop the triggers.But i am getting the following error for each trigger. SQL> drop trigger AURORA$SERVER$STARTUP; drop trigger AURORA$SERVER$STARTUP * ERROR at line 1: ORA-04080: trigger 'AURORA$SERVER$STARTUP' does not exist SQL> drop trigger AW_DROP_TRG; drop trigger AW_DROP_TRG * ERROR