Home » RDBMS Server » Server Administration » DUAL, FAST DUAL et. al.
DUAL, FAST DUAL et. al. [message #515910] Thu, 14 July 2011 03:07 Go to next message
Littlefoot
Messages: 19526
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As suggested by John Watson, LF split this topic from Not able to gather Statistics of neither schema nor table



Michel Cadot wrote on Thu, 14 July 2011 09:51
Littlefoot wrote on Thu, 14 July 2011 07:56
So it seems that DUAL is OK (as it contains only 1 record).

No because Oracle now bypasses dual table and use "fast dual" access (x$dual).
This is the reason I posted a so complicated script to check the content of the real table.

True, that's beyond my knowledge, but ...
  • you suggested
    select rowid from sys.dual;

  • he ran
    SQL> select rowid from sys.dual;
    
    ROWID
    ------------------
    AAAAECAABAAAAgiAAA
    
    SQL>
    and got one record ony
What does your "No" mean? I expected 1 record, he got 1 record (being it "dual" or "fast dual").

I Googled a little bit for "fast dual" so I guess I understood the concept, saying something that if you SELECT 'X' FROM DUAL, "fast dual" is used. If you SELECT DUMMY FROM DUAL, "dual" is used. I suppose you used SELECT ROWID FROM DUAL for the same reason (to force it to use "dual")? If so, well, the SELECT returned a single record anyway. Doesn't that mean that DUAL is really OK?

[Updated on: Thu, 14 July 2011 03:43]

Report message to a moderator

Re: Not able to gather Statistics of neither schema nor table [message #515913 is a reply to message #515910] Thu, 14 July 2011 03:25 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I suggested this just to get the rowid of the row that was created at database creation time.
This rowid is not retrieved by the query from the actual table but from an internal variable.
The only purpose of this query in my script is to fill the &rid SQL*Plus variable.

Let me show you. First I screw my dual table:
SYS> insert into dual values ('Y');

1 row created.

SYS> insert into dual values ('X');

1 row created.

SYS> commit;

Commit complete.

Now we check that we don't see anything from a normal user:
MICHEL> select * from dual;
D
-
X

1 row selected.

MICHEL> select count(*) from dual;
  COUNT(*)
----------
         1

1 row selected.

MICHEL> select rowid from sys.dual;
ROWID
------------------
AAAAECAABAAAAgiAAA

1 row selected.


In the end, I fix my dual table:
SYS> col rowid new_value rid
SYS> select rowid from sys.dual;
ROWID
------------------
AAAAECAABAAAAgiAAA

1 row selected.

SYS> delete sys.dual where rowid != '&rid';

1 row deleted.

SYS> delete sys.dual where rowid != '&rid';

1 row deleted.

SYS> delete sys.dual where rowid != '&rid';

0 rows deleted.

SYS> select * from sys.dual where rowid='&rid';
D
-
X

1 row selected.

SYS> select  dummy, count(*) from sys.dual group by dummy;
D   COUNT(*)
- ----------
X          1

1 row selected.

SYS> commit;

Commit complete.

As you cann see, Oracle knows there is only one row in the table and so delete only one each time I execute DELETE.
The two last statements are to check there is still the original row in the table.

Regards
Michel
Re: Not able to gather Statistics of neither schema nor table [message #515915 is a reply to message #515913] Thu, 14 July 2011 03:33 Go to previous messageGo to next message
Littlefoot
Messages: 19526
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Now that's really cool!

How come a "normal" user doesn't see newly added records? You did COMMIT those inserts, after all?

I couldn't believe my own eyes seeing that the first DELETE deleted one record only ... gee, what a mysterious beast Oracle is.

Thank you, that was an interesting lesson.
Re: Not able to gather Statistics of neither schema nor table [message #515916 is a reply to message #515913] Thu, 14 July 2011 03:37 Go to previous messageGo to next message
John Watson
Messages: 4515
Registered: January 2010
Location: Global Village
Senior Member
Would it be possible for this topic to be split? Sriram has already given the answer to the original question (bug 3945156, fixed in 10.2.0.4). The subsequent discussion of DUAL has been very instructive, and I'ld like to be able to get to it through its own topic.

If it is a hassle to do that, forget it. I find this site very useful, and have no right to ask for more.
Re: Not able to gather Statistics of neither schema nor table [message #515944 is a reply to message #515916] Thu, 14 July 2011 05:10 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
How come a "normal" user doesn't see newly added records? You did COMMIT those inserts, after all?

It sees them but Oracle bypasses the result and returns what it wants.
See the following test (10.2.0.4 but I bet the result is different across versions and even patchsets).
I screw up again my DUAL table:
SYS> insert into dual values ('X');

1 row created.

SYS> insert into dual values ('Y');

1 row created.

SYS> commit;

Commit complete.

Then I trace the session to query DUAL from my account (after flushing the sga):
MICHEL> alter system flush shared_pool;

System altered.

MICHEL> alter system flush buffer_cache;

System altered.

MICHEL> set serveroutput off
MICHEL> @traceon

Session altered.

MICHEL> select count(*) from sys.dual;
  COUNT(*)
----------
         1

1 row selected.

MICHEL> select dummy from sys.dual;
D
-
X

1 row selected.

MICHEL> select * from sys.dual;
D
-
X

1 row selected.

First the trace file and TKPROF show a bunch of recursive queries to get the information about sys.dual.
Then there is the part concerning the first query:
select count(*) 
from
 sys.dual


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.06       0.03          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          0          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.06       0.03          0          0          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 50  

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=0 pr=0 pw=0 time=56 us)
      1   FAST DUAL  (cr=0 pr=0 pw=0 time=18 us)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2        2.23          2.24

As you can see Oracle takes the FAST DUAL access path which is x$dual with no access to the actual table, so read, no io...

Then the second query, after one more recursive query to get if there is any histogram statistics on the table:
select dummy 
from
 sys.dual


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          2          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          2          3          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 50  

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS FULL DUAL (cr=3 pr=2 pw=0 time=1112 us)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  db file sequential read                         2        0.00          0.00
  SQL*Net message from client                     2        6.73          6.73

As you can see, Oracle actually read the real table (3 reads, 2 IO, 2 db sequential read waits) but returns ONE row. Why? because it knows there is (should be) only one row and then ignore the actual result to give us the first row.
Then the last one to confirm:
select * 
from
 sys.dual


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          3          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 50  

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS FULL DUAL (cr=3 pr=0 pw=0 time=108 us)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2        3.95          3.96

Still the 3 reads but no more io (the table is in the cache).
Oracle knows there should be one row and so returns one row and does not care if there are more rows.

Regards
Michel

Re: Not able to gather Statistics of neither schema nor table [message #515949 is a reply to message #515944] Thu, 14 July 2011 05:22 Go to previous messageGo to next message
Littlefoot
Messages: 19526
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Uh, oh ... I see. That's, though, somewhat "strange" because Oracle misinterprets reality. There actually ARE 3 records in the DUAL table, but it (Oracle) *knows* it should (actually: MUST) contain just a single record - so it returns that "single" record. I guess that my "misinterpreted reality" is Oracle internal stuff and suppose that normal user's tables don't behave like that.

As you, obviously, know enormously more about it than I do, would you be so kind and do another test (I'm afraid I'd screw my database and don't really feel like restoring it if I don't have to): what happens if you DELETE everything from dual, so that it is empty? What would Oracle return in that case? Would it "make up" one record? You know, some kind of analogy: "if there are too many records, return just one. If there are no records, also return (an imaginary) one".
Re: Not able to gather Statistics of neither schema nor table [message #515952 is a reply to message #515949] Thu, 14 July 2011 05:26 Go to previous messageGo to next message
ramoradba
Messages: 2452
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1562813956388
Re: Not able to gather Statistics of neither schema nor table [message #515958 is a reply to message #515949] Thu, 14 July 2011 05:32 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
what happens if you DELETE everything from dual, so that it is empty?

It is the next test I wanted to show but it is 12:30 in France time to eat (and it is important for us, above all on our National Day), I will make the test and post it when I'll return, say in 3 or 4 hours.

Regards
Michel
Re: Not able to gather Statistics of neither schema nor table [message #515959 is a reply to message #515952] Thu, 14 July 2011 05:33 Go to previous messageGo to next message
Littlefoot
Messages: 19526
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Thank you, Ramoradba, LMGTFY works just fine for me. Saw that discussion while Googling (see the initial post here). There are some DELETE FROM DUAL there, but not exactly what hoped Michel would do.

[Updated on: Thu, 14 July 2011 05:35]

Report message to a moderator

Re: Not able to gather Statistics of neither schema nor table [message #515960 is a reply to message #515959] Thu, 14 July 2011 05:35 Go to previous messageGo to next message
Littlefoot
Messages: 19526
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
No problem, Michel - bon appétit et bonne La Fête Nationale!
Re: Not able to gather Statistics of neither schema nor table [message #516010 is a reply to message #515960] Thu, 14 July 2011 08:50 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Here it is:
SYS> select rowid, d.* from sys.dual d;
ROWID              D
------------------ -
AAAAECAABAAAAgiAAA X

1 row selected.

SYS> delete sys.dual;

1 row deleted.

SYS> select rowid, d.* from sys.dual d;

no rows selected

SYS> commit;

Commit complete.


I add the TKPROF output between each statement, the explaination is the same as the previous one:
MICHEL> set serveroutput off
MICHEL> alter system flush shared_pool;

System altered.

MICHEL> alter system flush buffer_cache;

System altered.

MICHEL> @traceon

Session altered.

MICHEL> select count(*) from sys.dual;
  COUNT(*)
----------
         1

1 row selected.

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.03       0.03          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          0          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.03       0.03          0          0          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 50  

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=0 pr=0 pw=0 time=36 us)
      1   FAST DUAL  (cr=0 pr=0 pw=0 time=13 us)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2        4.30          4.30

--------------------------------------------------------------------------------

MICHEL> select dummy from sys.dual;

no rows selected

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          2          3          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          2          3          0           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 50  

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  TABLE ACCESS FULL DUAL (cr=3 pr=2 pw=0 time=959 us)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       1        0.00          0.00
  db file sequential read                         2        0.00          0.00
  SQL*Net message from client                     1        6.05          6.05

--------------------------------------------------------------------------------

MICHEL> select * from sys.dual;

no rows selected

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          3          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          3          0           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 50  

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  TABLE ACCESS FULL DUAL (cr=3 pr=0 pw=0 time=63 us)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1       14.27         14.27

--------------------------------------------------------------------------------

MICHEL> select 1 from sys.dual;
         1
----------
         1

1 row selected.

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          0          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          0          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 50  

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  FAST DUAL  (cr=0 pr=0 pw=0 time=17 us)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2        4.18          4.18

Note the difference between the last 3 ones. "select dummy or *" access the table, "select 1" does not.
We have the same thing with COUNT, compare the first one to this one:
SQL> select count(dummy) from sys.dual;
COUNT(DUMMY)
------------
           0

1 row selected.

As soon as you access the table column, you access the table but you get at most one row (it is true as well as for DELETE than for SELECT as I showed in my previous post) otherwise you access a virtual table.

Regards
Michel
Re: Not able to gather Statistics of neither schema nor table [message #516013 is a reply to message #516010] Thu, 14 July 2011 09:09 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Here are a couple of stuffs with more than one row (starting from my current empty DUAL):
SYS> insert into sys.dual values ('Y');

1 row created.

SYS> select * from sys.dual;
D
-
Y

1 row selected.

SYS> commit;

Commit complete.

SYS> insert into sys.dual select chr(ascii('A')+level-1) from dual connect by level <= 10;

10 rows created.

SYS> select * from sys.dual;
D
-
A

1 row selected.

SYS> select count(dummy) from sys.dual;
COUNT(DUMMY)
------------
          11

1 row selected.

SYS> select count(distinct dummy) from sys.dual;
COUNT(DISTINCTDUMMY)
--------------------
                  11

SYS> insert into sys.dual select chr(ascii('A')+level-1) from dual connect by level <= 10;

10 rows created.

SYS> select count(dummy) from sys.dual;
COUNT(DUMMY)
------------
          21

1 row selected.

SYS> select count(distinct dummy) from sys.dual;
COUNT(DISTINCTDUMMY)
--------------------
                  11

1 row selected.

SYS> select count(*) from sys.dual;
  COUNT(*)
----------
         1

1 row selected.

SYS> select dummy, count(*) from sys.dual group by dummy;
D   COUNT(*)
- ----------
A          1

1 row selected.

SYS> commit;

Commit complete.


In the end, I add the case of UPDATE:
SYS> update sys.dual set dummy='X' where dummy != 'X';

1 row updated.

SYS> /

1 row updated.

SYS> /

1 row updated.

SYS> /

1 row updated.

And so on until the last one.
I execute the script I provided to fix the table:
SYS> rollback;

Rollback complete.

SYS> col rowid new_value rid
SYS> select rowid, dummy from sys.dual;
ROWID              D
------------------ -
AAAAECAABAAAAgiAAA A

1 row selected.

SYS> delete sys.dual where rowid != '&rid';

1 row deleted.

SYS> /

1 row deleted.

... 20 times 1 row deleted ...

SYS> /

1 row deleted.

SYS> /

0 rows deleted.

SYS> select rowid, dummy from sys.dual;
ROWID              D
------------------ -
AAAAECAABAAAAgiAAA A

1 row selected.

SYS> select count(dummy) from sys.dual;
COUNT(DUMMY)
------------
           1

1 row selected.

SYS> update sys.dual set dummy='X';

1 row updated.

SYS> select rowid, dummy from sys.dual;
ROWID              D
------------------ -
AAAAECAABAAAAgiAAA X

1 row selected.

SYS> commit;

Commit complete.

Regards
Michel


Re: Not able to gather Statistics of neither schema nor table [message #516043 is a reply to message #516013] Thu, 14 July 2011 13:49 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I talked several times about x$dual.
What is it?
Before it is used for the FAST DUAL access, it was used to query DUAL when the database is not opened and so the instance can't access the table:
SYS> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS> startup nomount
ORACLE instance started.
Total System Global Area  209715200 bytes
Fixed Size                  1295832 bytes
Variable Size             130026024 bytes
Database Buffers           67108864 bytes
Redo Buffers               11284480 bytes
SYS> desc x$dual
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 ADDR                                      RAW(4)
 INDX                                      NUMBER
 INST_ID                                   NUMBER
 DUMMY                                     VARCHAR2(1)

SYS> select * from dual;
ADDR           INDX    INST_ID D
-------- ---------- ---------- -
038B05D4          0          1 X

1 row selected.

SYS> select dummy from dual;
D
-
X

1 row selected.

SYS> select dummy from sys.dual;
D
-
X

1 row selected.

And you can then do some "weird" thing like:
SYS> select inst_id from dual;
   INST_ID
----------
         1

to get the instance number, instead of:
SYS> select instance_number from v$instance;
INSTANCE_NUMBER
---------------
              1

It is long since I hacked some part of Oracle but I think I remember that SQL*Plus sends a bunch of queries on DUAL, even if the database is not opened, at least to get this (retrieved from another SQL*Plus session started after the above restart, so can't be gotten from the previous instance):
SYS> def
DEFINE _DATE           = "14/07/2011 20:37:27" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "MIKA" (CHAR)
DEFINE _USER           = "SYS" (CHAR)
DEFINE _PRIVILEGE      = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1002000400" (CHAR)
DEFINE _EDITOR         = "Notepad" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options" (CHAR)
DEFINE _O_RELEASE      = "1002000400" (CHAR)

The last two ones can only be retrieved from the server part as it is the instance version.
If you put a trace admin level on SQL*Net, client side, you will see the statements SQL*Plus sends (I'm too lazy to do it now);

Regards
Michel

Re: Not able to gather Statistics of neither schema nor table [message #516085 is a reply to message #516043] Fri, 15 July 2011 01:05 Go to previous messageGo to next message
Littlefoot
Messages: 19526
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
./fa/1578/0/ Thank you so much for all you did & examples you provided. This was really helpful and illustrative!

It seems that - if you know what you are doing - you can write code that'll work OK even though DUAL is screwed up. I guess that code that relies on a "valid" DUAL table would fail, but that's just what I already knew - don't modify data dictionary via SQL, ever.

No further questions, your honor.
How does it ruins Your database if you play with dual [message #516174 is a reply to message #516085] Fri, 15 July 2011 06:03 Go to previous message
ramoradba
Messages: 2452
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Yes...This is Why TOM says DUAL is "magic".
Now you got the proof from Michel for DML what about DDL?
Does it(oracle) allow you to perform ?If you try to drop the table and select rows from it..then

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 15 14:40:38 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select name from v$database;

NAME
---------
ORAFAQ

SQL> drop table dual;

Table dropped.

SQL> select * from dual;
select * from dual
              *
ERROR at line 1:
ORA-01775: looping chain of synonyms

SQL> create public synonym dual for x$dual;
create public synonym dual for x$dual
                      *
ERROR at line 1:
ORA-00955: name is already used by an existing object


SQL> desc dual;
SP2-0749: Cannot resolve circular path of synonym "dual"
SQL> create table dual as select * from x$dual;

Table created.


Quote:
It seems that - if you know what you are doing - you can write code that'll work OK even though DUAL is screwed up.


similar you can truncate...create case sensitive synonyms,alter its structure....then you will not allowed to alter it again.


SQL> select * from dual;

ADDR           INDX    INST_ID D
-------- ---------- ---------- -
0366CD54          0          1 X

SQL> sho user
USER is "SYS"
SQL> truncate table dual;

Table truncated.

SQL> select * from dual;

no rows selected

SQL> insert into dual select * from x$dual;

1 row created.


SQL> select * from dual;

ADDR           INDX    INST_ID D
-------- ---------- ---------- -
0366CD54          0          1 X

SQL> select count(*) from dual;

  COUNT(*)
----------
         1

SQL> select rowid from dual;

ROWID
------------------
AAAM1BAABAAAAgiAAA

SQL> select sysdate from dual;

SYSDATE
---------
15-JUL-11


SQL> rollback;

Rollback complete.


SQL>   select count(*), INST_ID from dual
  2   group by INST_ID ;

no rows selected

SQL> select * from dual;

no rows selected

SQL> insert into dual select * from x$dual;

1 row created.

SQL> commit;

Commit complete.

SQL> alter table dual modify dummy varchar2(10);

Table altered.

SQL>  select * from dual;

ADDR           INDX    INST_ID DUMMY
-------- ---------- ---------- ----------
0366CD54          0          1 X

SQL> select sysdate from dual;

SYSDATE
---------
15-JUL-11

SQL> update dual set dummy='SRIRAM' where dummy='X';

1 row updated.

SQL> select sysdate from dual;

SYSDATE
---------
15-JUL-11

SQL> select count(*) from dual;

  COUNT(*)
----------
         1

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

  COUNT(*)
----------
         1

SQL>  create or replace synonym "x$dual" for sys.dual;

Synonym created.

SQL> select * from x$dual;

ADDR           INDX    INST_ID D
-------- ---------- ---------- -
0366CD54          0          1 X

SQL>  select * from "x$dual" ;

ADDR           INDX    INST_ID DUMMY
-------- ---------- ---------- ----------
0366CD54          0          1 SRIRAM


SQL> drop table dual;
drop table dual
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01031: insufficient privileges

SQL> sho user
USER is "SYS"

SQL> truncate table dual;

Table truncated.

SQL> drop table dual;
drop table dual
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist

SQL> desc dual
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ADDR                                               RAW(4)
 INDX                                               NUMBER
 INST_ID                                            NUMBER
 DUMMY                                              VARCHAR2(10)

SQL> desc x$dual;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ADDR                                               RAW(4)
 INDX                                               NUMBER
 INST_ID                                            NUMBER
 DUMMY                                              VARCHAR2(1)

SQL> drop synonym "x$dual";
drop synonym "x$dual"
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist

SQL> select * from dual;

no rows selected

SQL> desc dual;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ADDR                                               RAW(4)
 INDX                                               NUMBER
 INST_ID                                            NUMBER
 DUMMY                                              VARCHAR2(10)

SQL> alter table dual drop column ADDR;
alter table dual drop column ADDR
*
ERROR at line 1:
ORA-12988: cannot drop column from table owned by SYS






[Updated on: Fri, 15 July 2011 06:06]

Report message to a moderator

Previous Topic: oracle 9i question
Next Topic: Oracle Rac Standard/Enterprise
Goto Forum:
  


Current Time: Sat Aug 30 11:21:56 CDT 2014

Total time taken to generate the page: 0.09108 seconds