Use of dual tables in Oracle

Dual is a virtual table that makes up the syntax rules for SELECT, and Oracle guarantees that there will always be only one record in dual. We can use it to do a lot of things, as follows:

1. To view the current user, you can execute the following statement in SQL Plus select User from dual;

2. Used to call system functions

Select To_char (sysdate, ' Yyyy-mm-dd hh24:mi:ss ') from dual;--get current system time

Select Sys_context (' USERENV ', ' TERMINAL ') from dual;--get host name

Select Sys_context (' USERENV ', ' language ') from dual;--get current locale

Select Dbms_random.random from dual;--to get a random number

3. Get the next value or the current value of the sequence, using the following statement

Select Your_sequence.nextval from dual;--to get the next value of the sequence Your_sequence

Select Your_sequence.currval from dual;--to get the current value of the sequence Your_sequence

4, can be used as a calculator select 7*9 from dual;

------

Oracle System Dual Table is a "mysterious" table, many online users have tested the table, the table is only one row, in fact, the table and other tables in the system, the same can perform insert, UPDATE, delete operations, you can also perform a drop operation. But do not go to perform the operation of the drop table, otherwise it will make the system can not be used, the database can not be reported, databases startup crashes with ORA-1092 error. Do not panic at this time, you can recover by performing the following steps. Can be logged in with the SYS user.

sql> create pfile= ' D:pfile.bak ' from SPFile

sql> shutdown Immediate

In the D:pfile.bak file, add one last:

replication_dependency_tracking = FALSE

To restart the database:

sql> startup pfile= ' D:pfile.bak '

sql> CREATE TABLE "sys". " DUAL "

[An error occurred while processing this directive]

=====

DUAL? What's so mysterious about that? When you want the Oracle system time, simply hit a line of SQL is not it? A trick or a trick ....

Sql> select Sysdate from dual;

Sysdate

---------

28-sep-03

Haha, really dual is very convenient to use. But do you know what object dual is, and what special behavior does it have?　Come on, let's have a look. First figure out what dual is, object:

Sql> Connect System/manager

Connected.

Sql> Select owner, object_name, object_type from dba_objects where object_name like '%dual% '; OWNER object_name object_type

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

SYS DUAL TABLE public DUAL synonym

The original dual is a table that belongs to the SYS schema and is then used as public synonym for other database user.

And look at the structure of it:

Sql> desc dual Name Null? Type

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

DUMMY VARCHAR2 (1)

Sql>

There is only one name called dummy's character-type column.

Then look at the data in the table:

Sql> select dummy from dual;

DUMMY

----------

X

Oh, there is only one record, and the value of dummy is ' X '. It's normal, nothing strange. Well, there's something wonderful going on down there!

Insert a record:

Sql> Connect SYS as SYSDBA

Connected.

Sql> INSERT into dual values (' Y ');

1 row created.

Sql> commit;

Commit complete.

Sql> Select COUNT (*) from dual;

COUNT (*)

----------

2

So far, everything is fine. But when we check the records again, something strange happens.

Sql> SELECT * from dual;

DUMMY

----------

X

The record you just inserted doesn't show up! Obviously, there are two records in the dual table, but only one is displayed!

Try again delete, ruthless a bit, all delete light!

Sql> Delete from dual; /* Note that there is no qualification to attempt to delete all records */

1 row deleted.

Sql> commit;

Commit complete.

Haha, only one record has been deleted,

Sql> SELECT * from dual;

DUMMY

----------

Y

Why is that? Does the syntax of SQL have no effect on dual? With this question,

I've queried some of the official Oracle information.　Oracle has done some internal processing on the dual table, as far as possible, to ensure that only one record is returned in the dual table. Of course, the internal operation is not visible. There seems to be an infinite mystery in Oracle!

Use of dual tables in Oracle