Re: Dual - multiple rows

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).

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.

>
>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 Received on Sat Sep 04 1999 - 07:39:49 CDT