Oracle dual table usage

 

Dual is a virtual table used to form the select syntax rule. Oracle ensures that there will always be only one record in dual. We can use it to do a lot of things, as shown below:

1. view the current user. You can execute the following statement Select User from dual in SQL plus;

2. Used to call system functions

Select to_char (sysdate, 'yyyy-mm-dd hh24: MI: ss') from dual; -- get the current system time

Select sys_context ('userenv', 'terminal') from dual; -- get the host name

Select sys_context ('userenv', 'language') from dual; -- get the current locale

Select dbms_random.random from dual; -- obtain a random number

3. Obtain the next or current value of the sequence. Use the following statement.

Select your_sequence.nextval from dual; -- Obtain the next value of the sequence your_sequence

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

4. You can use the calculator select 7*9 from dual;

------

In Oracle, the dual table is a "mysterious" table. Many online users have tested this table. This table has only one row and one column, in fact, this table is the same as other tables in the system. You can perform insert, update, and delete operations as well as drop operations. But do not perform the drop table operation, otherwise the system will not be available, the database cannot start, will report the database startup crashes with ORA-1092 error. In this case, do not be confused. You can perform the following steps to restore. You can use the Sys user to log on.

SQL> Create pfile = 'd: pfile. Bak 'from spfile

SQL> shutdown immediate

Add the last entry to the D: pfile. Bak file:

Replication_dependency_tracking = false

Restart the database:

SQL> startup pfile = 'd: pfile. Bak'

SQL> Create Table "sys". "dual"

[An error occurred while processing this directive]

=====

Dual? What's mysterious? If you want to get the time of the Oracle system, you can simply type a line of SQL? To learn more ....

SQL> select sysdate from dual;

Sysdate

---------

28-sep-03

Haha, it is indeed very convenient to use dual. But do you know what the dual is and what special behavior it has? Let's take a look. First, let's figure out what dual is:

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 in SYS schema and is then used by other database users in public synonym mode.

Let's look at its structure:

SQL> DESC dual name null? Type

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

Dummy varchar2 (1)

SQL>

There is only one empty column named dummy.

Query the data in the table:

SQL> select dummy from dual;

Dummy

----------

X

Oh, there is only one record. The dummy value is 'x'. It's normal. It's no surprise. Well, there's something amazing coming up!

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 works. But when we query records again, something strange happened.

SQL> select * from dual;

Dummy

----------

X

The record just inserted is not displayed! The dual table has two records, but only one record is displayed!

Try to delete the file. Click "delete" to delete all files!

SQL> Delete from dual;/* try to delete all records without restrictions */

1 row deleted.

SQL> commit;

Commit complete.

Haha, only one record is deleted,

SQL> select * from dual;

Dummy

----------

Y

Why? Does SQL syntax not work for dual? With this question,

I have queried some official Oracle documents. oracle performed some internal operations on the dual table to ensure that only one record is returned in the dual table. of course, this write operation is invisible. it seems that Oracle is an infinite mystery!

Appendix: Oracle interpretation of unusual features of dual tables there is internalized code that makes this happen. code checks that ensure that a table scan of SYS. dual only returns one row. svrmgrl behaviour is incorrect but this is now an obsolete product. the base issue you shoshould always remember and keep is: Dual table shoshould always have 1 row. dual is a normal table with one dummy column of varchar2 (1 ). this is basically used from several applications as a pseudo table for getting results from a select statement that use functions like sysdate or other prebuilt or application functions. if dual has no rows at all some applications (that use dual) may fail with no_data_found exception. if dual has more than 1 row then applications (that use dual) may fail with too_many_rows exception.