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.