Oracle's Dual Table

1, the use of dual table
Dual is a physically existing table in Oracle that can be read by any user, often in a SELECT statement block without a target table

--View current connected user
Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.2.0
Connected as SYS

Sql> Select User from dual;
USER
------------------------------
SYSTEM

--View current date, time
Sql> select Systimestamp from dual;
Systimestamp
--------------------------------------------------------------------------------
September-March-11 11.11.05.901000 am +08:00

Sql> Select To_char (sysdate, ' Yyyy-mm-dd hh24:mi:ss ') from dual;
To_char (sysdate, ' YYYY-MM-DDHH2
------------------------------
2011-03-09 11:12:03

--Used as a calculator
Sql> select 1+2 from dual;
1+2
----------
3
--View sequence values
sql> Create sequence a increment by 1 start with 1;
Sequence created

Sql> select A.nextval from dual;
Nextval
----------
1
Sql> select A.currval from dual;
Currval
----------
1
sql> drop sequence A;
Sequence dropped

2, about the dual table test and analysis
Dual is a row-by-column table, and if you do insert, delete, truncate operations, it can cause many programs to go wrong. The results are also different for tools such as sql*plus, PL/SQL Dev, and so on.
--See what dual is, object

--dual is a table that belongs to the SYS schema and is then used by public synonym for other database users.
Format the actual formatting of column headings and column data first:
sql> clear columns;
sql> COLUMN owner HEADING FORMAT A10 word_wrappend
sql> COLUMN object_name HEADING FORMAT A15 word_wrappend
sql> COLUMN object_type HEADING FORMAT A15 word_wrappend
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

sql> clear columns;

--View table structure, only one field dummy, for VARCHAR2 (1) type
sql> DESC Dual;
Name Type Nullable Default Comments
----- ----------- -------- ------- --------
DUMMY VARCHAR2 (1) Y

Structure of the--dual table:
CREATE TABLE SYS. DUAL
(
DUMMY VARCHAR2 (1)
)
Tablespace SYSTEM
Pctfree 10
Pctused 40
Initrans 1
Maxtrans 255
Storage
(
Initial 16K
Next 16K
Minextents 1
Maxextents 505
Pctincrease 50
);

/*
It's confusing, why is Oracle using the VARCHAR2 (1) type, char (1) is not good? From this table structure, the purpose of the dual table design is to be as simple as possible to reduce the cost of retrieval.
Also, the dual table is built in the system table space, the first is because the dual table is the SYS user-built, the default table space is the system, and second, this may be frequently queried by the table and the user table to separate storage, for system performance is

Benefits of.
It is not enough to have a table created and a synonym created. Dual is under the SYS schema, so it is not possible to query the table with another user, so you also need to authorize:
Grant SELECT on SYS. DUAL to public with GRANT option;
Grant SELECT permission to the public.
Next look at the data in the dual table, in fact, the data in the dual table is very important to the Oracle database environment (Oracle will not be paralyzed, but many stored procedures and some queries will not be executed correctly).
*/

--Query row count
--After the database is created, a record has been inserted into the dual table. Personally think: The value of the dummy field does not matter, it is important that the number of records in the dual table
Sql> Select COUNT (*) from dual;
COUNT (*)
----------
1

Sql> SELECT * from dual;
DUMMY
-----
X

--Using PL/SQL developer to insert data, all the data can be real.
Sql> INSERT into dual values (' Y ');
1 row inserted

Sql> INSERT into dual values (' X ');
1 row inserted

Sql> INSERT into dual values (' Z ');
1 row inserted

Sql> commit;
Commit Complete

Sql> SELECT * from dual;
DUMMY
-----
X
Y
X
Z

But only one line is displayed when we execute the following statement:
Sql> select Sysdate from dual;
Sysdate
-----------
2011-3-9 on 12

Sql> select Systimestamp from dual;
Systimestamp
--------------------------------------------------------------------------------
September-March-11 12.10.23.073000 pm +08:00

When the system user logs on with Oracle Sql*plus, the same operation is performed to show that the inserted data is successful, but the data is not queried, the statistics are only one data, all the data in the table is viewed, and only the data ' X ' in the original table is visible.

--Cut off the watch.
Sql> TRUNCATE TABLE dual;
Table truncated

Sql> commit;
Commit Complete

However, one of the data tables has not been deleted, which is why there is at least one piece of data in the table.
Sql> Select COUNT (*) from dual;
COUNT (*)
----------
1

Sql> SELECT * from dual;
No rows selected

However, the following statement will succeed:
Sql> select Sysdate from dual;
Sysdate
-----------
2011-3-9 on 12

--Try to remove the data from the dual table to see what happens:
Sql> Delete from dual;
1 row deleted

Sql> SELECT * from dual;
No rows selected

Sql> select Sysdate from dual;
Sysdate
-----------
September-March-11

We can fetch the system date. The system date was not available in the previous version, it is said that the reason is: Sysdate is a function for each data row. Now that there is no data, it is natural that the system date cannot be removed. But this was done in the 10g version.

Modify, if you delete all the data in the table, Oracle will keep an empty data in the table so that you can use this dual table for other operations.

--For the delete operation, Oracle has done some internal processing of the dual table, ensuring that only one record is returned in the dual table. Of course, it's not visible in the internal operation.
-no matter how many records are in the table (except for no records), Oracle deletes only one piece of data for each delete operation.
Sql> Select COUNT (*) from dual;
COUNT (*)
----------
4

Sql> Delete from dual;
4 rows deleted

Sql> commit;
Commit Complete

Sql> Select COUNT (*) from dual;
COUNT (*)
----------
1

Attached: Oracle's explanation of the unusual features of the dual table
There is internalized code, makes this happen. Code checks that ensurethat a table scan of SYS. DUAL only returns one row. SVRMGRL behaviour is incorrect and this

is now an obsolete product.
The base issue you should all remember and keep is:dual table should always has 1 ROW. Dual is a normal table with one dummy column of VARCHAR2 (1).
This was basically used from several applications as a pseudo table for getting results from a SELECT statement so use FU Nctions like Sysdate or other
prebuilt or application functions. If DUAL have no rows at all some applications (that's use DUAL) may fail with no_data_found exception. If DUAL have more than 1 row then

Applications (that is use DUAL) could fail with too_many_rows exception.
So DUAL should always has 1 and only 1 row

The dual table can perform INSERT, UPDATE, delete operations, and drop operations. 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.

3, if the dual table is "unfortunate" after the deletion of the recovery:
Log in with the SYS user.
Create a dual table.
Grant Public SELECT permissions (SQL as above, but do not give Update,insert,delete permissions).
Insert a record into the dual table (this only): INSERT into dual values (' X ');
Commit the changes.
--Log in with the SYS user.
sql> create pfile= ' D:/pfile.bak ' from SPFile
sql> shutdown Immediate
--One last entry in the D:/pfile.bak file: replication_dependency_tracking = FALSE
--Restart the database:
sql> startup pfile= ' D:/pfile.bak '
sql> CREATE TABLE "sys". " DUAL "
("DUMMY" VARCHAR2 (1))
Pctfree pctused 4;
Sql> INSERT into dual values (' X ');
Sql> commit;
Sql> Grant Select on dual to public;
Authorization is successful.

Sql> SELECT * from dual;
D
-
X

sql> shutdown Immediate
The database is closed.
The database has been uninstalled.
The ORACLE routine has been closed.
Sql> Startup
The ORACLE routine has been started.

Total System Global area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database buffers 25165824 bytes
Redo buffers 667648 bytes
The database is loaded.
The database is already open.

Oracle's Dual Table