Dual? What mysterious? When you want to get an Oracle system time, a simple single line SQL
Don't you get?
SQL> SELECT SYSDATE from DUAL;
Sysdate
---------
28-Sep-03
Haha, it is really easy to use. But everyone knows what Dual is Object. Is there any special behavior? Come, let's take a look.
First, please figure out what the 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
It turns out that Dual is a table belonging to Sys Schema, then uses in the way in the public synonym for other databases User.
Take a look at its structure:
SQL> DESC DUAL
Name NULL? TYPE
------------------------------------- ---------------------------
Dummy varchar2 (1)
SQL>
There is only one name called Dummy characters Column.
Then query the data in the table:
SQL> SELECT DUMMY from DUAL;
Dummy
------------
X
Oh, there is only one record, the value of Dummy is 'x'. Very normal, there is not a strange thing. Ok, there is a wonderful thing in the following!
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 normal. However, when we query the record again, the strange thing happened.
SQL> SELECT *.
Dummy
------------
X
The record just inserted did not show it! There are two records in the DUAL table, but only one is displayed!
Try to delete, pay attention, completely delete!
SQL> Delete from Dual; / * Note There is no qualification, try to delete all records * /
1 row deleded.
SQL> commit;
COMMIT COMPLETE.
Haha, there is only one record to be deleted.
SQL> SELECT *.
Dummy
------------
Y
Why is this this? Is the SQL syntax does not work on DUAL? With this question, I queried some Oracle official information. It turns out that Oracle has some internal processing to the DUAL table, try to ensure that only the DUAL table returns only A record. Of course, this is invisible to write internal operations.
It seems that oracle is really an infinite mystery!
Attachment: Oracle About Dual Table Explanation of Unusual Characteristics
There IS INTERNALIZED Code That Makes This Happy Code Checks That Ensure
That a Table Scan of Sys.Dual Only Returns One Row. SVRMGRL Behaviour IsincorRect But this is now an Obsolete Product.
The Base Issue You Should Always Remember and Keep Is: Dual Table Should 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 Uses Like Sysdate or Other
Prebuilt or Application Functions. if Dual Has No Rows At All Some Applications
(for use 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.
So Dual SHOULD Always Have 1 and Only 1 ROW
