An explanation of the dual table in Oracle

Oracle the dual table in the detailed

1,DUALpurpose of the 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
Sql> Select User from dual;
USER
------------------------------
SYSTEM
--View current date, time
Sql> select Sysdate from dual;
Sysdate
-----------
2007-1-24 on 1
Sql> Select To_char (sysdate, "Yyyy-mm-dd Hh24:mi:ss") from dual;
To_char (Sysdate, "YYYY-MM-DDHH2
------------------------------
2007-01-24 15:02:47
--Used as a calculator
Sql> select 1+2 from dual;
1+2
----------
3
--View sequence values
sql> Create sequence AAA increment by 1 start with 1;
Sql> select Aaa.nextval from dual;
Nextval
----------
1
Sql> select Aaa.currval from dual;
Currval
----------
1

2, aboutDUALtest and analysis of the table
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.
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

--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 varchar (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 separate storage, for system performance is good.
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

--inserting data, querying records, returning only one row of records
Sql> INSERT into dual values (' Y ');
1 row created.
Sql> commit;
Commit complete.
Sql> INSERT into dual values (' X ');
1 row created.
Sql> INSERT into dual values (' Z ');
1 row created.
Sql> commit;
Commit complete.
Sql> Select COUNT (*) from dual;
COUNT (*)
----------
4
Sql> SELECT * from dual;
DUMMY
-----
X

/*
--False We insert a piece of data, the dual table does not return a row, but a multiline record, what will be the result?
Sql> INSERT into dual values (' Y ');
1 rows inserted
Sql> commit;
Submit Complete
Sql> SELECT * from dual;
DUMMY
-----
X
Y
Sql> select Sysdate from dual;
Sysdate
-----------
2004-12-15
2004-12-15

This is the time to return two records, which can also cause problems. In the by using
Select Sysdate to v_sysdate from dual;
To obtain time or other information stored procedures, Oracle throws an Too_many_rows (ORA-01422) exception.
Therefore, it is necessary to ensure that there is only one record within the dual table. Of course, it is not possible to release the Update,insert,delete permission of the dual table freely, which is very dangerous for the system.
*/

--Cut off the watch.
Sql> TRUNCATE TABLE dual;
Table truncated.
Sql> Select COUNT (*) from dual;
COUNT (*)
----------
0
Sql> SELECT * from dual;
No rows selected
Sql> select Sysdate from dual;
No rows selected

--Try to remove the data from the dual table to see what happens:
Sql> Delete from dual;
1 row deleted
Sql> SELECT * from dual;
DUMMY
-----
Sql> select Sysdate from dual;
Sysdate
-----------
/*
We will not get the system date. Because, Sysdate is a function that is used for every row of data. Now that there is no data, it is natural that the system date cannot be removed.
This for a lot of use
Select Sysdate to v_sysdate from dual;
This approach is fatal for stored procedures that take system time and other information, because Oracle throws an No_data_found (ORA-01403) exception immediately, and even if the exception is caught, the stored procedure will not complete the requested action correctly.
*/
--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 (*)
----------
2
Sql> Delete from dual;
1 row deleted
Sql> commit;
Submit 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 it is now a 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's use DUAL) may 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. Butdon't do it.Dropthe operation of the table, otherwise it will make the system useless, the database can not be reportedDatabase startup crashes with ORA-1092error.
3, ifDUALtable is"unfortunate"post-deletion 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.

Specific operation:

--Log in with the SYS user.
Sql>
sql> shutdown immediatecreate pfile= ' D:/pfile.bak ' from SPFile
--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.

An explanation of the dual table in Oracle