Funny test of dual table

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:
Oracle9i Enterprise Edition Release 9.0.1.1.1-Production
With the partitioning Option
Jserver release 9.0.1.1.1-Production

SQL> select count (*) from dual;

Count (*)
----------
1

SQL> select * from dual;

D
-
X

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;

D
-
X

SQL> select sysdate from dual;

Sysdate
---------
08-aug-02

SQL> select count (*) from dual;

Count (*)
----------
4

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

SQL> insert into dual values ('x ');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from dual;

D
-
X

SQL> select count (*) from dual;

Count (*)
----------
1

SQL> select sysdate from dual;

Sysdate
---------
08-aug-02

SQL>

***************************************

Author:4palTime:02-08-08

Hey. Does snow know what dual is? I still don't know

Author:Rejoice999Time:02-08-08 23:55

Dual is a table with one row and one column. if you add a row, many programs may fail. Therefore, from oracle8 onwards, Oracle added code to take special care of dual before the funny test result of the snow moderator will be available.
Http://www.itpub.net/showthread.php...&highlight=dual

Quote:

Initially released by 4pal
Hey. Does snow know what dual is? I still don't know

Author:OldwainTime:02-08-10 21:19

Quote:

Initially released by rejoice999
Since oracle8, Oracle has added code to take special care of dual,

No! No!

Oracle does not take special care of Dual. The special care is SQL * Plus.

Using other tools (such as PL/SQL Dev), you will find that select * from dual will get real records.

In addition, execute create table... as select * from dual, inset... select * from dual in sqlplus.

I personally think that sqlplus is a silly little clever.

Author:FenngTime:02-08-10 23: 24

I followed this post and checked it on asktom. I found that there was a post about it.
You asked
Hi Tom

Feels good to post a question after quite some time.
Look at the following:
SQL> DESC dual
Name null? Type
-----------------------------------------------------------------------------
Dummy varchar2 (1)

SQL> select * from dual;

D
-
X

SQL> select count (*) from dual;

Count (*)
----------
1

SQL> select 'sdfsdfsdfsdfsdf 'from dual;

'Sdfsdfsdfsdfsdfsd
------------------
Sdfsdfsdfsdfsdfsdf

SQL> select 3434334 from dual;

3434334
----------
3434334

SQL> truncate table dual;
Truncate table dual
*
Error at line 1:
ORA-04020: deadlock detected while trying to lock object
3510531527724648449742036

SQL> Delete from dual;
Delete from dual
*
Error at line 1:
ORA-01031: insufficient privileges

SQL> connect system/Manager
Connected.
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 exist

SQL> select * from dual;

No rows selected

1. What is the dual table, what is its purpose.
2. Why does it contain only one column with datatype 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
------------------------------
Scott

5. 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 dual

6. SQL> select count (*) from dual;

Count (*)
----------
2

SQL> insert into dual values ('x ');

1 row created.

SQL> insert into dual values ('x ');

1 row created.

SQL> commit;

Commit complete.

SQL> select count (*) from dual;

Count (*)
----------
4

SQL> select * from dual;

D
-
X

Why is it returning 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
-
X

SQL> select count (*) from dual;

Count (*)
----------
3

I want to delete all the rows, I do a delete from dual, and oooo mama
Look 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 (*)
----------
1

Now we have only one row in dual.

Lets create a example function.

SQL> Create or replace function Foo return number
2
3 x number;
4 begin
5 x: = 1;
6 return 1;
7 end;
8/

Function created.

SQL> select Foo from dual;

Foo
----------
1

SQL> insert into dual values ('x ');

1 row created.

SQL> commit;

Commit complete.

SQL> select count (*) from dual;

Count (*)
----------
2

SQL> select Foo from dual;

Foo
----------
1

Shouldnt it return 2 rows of value 1?

Because, look below, I have got 14 rows back, because I had 14 rows in
Table.

SQL> select Foo from EMP;

Foo
----------
1
1
1
1
1
1
1
1
1
1
1

Foo
----------
1
1
1

14 rows selected.

SQL>

Kindly elucidate the concept of dual table, and if possible implements strate it.

Thank you

--------------------------------------------------------------------------------
And we said...

Let 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 your 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 don't 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.

2. Why does it contain only one column with datatype varchar2, why not
Number.

Truly, why no. why not a date you wocould ask then. 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 in it.

4. Why do we usually select user from dual,
Why cant I do it like
SQL> Select User from EMP where rownum <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 dual exists. I know it has at least 1 and at most 1 row. I know the optimizer knows all about dual and does the most efficient thing for me.

5) Yes

6) 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 shoshould 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. you shoshould naturally perform CT very bad things to happen.

OK, here is some trivia for you out there. How did I do this:

Svrmgr> select * from dual;
D
-
X
1 row selected.

Svrmgr> ????????????????????;
Statement processed.

Svrmgr> select * from dual;
ADDR indx inst_id d
-----------------------------
01680288 0 1 x
1 row selected.

What was that magic command?

Author:FenngTime:02-08-10 23: 26

Http://asktom.oracle.com/pls/ask/f ?... 6388, % 7 bdual % 7D

This is the connection from Tom.

Author:Biti_rainyTime:02-08-11

Hehe

Read the definition of the permissions and caller permissions, combined with Oracle's so-called "fine access control"
Based on the sys_context () function

You can make such a table yourself.

In addition, you can create a table that meets your needs more satisfied than dual.