OraFAQ Forum: Server Utilities » SQLplus Login Info display

Home » RDBMS Server » Server Utilities » SQLplus Login Info display

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQLplus Login Info display [message #233940]

Sat, 28 April 2007 06:51

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?

Report message to a moderator

Re: SQLplus Login Info display [message #233945 is a reply to message #233940]

Sat, 28 April 2007 07:18

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

dbadmin@republic_lawt1 > select instance_name from v$instance;

INSTANCE_NAME
----------------
LAWT1

Report message to a moderator

Re: SQLplus Login Info display [message #233946 is a reply to message #233945]

Sat, 28 April 2007 07:22

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

Report message to a moderator

Re: SQLplus Login Info display [message #233949 is a reply to message #233946]

Sat, 28 April 2007 07:28

Mahesh Rajendran
Messages: 10707
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

bbaz
Messages: 138
Registered: April 2007

Senior Member

Great, much appreciated Mahesh. Thanks

Baz

Report message to a moderator

Re: SQLplus Login Info display [message #233952 is a reply to message #233950]

Sat, 28 April 2007 07:36

Mahesh Rajendran
Messages: 10707
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

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>What is the command to display the session details
SELECT * FROM V$SESSION;

Report message to a moderator

Re: SQLplus Login Info display [message #234079 is a reply to message #233964]

Sun, 29 April 2007 11:36

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

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

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

orcl

Regards
Taj

Report message to a moderator

Previous Topic:	DBA_JOBS not getting executed.
Next Topic:	SQL Loader problem with Date Format

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Jun 18 08:29:46 CDT 2024