Home » RDBMS Server » Server Utilities » SQLplus Login Info display
SQLplus Login Info display [message #233940] Sat, 28 April 2007 06:51 Go to next message
bbaz
Messages: 138
Registered: April 2007
Senior Member
To show the user, we use the command:

SHOW USER

What is the command to display the session details/instance name?

If I use SHOW PARAMETERS, it displays all the DB instance Initialization parameters including the instance_name. Is there an easier way to get the Instance name when logged in other than from the initialization file?

Re: SQLplus Login Info display [message #233945 is a reply to message #233940] Sat, 28 April 2007 07:18 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
dbadmin@republic_lawt1 > select instance_name from v$instance;

INSTANCE_NAME
----------------
LAWT1
Re: SQLplus Login Info display [message #233946 is a reply to message #233945] Sat, 28 April 2007 07:22 Go to previous messageGo to next message
bbaz
Messages: 138
Registered: April 2007
Senior Member
Thanks for the swift response. So it has to be retrieved from the DB table and not through a SQLplus command.

regards,
Baz
Re: SQLplus Login Info display [message #233949 is a reply to message #233946] Sat, 28 April 2007 07:28 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
dbadmin@republic_lawt1 > show parameter instance_name;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_name                        string      LAWT1

It is upto you.
You asked
>>Is there an easier way to get the Instance name when logged in other than from the initialization file?

Regards~

[Updated on: Sat, 28 April 2007 07:29]

Report message to a moderator

Re: SQLplus Login Info display [message #233950 is a reply to message #233949] Sat, 28 April 2007 07:31 Go to previous messageGo to next message
bbaz
Messages: 138
Registered: April 2007
Senior Member
Great, much appreciated Mahesh. Thanks

Baz
Re: SQLplus Login Info display [message #233952 is a reply to message #233950] Sat, 28 April 2007 07:36 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
And if you are using RAC,
use GV$ views to get information on all instances on all participating nodes.
dbadmin@republic_lawt1 > select instance_name from gv$instance;

INSTANCE_NAME
----------------
LAWT2
LAWT1

[Updated on: Sat, 28 April 2007 07:36]

Report message to a moderator

Re: SQLplus Login Info display [message #233964 is a reply to message #233940] Sat, 28 April 2007 09:01 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>What is the command to display the session details
SELECT * FROM V$SESSION;
Re: SQLplus Login Info display [message #234079 is a reply to message #233964] Sun, 29 April 2007 11:36 Go to previous message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,
You can also check through below command when you don't have "select any dictionary" privs.

1.
SQL> conn scott/tiger
Connected.
SQL> show parameter instance_name
ORA-00942: table or view does not exist


SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------

ORCL


2.
SQL> select sys_context('USERENV','INSTANCE_NAME') from dual;

SYS_CONTEXT('USERENV','INSTANCE_NAME')
--------------------------------------------------------------------------------

orcl



Regards
Taj
Previous Topic: DBA_JOBS not getting executed.
Next Topic: SQL Loader problem with Date Format
Goto Forum:
  


Current Time: Sat Dec 03 13:51:02 CST 2016

Total time taken to generate the page: 0.08373 seconds