Home » RDBMS Server » Server Administration » Dual returns no rows (ORACLE 10g)
Dual returns no rows [message #435649] Thu, 17 December 2009 04:23 Go to next message
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 #435652 is a reply to message #435649] Thu, 17 December 2009 04:45 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Quote:
Will this be a problem in the future, as dual returns no rows?
For sure! DUAL is a part of the data dictionary.
Re: Dual returns no rows [message #435654 is a reply to message #435649] Thu, 17 December 2009 04:50 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #435656 is a reply to message #435655] Thu, 17 December 2009 04:55 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

rishab wrote on Thu, 17 December 2009 11:51

The problem with dual is that, its not very widely described and or the consequences which it can cause. Neither on book nor online
So ? You should never modify the data dictionary.
Re: Dual returns no rows [message #435657 is a reply to message #435655] Thu, 17 December 2009 04:55 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
rishab wrote on Thu, 17 December 2009 10:51
What 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 #435659 is a reply to message #435655] Thu, 17 December 2009 04:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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

As all the Oracle code, it is not documented and you don't try to modify it even with an hexa editor, so it is the same thing with SYS tables, you don't modify them in any way.

Regards
Michel
Re: Dual returns no rows [message #435661 is a reply to message #435657] Thu, 17 December 2009 04:59 Go to previous messageGo to next message
rishab
Messages: 7
Registered: December 2009
Location: India
Junior Member
Thanks everyone...
Now I know this was an idiotic move Razz
And mental note taken : Never to screw with DATA DICTIONARY

And not being a DBA this was even worse for us Razz


Thanks to all
Re: Dual returns no rows [message #435662 is a reply to message #435661] Thu, 17 December 2009 05:02 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>And not being a DBA this was even worse for us
Not at all.
I have seen DBA's do this all the time. With pride.

[Updated on: Thu, 17 December 2009 05:03]

Report message to a moderator

Re: Dual returns no rows [message #435666 is a reply to message #435662] Thu, 17 December 2009 05:15 Go to previous messageGo to next message
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 #435670 is a reply to message #435666] Thu, 17 December 2009 05:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I mean will insertion of 'X' will solve it completely or something else will surface later?

Only Oracle knows the answer of this question.
I advice you to immediatly stop others work, insert the row and restart the database.

Regards
Michel
Re: Dual returns no rows [message #435690 is a reply to message #435670] Thu, 17 December 2009 06:28 Go to previous messageGo to next message
rishab
Messages: 7
Registered: December 2009
Location: India
Junior Member
Thanks again.. and OK I will do that.
Insert an X
then stop all work
then restart database


Hope nothing else goes wrong

Re: Dual returns no rows [message #435691 is a reply to message #435690] Thu, 17 December 2009 06:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Actually you should stop others work immediately as your database is not properly working and result of works might be wrong.

Regards
Michel
Re: Dual returns no rows [message #435886 is a reply to message #435649] Fri, 18 December 2009 09:43 Go to previous messageGo to next message
Soph
Messages: 1
Registered: December 2009
Junior Member
i agree
Re: Dual returns no rows [message #436173 is a reply to message #435886] Tue, 22 December 2009 00:29 Go to previous messageGo to next message
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 Go to previous messageGo to next message
ramoradba
Messages: 2454
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 Smile

[Updated on: Tue, 22 December 2009 00:39]

Report message to a moderator

Re: Dual returns no rows [message #436191 is a reply to message #436174] Tue, 22 December 2009 01:22 Go to previous messageGo to next message
Littlefoot
Messages: 20894
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
So what? The man has said that this is an experiment; he has got a spare database that can be destroyed and nothing serious would happen. If he wants to see what consequences takes messing up with the DUAL table, so be it. I just hope he'll post a conclusion once the experiment is over.
Re: Dual returns no rows [message #436240 is a reply to message #436191] Tue, 22 December 2009 05:22 Go to previous messageGo to next message
rishab
Messages: 7
Registered: December 2009
Location: India
Junior Member
@Littlefoot
Thanks for the support.


And I'll get back with the update on the problem/s faced.
Let the experiments begin Cool
Re: Dual returns no rows [message #436244 is a reply to message #436240] Tue, 22 December 2009 05:31 Go to previous message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
So upto that time i should be in this position.
./fa/1985/0/
And do this
./fa/1605/0/


Goodluck ./fa/2115/0/

sriram Smile
Previous Topic: ORA-12154: error
Next Topic: change database global name without using a dot
Goto Forum:
  


Current Time: Mon Dec 05 08:48:02 CST 2016

Total time taken to generate the page: 0.22783 seconds