Could put this in a script called "set_prompt.sql" or cut and paste.
DEFINE v_DBNAME="?"
DEFINE v_USER="?"
DEFINE v_SID="?"
DEFINE v_HOST="?"
COLUMN v_DBNAME NEW_VALUE v_DBNAME NOPRINT
COLUMN v_USER NEW_VALUE v_USER NOPRINT
COLUMN v_SID NEW_VALUE v_SID NOPRINT
COLUMN v_HOST NEW_VALUE v_HOST NOPRINT
SELECT
SYS_CONTEXT('USERENV','DB_NAME') V_DBNAME
,USER V_USER
,SYS_CONTEXT('USERENV','SESSIONID') v_SID
,UTL_INADDR.GET_HOST_NAME v_HOST
FROM
DUAL
;
SELECT
LTRIM(SID) v_SID
FROM
V$SESSION
WHERE
AUDSID=SYS_CONTEXT('USERENV','SESSIONID')
AND
STATUS='ACTIVE'
;
SET SQLPROMPT "&v_HOST:&v_DBNAME:&v_USER(&v_SID)> "
UNDEFINE v_DBNAME
UNDEFINE v_USER
UNDEFINE v_SID
UNDEFINE v_HOST
-----Original Message-----
From: Poole David
[mailto:oracledba-ezmlmshield-x90891824.[Email address protected]
Sent: Thursday, April 28, 2005 12:42 PM
To: LazyDBA Discussion
Subject: A Question Of SQLPLUS
Hi
Anyone know if its possible to change the sqlplus prompt to reflect my new
database connection? For example below you can see that I logged into
sqlplus as user 'user1' on database 'db1' on box 'box1', however if I use
the connect command to change to 'user2' to database 'db2' the sqlplus
prompt does not change. I know the connection settings are picked up
whenever I log into sqlplus via the glogin.sql file however I do not want to
keep logging in and out of sqlplus just to pick up the new connection
settings.
Thanks in advance
David
------------------------------------------------------------------
SQL*Plus: Release 9.2.0.1.0 - Production on Thu Apr 28 16:36:11 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
[Email address protected] connect user2/[Email Address Removed] address protected]
----------------------------------------------------------------
Systems Operations, Thus Plc.
.
. . _
www.thus.net www.demon.net
email. david.[Email address protected]
internal. 740 4156
external. 0141 566 4156
mobile. 07880 502093
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail.
Oracle LazyDBA home page