Re: How to get the INSTANCE and SID name via SQL

"Sybrand Bakker" <gooiditweg_at_sybrandb.demon.nl> wrote in message news:h0jtsuch51q491hibsnh8f4ug1ms9a66db_at_4ax.com...
> On Sun, 10 Nov 2002 19:34:28 +0100, Robert Weiss <roweiss_at_web.de>
> wrote:
>
> >Anton Buijs wrote:
> >
> >> V$INSTANCE.INSTANCE_NAME is the name of the instance, what is the SID.
> >
> >V$SESSION.SID (the Session-ID)
> >
> >Mfg, Robert
>
>
> No. The term 'SID' is used in several contexts. The poster was
> referring to the SID of the database.

If I was being picky (which I am), then the obvious reply is that databases don't have SIDs, Instances do.

Or, rather, whatever is the current ORACLE_SID when the command 'startup' is issued is what gives an Instance its true name. Database names are given, as has already been mentioned, by whatever you supplied when you issued the command 'create database' -which has to match the 'db_name' parameter in the init.ora.

Nothing ties the Instance name (ie, ORACLE_SID) to the database name, and there is no requirement for them to match (though I know the name of a good psychiatrist if you decide you want them to be different).

The confusion, I think, arises from the INSTANCE_NAME parameter in the init.ora, which people tend to assume gives the Instance a name.... as we should all know, it does no such thing, but merely provides the 'handle' under which the Instance automatically registers itself with the Listener. But now there are three degrees of freedom: the Instance gets its name from ORACLE_SID, which should ideally be the same as DB_NAME, but doesn't have to be, and registers itself as the INSTANCE_NAME value... which ought to be the same as DB_NAME and ORACLE_SID, but doesn't have to be.

Regards
HJR
>It is usually equal to the
> instance name, though in 8i and higher you can override the instance
> name.
>
> Regards
>
>
> Sybrand Bakker, Senior Oracle DBA
>
> To reply remove -verwijderdit from my e-mail address
Received on Sun Nov 10 2002 - 16:19:46 CST