1. Use of DUAL tables
Dual is an existing table in Oracle that can be read by any user. It is often used in Select statement blocks without a target table-view the current connected user
SQL> select user from dual;
USER
------------------------------
SYSTEM -- view the current date and time
SQL> select sysdate from dual;
SYSDATE
-----------
2007-1-24 1SQL> select to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss') from dual;
TO_CHAR (SYSDATE, 'yyyy-MM-DDHH2
------------------------------
15:02:47 -- used as a calculator
SQL> select 1 + 2 from dual;
1 + 2
----------
3 -- view the Sequence Value
SQL> create sequence aaa increment by 1 start with 1;
SQL> select aaa. nextval from dual;
NEXTVAL
----------
1sql> select AAA. currval from dual;
Currval
----------
12. Dual table testing and analysis
Dual is a table with one row and one column. If you execute insert, delete, and truncate operations in the table, many programs may fail. The results also vary with SQL * Plus, PL/SQL Dev, and other tools. -- View what dual is.
-- Dual is a table in SYS schema, which is then used by other database users in the form of public synonym.
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 the table structure. There is only one field DUMMY, In the VARCHAR2 (1) type.
SQL> desc dual
Name Type Nullable Default Comments
---------------------------------------
DUMMY VARCHAR2 (1) Structure of the Y--DUAL table:
Create table SYS. DUAL
(
DUMMY VARCHAR2 (1)
)
Tablespace SYSTEM
Pctfree 10
Pctused 40
Initrans 1
Maxtrans 255
Storage
(
Initial 16 K
Next 16 K
Minextents 1
Maxextents 505
Pctincrease 50
);/*
I am very confused. Why does ORACLE use VARCHAR (1) and CHAR (1? From the perspective of such a table structure, the DUAL table is designed to be as simple as possible to reduce the retrieval overhead.
Also, the DUAL table is created in the SYSTEM tablespace. The first reason is that the DUAL table is created by the user SYS. The default tablespace is SYSTEM, it is good for system performance to store the table that may be frequently queried separately from the user table. It is not enough to create a table or a synonym. DUAL is under the SYS Schema. Therefore, other users cannot log on to the table. Therefore, authorization is required:
Grant select on SYS. DUAL to PUBLIC with grant option;
Grant the Select permission to the public. Next, let's look at the data in the DUAL table. In fact, the data in the DUAL table has a very important relationship with the ORACLE database environment (ORACLE will not be paralyzed by this, however, many stored procedures and some queries cannot be correctly executed ).
*/-- Query the number of rows
-- After the database is created, a record is inserted in the DUAL table. In my opinion, the value of the DUMMY field does not matter. What is important is the number of records in the DUAL table.
SQL> select count (*) from dual;
COUNT (*)
----------
1
SQL> select * from dual;
DUMMY
-----
X -- insert data and query records. Only one row of records is returned.
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 /*
-- If we insert a piece of data, the dual table does not return a row but multiple rows of records. What is the result?
SQL> insert into dual values ('y ');
One row has been inserted with SQL> commit;
SQL> select * from dual;
Dummy
-----
X
Y
SQL> select sysdate from dual;
Sysdate
-----------
2004-12-15
At this time, two records are returned, which will also cause problems. When using
Select sysdate into v_sysdate from dual;
Oracle throws a too_many_rows (ORA-01422) exception for stored procedures that get time or other information.
Therefore, ensure that there is only one record in the dual table. Of course, the dual table's update, insert, and delete permissions cannot be released at will, which is very dangerous to the system.
*/-- Cut off the table
SQL> truncate table dual;
Table truncated. SQL> select count (*) from dual;
Count (*)
----------
0sql> select * from dual;
No rows selectedsql> select sysdate from dual;
No rows selected -- try to delete the data in the dual table to see what results will appear:
SQL> Delete from dual;
1 line of deleted SQL> select * from dual;
Dummy
----- SQL> select sysdate from dual;
Sysdate
-----------/*
We cannot get the system date. Because sysdate is a function that acts on every data row. If no data is available, the system date cannot be retrieved.
This is widely used.
Select sysdate into v_sysdate from dual;
This method is fatal for stored procedures that take system time and other information, because Oracle immediately throws a no_data_found (ORA-01403) exception, even if the exception is caught, stored Procedures cannot correctly complete required actions.
*/-- For delete operations, Oracle performs some internal processing on the dual table operations, and tries to ensure that only one record is returned in the dual table. Of course, this write internal operation is invisible.
-- No matter how many records exist in the table (except for no records), Oracle deletes only one data record for each delete operation.
SQL> select count (*) from dual;
Count (*)
----------
2sql> Delete from dual;
1 line of deleted SQL> commit;
SQL> select count (*) from dual;
Count (*)
----------
1 /*
Appendix: Oracle interpretation of unusual features of dual tables there is internalized code that makes this happen. code checks that ensurethat 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.
So dual shoshould always have 1 and only 1 row */dual tables can be inserted, updated, deleted, or dropped. 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. 3. If the dual table is "Unfortunately" deleted and restored:
Log On with the Sys user.
Create a dual table.
Grant select permission to the public (for example, SQL, but do not grant update, insert, and delete permissions ).
Insert a record to the dual table (only one record): insert into dual values ('x ');
Submit changes. -- Log On with the Sys user.
SQL> Create pfile = 'd: \ pfile. Bak 'from spfile
SQL> shutdown immediate -- add the 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 10 pctused 4;
SQL> insert into dual values ('x ');
SQL> commit;
SQL> Grant select on dual to Public;
Authorization successful.
SQL> select * from dual;
D
-
X
SQL> shutdown immediate
The database has been closed.
The database has been detached.
The ORACLE routine has been disabled.
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 has been loaded.
The database has been opened.
SQL>
-- OK, the following can be used normally.
