Dual in oracle

In oracle dual explanation, oracle introduces dual to conform to the Syntax 1. From the perspective of name, dual is not a acronym, but a complete word. Dual refers to logarithm, Which is dual and dual when used as an adjective. 2. The dual table in Oracle is a single row, single column virtual table. 3. the Dual table is a table automatically created by oracle and the data dictionary. The table has only one column: DUMMY, the data type is VERCHAR2 (1), and the dual table has only one data 'x ', oracle has internal logic to ensure that there is always only one data record in the dual table. 4. Dual tables are mainly used to select system variables or evaluate the value of an expression.
5. more specifically, we have seen a simple example: SELECT sysdate FROM daul and Oracle's SELECT syntax is limited to: SELECT * | [column1 [AS alias1], column2 [AS alias2] FROM table www.2cto.com, so there is no way to query without a table name, and the time and date are not stored in any table, so the concept of dual virtual table is introduced. 1. DUAL table purpose Dual is an existing table in Oracle, which can be read by any user, commonly 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: s'') from dual; TO_CHAR (SYSDATE, ''yyyy-MM-DDHH2------------------------------2007-01-24 15:02:47 -- used as a calculator with SQL> select 1 + 2 from dual; 1 + 2 www.2cto. Com ---------- 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----------1 2. DUAL table test and analysis DUAL is a table with one row and one column. If you execute insert, delete, truncate in, this will cause many program problems. The results also vary with SQL * plus, pl/SQL dev, and other tools. -- View what DUAL is. The OBJECT-DUAL is a table in SYS schema and is 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 ---------- invalid argument SYS DUAL TABLEPUBLIC DUAL SYNONYM www.2cto.com -- view the table structure, there is only one field DUMMY, which is VARCHAR2 (1) Type SQL> desc dualName Type Nullable Default Comments ------------------------ ------- -------- DUMMY VARCHAR2 (1) Y -- DUAL table structure: create table SYS. DUAL (DUMMY VARCHAR2 (1) tablespace SYSTEMpctfree 10 pctused 40 initrans 1 maxtrans 255 storage (initial 16 Knext 16 Kminextents 1 maxextents 505 pctincrease 50);/* 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 ). */Www.2cto.com -- query the number of rows -- after the database is created, a record is inserted in the DUAL table. I personally think: the value of the DUMMY field does not matter. What is important is that the number of records in the DUAL table SQL> select count (*) from dual; COUNT (*) ---------- 1 SQL> select * from dual; DUMMY-----X -- insert data, query records, return 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 multiple rows of records, what is the result? SQL> insert into dual values ('y'); 1 line inserted www.2cto.com SQL> commit; Submit completed SQL> select * from dual; DUMMY-----XYSQL> select sysdate from dual; the SYSDATE-----------2004-12-152004-12-15 returns two records at this time, which can also cause problems. ORACLE throws a TOO_MANY_ROWS (ORA-01422) exception in stored procedures that obtain time or other information by using select sysdate into v_sysdate from dual. 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 (*) ---------- 0 www.2cto.com SQL> 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 row deleted SQL> select * from dual; DUMMY-----SQL> select sysdate from dual; SYSDATE -----------/* we cannot get the system The system date is reached. Because sysdate is a function that acts on every data row. If no data is available, the system date cannot be retrieved. This is fatal for many stored procedures that use the select sysdate into v_sysdate from dual; Method to retrieve system time and other information, because ORACLE will immediately throw a NO_DATA_FOUND (ORA-01403) even if the exception is caught, the stored procedure cannot correctly complete the required action. */-- For the DELETE operation, ORACLE performs some internal processing on the operation of the DUAL table, and tries to ensure that only one record is returned in the DUAL table. of course, this write operation is invisible-no matter how many records exist in the table (except for no records), ORACLE only deletes one data record for each DELETE operation. SQL> select count (*) from dual; COUNT (*) ---------- 2SQL> delete from dual; 1 line deleted SQL> commit; Submit completed SQL> select count (*) from dual; COUNT (*) www.2cto.com ---------- 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 sh Ocould 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 otherprebuilt 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 restored after it is deleted unfortunately, log on to the table using 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 the modification. Www.2cto.com -- log in with the sys user. SQL> create pfile = 'd: \ pfile. bak 'from spfileSQL> 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 database is disabled. The database has been detached. The ORACLE routine has been disabled. The SQL> startupORACLE routine has been started. Total System Global Area 135338868 bytesFixed Size 453492 bytesVariable Size 109051904 bytesDatabase Buffers 25165824 bytesRedo Buffers 667648 bytes database loaded. The database has been opened. SQL> www.2cto.com -- OK, the following can be used normally. Finally, I am wondering, because today I suddenly need to query the Needs of the last day of each month, so I can find a direct function in Oracle online, then I suddenly wondered why I didn't have the function for the first day. Maybe I didn't look for it carefully. Anyway, no matter how much it is, I just wrote it myself, write an SQL statement as follows:
Select to_char (last_day (to_date (to_char (sysdate, 'mm')-1, 'mm') + 1), 'yyyy-mm-dd') as startday, to_char (last_day (sysdate), 'yyyy-mm-dd') as lastday from dual; some to_char in it is useless, but in order to convert it to a consistent date format, so I still use it. It is mainly written here for the sake of memory, and my memory is poor.