Re: Dual - multiple rows

Thomas Kyte <tkyte_at_us.oracle.com> wrote:
: A copy of this was sent to Norris <johnnie_at_cooper.com.hk>
: (if that email address didn't require changing)
: On 4 Sep 1999 03:53:13 GMT, you wrote:

:>How can we guarantee dual has only one row?  If not, then it would not be safe
:>to use select 'abc' from dual.
:>

: dual is owned by SYS.

: only DBA's can muck with DUAL (or people with DBA like priveleges).

: If your DBA's insert/delete a row into DUAL "just to see what happens" -- then
: you have a bigger problem then just having an extra row in DUAL. You have a DBA
: that is going to be a major problem for you.

: Technically you could probably put a check constring on DUAL to ensure that
: dummy = 'X'

: (eg: alter table dual add constraint dual_check1 check dummy = 'X';)

: and then put a unique constraint on dummy

: (eg: alter table dual add constraint dual_check2 unique (x);)

: that will ensure that DUAL has at MOST one row (its value will be X as it
: already is and dummy must be unique).

: this will not prevent people from deleting from dual however. so dual may still
: be 'incorrect'.

: It will also put an index on DUAL which *may* mess up query plans in some
: unforseen way (so it is not recommended).

If a table has only one record with unique index, I think most RDBMS query optimizer wouldn't use that index in the query plan unless we have special instruction.

: The best way to ensure dual has 1 row is to keep control of who has DBA and make
: sure they are competent for the job.

Can we add audit trail on the dual so that we can know which DBA have modified the dual?

:>
:>Thomas Kyte <tkyte_at_us.oracle.com> wrote:
:>> A copy of this was sent to Norris <johnnie_at_cooper.com.hk>
:>> (if that email address didn't require changing)
:>> On 3 Sep 1999 09:49:53 GMT, you wrote:
:>
:>>>Can we add trigger on dual to make sure that it can only have one record?
:>
:>> No you cannot.  You cannot put triggers on objects owned by SYS and dual is
:>> owned by SYS.  
:>
:>
:>>>
:>>>Thomas Kyte <tkyte_at_us.oracle.com> wrote:
:>>>> A copy of this was sent to g.renfrew_at_acca.org.uk
:>>>> (if that email address didn't require changing)
:>>>> On Thu, 02 Sep 1999 10:11:43 GMT, you wrote:
:>>>
:>>>>>Hi everyone,
:>>>>>
:>>>>>I'm running 7.3.2.3 on Solaris 2.6 and came across a bit of a weird
:>>>>>problem with the DUAL table.  When I selected from DUAL it always
:>>>>>returned two identical rows.  On all my other databases I would only
:>>>>>ever expect one row returned, as is normal.   This problem caused a
:>>>>>couple of errors with "ora-1422: exact fetch returns more than
:>>>>>requested number of rows". When I did "select * from dual" it returned
:>>>>>the following:
:>>>>>DUMMY
:>>>>>-----
:>>>>>X
:>>>>>x
:>>>>>
:>>>>>Since all my other databases returned only 'X' I deleted from dual
:>>>>>where DUMMY = 'x' and everything is ok now.
:>>>>>Any ideas on how an extra row would appear in DUAL?
:>>>>>
:>>>
:>>>
:>>>> somebody inserted it (one of your DBAs probably).
:>>>
:>>>> DUAL is a real table -- its not special or anything.  If it has no rows --
:>>>> that'll cause havoc.  If it has >1 row, same thing.
:>>>
:>>>>>Best Regards
:>>>>>Graeme Renfrew
:>>>>>DBA
:>>>>>ACCA
:>>>>>Glasgow
:>>>>>
:>>>>>
:>>>>>Sent via Deja.com http://www.deja.com/

:>>>>>Share what you know. Learn what you don't.
:>>>
:>>>
:>>>> -- 
:>>>> See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
:>>>> Current article is "Part I of V, Autonomous Transactions" updated June 21'st
:>>>>  
:>>>> Thomas Kyte                   tkyte_at_us.oracle.com
:>>>> Oracle Service Industries     Reston, VA   USA
:>>>
:>>>> Opinions are mine and do not necessarily reflect those of Oracle Corporation
:>
:>
:>> -- 
:>> See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
:>> Current article is "Part I of V, Autonomous Transactions" updated June 21'st
:>>  
:>> Thomas Kyte                   tkyte_at_us.oracle.com
:>> Oracle Service Industries     Reston, VA   USA
:>
:>> Opinions are mine and do not necessarily reflect those of Oracle Corporation

: --
: See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
: Current article is "Part I of V, Autonomous Transactions" updated June 21'st
:
: Thomas Kyte tkyte_at_us.oracle.com
: Oracle Service Industries Reston, VA USA

: Opinions are mine and do not necessarily reflect those of Oracle Corporation

--
--
http://www.washington.edu/pine/faq/ Received on Sun Sep 05 1999 - 22:40:31 CDT