A few basic notes on Oracle Database Administration.

Tuesday, May 19, 2009

Making problems for myself

Playing around with my toy database I asked myself, "What happens if DUAL has more than 1 row?" I found out.


SQL> insert into dual values ('Y');



1 row created.



SQL> select * from dual;

D
-
X


SQL> select count(*) from dual;

COUNT(*) ---------- 1


I tried it again. Same result. "Oh, I guess I can't insert into DUAL", says I, and I went about my business.


Later I logged on as SCOTT and tried to drop a table. Playing I have more EMP tables than employees.



SQL> DROP TABLE EMP4;


ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-01422: exact fetch returns more than requested number of rows



WHAT??!!?


Yes it is there and there is only 1 table called EMP4.


SELECT OWNER, OBJECT_NAME FROM ALL_OBJECTS WHERE OBJECT_NAME = 'EMP4'


OWNER OBJECT_NAME

------------------------------ ------------------------------

SCOTT EMP4



I looked the matter up at orafaq. and followed the instructions.

SQL> select * from dual;

D

-

X


SQL> create table temp_dual as select * from dual;

Table created.


SQL> select * from temp_dual;

D

-

X

Y

Y


Yes, I plead guilty. I DID succeed in inserting those rows into DUAL.


SQL> delete from dual where dummy = 'Y';

1 row deleted.


Strange. It deleted 1 row even though I had put 2 in.




SQL> drop table temp_dual;

drop table temp_dual

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-01422: exact fetch returns more than requested number of rows



I deleted the second excess row:

SQL> delete from dual where dummy = 'Y';

1 row deleted.


and I had a functioning database back.


SQL> DROP TABLE TMP_DUAL;

Table dropped.


and then as SCOTT


SQL> DROP TABLE EMP4;

Table dropped.



OK, I get it, Oracle consults DUAL in the drop process. And don't go messing up a database of any importance. But it is odd how the fact that I was succeeding to mess things up was hidden from me. Yes it told me that I had inserted the row, but then it didn't display it with a select. It was an interesting bit of play.




8 comments:

Michael A. Rife said...

I had a DBA in the 90's in an Oracle V6 database run the data dictionary catalog script by accident a second time. The catalog script created the DUAL table and inserted one row into it. The create failed, but the insert succeeded. We ended up with two rows in the DUAL table. Our applications were all SQL*Forms V2. The logic for each trigger step in the forms was SELECT ... FROM DUAL branch on success or branch on failure. You talk about getting a lot calls from users all at once.

girlgeek said...

LOL - I can just imagine the chaos!

Coskan Gundogar said...

Once I deleted from dual and did not see any resultset after selecting * from dual, but I never tried to insert in it to see the effects. Nice to know this

Thank you very much

Marco Gralike said...

"AskTom" has two threads that really are worth reading in this respect.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1562813956388


http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:7022211542199


Grz

Marco

Log Buffer said...

"Girlgeek Claudia Zeiler made problems for herself: 'Playing around with my toy database I asked myself [...] '”

Log Buffer #147

Martin Berger said...

You didn't menton the oracle-version, but I assume, the oracle optimizer played games with you replacing DUAL to FAST_DUAL for selects (but obviously not for DMLs).
Nice lessones learned!

dBuggr said...

Hi Claudia girlgeek, :)

Excellent writing! Thanks!

We just started a Technical knowledge base with Revenue Sharing concept called dBuggr @ http://dbuggr.com, and would like to invite you to join and share our ad revenue. (50% of our total ad revenue)

dBuggr is completely FREE to join! No catch what-so-ever.

You can simply copy and paste your existing knowledge to dBuggr and continue to add new knowledge going forward.

The more you share, the more money you can share out of the 50%.

We found your blog and are SUPER impressed with your technical expertise and knowledge.

We sincerely hope you to check out dBuggr.com and read the FAQ.

We are confident you'll like the idea and would enjoy the extra money you make out of your knowledge!

Email us at contact@dbuggr.com if you have any additional question!

Thanks for your support and keep up the technical guru level of writing!

- Willy
dBuggr team

Tani said...

Very helpful post you have provided here. I have not faced this problem so far but after knowing the problem and its recommended solution I will remember this point and take care of it. Thanks for sharing it.
sap upgrade evaluation