When I’m trying to drop table then I’m getting error
SQL Error: ORA-00604: error occurred at recursive SQL level 2
ORA-01422: exact fetch returns more than requested number of rows
00604. 00000 - "error occurred at recursive SQL level %s"
*Cause: An error occurred while processing a recursive SQL statement
(a statement applying to internal dictionary tables).
*Action: If the situation described in the next error on the stack
can be corrected, do so; otherwise contact Oracle Support.
asked May 27, 2015 at 9:15
RaviRavi
30.6k42 gold badges119 silver badges171 bronze badges
9
One possible explanation is a database trigger that fires for each DROP TABLE statement. To find the trigger, query the _TRIGGERS dictionary views:
select * from all_triggers
where trigger_type in ('AFTER EVENT', 'BEFORE EVENT')
disable any suspicious trigger with
alter trigger <trigger_name> disable;
and try re-running your DROP TABLE statement
answered May 27, 2015 at 10:14
Frank SchmittFrank Schmitt
29.9k12 gold badges72 silver badges107 bronze badges
2
I noticed following line from error.
exact fetch returns more than requested number of rows
That means Oracle was expecting one row but It was getting multiple rows. And, only dual table has that characteristic, which returns only one row.
Later I recall, I have done few changes in dual table and when I executed dual table. Then found multiple rows.
So, I truncated dual table and inserted only row which X value. And, everything working fine.
answered May 28, 2015 at 6:58
RaviRavi
30.6k42 gold badges119 silver badges171 bronze badges
2
I know the post is old and solved, but maybe someone is facing or will face my situation, so I want to leave the aquired knowledge here, after deal with the error for a week. I was facing the error: «ORA-00604: error occurred at recursive SQL level 1″ , but with the internal error: » ORA-06502: error: character string buffer too smal numeric or value», this happened only when I try to logon the database, and using an specific driver, trying to connect from an Visual Studio C# application, the weirdest thing on that moment was that I connect from SQLDeveloper or TOAD and everything worked fine.
Later I discovered that my machine name had this format «guillermo-aX474b5», then I proceed to rename it like this «guillermo» without the «-» and the other stuff, and it worked!! Looks like in some drivers and situations, ORACLE Database does’nt like the «-» in the LogOn connection.
Hope it helps!
answered Oct 4, 2022 at 12:53
In my previous article, I have explained about the most common errors in Oracle. In This article, I will try to explain another most common error, which has been searched approximately 20000 times in a month by DBAs and developers. While working with a database and performing different scenarios of database every developer or dba might have faced error called as ORA-00604: error occurred at recursive SQL level 1. While working with databases I have frequently faced ORA-00604: recursive error and struggled to solve and debug this issue. I would like to share my experience working and debugging this error. This is most common error and very tricky to solve it.
A recursive SQL statement is a statement that is applied to internal dictionary table.
Why ORA-00604 error will come?
There may be multiple reasons for which this error will come. In this section, I will try to explain what will be possible root cause of this error. Because there are many, possible reasons for the error, Oracle simply states that if the situation described in the next error on the stack can be corrected, it should be corrected. Otherwise, the user should contact the Oracle support line.
Reason 1:
Table and view does not Exist
This may be the one possible cause of this error. If due to any reason if one of the table (system table of oracle) is deleted and user tries to insert or update the data in the table this error will occur.
Reason 2:
Trigger Error
This may be another cause of the error. If trigger attempting to insert the records in audit_log table and audit_log table is dropped by cleanup script then this kind of error will come. This kind of error will occur mostly in system triggers.
Reason 3:
User attempts to run newly created table
When user attempts to run the newly created table this error will occure.The package related to the newly created table needs to be compiled to resolve this error.
NO TIME TO READ CLICK HERE TO GET THIS ARTICLE
Resolution of the error:
I have explained that there is no specific reason of this error. There might be the different possible causes of this error, which I have explained above. In this section, I will try to explain the resolutions of this error.
Solution 1:
Check for table availability
Check for whether all tables used in the triggers are available or not in that oracle schema.If the table is not available then user needs to create the table.
Solution 2:
Trigger Issue Resolution
To check whether this issue is because of trigger execution you need to check:
Alter system set “_system_trig_enabled”=FALSE;
View all triggers where trigger_type is before each row and after each row:
SELECT * FROM dba_triggers
WHERE trigger_type not in (‘before each row’,’after each row’);
To find the most relevant triggers, filter the triggering_event column.
Find the trigger that is causing the problem and disable it or drop it to resolve the issue. Usually, this error occurs in the Oracle database by the system level triggers on DDL or SYSTEM events.
Solution 3:
New table creation issue
If this error will occur due to newly created table then user needs to check the related system packages of oracle and compile package specification and body once.
Example:
User needs to recompile DBMS_CDC_PUBLISH package. User needs to compile all invalid packages that are no longer viewed.So this may be the third possible solution to resolve this kind of error.
Hope you like this article.Please don’t forget to share it with everyone.
When I am trying to drop a tablespace as
drop tablespace HC0974888012317 including contents and datafiles cascade CONSTRAINTS;
I am getting this error
ORA-00604: error occurred at recursive SQL level 1 ORA-01555: snapshot
too old: rollback segment number 0 with name «SYSTEM» too small
Alfabravo
7,4846 gold badges46 silver badges81 bronze badges
asked Mar 21, 2017 at 15:06
1
I got the same error before. I solved it by using this.
Open the database and enter these commands.
SQL> startup mount
SQL> alter session set events '10015 trace name adjust_scn level 1';
SQL> alter database open resetlogs;
I hope it will solve the problem.
answered Mar 21, 2017 at 15:16
Hamza AnisHamza Anis
2,4271 gold badge26 silver badges36 bronze badges
ORA-00604
ORA-00604: ошибка случилась на рекурсивном уровне SQL «номер»
Причина:
Ошибка происходит при обработке рекурсивного оператора SQL (оператор применяется к внутренним словарным таблицам).
Действие:
Если эта ситуация объясняется в следующем сообщении об ошибке, то попробуйте исправить; в другом случае контакт с вашим представителем Customer Support.
- Новые
- Лучшие
- Все
Исправляем ошибку ORA-00604: error occured at recursive SQL level 1ORA-12705 в Oracle SQL Developer
Осваиваем Oracle и PL/SQL
ORA-00604: error occured at recursive SQL level 1ORA-12705: Cannot access NLS data files or invalid enviroment specified
Для исправления делаем следующие действия:
Открываем <папку_со_средой>idebinide.conf
Дописываем туда две строчки:
AddVMOption -Duser.language=en
AddVMOption -Duser.region=us
И всё!
Oracle
ошибка подключения
Roman
03 May 2014, 01:21
22960
3
0
0
Оставить первый комментарий:
- Популярное
Работа со строками в Oracle (PL/SQL)
Основные способы обработки строковых значений в Oracle. В этой публикации я приведу основные функции (читать далее…)
615
Функция Oracle TO_DATE (PL/SQL)
Функция TO_DATE — преобразует строку в переменную времени DATE. Синтаксис: TO_DATE(исходная_строка, (читать далее…)
354
Объединение выборок UNION, INTERSECT, MINUS в Oracle (PL/SQL)
В Oracle присутствует возможность объединять выборки. Для объединения используются операторы: UNION (читать далее…)
336
Работа с XML в Oracle PL/SQL (Часть 1)
В этой публикации я приведу основные способы работы с XML в Oracle, которые сам использую. Здесь буд (читать далее…)
331
XML в Oracle PL/SQL (Часть 2 — Выборки в виде XML)
В предыдущей публикации были рассмотрены некоторые приёмы манипуляции с XML в Oracle, теперь рассмот (читать далее…)
284