Home » SQL & PL/SQL » SQL & PL/SQL » DUAL TABLE
DUAL TABLE [message #242291] Fri, 01 June 2007 08:47 Go to next message
ganeshsv
Messages: 51
Registered: January 2006
Member
Does dual table contains more than one column in any of the stage of the database?
Does it contain more than one column in NOMOUNT mode?

Thanks
SG
Re: DUAL TABLE [message #242292 is a reply to message #242291] Fri, 01 June 2007 08:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oracle version?

In nomount it always contains 1 row.
Er, at least in mount, I'm not sure you can query it in nomount.

Regards
Michel

[Updated on: Fri, 01 June 2007 08:55]

Report message to a moderator

Re: DUAL TABLE [message #242297 is a reply to message #242291] Fri, 01 June 2007 09:17 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
SQL> startup nomount
ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  1250428 bytes
Variable Size             243272580 bytes
Database Buffers          360710144 bytes
Redo Buffers                7135232 bytes
SQL> select * from dual;

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

SQL> alter database mount;

Database altered.

SQL> select * from dual;

ADDR           INDX    INST_ID D
-------- ---------- ---------- -
0362AD54          0          1 X

SQL> alter database open
  2  ;

Database altered.

SQL> select * from dual;

D
-
X


Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

Re: DUAL TABLE [message #242304 is a reply to message #242297] Fri, 01 June 2007 09:37 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Hmmmm... Funny thing, that.

Never have seen this before either, but it's the same on 9.X it seems.

/home/oracle > sqlplus /nolog

SQL*Plus: Release 9.2.0.8.0 - Production on Fr Jun 1 16:30:35 2007

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> connect / as sysdba
Connected to an idle instance.
SQL> select * from dual;
select * from dual
*
ERROR at line 1:
ORA-01034: ORACLE not available


SQL> startup nomount
ORACLE instance started.

Total System Global Area  253197512 bytes
Fixed Size                   740552 bytes
Variable Size             218103808 bytes
Database Buffers           33554432 bytes
Redo Buffers                 798720 bytes
SQL> select * from dual;

ADDR                   INDX    INST_ID D
---------------- ---------- ---------- -
0000000110157FAC          0          1 X

SQL> alter database mount;

Database altered.

SQL> select * from dual;

ADDR                   INDX    INST_ID D
---------------- ---------- ---------- -
0000000110157FAC          0          1 X

SQL> alter database open;

Database altered.

SQL> select * from dual;

D
-
X

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production
CORE    9.2.0.8.0       Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production

Re: DUAL TABLE [message #242309 is a reply to message #242291] Fri, 01 June 2007 09:48 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
If you select * from x$dual you will get the same result when the db is open (i.e. the 3 columns) my guess is that in nomount/mount when you select from dual, you are actually selecting from x$dual, once you alter into open mode, then you start to select from the dual table. I'm about to read an asktom on it to see if I can make sense Smile
Re: DUAL TABLE [message #242315 is a reply to message #242291] Fri, 01 June 2007 10:13 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
It would appear that this 'special case' Dual comes into play for the use of RMAN to be able to select from dual when the db is shutdown.
Re: DUAL TABLE [message #242327 is a reply to message #242315] Fri, 01 June 2007 10:40 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Not only RMAN but other internal stuff.
This is why it is present at the lowest level.

Regards
Michel
Previous Topic: PLS-00553 Trigger compilation error
Next Topic: Need to Tune this query
Goto Forum:
  


Current Time: Thu Dec 08 18:27:09 CST 2016

Total time taken to generate the page: 0.10812 seconds