Funny Test of Dual Table
C: /> SQLPLUS "/ as sysdba" SQL * Plus: Release 9.0.1.0.1 - Production on Thu Aug 8 09:20:57 2002 (c) Copyright 2001 Oracle Corporation. All Rights Reserved.connected to: Oracle9i Enterprise Edition Release 9.0.1.1.1 - Productionwith The Partitioning OptionjServer Release 9.0.1.1.1 - Productionsql> Select Count (*) from Dual; Count (*) ---------- 1SQL> SELECT * from DUAL; d -Xsql> INSERT INTO DUAL VALUES ('Y'); 1 row created.sql> commit; commit completion.sql> INSERT INTO DUAL VALUES; 1 ROW CREATED.SQL> INSERT INTO DUAL VALUES ('Z' ); 1 row created.sql> commit; commit completion.sql> select count (*) from dual; count (*) ---------- 4SQL> Select * from dual; d-xsql> Select sysdate From dual; sysdate --------- 08-aug-02sql> select count (*) from dual; count (*) ---------- 4SQL> TRUNCATE TABLE DUAL; TABLE TRUNCATED. SQL> Select Count (*) from Dual; Count (*) ---------- 0SQL> Select * from Dual; no rows SELECTEDSQL> SELECT SYSDATE ALECTEDSQL> SELECT SYSDATE AROAL; NO ROWS SELECTEDSQL> INSERT INTO DUAL VALUES (' X '); 1 row created.sql> commit; commit complel Ete.sql> select * from dual; d-xsql> select count (*) from dual; count (*) ---------- 1SQL> SELECT SYSDATE from Dual; sysdate ------- --08-aug-02sql>
***********************************************
Author: 4pal Date: 02-08-08 22:01
嘿嘿. Snow knows what DUAL is? I am still unclear.
Author: rejoice999 Date: 02-08-08 23:55
Dual is a list of a row. If you add a line in Rig, it will lead to a lot of programs. So starting from Oracle8, Oracle adds code to Dual special care, there will be a Snow moderator's Funny Test Result. Http://www.itpub.net/showthread.php...&highlight=dual
Quote:
Originally published by 4pal. Snow knows what DUAL is? I am still unclear.
Author: oldwain Date: 02-08-10 21:19
Quote:
I originally released by Rejoice999, starting from Oracle8, Oracle adds code to DUAL special care, no! No! Oracle does not take special care for DUAL, special care is SQL * plus. Use other tools (such as PL / SQL DEV, etc.) Select * from Dual will be real records. In addition, the results of Create Table in SQLPLUS .. as select * from dual, INSET ..select * from Dual is true. Personally think that Sqlplus is like this little smart, it is Very stupid little smart.
Author: Fenng Date: 02-08-10 23:24
Looking at this post, then I checked it to askTOM, I found it. I really had a post About it .ny askedhi tomfeels good to post a question after QUITE SOME.LOOK AT THE FOLLOWING: SQL> DESC DUALNAME NULL? TYPE ------------------------------------- --------------------------- Dummy varchar2 (1) SQL> Select * from dual; d-xsql> select count (*) from Dual ; Count (*) ---------- 1SQL> select 'sdfsdfsdfsdfsdfsdf' from dual; 'sdfsdfsdfsdfsdfsd ------------------ SDFSDFSDFSDFSDFSDFSQL> SELECT 3434334 from DUAL ; 3434334 ---------- 3434334SQL> truncate table dual; truncate table dual * ERROR at line 1: ORA-04020: deadlock detected while trying to lock object3510531527724648449742036SQL> delete from dual; delete from dual * ERROR at line 1: ORA-01031: insufficient privilegesSQL> connect system / managerConnected.SQL> / 1 row deleted.SQL> commit; Commit complete.SQL> select * from dua; select * from dua * ERROR at line 1: ORA-00942: table Or view does not existsql> select * from dual; no rows success, what is its purpose.2.why does it Contain Only One Column with Dattype Varchar2, Why Not Number .3.Does It Contain One Row by Default.4.Why Do We Usually Select User From Dual, Why Cant i Do It Like SQL> SELECT User from Emp Where Rownum <2; User ------------------------------ Scott5.Does This Mean That We Are Using The Dual Table Only for the Convenience That It Has Only One Row, And It Will Return Only One Row Back, When We Give Queries Like Select User from Dual6.SQL> Select Count (*) from Dual; Count (*) ---------- 2Sql> Insert Into Dual Values; 1 row created.sql> INSERT INTO DUAL VALUES ('x'); 1 row created.sql> commit; commit completion.sql> select count (*) from dual; count (*) - --------- 4SQL>
Select * from dual; D-xwhy is it returbining only one row, IT Has to return 4 rows, when it has allowed me to insert 4 rows.7.sql> delete from dual; 1 row deleted.sql> Select * from dual ; D-XSQL> Select count (*) from dual; count (*) ---------- 3i Want to Delete All the Rows, I do a delete from Dual, And Ooo Mamalook AT IT, IT deletes Only One Row, Why? 8.sql> Delete from Dual; 1 Row Deleted.sql> Delete from Dual; 1 Row Deleted.SQL> Select Count (*) from Dual; Count (*) -------- --1Now We Have ONLY One Row In Dual.Sql> Create or Replace Function Foo Return Number2 As3 x Number; 4 Begin5 x: = 1; 6 Return 1; 7 End; 8 / Function Created.sql > SELECT FOO from Dual; Foo ---------- 1SQL> Insert Into Dual Values ('x'); 1 row created.sql> commit; commit completion.sql> select count (*) from dual; Count (*) ---------- 2Sql> Select foo from dual; foo ---------- 1SHOULDNT IT RETURN 2 ROWS OF VALUE 1? Because, Look Below, I have got 14 Rows Back, Because I Had 14 Rows in The Table.Sql> Select Foo Fro M Emp; Foo ---------- 1111111111foo ---------- 11114 rows SELECTED.SQL>
Kindly Elucidate The Concept of Dual Table, And IF Possible Illustrate It.THANK you ---------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------- And We Said. ..Ount me Just Start by Saying - Dual Is Owned by Sys. Sys owns The Data Dictionary, Therefore Dual IS Part of The Data Dictionary. You are not to modify the data Dictionary Via Sql Ever - Wierd Things Can and Will Happen - you are just demonstrating some of them We can make many strange things happen in Oracle by updating the data dictionary It is neither recommend, supported nor a very good idea.1.What is the dual table, what is its purpose... . dual is just a convienence table you do not need to use it, you can use anything you want The advantage to dual is the optimizer understands dual is a special one row, one column table -. when you use it in queries, IT Uses this Knowledge When Developing The Plan.why Does It Contain Only One Column with DataType Varchar2, Why NOT Number .Truly, Why No. Why Not a Date You Would Ask The Column, ITS Name, ITS DataType and Even ITS Value Are Not Relevant. Dual Exists Solely As a Means To Have A 1 Row Table We can reliably select from. Thats all.3.does it Contain One Row By Default.yes, WHEN WE BUILD THE DATABASE, WE BUILD DUAL AND PUT A SINGLE ROW ITUSER FROM <2; Truly , why can't you? Is Something preventing you from doing so? you can if you want. me, I'll Stick with "SELECT User from DUAL"
I Know It has at Least 1 and at MOST 1 ROW. I KNOW The Optimizer Knows All About Dual and Does The MOST Effic Thing for me.5) YES6) The Optimizer Understands Dual IS a MAGIC, Special 1 row table. It stopped on the select * because there is to be one row in there. Its just the way it works. Hopefully you reset dual back to 1 row after your testing or you just totally broke your database! 7) like I said , duals magic, the optimizer knows all about what DUAL should be and does things based on that. 8) dual = magic. dual is a one row table however having more then 1 or less then one is dangerous. You are updating the data dictionary YOULD NATURALLY EXPECT VERY BAD Things to happen.ok, Here Is Some Trivia for you out there. How Did i do this: svrmgr> select * from dual; d-x1 row selected.svrmgr> ??????? ????????????????????; statement processed.svrmgr> select * from dual; addr index inst_id d -------------------- ------ -01680288 0 1 x1 row selected.what Was That Magic Co mmand Author:? Fenng Time: 02-08-10 23:26
http://asktom.racle.com/pls/ask/f?.6388 ,{dual} This is the connection on TOM
Author: biti_rainy Date: 02-08-11 09:49
HEHE
Looking at the definition part of the authority and the caller rights, combined with Oracle's on the so-called "fine access control" on the basis of using the sys_context () function, you can do such a table, you can do a ratio that meets your needs. Dual makes you satisfied with the table
