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: How to prevent DUAL having more than 1 row

RE: How to prevent DUAL having more than 1 row

From: Steve Adams <steve.adams_at_ixora.com.au>
Date: Fri, 8 Sep 2000 10:22:41 +1000
Message-Id: <10612.116476@fatcity.com>


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 Received on Thu Sep 07 2000 - 19:22:41 CDT

Original text of this message

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