| Dual returns no rows [message #435649] |
Thu, 17 December 2009 04:23  |
rishab
Messages: 7
Registered: December 2009
Location: India
|
Junior Member |
|
|
Ok so this is how it started
One day we were experimenting and inserted a row into dual;
SQL> insert into dual values('A');
1 row inserted
A few days later we suddenly started facing problems in all DDL statements
and the error shown was this
ORA-00604: error occurred at recursive SQL level 1
ORA-01422: exact fetch returns more than requested number of rows
The recursive level did change on some accounts the max was
ORA-00604: error occurred at recursive SQL level 5
It was fixed by truncating the dual table;
SQL> truncate table dual;
Now DMLs work but this is the situation
_______________________________________________________
SQL> select * from dual;
no rows selected
SQL> select count(1) from dual;
COUNT(1)
----------
1
SQL> desc dual;
Name Null? Type
----------------------------------------- --------
DUMMY VARCHAR2(1)
_______________________________________________________
My Question is>
Will this be a problem in the future, as dual returns no rows?
Or any info on this will be really helpful.
|
|
|
|
|
|
| Re: Dual returns no rows [message #435654 is a reply to message #435649] |
Thu, 17 December 2009 04:50  |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Congratulations - you've done something very stupid, and them fixed it by doing something even more stupid.
Yes, I would anticipate it causing many varied and unpredictable problems.
If I were you, I'd insert a single row, containing the value 'X' into dual (which is what it had before) and make a mental note not to mess about with things that I didn't understand.
here is Tom Kyte on the subject:Quote:Let me just start by saying -- DUAL is owned by SYS. SYS owns the data dictionary,
therefore DUAL is part of the data dictionary. You are not to modify the data dictionary
via SQL ever -- wierd things can and will happen -- you are just demonstrating some of
them. We can make many strange things happen in Oracle by updating the data dictionary.
It is neither recommend, supported nor a very good idea.
|
|
|
|
| Re: Dual returns no rows [message #435655 is a reply to message #435652] |
Thu, 17 December 2009 04:51 |
rishab
Messages: 7
Registered: December 2009
Location: India
|
Junior Member |
|
|
What should or can I do to remedy this?
And what may be the consequences
The problem with dual is that, its not very widely described and or the consequences which it can cause. Neither on book nor online
|
|
|
|
|
|
| Re: Dual returns no rows [message #435657 is a reply to message #435655] |
Thu, 17 December 2009 04:55 |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
rishab wrote on Thu, 17 December 2009 10:51What should or can I do to remedy this? JRowBottom has already told you
Quote:And what may be the consequences You have already seen some of the consequences
Quote:
The problem with dual is that, its not very widely described and or the consequences which it can cause. Neither on book nor online
All you need to know is that it belongs to sys, is part of the data dicytionary and should NEVER be manipulated.
|
|
|
|
|
|
|
|
|
|
| Re: Dual returns no rows [message #435666 is a reply to message #435662] |
Thu, 17 December 2009 05:15 |
rishab
Messages: 7
Registered: December 2009
Location: India
|
Junior Member |
|
|
@above
Hello,
As you have seen these types of errors can you put anymore light on this?
Thanks to the person with the original answer but
I mean will insertion of 'X' will solve it completely or something else will surface later?
Sorry but I am still a little worried
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Dual returns no rows [message #436173 is a reply to message #435886] |
Tue, 22 December 2009 00:29 |
rishab
Messages: 7
Registered: December 2009
Location: India
|
Junior Member |
|
|
Because of this... I had another database (test) where I truncated the dual table and then inserted the 'X' and kept another one (the one originally with the problem) without an 'X'
been quite a few days but its working fine without the 'X'.
This is as an experimentation. If the original crashes then backup is there to recreate it from scratch.
So the chance is taken on it. Plus its also a test database so no business loss will be there
Lets see what it does.
|
|
|
|
| Re: Dual returns no rows [message #436174 is a reply to message #436173] |
Tue, 22 December 2009 00:37 |
 |
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
|
Senior Member |
|
|
Quote:Thanks everyone...
Now I know this was an idiotic move
And mental note taken : Never to screw with DATA DICTIONARY
Again and again the same thing ?
Read this once againPosted by Jrowbottom before.
Quote:Quote:
Let me just start by saying -- DUAL is owned by SYS. SYS owns the data dictionary,
therefore DUAL is part of the data dictionary. You are not to modify the data dictionary
via SQL ever -- wierd things can and will happen -- you are just demonstrating some of
them. We can make many strange things happen in Oracle by updating the data dictionary.
It is neither recommend, supported nor a very good idea.
sriram 
[Updated on: Tue, 22 December 2009 00:39] Report message to a moderator |
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
