Insert, Delete, Truncate Operations with Oracle Dual Table. Just Not Fun


Don’t play with dual table in an active database. Really it is not fun.
Insert Row in Dual
If you insert a row into dual, it will not list in the select command. But if you try to create a dummy table from the dual it will show the actual content of the dual table.


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

1 row created.

SQL> commit;

Commit complete.

SQL> select * from dual;

D
-
X

Creating a dummy table from the dual and which contains two rows.
SQL> create table dummy_dual as select * from dual;

Table created.

SQL> select * from dummy_dual;

D
-
X
Y
If you insert more than one row into the dual table to the database prior to 11g version, the drop table command will show the following error.
drop table dummy_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
Truncate  and Delete operations on Dual
Delete command cannot delete the row from the dual only truncate command can delete the row from dual.

Example
SQL> select * from dual;

D
-
X

SQL> delete from dual;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from dual;

D
-
X

SQL> create table dummy_dual as select * from dual;

Table created.

SQL> select * from dummy_dual;

D
-
X
SQL> select * from dual;

D
-
X

SQL> truncate table dual;

Table truncated.

SQL> select * from dual;

no rows selected

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

1 row created.

SQL> commit;

Commit complete.

SQL> select * from dual;

D
-
X
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google