RE: How to prevent DUAL having more than 1 row

It hadn't occurred to me to check count(*) as well.

Sheesh! Why not just prevent DML on dual?

Jared

On Fri, 8 Sep 2000, Steve Adams wrote:

> Hi Jared,
>
> The insert does work, however the kernel "knows" about DUAL and so only returns
> the first row when you select from it. However, if you select count(*) you'll
> get the real answer.
>
> SQL> insert into dual select * from dual;
>
> 1 row created.
>
> SQL> commit;
>
> Commit complete.
>
> SQL> select * from dual;
>
> D
> -
> X
>
> SQL> select count(*) from dual;
>
> COUNT(*)
> ----------
> 2
>
> How's that for smart?
>
> @ Regards,
> @ Steve Adams
> @ http://www.ixora.com.au/
> @ http://www.christianity.net.au/
> @
> @ Going to OpenWorld?
> @ Catch the Ixora performance tuning seminar too!
> @ See http://www.ixora.com.au/seminars/ for details.
>
>
> -----Original Message-----
> From: Jared Still [mailto:jkstill_at_bcbso.com]
> Sent: Friday, 8 September 2000 9:09
> To: Steve Adams
> Cc: babu.nagarajan_at_mail.iflexsolutions.com; ORACLE-L_at_fatcity.com;
> oracledba_at_lazydba.com; answers_at_ixora.com.au
> Subject: RE: How to prevent DUAL having more than 1 row
>
>
>
> In previous versions of Oracle I have seen this happen.
>
> However, I've tried to insert into dual in more recent
> versions and was unable to.
>
> No error messages, but no data added to dual either.
>
> Jared
>
> On Fri, 8 Sep 2000, Steve Adams wrote:
>
> > Hi Babu,
> >
> > I can't tell you how this happened, but I can suggest an unsupported way of
> > making sure that it never happens again. The APT script "replace_dual.sql" at
> > http://www.ixora.com.au/scripts/cache.htm#replace_dual can replace the
> SYS.DUAL
> > table with a view onto X$DUAL. Once that's done, it will not be possible for
> > extra rows to appear in DUAL again. From a performance point of view, there is
> a
> > slight increase in the parse overhead, but a slight reduction is the execution
> > cost of statements that refer to DUAL. Once again, this is unsupported -
> > mentioned for information only.
> >
> > @ Regards,
> > @ Steve Adams
> > @ http://www.ixora.com.au/
> > @ http://www.christianity.net.au/
> > @
> > @ Going to OpenWorld?
> > @ Catch the Ixora performance tuning seminar too!
> > @ See http://www.ixora.com.au/seminars/ for details.
> >
> >
> > -----Original Message-----
> > From: babu.nagarajan_at_mail.iflexsolutions.com
> > Sent: Thursday, 7 September 2000 21:33
> > To: ORACLE-L_at_fatcity.com; oracledba_at_lazydba.com
> > Subject: dual having more than 1 row
> >
> >
> > In one of the databases here, count(*) from dual gave me 3. I deleted two rows
> > based on the rowid's and now things are working fine. I was wondering how it
> > could happen in the first place. No, no one logging in as SYS inserted this.
> > Then how?
> >
> >
> > --------
> > If you're bored, then visit the list's website: http://www.lazydba.com
> (updated daily)
> > to unsubscribe, send a blank email to oracledba-unsubscribe_at_quickdoc.co.uk
> > to subscribe send a blank email to oracledba-subscribe_at_quickdoc.co.uk
> >
>
>
> Jared Still
> Certified Oracle DBA and Part Time Perl Evangelist ;-)
> Regence BlueCross BlueShield of Oregon
> jkstill_at_bcbso.com - Work - preferred address
> jkstill_at_teleport.com - private
>
>
> --------
> If you're bored, then visit the list's website: http://www.lazydba.com (updated daily)
> to unsubscribe, send a blank email to oracledba-unsubscribe_at_quickdoc.co.uk
> to subscribe send a blank email to oracledba-subscribe_at_quickdoc.co.uk
>

Jared Still
Certified Oracle DBA and Part Time Perl Evangelist ;-) Received on Thu Sep 07 2000 - 18:48:40 CDT