subject:"RE\: Instance Name in Sql Prompt"

Re: Instance Name in Sql Prompt

2001-10-28 Thread C.S.Venkata Subramanian


Try this one.

set serveroutput on size 100

set trimspool on
set long 5000
set linesize 100
set pagesize 

column plan_plus_exp format a80

column global_name new_value gname
set termout off
select lower(user) || '@' ||
decode(global_name, 'ORACLE8.WORLD', '8.0', 'ORA8I.WORLD',
'8i', global_name ) global_name from global_name;
set sqlprompt '&gname> '
set termout on
--

On Thu, 25 Oct 2001 06:35:21  
 Ramon Estevez wrote:
>Hi Friends,
>
>Excuse me for this dumb question,  how do I display the instance name in the
>sql prompt.  Have 3 DB and would like to know to which one I am connected.
>
>I mean something like that.
>
>DBA1 >
>DBA2 >
>DB01 >
>
>Regards,
>
>Ramon E. Estevez
>[EMAIL PROTECTED]
>Dominican Republic
>809-565-3121
>
>


Make a difference, help support the relief efforts in the U.S.
http://clubs.lycos.com/live/events/september11.asp
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: C.S.Venkata Subramanian
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Instance Name in Sql Prompt

2001-10-26 Thread Ramon Estevez


Now it works correct for the both cases,

Thanks, that's what I was looking for,

Gracias,

Ramon E. Estevez
[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
809-565-3121


-Mensaje original-
De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]En nombre de
[EMAIL PROTECTED]
Enviado el: Friday, 26 October, 2001 2:35 AM
Para: Multiple recipients of list ORACLE-L
Asunto: RE: Instance Name in Sql Prompt





Hi,


To overcome the connect within a session not changing the prompt  a created
a script (connect.sql) and put it in the directory from SQL_PATH registry
entry. When I want to change the connection i type @connect i.s.o connect




accept username prompt "Type username :";
accept password prompt "Type Password (hidden) :" hide;
accept sid prompt "Type instance name (SID) :";
connect &username/&password@&sid;
@d:\oracle\ora80\Plus80\glogin.sql;
undefine username;
undefine password;
undefine sid;
rem CLEAR SCREEN;



Jack




"Ramon Estevez" <[EMAIL PROTECTED]>@fatcity.com on 25-10-2001
22:15:21

Please respond to [EMAIL PROTECTED]

Sent by:  [EMAIL PROTECTED]


To:   Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:(bcc: Jack van Zanen/nlzanen1/External/MEY/NL)

Thanks to Kevin and all that reply.

I noticed when doing a connect inside a session.

This will help.

Ramon E. Estevez
[EMAIL PROTECTED]
Dominican Republic
809-565-3121
 -Mensaje original-
 De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]En nombre de Kevin Lange
 Enviado el: Thursday, 25 October, 2001 11:10 AM
     Para: Multiple recipients of list ORACLE-L
 Asunto: RE: Instance Name in Sql Prompt

 Ramon;
   In your startup directory on SQL Plus, you can automatically run a
 script that will setup the environment like you want it.  Its called
 LOGIN.SQL.

 Here is a copy of mine:

 set termout on
 set pagesize 0
 set echo off
 set feedback off
 set sqlprompt ''
 spool c:\temp.sql
 select 'set sqlprompt '''||name||' > '';' from v$database;
 spool off
 @c:\temp.sql
 set pagesize 35
 set feedback on
 set termout off
 What this does is to take the name of the database from v$database and
 create a temporary SQL file, then runs it.  This will set my prompt to

 INSTANCE_NAME >

 where INSTANCE_NAME is the name of the database.

 For example, if I log onto DEV then the SQLPROMPT will be

 DEV >

 Hope you can use it.

 Kevin

 p.s. ...  Remember that  this only works for the initialization of SQL
 Plus.  If you do a connect inside of your session then the prompt will
 not change.
  -Original Message-
  From: Ramon Estevez [mailto:[EMAIL PROTECTED]]
  Sent: Thursday, October 25, 2001 9:35 AM
  To: Multiple recipients of list ORACLE-L
  Subject: Instance Name in Sql Prompt

  Hi Friends,

  Excuse me for this dumb question,  how do I display the instance
  name in the
  sql prompt.  Have 3 DB and would like to know to which one I am
  connected.

  I mean something like that.

  DBA1 >
  DBA2 >
  DB01 >

  Regards,

  Ramon E. Estevez
  [EMAIL PROTECTED]
  Dominican Republic
  809-565-3121




=
De informatie verzonden in dit e-mailbericht is vertrouwelijk en is
uitsluitend bestemd voor de geadresseerde. Openbaarmaking,
vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan
derden is, behoudens voorafgaande schriftelijke toestemming van Ernst &
Young, niet toegestaan. Ernst & Young staat niet in voor de juiste en
volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch
voor tijdige ontvangst daarvan. Ernst & Young kan niet garanderen dat een
verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten
worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden.

Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u
vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender
en het origineel en eventuele kopieën te verwijderen en te vernietigen.

Ernst & Young hanteert bij de uitoefening van haar werkzaamheden algemene
voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De
algemene voorwaarden worden u op verzoek kosteloos toegezonden.
=
The information contained in this communication is confidential and is
intended solely for the use of the individual or entity to whom it is
addressed. You

RE: Instance Name in Sql Prompt

2001-10-26 Thread Thomas, Kevin


There is a way of changing the prompt when doing a "connect", however it's
done by running a script called connect. 
 
So instead of the usual:
 
 SQL> connect /@ 
 
You would do:
 
SQL> @connect /@
 
Try this URL:
http://asktom.oracle.com/pls/ask/f?p=4950:8:214627::NO::F4950_P8_DISPLAYID,F
4950_P8_CRITERIA:928829365838,%7Bsqlprompt%7D
<http://asktom.oracle.com/pls/ask/f?p=4950:8:214627::NO::F4950_P8_DISPLAYID,
F4950_P8_CRITERIA:928829365838,%7Bsqlprompt%7D> 
 
Cheers,
Kev.
"hit any user to continue" 
__ 

Kevin Thomas 
Technical Analyst 
Deregulation Services 
Calanais Ltd. 
(2nd Floor East - Weirs Building) 
Tel: 0141 568 2377 
Fax: 0141 568 2366 
http://www.calanais.com <http://www.calanais.com/>  

-Original Message-
Sent: 25 October 2001 21:15
To: Multiple recipients of list ORACLE-L


Thanks to Kevin and all that reply.
 
I noticed when doing a connect inside a session.
 
This will help.
 
Ramon E. Estevez
[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> 
Dominican Republic
809-565-3121

-Mensaje original-
De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]En nombre de Kevin Lange
Enviado el: Thursday, 25 October, 2001 11:10 AM
Para: Multiple recipients of list ORACLE-L
Asunto: RE: Instance Name in Sql Prompt


Ramon;
  In your startup directory on SQL Plus, you can automatically run a script
that will setup the environment like you want it.  Its called LOGIN.SQL.
 
Here is a copy of mine:
 
set termout on
set pagesize 0
set echo off
set feedback off
set sqlprompt ''
spool c:\temp.sql
select 'set sqlprompt '''||name||' > '';' from v$database;
spool off
@c:\temp.sql
set pagesize 35
set feedback on
set termout off

What this does is to take the name of the database from v$database and
create a temporary SQL file, then runs it.  This will set my prompt to 
 
INSTANCE_NAME > 
 
where INSTANCE_NAME is the name of the database.
 
For example, if I log onto DEV then the SQLPROMPT will be
 
DEV > 
 
Hope you can use it.
 
Kevin
 
p.s. ...  Remember that  this only works for the initialization of SQL Plus.
If you do a connect inside of your session then the prompt will not change.

-Original Message-
Sent: Thursday, October 25, 2001 9:35 AM
To: Multiple recipients of list ORACLE-L


Hi Friends,
 
Excuse me for this dumb question,  how do I display the instance name in the
sql prompt.  Have 3 DB and would like to know to which one I am connected.
 
I mean something like that.
 
DBA1 >
DBA2 >
DB01 >
 
Regards,
 
Ramon E. Estevez
[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> 
Dominican Republic
809-565-3121
 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Thomas, Kevin
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Instance Name in Sql Prompt

2001-10-25 Thread nlzanen1





Hi,


To overcome the connect within a session not changing the prompt  a created
a script (connect.sql) and put it in the directory from SQL_PATH registry
entry. When I want to change the connection i type @connect i.s.o connect



accept username prompt "Type username :";
accept password prompt "Type Password (hidden) :" hide;
accept sid prompt "Type instance name (SID) :";
connect &username/&password@&sid;
@d:\oracle\ora80\Plus80\glogin.sql;
undefine username;
undefine password;
undefine sid;
rem CLEAR SCREEN;


Jack




"Ramon Estevez" <[EMAIL PROTECTED]>@fatcity.com on 25-10-2001
22:15:21

Please respond to [EMAIL PROTECTED]

Sent by:  [EMAIL PROTECTED]


To:   Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:(bcc: Jack van Zanen/nlzanen1/External/MEY/NL)

Thanks to Kevin and all that reply.

I noticed when doing a connect inside a session.

This will help.

Ramon E. Estevez
[EMAIL PROTECTED]
Dominican Republic
809-565-3121
 -Mensaje original-
 De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]En nombre de Kevin Lange
 Enviado el: Thursday, 25 October, 2001 11:10 AM
 Para: Multiple recipients of list ORACLE-L
 Asunto: RE: Instance Name in Sql Prompt

 Ramon;
   In your startup directory on SQL Plus, you can automatically run a
 script that will setup the environment like you want it.  Its called
 LOGIN.SQL.

 Here is a copy of mine:

 set termout on
 set pagesize 0
 set echo off
 set feedback off
 set sqlprompt ''
 spool c:\temp.sql
 select 'set sqlprompt '''||name||' > '';' from v$database;
 spool off
 @c:\temp.sql
 set pagesize 35
 set feedback on
 set termout off
 What this does is to take the name of the database from v$database and
 create a temporary SQL file, then runs it.  This will set my prompt to

 INSTANCE_NAME >

 where INSTANCE_NAME is the name of the database.

 For example, if I log onto DEV then the SQLPROMPT will be

 DEV >

 Hope you can use it.

 Kevin

 p.s. ...  Remember that  this only works for the initialization of SQL
 Plus.  If you do a connect inside of your session then the prompt will
 not change.
  -Original Message-
  From: Ramon Estevez [mailto:[EMAIL PROTECTED]]
  Sent: Thursday, October 25, 2001 9:35 AM
  To: Multiple recipients of list ORACLE-L
  Subject: Instance Name in Sql Prompt

  Hi Friends,

  Excuse me for this dumb question,  how do I display the instance
  name in the
  sql prompt.  Have 3 DB and would like to know to which one I am
  connected.

  I mean something like that.

  DBA1 >
  DBA2 >
  DB01 >

  Regards,

  Ramon E. Estevez
  [EMAIL PROTECTED]
  Dominican Republic
  809-565-3121




=
De informatie verzonden in dit e-mailbericht is vertrouwelijk en is
uitsluitend bestemd voor de geadresseerde. Openbaarmaking,
vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan
derden is, behoudens voorafgaande schriftelijke toestemming van Ernst &
Young, niet toegestaan. Ernst & Young staat niet in voor de juiste en
volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch
voor tijdige ontvangst daarvan. Ernst & Young kan niet garanderen dat een
verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten
worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden.

Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u
vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender
en het origineel en eventuele kopieën te verwijderen en te vernietigen.

Ernst & Young hanteert bij de uitoefening van haar werkzaamheden algemene
voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De
algemene voorwaarden worden u op verzoek kosteloos toegezonden.
=
The information contained in this communication is confidential and is
intended solely for the use of the individual or entity to whom it is
addressed. You should not copy, disclose or distribute this communication
without the authority of Ernst & Young. Ernst & Young is neither liable for
the proper and complete transmission of the information contained in this
communication nor for any delay in its receipt. Ernst & Young does not
guarantee that the integrity of this communication has been maintained nor
that the communication is free of viruses, inte

RE: Instance Name in Sql Prompt

2001-10-25 Thread Ramon Estevez




Thanks 
David, Jack it works !!
 
 
 
Ramon E. 
Estevez
[EMAIL PROTECTED]
Dominican Republic
809-565-3121

  -Mensaje original-De: [EMAIL PROTECTED] 
  [mailto:[EMAIL PROTECTED]]En nombre de David WagonerEnviado 
  el: Thursday, 25 October, 2001 10:22 AMPara: Multiple 
  recipients of list ORACLE-LAsunto: RE: Instance Name in Sql 
  Prompt
  
  Add 
  the following to your login.sql or glogin.sql 
  file:
   
  set 
  serveroutput on
  set termout 
  off
  column 
  instance new_value prompt_instance
  select 
  substr(instance_name, 1, 10)instance
  from 
  v$instance
  /
  set sqlprompt 
  "&&prompt_instance> "
  set termout 
  on
   
   
  HTH,
   
  david
   
  David B. 
  Wagoner
  Database 
  Administrator
  Arsenal 
  Digital Solutions Worldwide Inc.
  4815 
  Emperor Blvd., Suite 110
  Durham, 
  NC 27703
  Tel. 
  (919) 941-4645
  Fax 
  (919) 474-0735
  Email 
  mailto:[EMAIL PROTECTED]
  Web 
  http://www.arsenaldigital.com/
   
    
  ***  NOTICE  ***
  This 
  e-mail message is confidential, intended only for the named recipient(s) above 
  and may contain information that is privileged, work product or exempt from 
  disclosure under applicable law.  
  If you have received this message in error, or are not the named 
  recipient(s), please immediately notify the sender at (919) 941-4645 and 
  delete this e-mail message from your computer.  Thank you.
   
  -Original 
  Message-From: Ramon 
  Estevez [mailto:[EMAIL PROTECTED]]Sent: Thursday, October 25, 2001 10:35 
  AMTo: Multiple recipients of 
  list ORACLE-LSubject: 
  Instance Name in Sql Prompt
   
  Hi 
  Friends,
   
  Excuse me for this 
  dumb question,  how do I display the instance name in 
  the
  sql 
  prompt.  Have 3 DB and would like to know to which one I am 
  connected.
   
  I 
  mean something like that.
   
  DBA1 
  >
  DBA2 
  >
  DB01 
  >
   
  Regards,
   
  Ramon 
  E. Estevez
  [EMAIL PROTECTED]
  Dominican 
  Republic
  809-565-3121

RE: Instance Name in Sql Prompt

2001-10-25 Thread Ramon Estevez




Thanks to 
Kevin and all that reply.
 
I noticed 
when doing a connect inside a session.
 
This will 
help.
 
Ramon E. 
Estevez
[EMAIL PROTECTED]
Dominican Republic
809-565-3121

  -Mensaje original-De: [EMAIL PROTECTED] 
  [mailto:[EMAIL PROTECTED]]En nombre de Kevin LangeEnviado el: 
  Thursday, 25 October, 2001 11:10 AMPara: Multiple recipients of 
  list ORACLE-LAsunto: RE: Instance Name in Sql 
  Prompt
  Ramon;
    In your startup directory on SQL Plus, you can automatically run 
  a script that will setup the environment like you want it.  Its called 
  LOGIN.SQL.
   
  Here 
  is a copy of mine:
   
  set 
  termout onset pagesize 0set echo offset feedback offset 
  sqlprompt ''spool c:\temp.sqlselect 'set sqlprompt '''||name||' > 
  '';' from v$database;spool off@c:\temp.sqlset pagesize 35set 
  feedback onset termout off
  What 
  this does is to take the name of the database from v$database and create a 
  temporary SQL file, then runs it.  This will set my prompt to 
  
   
  INSTANCE_NAME > 
   
  where INSTANCE_NAME is the name of the database.
   
  For 
  example, if I log onto DEV then the SQLPROMPT will be
   
  DEV 
  > 
   
  Hope 
  you can use it.
   
  Kevin
   
  p.s. 
  ...  Remember that  this only works for the initialization of SQL 
  Plus.  If you do a connect inside of your session then the prompt will 
  not change.
  
-Original Message-From: Ramon Estevez 
[mailto:[EMAIL PROTECTED]]Sent: Thursday, October 25, 
2001 9:35 AMTo: Multiple recipients of list 
ORACLE-LSubject: Instance Name in Sql Prompt
Hi 
Friends,
 
Excuse me for this 
dumb question,  how do I display the instance name in 
the
sql prompt.  
Have 3 DB and would like to know to which one I am 
connected.
 
I mean something like 
that.
 
DBA1 
>
DBA2 
>
DB01 
>
 
Regards,
 
Ramon E. 
Estevez
[EMAIL PROTECTED]
Dominican Republic
809-565-3121

Ang: RE: Instance Name in Sql Prompt

2001-10-25 Thread Roland . Skoldblom



Thanks it was great

Roland




"IT - Database (Do Not Use)" <[EMAIL PROTECTED]>@fatcity.com den 
2001-10-25 07:15 PST

Sänd svar till [EMAIL PROTECTED]

Sänt av:  [EMAIL PROTECTED]


Till: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Kopia:

I got this from someone on the list.  It shows server, instance and username.  Put it 
in your login.sql

set sqlprompt 'SQL> '

column SQLQueryDatabase   new_value SQLPromptDatabase   noprint
column SQLQueryTablespace new_value SQLPromptTablespace noprint
column SQLQueryUsername   new_value SQLPromptUsername   noprint
column SQLQueryHost   new_value SQLPromptHost   noprint

/* remove ".WORLD"; append "." just
 * in case it is absent
 */
select initcap(substr(g.GLOBAL_NAME,
  1,
  instr(g.GLOBAL_NAME || '.',
'.') - 1)) SQLQueryDatabase,
   u.DEFAULT_TABLESPACESQLQueryTablespace,
   u.USERNAME  SQLQueryUsername
   from GLOBAL_NAME g,
USER_USERS  u
/

/* this defaults the SQLQueryHost variable
 * in case we have no privileges on the
 * v$session view
 */
select '' SQLQueryHost
   from USER_USERS
   where rownum = 1
/

select initCap(decode(s.MACHINE,
  NULL, '',
  '', '',
  s.MACHINE || '/')) SQLQueryHost
   from V$SESSION s
   where upper(s.PROGRAM) like 'ORACLE%.EXE' or  -- uSoft executable
 upper(s.PROGRAM) like 'ORACLE%(PMON)%'  -- Unix ps style
/

--set sqlprompt '[&SQLPromptHost&SQLPromptDatabase:&SQLPromptTablespace]
&SQLPromptUsername> '
set sqlprompt '[&SQLPromptHost&SQLPromptDatabase] &SQLPromptUsername> '

select ''SQLQueryDatabase,
   ''SQLQueryHost,
   ''SQLQueryTablespace,
   'SQL' SQLQueryUsername
   from USER_USERS
/

column SQLQueryDatabase   clear
column SQLQueryHost   clear
column SQLQueryTablespace clear
column SQLQueryUsername   clear

input --
del 1 last

set termout on
set pages 
 -Original Message-
 From: Ramon Estevez [mailto:[EMAIL PROTECTED]]
 Sent: Thursday, October 25, 2001 8:35 AM
 To: Multiple recipients of list ORACLE-L
 Subject: Instance Name in Sql Prompt

 Hi Friends,

 Excuse me for this dumb question,  how do I display the instance name in the
 sql prompt.  Have 3 DB and would like to know to which one I am connected.

 I mean something like that.

 DBA1 >
 DBA2 >
 DB01 >

 Regards,

 Ramon E. Estevez
 [EMAIL PROTECTED]
 Dominican Republic
 809-565-3121









--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Instance Name in Sql Prompt

2001-10-25 Thread Kevin Lange




David;
  
I never knew of the NEW_VALUE option on the COLUMN command.   

 
I 
always wrote the data out to a temp query and then ran it.   Thanks a 
lot.  I have replaced my login script and it works 
fine.

  -Original Message-From: David Wagoner 
  [mailto:[EMAIL PROTECTED]]Sent: Thursday, October 25, 
  2001 10:22 AMTo: Multiple recipients of list 
  ORACLE-LSubject: RE: Instance Name in Sql 
  Prompt
  
  Add 
  the following to your login.sql or glogin.sql 
  file:
   
  set 
  serveroutput on
  set termout 
  off
  column 
  instance new_value prompt_instance
  select 
  substr(instance_name, 1, 10)instance
  from 
  v$instance
  /
  set sqlprompt 
  "&&prompt_instance> "
  set termout 
  on
   
   
  HTH,
   
  david
   
  David B. 
  Wagoner
  Database 
  Administrator
  Arsenal 
  Digital Solutions Worldwide Inc.
  4815 
  Emperor Blvd., Suite 110
  Durham, 
  NC 27703
  Tel. 
  (919) 941-4645
  Fax 
  (919) 474-0735
  Email 
  mailto:[EMAIL PROTECTED]
  Web 
  http://www.arsenaldigital.com/
   
    
  ***  NOTICE  ***
  This 
  e-mail message is confidential, intended only for the named recipient(s) above 
  and may contain information that is privileged, work product or exempt from 
  disclosure under applicable law.  
  If you have received this message in error, or are not the named 
  recipient(s), please immediately notify the sender at (919) 941-4645 and 
  delete this e-mail message from your computer.  Thank you.
   
  -Original 
  Message-From: Ramon 
  Estevez [mailto:[EMAIL PROTECTED]]Sent: Thursday, October 25, 2001 10:35 
  AMTo: Multiple recipients of 
  list ORACLE-LSubject: 
  Instance Name in Sql Prompt
   
  Hi 
  Friends,
   
  Excuse me for this 
  dumb question,  how do I display the instance name in 
  the
  sql 
  prompt.  Have 3 DB and would like to know to which one I am 
  connected.
   
  I 
  mean something like that.
   
  DBA1 
  >
  DBA2 
  >
  DB01 
  >
   
  Regards,
   
  Ramon 
  E. Estevez
  [EMAIL PROTECTED]
  Dominican 
  Republic
  809-565-3121

Re: Instance Name in Sql Prompt

2001-10-25 Thread tday6


Put something like this in your glogin.sql or run it after you log in.

set head off
set termout off
column nombre new_v nombre_basedatos
select name nombre FROM V$DATABASE
/
set sqlprompt '&nombre_basedatos> '
set head on
set termout on



   

Ramon Estevez  



@codetel.net.cc:   

do>  Subject: Instance Name in Sql Prompt  

Sent by: root  

   

   

10/25/2001 

10:35 AM   

Please 

respond to 

ORACLE-L   

   

   






Hi  Friends,

Excuse me for this dumb  question,  how do I display the instance name in
the
sql prompt.  Have 3  DB and would like to know to which one I am connected.

I mean something like  that.

DBA1  >
DBA2  >
DB01  >

Regards,

Ramon E.  Estevez
[EMAIL PROTECTED]
Dominican Republic
809-565-3121



--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Instance Name in Sql Prompt

2001-10-25 Thread Kevin Lange




Ramon;
  
In your startup directory on SQL Plus, you can automatically run a script that 
will setup the environment like you want it.  Its called 
LOGIN.SQL.
 
Here 
is a copy of mine:
 
set 
termout onset pagesize 0set echo offset feedback offset 
sqlprompt ''spool c:\temp.sqlselect 'set sqlprompt '''||name||' > 
'';' from v$database;spool off@c:\temp.sqlset pagesize 35set 
feedback onset termout off
What 
this does is to take the name of the database from v$database and create a 
temporary SQL file, then runs it.  This will set my prompt to 

 
INSTANCE_NAME > 
 
where 
INSTANCE_NAME is the name of the database.
 
For 
example, if I log onto DEV then the SQLPROMPT will be
 
DEV 
> 
 
Hope 
you can use it.
 
Kevin
 
p.s. 
...  Remember that  this only works for the initialization of SQL 
Plus.  If you do a connect inside of your session then the prompt will not 
change.

  -Original Message-From: Ramon Estevez 
  [mailto:[EMAIL PROTECTED]]Sent: Thursday, October 25, 
  2001 9:35 AMTo: Multiple recipients of list 
  ORACLE-LSubject: Instance Name in Sql Prompt
  Hi 
  Friends,
   
  Excuse me for this dumb 
  question,  how do I display the instance name in the
  sql prompt.  Have 
  3 DB and would like to know to which one I am connected.
   
  I mean something like 
  that.
   
  DBA1 
  >
  DBA2 
  >
  DB01 
  >
   
  Regards,
   
  Ramon E. 
  Estevez
  [EMAIL PROTECTED]
  Dominican Republic
  809-565-3121

RE: Instance Name in Sql Prompt

2001-10-25 Thread Thomas, Kevin


Hi Ramon,
 
You could put this into your login.sql script if you have one. This will
automatically take your login name and database and set your sqlprompt
accordingly.
 
col user_name new_value user_name
col db_name new_value db_name
select lower(user) user_name, 
   ltrim(rtrim(lower(global_name))) db_name 
from global_name;
set sqlprompt "&user_name@&db_name> "
 
HTH
Kev.

"hit any user to continue" 
__ 

Kevin Thomas 
Technical Analyst 
Deregulation Services 
Calanais Ltd. 
(2nd Floor East - Weirs Building) 
Tel: 0141 568 2377 
Fax: 0141 568 2366 
http://www.calanais.com   

-Original Message-
Sent: 25 October 2001 15:35
To: Multiple recipients of list ORACLE-L


Hi Friends,
 
Excuse me for this dumb question,  how do I display the instance name in the
sql prompt.  Have 3 DB and would like to know to which one I am connected.
 
I mean something like that.
 
DBA1 >
DBA2 >
DB01 >
 
Regards,
 
Ramon E. Estevez
[EMAIL PROTECTED]  
Dominican Republic
809-565-3121
 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Thomas, Kevin
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Instance Name in Sql Prompt

2001-10-25 Thread David Wagoner










Add the
following to your login.sql or glogin.sql file:

 

set serveroutput on

set termout off

column instance new_value prompt_instance

select substr(instance_name, 1, 10)instance

from v$instance

/

set sqlprompt "&&prompt_instance>
"

set termout on

 

 

HTH,

 

david

 

David B. Wagoner

Database Administrator

Arsenal Digital Solutions Worldwide Inc.

4815 Emperor
Blvd., Suite 110

Durham, NC 27703

Tel. (919)
941-4645

Fax (919)
474-0735

Email mailto:[EMAIL PROTECTED]

Web http://www.arsenaldigital.com/

 

 
***  NOTICE  ***

This e-mail
message is confidential, intended only for the named recipient(s) above and may
contain information that is privileged, work product or exempt from disclosure
under applicable law.  If you have
received this message in error, or are not the named recipient(s), please
immediately notify the sender at (919) 941-4645 and delete this e-mail message
from your computer.  Thank you.

 

-Original
Message-
From: Ramon Estevez
[mailto:[EMAIL PROTECTED]]
Sent: Thursday, October 25, 2001
10:35 AM
To: Multiple recipients of list
ORACLE-L
Subject: Instance Name in Sql
Prompt

 

Hi
Friends,

 

Excuse
me for this dumb question,  how do I display the instance name in the

sql
prompt.  Have 3 DB and would like to know to which one I am connected.

 

I
mean something like that.

 

DBA1
>

DBA2
>

DB01
>

 

Regards,

 

Ramon
E. Estevez

[EMAIL PROTECTED]

Dominican
Republic

809-565-3121

Re: Instance Name in Sql Prompt

2001-10-25 Thread nlzanen1





Hi


Add following to your GLOGIN.SQL


*
rem Display the DB SID and username in the Sql*Plus prompt
column dbsid new_value dbsid noprint
select name dbsid from v$database;
column dbusr new_value dbusr noprint
select user dbusr from dual;
column week new_value week noprint
select to_char(sysdate,'WW') week from dual;
set sqlprompt '&week-&dbsid-&dbusr> '
***

Jack




"Ramon Estevez" <[EMAIL PROTECTED]>@fatcity.com on 25-10-2001
16:35:21

Please respond to [EMAIL PROTECTED]

Sent by:  [EMAIL PROTECTED]


To:   Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:(bcc: Jack van Zanen/nlzanen1/External/MEY/NL)

Hi Friends,

Excuse me for this dumb question,  how do I display the instance name in
the
sql prompt.  Have 3 DB and would like to know to which one I am connected.

I mean something like that.

DBA1 >
DBA2 >
DB01 >

Regards,

Ramon E. Estevez
[EMAIL PROTECTED]
Dominican Republic
809-565-3121




===
De informatie verzonden met dit E-mail bericht is uitsluitend bestemd voor
de geadresseerde. Gebruik van deze informatie door anderen dan de
geadresseerde is verboden. Openbaarmaking, vermenigvuldiging, verspreiding
en/of verstrekking van deze informatie aan derden is niet toegestaan.
Ernst & Young staat niet in voor de juiste en volledige overbrenging van de
inhoud van een verzonden E-mail, noch voor tijdige ontvangst daarvan.
===
The information contained in this communication is confidential and may be
legally privileged. It is intended solely for the use of the individual or
entity to whom it is addressed and others authorised to receive it. If you
are not the intended recipient you are hereby notified that any disclosure,
copying,  distribution or taking any action in reliance on the contents of
this information is strictly prohibited and may be unlawful. Ernst &
Young is neither liable  for the proper and complete transmission of the
information contained in this communication nor for any delay in its
receipt.
===





-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Instance Name in Sql Prompt

2001-10-25 Thread IT - Database (Do Not Use)




I got 
this from someone on the list.  It shows server, instance and 
username.  Put it in your login.sql
 
set 
sqlprompt 'SQL> '
 
column 
SQLQueryDatabase   new_value SQLPromptDatabase   
noprintcolumn SQLQueryTablespace new_value SQLPromptTablespace 
noprintcolumn SQLQueryUsername   new_value 
SQLPromptUsername   noprintcolumn 
SQLQueryHost   new_value 
SQLPromptHost   noprint
 
    
/* remove ".WORLD"; append "." 
just 
* in case it is 
absent 
*/select 
initcap(substr(g.GLOBAL_NAME,  
1,  
instr(g.GLOBAL_NAME || 
'.',    
'.') - 1)) SQLQueryDatabase,   
u.DEFAULT_TABLESPACE    
SQLQueryTablespace,   
u.USERNAME  
SQLQueryUsername   from GLOBAL_NAME 
g,    USER_USERS  
u/
 
    
/* this defaults the SQLQueryHost 
variable 
* in case we have no privileges on 
the 
* v$session 
view 
*/select '' SQLQueryHost   from USER_USERS   
where rownum = 1/
 
select 
initCap(decode(s.MACHINE,  
NULL, 
'',  
'', 
'',  
s.MACHINE || '/')) SQLQueryHost   from V$SESSION s   
where upper(s.PROGRAM) like 'ORACLE%.EXE' or  -- uSoft 
executable upper(s.PROGRAM) 
like 'ORACLE%(PMON)%'  -- Unix ps style/
 
--set 
sqlprompt 
'[&SQLPromptHost&SQLPromptDatabase:&SQLPromptTablespace]&SQLPromptUsername> 
'set sqlprompt '[&SQLPromptHost&SQLPromptDatabase] 
&SQLPromptUsername> '
 
select 
''    SQLQueryDatabase,   
''    SQLQueryHost,   
''    SQLQueryTablespace,   
'SQL' SQLQueryUsername   from USER_USERS/
 
column 
SQLQueryDatabase   clearcolumn 
SQLQueryHost   clearcolumn 
SQLQueryTablespace clearcolumn SQLQueryUsername   
clear
 
input 
--del 1 last
 
set 
termout onset pages 

  -Original Message-From: Ramon Estevez 
  [mailto:[EMAIL PROTECTED]]Sent: Thursday, October 25, 
  2001 8:35 AMTo: Multiple recipients of list 
  ORACLE-LSubject: Instance Name in Sql Prompt
  Hi 
  Friends,
   
  Excuse me for this dumb 
  question,  how do I display the instance name in the
  sql prompt.  Have 
  3 DB and would like to know to which one I am connected.
   
  I mean something like 
  that.
   
  DBA1 
  >
  DBA2 
  >
  DB01 
  >
   
  Regards,
   
  Ramon E. 
  Estevez
  [EMAIL PROTECTED]
  Dominican Republic
  809-565-3121

Re: Instance Name in Sql Prompt

RE: Instance Name in Sql Prompt

RE: Instance Name in Sql Prompt

RE: Instance Name in Sql Prompt

RE: Instance Name in Sql Prompt

RE: Instance Name in Sql Prompt

Ang: RE: Instance Name in Sql Prompt

RE: Instance Name in Sql Prompt

Re: Instance Name in Sql Prompt

RE: Instance Name in Sql Prompt

RE: Instance Name in Sql Prompt

RE: Instance Name in Sql Prompt

Re: Instance Name in Sql Prompt

RE: Instance Name in Sql Prompt

14 matches

Site Navigation

Mail list logo

Footer information