Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: dual

RE: dual

From: <AdamDonahue_at_maximus.com>
Date: Thu, 30 Oct 2003 15:24:31 -0800
Message-ID: <F001.005D51BA.20031030152431@fatcity.com> 





Refer to 



http://asktom.oracle.com/pls/ask/f?p=4950:8:5481819534388360937::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:1562813956388,



for the answers to some of your questions below.  I think it's safe to say 
that DUAL is a rather 'magic' table.  The normal rules for DML don't work 
on it; I'd not even posit that 'rownum < 1' is in there anywhere.  More 
likely it's intercepted by the optimizer and treated specially, without 
any actual data access, in certain contexts.



Adam






Jared.Still_at_radisys.com 


Sent by: ml-errors_at_fatcity.com


10/30/2003 02:49 PM


Please respond to


ORACLE-L_at_fatcity.com




To


Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
cc



Subject


RE: dual









I encountered the same problem once many years ago, for the same reason. 



Quite a pickle for a newbie - OWW bailed me out on that one.  :) 



Jraed 







"M Rafiq" <rafiq9857_at_hotmail.com> 


Sent by: ml-errors_at_fatcity.com 


 10/30/2003 12:29 PM 


 Please respond to ORACLE-L 

        
        To:        Multiple recipients of list ORACLE-L 
<ORACLE-L_at_fatcity.com> 
        cc:         
        Subject:        RE: dual







I have observed  2 rows in dual till version 7.3.4. All application using 
dual in their logic having more than 2 rows were giving wrong results. 
Quick 


fix was to track it and delete more than one row(s).  Duplicate import of 
sys/system stuff were known to be culprit.



Regards


Rafiq






Reply-To: ORACLE-L_at_fatcity.com


To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
Date: Thu, 30 Oct 2003 11:24:24 -0800



I have seen many databases crash, yes crash, when dual had more than one 
row


or less than one?



Why you say?  As someone pointed out, this was an internal table to the
kernel so Oracle used it as they felt.  It was and still is considered a
heartbeat mechanism within the kernel.  In other words, don't mess with 
it.



Thank You



Stephen P. Karniotis


Technical Alliance Manager


Compuware Corporation

Direct:          (313) 227-4350
Mobile:         (248) 408-2918




Email: Stephen.Karniotis_at_Compuware.com


Web:  www.compuware.com



-----Original Message-----



Sent: Thursday, October 30, 2003 2:05 PM
To: Multiple recipients of list ORACLE-L



Do you think it will work if it has no rows ?



Waleed


-----Original Message-----



Sent: Thursday, October 30, 2003 1:44 PM
To: Multiple recipients of list ORACLE-L



You may find this interesting.



Looks like a 'where rownum = 1' is always imposed on dual.



Same results on 8.1.7.4 and 9.2.0.4



Don't try this on anything other than a trashable test database.



Jared





10:42:04 dv03>@dt
10:42:05 dv03>
10:42:05 dv03>set echo on
10:42:05 dv03>
10:42:05 dv03>create table jkstill.dual as select * from sys.dual;





Table created.



10:42:05 dv03>


10:42:05 dv03>select * from jkstill.dual;



D


-



X



1 row selected.



10:42:05 dv03>


10:42:05 dv03>drop table jkstill.dual;



Table dropped.



10:42:05 dv03>


10:42:05 dv03>insert into sys.dual values('Y');



1 row created.



10:42:05 dv03>insert into sys.dual values('Z');



1 row created.



10:42:05 dv03>


10:42:05 dv03>commit;



Commit complete.



10:42:05 dv03>


10:42:05 dv03>select * from sys.dual;



D


-



X



1 row selected.



10:42:05 dv03>


10:42:05 dv03>create table jkstill.dual as select * from sys.dual;



Table created.



10:42:05 dv03>


10:42:05 dv03>select * from jkstill.dual;



D


-



X


Y


Z



3 rows selected.



10:42:05 dv03>


10:42:05 dv03>drop table jkstill.dual;



Table dropped.



10:42:05 dv03>


10:42:05 dv03>delete from sys.dual;



1 row deleted.



10:42:05 dv03>delete from sys.dual;



1 row deleted.



10:42:05 dv03>delete from sys.dual;



1 row deleted.



10:42:05 dv03>


10:42:05 dv03>insert into sys.dual values('X');



1 row created.



10:42:05 dv03>commit;



Commit complete.


10:42:05 dv03>
10:42:05 dv03>
10:42:05 dv03>create table jkstill.dual as select * from sys.dual;





Table created.



10:42:05 dv03>


10:42:05 dv03>select * from jkstill.dual;



D


-



X



1 row selected.



10:42:05 dv03>


10:42:05 dv03>drop table jkstill.dual;



Table dropped.



10:42:05 dv03>








<bulbultyagi_at_now-india.net.in>


Sent by: ml-errors_at_fatcity.com


 10/30/2003 08:54 AM


 Please respond to ORACLE-L



        To:        Multiple recipients of list ORACLE-L
<ORACLE-L_at_fatcity.com>

        cc:
        Subject:        dual







List, here is a rtfm question which I was scared to ask, but its
bothering me too much so I just can't stay quite :



"why do multiple inserts into sys.dual complete sucessfully when connected
as


sysdba, but a subsequent select * from dual show only 1 row ?"




--



Please see the official ORACLE-L FAQ: http://www.orafaq.net


--



Author: <bulbultyagi_at_now-india.net.in


 INET: bulbultyagi_at_now-india.net.in


Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services


---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message


to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).







The contents of this e-mail are intended for the named addressee only. It
contains information that may be confidential. Unless you are the named
addressee or an authorized designee, you may not copy or use it, or 
disclose


it to anyone else. If you received it in error please notify us 
immediately


and then destroy it.





Enjoy MSN 8 patented spam control and more with MSN 8 Dial-up Internet 
Service.  Try it FREE for one month! http://join.msn.com/?page=dept/dialup



-- 



Please see the official ORACLE-L FAQ: http://www.orafaq.net


-- 



Author: M Rafiq


 INET: rafiq9857_at_hotmail.com


Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services


---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message


to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).





-- 



Please see the official ORACLE-L FAQ: http://www.orafaq.net


-- 



Author: 


  INET: AdamDonahue_at_maximus.com


Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services


---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message


to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Thu Oct 30 2003 - 17:24:31 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US