If you did not commit the inserts to dual, then everyone will only see one
row.
If you committed the data and still one row appeared, then you must have a
row defined for dual.
Suggestion: Don't add more rows to dual if you value your dba life.
Thank You
Stephen P. Karniotis
Technical
It's because we designed it that way. In earlier releases (can't remember
the specific version we brought this in with), you could put more than one
row in DUAL and then select them all, but all the things that should have
returned only one row then failed with a 1403 (?) error - single row query
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 dv03set echo
Do you think it
will work if it has no rows ?
Waleed
-Original Message-From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]Sent: Thursday, October 30, 2003
1:44 PMTo: Multiple recipients of list ORACLE-LSubject:
Re: dualYou may find
this interesting. Looks like a
'where
: Thursday, October 30, 2003
2:05 PM
To: Multiple recipients of list
ORACLE-L
Subject: RE: dual
Do you think
it will work if it has no rows ?
Waleed
-Original
Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]
Sent: Thursday, October 30, 2003
1:44 PM
To: Multiple recipients of list
Didn't try no rows. I imagine all kinds of things would break.
Khedr, Waleed [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
10/30/2003 11:04 AM
Please respond to ORACLE-L
To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:RE: dual
Do you
Thanks
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Thursday, October 30, 2003 23:49
It's because we designed it that way. In earlier releases (can't remember
the specific version we brought this in with), you could put more than one
row in
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:
-Original Message-From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]Sent: Thursday, October 30, 2003
3:09 PMTo: Multiple recipients of list ORACLE-LSubject:
RE: dual
Didn't try no rows. I imagine all
kinds of things would break. [Shrek]
yes
they will... trust me
ORACLE-L [EMAIL PROTECTED]
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
]
To
Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
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 [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
10/30/2003 12
Hi,
Is the link misspelled? It says Page cannot be found..
Jai
Yechiel Adar [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
12/17/02 11:34 AM
Please respond to ORACLE-L
To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:Re: DUAL
So listen
recipients of list ORACLE-L
[EMAIL PROTECTED]
cc:
Subject:Re: DUAL
So listen up: For better April 1'st - add another record to dual. That is
known to cause a deteriorating effect on developers.
:-)
Yechiel Adar
Mehish
- Original Message
Title: RE: DUAL
That's
it. I'm inventing my own dummy table.
CREATE
TABLE dummy (DUAL varchar2(20));
-Original Message-From: Yechiel Adar
[mailto:[EMAIL PROTECTED]]Sent: Monday, December 16, 2002 10:04
PMTo: Multiple recipients of list ORACLE-LSubject: Re:
DUAL
So
To: Multiple recipients of list ORACLE-L
Sent: Monday, December 16, 2002 6:29 PM
Subject: RE: DUAL
Careful. We are listening.
;-)
(that woulda messed me up for a while)
-Original Message-
From: Babette Turner-Underwood
[mailto:[EMAIL PROTECTED
-
From: Jeremy Pulcifer
To: Multiple recipients of list ORACLE-L
Sent: Monday, December 16, 2002 6:29 PM
Subject: RE: DUAL
Careful. We are listening.
;-)
(that woulda messed me up for a while)
-Original Message-
From: Babette Turner
, December 16, 2002 6:29 PM
Subject: RE: DUAL
Careful. We are listening.
;-)
(that woulda messed me up for a while)
-Original Message-
From: Babette Turner-Underwood
[mailto:[EMAIL PROTECTED]]
Sent: Friday, December 13, 2002 12:04 PM
To: Multiple
effect on developers.
:-)
Yechiel Adar
Mehish
- Original Message -
From: Jeremy Pulcifer
To: Multiple recipients of list ORACLE-L
Sent: Monday, December 16, 2002 6:29 PM
Subject: RE: DUAL
Careful. We are listening.
;-)
(that woulda messed me up
Paquette [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
12/17/2002 10:26 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:Re: DUAL
I've seen that when I was a developper ...using
Oracle 6/vax vms and Sql*Forms
Sent: Monday, December 16, 2002 6:29 PM
Subject: RE: DUAL
Careful. We are listening.
;-)
(that woulda messed me up for a while)
-Original Message-
From: Babette Turner-Underwood
[mailto:[EMAIL PROTECTED]]
Sent: Friday, December 13, 2002 12:04 PM
Title: RE: DUAL
Careful. We are listening.
;-)
(that woulda messed me up for a while)
-Original Message-
From: Babette Turner-Underwood [mailto:[EMAIL PROTECTED]]
Sent: Friday, December 13, 2002 12:04 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: DUAL
Thanks
Title: RE: DUAL
So listen up: For better April 1'st - add another
record to dual. That is known to cause a deteriorating effect on
developers.
:-)
Yechiel AdarMehish
- Original Message -
From:
Jeremy Pulcifer
To: Multiple recipients of list ORACLE-L
Sent: Monday
Thanks for the great suggestion!
I will remember to use that one when
those developers start getting too lippy ;-)
Babette
-Original Message-
Richard
Sent: Thursday, December 12, 2002 4:14 PM
To: Multiple recipients of list ORACLE-L
Howdy,
I believe that truncating DUAL has also been
Hallo,
Dave- what is, Dual for Dummies?
-Original Message-
Sent: Thursday, December 12, 2002 1:14 PM
To: Multiple recipients of list ORACLE-L
Hallo,
Problems with dual, please read link
http://searchdatabase.techtarget.com/tip/0,289483,sid13_gci831497,00.html
TIA,
Dave
--
Please
Howdy,
I believe that truncating DUAL has also been known to leave developers
scratching their head. Of course I could never recommend that anyone try
it though.
Ditto for PostgreSQL. For portability you can create a dual table but you
have to ensure that it only has one row. ;-) I finding that portability
between PostgreSQL and Oracle is easier than most... same to_date, to_char
functions, and stuff. Seems like that's part of Postgres' strategy.
We are using PostgreSQL, but still have problems with statistics.
Sometimes after VACUUM ANALYZE optimizer doesn't use indexes
and performance goes down.
JP
On Friday 20 September 2002 18:43, you wrote:
Ditto for PostgreSQL. For portability you can create a dual table but you
have to ensure
Jan,
Which version of PostgrSQL are you using?
--Walt Weaver
Bozeman, Montana
-Original Message-
Sent: Friday, September 20, 2002 11:35 AM
To: Multiple recipients of list ORACLE-L
DBA/Developer/
We are using PostgreSQL, but still have problems with statistics.
Sometimes after
7.0
JP
On Friday 20 September 2002 20:29, you wrote:
Jan,
Which version of PostgrSQL are you using?
--Walt Weaver
Bozeman, Montana
-Original Message-
Sent: Friday, September 20, 2002 11:35 AM
To: Multiple recipients of list ORACLE-L
DBA/Developer/
We are using
Thanks, Jan.
Are you still at work?
--Walt (1.85254E-16ly tall) Weaver
-Original Message-
Sent: Friday, September 20, 2002 1:59 PM
To: Multiple recipients of list ORACLE-L
DBA/Developer/
7.0
JP
On Friday 20 September 2002 20:29, you wrote:
Jan,
Which version of PostgrSQL are you
I followed the instructions but I get the error below. I do not get the same error
when I select from dual. And yes I did to the public grant. And other clues? DB
8.1.7.3
Thanks Kathy
Execution Plan
Just wondering here but has anyone tried doing that select from something
like v$database;
Statistics
--
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
...if you're going to query from an account other than SYS, then please SET
AUTOTRACE TRACEONLY STATISTICS (instead of SET AUTOTRACE ON) so it doesn't
try to do an EXPLAIN PLAN. You cannot grant SELECT on X$ tables to anyone
other than SYS, which affects EXPLAIN PLAN also...
- Original
But I don't get this error when I query from dual just from the new v$dual.
Kathy
-Original Message-
Sent: Friday, August 02, 2002 12:54 PM
To: Multiple recipients of list ORACLE-L
...if you're going to query from an account other than SYS, then please SET
AUTOTRACE TRACEONLY
Title: RE: DUAL revisited
-Original Message-
From: Kathy Duret [mailto:[EMAIL PROTECTED]]
But I don't get this error when I query from dual just from
the new v$dual.
That's because your v$dual view is assessing a SYS.X$ table. To do an explain plan on a SYS.X$ table you have
Correct. DUAL is a table owned by SYS for which SELECT permissions have
been granted to PUBLIC. V$DUAL is a view on a special fixed table named
X$DUAL. The SELECT permissions here are granted on the *view*; they cannot
be granted on a fixed table (i.e. X$ table). So, you're queries succeed
Sorry brain dead. Been working day and night for 5 days. Not much sleep. Makes
perfect sense now.
Kathy
-Original Message-
Sent: Friday, August 02, 2002 4:38 PM
To: Multiple recipients of list ORACLE-L
Correct. DUAL is a table owned by SYS for which SELECT permissions have
been
Kinda neat.
Thanks!
Patrice.
-Original Message-
Sent: Saturday, June 22, 2002 2:38 PM
To: Multiple recipients of list ORACLE-L
For those who want to optimize select something from dual, look at the
following link...
http://www.oracledba.co.uk/tips/dual_speed.htm
Regards,
Vladimir
i used sql.bsq and it worked
shirish
-Original Message-
Sent: Wednesday, June 20, 2001 6:47 PM
To: LazyDBA.com Discussion
Can you enlighten us with the resolution ?
-Original Message-
Sent: 20 June 2001 14:18
To: LazyDBA.com Discussion
thanks all,,my probs with dual is
thanks all,,my probs with dual is is solved now
Shirish Khapre, SE Rolta India Ltd.
Off Ph No. (+91) (022) 832,826,8300568
Ext'n 2730
Minds are like parachutes. They only function when they are open
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Shirish
re: Your sig line on parachutes.
Yes, but to be considered properly prepared for use, they must be closed.
-Original Message-
Sent: Wednesday, June 20, 2001 10:06 AM
To: Multiple recipients of list ORACLE-L
thanks all,,my probs with dual is is solved now
Shirish Khapre, SE Rolta
it creates various support tables and views for eg dual, or tab(when u do
select * from tab) etc
for more info open the sql.bsq from u'r orant/home/rdbms80/admin/sql.bsq
Shirish Khapre, SE Rolta India Ltd.
Off Ph No. (+91) (022) 832,826,8300568
Ext'n 2730
Minds are like parachutes. They
42 matches
Mail list logo
