Doubts (Vry Urgent!) [message #9693] |
Tue, 02 December 2003 01:49 |
Cindreen Clarence
Messages: 41 Registered: March 2003 Location: Bangalore
|
Member |
|
|
Hi All,
These are some doubts which need urgent clarification:
1.Whats the difference between VARCHAR & VARCHAR2?
2.Why cant we perform a DML operation on DUAL?
3.Dual belongs to SYS, SYSTEM, SCHEMA or INSTANCE?
4.Whats the difference between an instance & a session?
Thanks in advance.
Reena
|
|
|
Re: Doubts (Vry Urgent!) [message #9699 is a reply to message #9693] |
Tue, 02 December 2003 02:18 |
William Robertson
Messages: 1643 Registered: August 2003 Location: London, UK
|
Senior Member |
|
|
> 1. What's the difference between VARCHAR & VARCHAR2?
Currently none, but VARCHAR may change in the future if Oracle feel like it, which is why they recommend you use VARCHAR2. See documentation.
> 2.Why can't we perform a DML operation on DUAL?
You can, but since a lot of stuff relies on it, it might not be a good idea.
> 3.Dual belongs to SYS, SYSTEM, SCHEMA or INSTANCE?
<pre style="color: navy;">SELECT owner, object_type FROM all_objects WHERE object_name = 'DUAL';
OWNER OBJECT_TYPE
-------------------- ------------------
SYS TABLE
PUBLIC SYNONYM
2 rows selected.</pre>
> 4. What's the difference between an instance & a session?
An instance is the set of background processes and memory buffers that make a database available. Unless you are using Real Application Clusters, there will be one instance per database.
A session is what a user gets when they connect to an instance.
|
|
|
Re: Doubts (Vry Urgent!) [message #9705 is a reply to message #9699] |
Tue, 02 December 2003 02:34 |
Cindreen Clarence
Messages: 41 Registered: March 2003 Location: Bangalore
|
Member |
|
|
Thanx a lot,
When i tried to insert a row into dual after logging in from SYS, it gave an error that i did not have sufficient privileges. y so?
Pls sort out my prob.
Reena
|
|
|
Re: Doubts (Vry Urgent!) [message #9716 is a reply to message #9705] |
Tue, 02 December 2003 06:43 |
William Robertson
Messages: 1643 Registered: August 2003 Location: London, UK
|
Senior Member |
|
|
I would expect SYS to be able to insert a row into SYS.DUAL. However SYS.DUAL comes with exactly one row in it when the database is installed, and adding or deleting rows from it would cause chaos. Why do you need to insert a row? Is it empty?
|
|
|
Re: Doubts (Vry Urgent!) [message #9746 is a reply to message #9716] |
Tue, 02 December 2003 21:29 |
Cindreen Clarence
Messages: 41 Registered: March 2003 Location: Bangalore
|
Member |
|
|
Hi,
Thanx again.Actually this question was asked in an interview.i tried it out in Oracle 8i and got the error mentioned earlier. OK will try it out once more.
Reena
|
|
|
Re: Doubts (Vry Urgent!) [message #9751 is a reply to message #9746] |
Wed, 03 December 2003 02:09 |
William Robertson
Messages: 1643 Registered: August 2003 Location: London, UK
|
Senior Member |
|
|
Perhaps what the interviewer had in mind was that if DUAL contained two rows, or was empty, queries such as
<pre style="color: navy;">SELECT someseq.NEXTVAL INTO somevar FROM dual;</pre>would fail with TOO_MANY_ROWS/NO_DATA_FOUND, or at least produce unexpected results. DUAL must contain one and only one record - that is its whole purpose.
|
|
|