FW: SV: Re: Re: Select from dual return 3 rows !

From: Pete Sharman <peter.sharman_at_oracle.com>
Date: Wed, 9 Nov 2005 04:40:45 +1100
Message-ID: <20051109044045214.00000003108@psharman-au>

Damn, I hate this having to hit reply all thing. L

In case anyone else might be interested …

Pete

"Controlling developers is like herding cats."

Kevin Loney, Oracle DBA Handbook

"Oh no, it's not. It's much harder than that!"

Bruce Pihlamae, long-term Oracle DBA

From: Pete Sharman [mailto:peter.sharman@oracle.com]
Sent: Wednesday, 9 November 2005 4:38 AM
To: frank4oraclel@yahoo.dk
Cc: Peter Ross Sharman
Subject: RE: SV: Re: Re: Select from dual return 3 rows !

Painful means of death should definitely be inflicted. ;)

Just as a matter of interest, you could end up with more than one row in DUAL in versions that are now ancient history for most of us. I remember in a previous life having a manager do maintenance on the database one night (at his insistence that he knew what he was doing). Next day, all the Forms 3 apps we had died on startup because they had fields populated by a SELECT INTO FROM DUAL type statement. Turned out we now had two rows in DUAL, because the “knowledgeable” manager had run an import twice. We never let him forget what he’d done. J

If memory serves me correctly, later versions now ignore multiple rows in DUAL anyway. Not sure where that came in though – 9.2?

Pete

"Controlling developers is like herding cats."

Kevin Loney, Oracle DBA Handbook

"Oh no, it's not. It's much harder than that!"

Bruce Pihlamae, long-term Oracle DBA

From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Frank B Hansen
Sent: Tuesday, 8 November 2005 11:08 PM
To: rjamya@gmail.com
Cc: David Sharples; ORACLE-L; frank.hansen@kriminalforsorgen.dk
Subject: SV: Re: Re: Select from dual return 3 rows !

Hi Raj

That was the issue !

system@HCRD9397> select object_name , object_type, owner from dba_objects where object_name like '%DUAL%';

OBJECT_NAME                    OBJECT_TYPE        OWNER
------------------------------ ------------------ ------------------------------
DUAL                           TABLE              SYS
DUAL                           SYNONYM            PUBLIC
DUAL                           TABLE              SYSTEM

system@HCRD9397> drop table system.dual;

Table dropped.

system@HCRD9397> select object_name , object_type, owner from dba_objects where object_name like '%DUAL%';

OBJECT_NAME                    OBJECT_TYPE        OWNER
------------------------------ ------------------ ------------------------------
DUAL                           TABLE              SYS
DUAL                           SYNONYM            PUBLIC

system@HCRD9397> select * from dual;

D
-
X

I will now try to hunt down the ones responsible and kill them slowly !

Thanks, Frank

rjamya <rjamya@gmail.com> skrev:

probably system owns a table called dual ... check that first.

Raj

On 11/8/05, Frank B Hansen <frank4oraclel@yahoo.dk > wrote:

Hi David

OK. Next question and some additional info:

It is only when connected as SYSTEM that 3 rows are returned. If I connect as SYS og SCOTT, only 1 row is returned.

A select from x$dual returns one row:

sys@HCRD9397> select * from x$dual;

ADDR INDX INST_ID D
-------- ---------- ---------- -
01B74DC8 0 1 X

and the question:

How do I delete row number the second and third row from dual ?

Thanks,

Frank

David Sharples <davidsharples@gmail.com> skrev:

someone inserted 2 extra rows into dual, find them and tell them politely not to do it again.

Then delete the other rows as you have a broken database right now

On 11/8/05, Frank B Hansen <frank4oraclel@yahoo.dk > wrote:

No matter what I select from dual, I get 3 rows. Anyone seen this behaviour ?

--
----------------------------------------------
This space is available for rent.

-- http://www.freelists.org/webpage/oracle-l Received on Tue Nov 08 2005 - 11:44:01 CST