DUAL is an interesting table in Oracle. You use it to do dirty work, like obtaining the SYSDATE, or doing some calculation when you lose your calculator. There is only one row, and no one care what is stored in the row. It is more funny if you are a Chinese, because it sounds like a foul word in Chinese.

Undoubtedly, you have doubt about what will happen if DUAL has more or fewer (i.e., 0) rows. What if it is dropped, and how about recreating it?

Let's try various DML and DDL statements with DUAL…

(I know no one will follow this document, but if you want to try the experiment below, make sure to ensure you have a usable backup of the database.)

This document is based on Oracle 10g Express Edition. Other 10g editions should behave the same. But earlier versions are different.

SQL> select * from v$version; BANNER ——————————————————————————– Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product PL/SQL Release 10.2.0.1.0 - Production CORE    10.2.0.1.0    Production TNS for 32-bit Windows: Version 10.2.0.1.0 - Production NLSRTL Version 10.2.0.1.0 - Production

INSERT into DUAL …

Let's add something to DUAL. It seems this will cause many scripts which does “select user into p_user from dual;” to break. And the situation is detected when the PL/SQL is run. Great.

SQL> insert into dual values ('Y'); 1 row created. SQL> commit; Commit complete. SQL> select * from dual; DU – X

Ooops! It disappeared. Perhaps Oracle is doing something like VPD on DUAL? Or DUAL is a TEMPORARY TABLE?

Let's try something more interesting… create a primary key on the DUMMY column to see if Oracle allow 2 Y's in DUAL.

SQL> alter table dual   2  add constraint pk_dual   3  primary key (dummy); Table altered. SQL> insert into dual values ('Y'); insert into dual values ('Y') * ERROR at line 1: ORA-00001: unique constraint (SYS.PK_DUAL) violated

Good. Y is still there. But it is still missing from DUAL?! 

SQL> select * from dual; DU – X SQL> select * from dual where dummy<>'X'; DU – Y

Seems that if X is found, Y is not found. How does Oracle do this?

I don't know. It seems Oracle is trying to guard DUAL from returning > 1 rows. This is a good idea, so that people like me won't break many PL/SQL programs.

Let's try more complex SQL…

SQL> select * from dual   2  order by dummy desc; DU – Y X

The guard is defeated by an ORDER clause.

So, in general, things will still work if you insert a row into dual, unless you like to sort your single row result set. 

DELETE from DUAL …

SQL> DELETE FROM DUAL; 1 row deleted. SQL> SELECT * FROM DUAL; no rows selected SQL> SELECT USER FROM DUAL; USER ———————————————————— SYS

DUAL is sometimes empty and sometimes not?! Let's see what Oracle is doing when using the DUAL table.

SQL> EXPLAIN PLAN FOR   2  SELECT * FROM DUAL; Explained. SQL> SELECT * FROM PLAN_VIEW; PLAN_TABLE_OUTPUT ——————————————————————————– Plan hash value: 272002086 ————————————————————————– | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     | ————————————————————————– |   0 | SELECT STATEMENT  |      |     1 |     1 |     2   (0)| 00:00:01 | |   1 |  TABLE ACCESS FULL| DUAL |     1 |     1 |     2   (0)| 00:00:01 | ————————————————————————– 8 rows selected. SQL> EXPLAIN PLAN FOR   2  SELECT USER FROM DUAL; Explained. SQL> SELECT * FROM PLAN_VIEW; PLAN_TABLE_OUTPUT ——————————————————————————– Plan hash value: 1388734953 —————————————————————– | Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     | —————————————————————– |   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 | |   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 | —————————————————————– 8 rows selected.

Everything is clear. Oracle 10g CBO has a new feature, FAST DUAL, designed specifically for DUAL. The idea is that if DUAL is used but not DUAL's data, the operation treat it as a single-row table, not doing any verification. This is to reduce logical I/Os with DUAL.  

ANALYZE TABLE …

We have done with DML, let's try some DDL. (Actually, we have tried Primary Key constraint already.)

Analyzing DUAL does not cause any unusual thing to occur, except that although USER_TABLES.NUM_ROWS is 2, CBO still assume that table has only one row, as seen from the execution plan even when doing FULL TABLE SCAN. But bad assumption in execution plan will not affect correctness.

ALTER DUAL add …

Adding a column in DUAL is easy. Nothing special for DUAL happens with the addiitonal column.

SQL> alter table dual add (dummy2 varchar2(1)); Table altered. SQL> select * from dual; no rows selected SQL> select user from dual; USER ———————————————————— SYS SQL> select user, dummy2 from dual; no rows selected

But removing the column is tricky. Of course, this is not DUAL's problem. It's SYS'.

SQL> alter table dual drop column dummy2; alter table dual drop column dummy2 * ERROR at line 1: ORA-12988: cannot drop column from table owned by SYS

DROP TABLE DUAL …

The above DDLs causes only minor abnormalty. Let's try something more exciting: drop it…

But before doing this, make sure we can re-create it.

SQL> select dbms_metadata.get_ddl('TABLE', 'DUAL') from dual; DBMS_METADATA.GET_DDL('TABLE','DUAL') ——————————————————————————–   CREATE TABLE “SYS”.”DUAL”    (    “DUMMY” VARCHAR2(1)    ) PCTFREE 10 PCTUSED 4 SQL> drop table dual; Table dropped.

Tests showed that normal SQL statement still work. One interesting thing is whether SQL statements using FAST DUAL still work. So, see who am I again…

SQL> SELECT USER FROM DUAL; SELECT USER FROM DUAL                  * ERROR at line 1: ORA-01775: looping chain of synonyms

OK. Since PUBLIC.DUAL is a public synonym to SYS.DUAL, this is understandable. Let's drop the public synonym.

SQL> drop public synonym dual; drop public synonym dual * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-01031: insufficient privileges

Oh really? I am SYS AS SYSDBA, but I cannot drop synonym?  Is this real?

SQL> select * from session_privs   2  where privilege='DROP PUBLIC SYNONYM'; PRIVILEGE ——————————————————————————– DROP PUBLIC SYNONYM

So things are obvious. Oracle is screwed and really missing DUAL… In general, DDL won't work. As another example, let's truncate something:

SQL> truncate table plan_table; truncate table plan_table                * ERROR at line 1: ORA-00942: table or view does not exist

CREATE TABLE DUAL …

Can re-creating DUAL resolve this? I am glad that I kept the create SQL before dropping it. 

SQL> CREATE TABLE “SYS”.”DUAL”   2   (    “DUMMY” VARCHAR2(1)   3   ) PCTFREE 10 PCTUSED 4; Table created. SQL> insert into dual values ('X'); 1 row created. SQL> commit; Commit complete.

(Fortunately create table works without DUAL.) 

SQL> drop public synonym plan_view; drop public synonym plan_view * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-01031: insufficient privileges SQL> SET AUTOTRACE ON SP2-0575: Use of Oracle SQL feature not in SQL92 Entry Level. SQL> SELECT USER FROM DUALX; USER ———————————————————— SYS

Dropping DUAL seems to cause permanent problem with Oracle. DDL does not work. Even auto-trace does not work… It is time to play with Point-in-time recovery .

RENAME TABLE DUAL …

What's the difference between the original DUAL and newly created DUAL such that the new DUAL cannot resolve the problem? One obvious candidate is Object ID. Let's see…

SQL> RENAME DUAL TO DUALX; Table renamed. SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); PLAN_TABLE_OUTPUT ——————————————————————————– ERROR: cannot get definition for table 'PLAN_TABLE' ORA-44002: invalid object name SQL> SELECT USER FROM DUAL; SELECT USER FROM DUAL                  * ERROR at line 1: ORA-01775: looping chain of synonyms SQL> SELECT USER FROM DUALX; USER ———————————————————— SYS

Great, renaming DUAL to something else has similar effect as dropping it. So Object ID is not the only factor, or even not a factor.

SQL> RENAME DUALX TO DUAL; Table renamed. SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); PLAN_TABLE_OUTPUT ——————————————————————————– ERROR: cannot get definition for table 'PLAN_TABLE' ORA-44002: invalid object name

Even renaming back to DUAL, the damage is still there. What causes this? I don't know. It seems to me that everything is restored…

Another Point-in-time recovery pending.

Conclusion …

It seems DUAL acts like normal tables when with DML. However, there is special logic to make sure it looks like a single-row table, so that normally scripts using DUAL should still work. But the checking is not robust and can be defeated easily.

 

DML statements cannot break DUAL easily unless you used DUAL in really creative ways. However, you are asking for problem if you run a DDL statement. It is nearly definite that a database restore is needed to make the database become normal again if you run a DDL.

 

2007/01/31: Just saw a comment from Metalink, saying that DUAL should be considered part of the data dictionary. As a result, any manual operation should be prohibited (just as one won't touch USER$ or OBJ$). Anyone who plays with DUAL is an idiot. [But AUD$ is also part of the data dictionary. And everyone who uses this table will move it around.]