Oracle's DUAL Table (part 1)

There are 2 objects in the database called DUAL, a table owned by SYS and a public synonym:

SQL> conn / as sysdba
Connected.
SQL> col owner format a20
SQL> col object_type format a20
SQL> select owner, object_type
  2  from dba_objects
  3  where object_name = 'DUAL'
  4  /

OWNER                OBJECT_TYPE
-------------------- --------------------
SYS                  TABLE
PUBLIC              SYNONYM

SQL>

The table SYS.DUAL has one column:

SQL> desc sys.dual
Name                      Null?    Type
-------------------------- -------- ------------------
DUMMY                              VARCHAR2(1)

SQL>

And one row with a value of X:

SQL> select * from dual
  2  /

D
-
X

SQL>

The public synonym points to the SYS.DUAL table:

SQL> col table_owner format a20
SQL> col table_name format a20
SQL> select table_owner, table_name
  2  from dba_synonyms
  3  where owner = 'PUBLIC'
  4  and synonym_name = 'DUAL'
  5  /

TABLE_OWNER          TABLE_NAME
-------------------- --------------------
SYS                  DUAL

SQL> 

In common with other objects owned by SYS (except SYS.AUD$), you should not change the contents of SYS.DUAL. It is set up when the database is created. In Oracle 9 this took place in sql.bsq. In Oracle 11 the table creation seems to have moved to dcode.bsq but the SQL is still the same i.e.

create table dual
(dummy varchar2(1))
storage (initial 1)
/
insert into dual values('X')
/
create public synonym dual for dual
/
grant select on dual to public with grant option
/

If you log in as SYS, there is nothing to stop you deleting the row from DUAL although YOU SHOULD NEVER TRY THIS IN A PRODUCTION DATABASE:

SQL> conn / as sysdba
Connected.
SQL> delete dual
  2  /

1 row deleted.

SQL> select * from dual
  2  /

no rows selected

SQL> rollback
  2  /

Rollback complete.

SQL> select * from dual
  2  /

D
-
X

SQL> commit
  2  /

Commit complete.

SQL> 

You can also update it:

SQL> update dual set dummy = 'Y'
  2  /

1 row updated.

SQL> select * from dual
  2  /

D
-
Y

SQL> rollback
  2  /

Rollback complete.

SQL> select * from dual
  2  /

D
-
X

SQL> commit
  2  /

Commit complete.

SQL> 

... but inserting a row causes some strange behaviour. The insert appears to work:

SQL> insert into dual values ('Z')
  2  /

1 row created.

SQL>

... but you can only see the inserted row:

SQL> select * from dual
  2  /

D
-
Z

SQL> 

... and if you then delete all the rows from the table:

SQL> delete from dual
  2  /

1 row deleted.

SQL>

The original row suddenly reappears:
 

SQL> select * from dual
  2  /

D
-
X

SQL> rollback
  2  /

Rollback complete.

SQL> select * from dual
  2  /

D
-
X

SQL> commit
  2  /

Commit complete.

SQL>

Fortunately, ordinary users cannot update DUAL at all:

SQL> conn andrew/reid
Connected.
SQL> update dual set dummy = 'Z'
  2  /
update dual set dummy = 'Z'
      *
ERROR at line 1:
ORA-01031: insufficient privileges

SQL>