Dual virtual table in oracle

The dual virtual table dual in oracle 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. To view the current user, you can execute the following statement select user from dual in SQL Plus; 2. It is used to call the system function select to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss') from dual; -- Obtain the current system time select SYS_CONTEXT ('userenv', 'terminal') from dual; -- Obtain the host name from dual; -- Obtain the current locale select dbms_random.random from dual; -- obtain a random number 3. Obtain the next value or current value of the sequence. Use the following statement to select your_sequence.nextval from dual; -- Obtain the values of the sequence your_sequence. Select your_sequence.currval from dual; -- Obtain the current value of the sequence your_sequence. 4. You can use the calculator select 7*9 from dual; ------ In the Oracle System, 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 in d: pfile. add the last entry in the bak file: replication_dependency_tracking = FALSE to 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 background of DUAL is? Does it have any special behavior? Let's take a look. first, understand 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 originally DUAL is a TABLE in SYS schema, and then it is used as public synonym for other database users. 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. There is only one record. The DUMMY value is 'x '. it's normal. It's nothing strange. well, there's something amazing! 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 records again, the strange thing is that SQL> select * from dual; DUMMY ---------- the record just inserted by X 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 was deleted. SQL> select * from dual; DUMMY ---------- Y ORACLE performed some internal processing on the operations on the DUAL table, make sure that only one record is returned in the DUAL table. of course, this write operation is invisible.