RE: sqlplus prompt question in 9i
Title: RE: sqlplus prompt question in 9i
Thanks David!
That's exactly what I am looking
for.
Guang
-Original Message-From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]On Behalf Of david hillSent:
Thursday, October 30, 2003 11:30 AMTo: Multiple recipients of list
ORACLE-LSubject: RE: sqlplus prompt question in
9i
It could be your sql Here is the
prompt I have been using for a while with no problems at all If the db is down you get a prompt of Nolog>
set termout off
define new_prompt='nolog' column
value new_value new_prompt select
SYS_CONTEXT('USERENV', 'SESSION_USER') || ':' ||SYS_CONTEXT('USERENV',
'DB_NAME') value
from dual; set sqlprompt
"&new_prompt> " set termout on
RE: sqlplus prompt question in 9i
Title: RE: sqlplus prompt question in 9i
It could be your sql
Here is the prompt I have been using for a while with no problems at all
If the db is down you get a prompt of
Nolog>
set termout off
define new_prompt='nolog'
column value new_value new_prompt
select SYS_CONTEXT('USERENV', 'SESSION_USER') || ':' ||SYS_CONTEXT('USERENV', 'DB_NAME') value
from dual;
set sqlprompt "&new_prompt> "
set termout on
-Original Message-
From: Joan Hsieh [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, October 29, 2003 6:19 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: sqlplus prompt question in 9i
Guang,
You don't need to do it manually, you just type what it prompt you, it will
connect to database without any problem. It had problem in crontab batch job
only.
Joan
Quoting Guang Mei <[EMAIL PROTECTED]>:
> Joan:
>
> Thanks for the reply. This would work if dbstart is called when starting
> instance. But if I do it manually (although not often I would say), I need
> to remember this and do it by hand.
>
> Also I think you only need to do it with dbstart script, not dbshut.
>
> Guang
>
> -Original Message-
> Joan Hsieh
> Sent: Wednesday, October 29, 2003 3:55 PM
> To: Multiple recipients of list ORACLE-L
>
>
> I had same problem with 9i, what I did is at the begining of the
> dbstarup or stop script, I mv the glogin.sql to _old, at end of scripts
> I mv back to the original name.
>
> Joan
>
> Guang Mei wrote:
> >
> > Hi:
> >
> > With Oracle 8i, I always modified $ORACLE_HOME/sqlplus/admin/glogin.sql,
> and
> > added
> >
> > set termout off
> > col site_name noprint new_value site_name_new
> > select 'SQL> ' site_name from dual;
> > select user
> > || substr(proc.program, instr(proc.program,'@'),
> > instr(proc.program,' ') - instr(proc.program,'@'))
> > || '-SQL> ' site_name
> > from v$process proc
> > where proc.pid = 2;
> > set sqlprompt '&&site_name_new'
> > set termout on
> >
> > so that when a user launches sqlplus, it would show something at prompt
> like
> >
> > [EMAIL PROTECTED]>
> >
> > instead of
> >
> > SQL>
> >
> > I found that doing this in 9i will prevent me starting up my instance,
> when
> > I use
> >
> > sqlplus '/as sysdba'
> >
> > In 8i, I always used svrmgrl to bounce db so there was no problem with
> > modified glogin.sql.
> >
> > Has anyone found a work-around in 9i so that sqlplus prompt displays
> > "username" and "hostname" when launched? I know there is a new
> > _CONNECT_IDENTIFIER in 9i, but that's not good enough.
> >
> > TIA.
> >
> > Guang
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Guang Mei
> > INET: [EMAIL PROTECTED]
> >
> > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > San Diego, California -- Mailing list and web hosting services
> > -
> > 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).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Joan Hsieh
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> -
> 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).
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Guang Mei
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
>
RE: sqlplus prompt question in 9i
Guang, You don't need to do it manually, you just type what it prompt you, it will connect to database without any problem. It had problem in crontab batch job only. Joan Quoting Guang Mei <[EMAIL PROTECTED]>: > Joan: > > Thanks for the reply. This would work if dbstart is called when starting > instance. But if I do it manually (although not often I would say), I need > to remember this and do it by hand. > > Also I think you only need to do it with dbstart script, not dbshut. > > Guang > > -Original Message- > Joan Hsieh > Sent: Wednesday, October 29, 2003 3:55 PM > To: Multiple recipients of list ORACLE-L > > > I had same problem with 9i, what I did is at the begining of the > dbstarup or stop script, I mv the glogin.sql to _old, at end of scripts > I mv back to the original name. > > Joan > > Guang Mei wrote: > > > > Hi: > > > > With Oracle 8i, I always modified $ORACLE_HOME/sqlplus/admin/glogin.sql, > and > > added > > > > set termout off > > col site_name noprint new_value site_name_new > > select 'SQL> ' site_name from dual; > > selectuser > >|| substr(proc.program, instr(proc.program,'@'), > > instr(proc.program,' ') - instr(proc.program,'@')) > >|| '-SQL> ' site_name > > from v$process proc > > where proc.pid = 2; > > set sqlprompt '&&site_name_new' > > set termout on > > > > so that when a user launches sqlplus, it would show something at prompt > like > > > > [EMAIL PROTECTED]> > > > > instead of > > > > SQL> > > > > I found that doing this in 9i will prevent me starting up my instance, > when > > I use > > > > sqlplus '/as sysdba' > > > > In 8i, I always used svrmgrl to bounce db so there was no problem with > > modified glogin.sql. > > > > Has anyone found a work-around in 9i so that sqlplus prompt displays > > "username" and "hostname" when launched? I know there is a new > > _CONNECT_IDENTIFIER in 9i, but that's not good enough. > > > > TIA. > > > > Guang > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: Guang Mei > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > > San Diego, California-- Mailing list and web hosting services > > - > > 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). > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Joan Hsieh > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > 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). > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Guang Mei > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > 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). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Joan Hsieh INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: sqlplus prompt question in 9i
Joan: Thanks for the reply. This would work if dbstart is called when starting instance. But if I do it manually (although not often I would say), I need to remember this and do it by hand. Also I think you only need to do it with dbstart script, not dbshut. Guang -Original Message- Joan Hsieh Sent: Wednesday, October 29, 2003 3:55 PM To: Multiple recipients of list ORACLE-L I had same problem with 9i, what I did is at the begining of the dbstarup or stop script, I mv the glogin.sql to _old, at end of scripts I mv back to the original name. Joan Guang Mei wrote: > > Hi: > > With Oracle 8i, I always modified $ORACLE_HOME/sqlplus/admin/glogin.sql, and > added > > set termout off > col site_name noprint new_value site_name_new > select 'SQL> ' site_name from dual; > selectuser >|| substr(proc.program, instr(proc.program,'@'), > instr(proc.program,' ') - instr(proc.program,'@')) >|| '-SQL> ' site_name > from v$process proc > where proc.pid = 2; > set sqlprompt '&&site_name_new' > set termout on > > so that when a user launches sqlplus, it would show something at prompt like > > [EMAIL PROTECTED]> > > instead of > > SQL> > > I found that doing this in 9i will prevent me starting up my instance, when > I use > > sqlplus '/as sysdba' > > In 8i, I always used svrmgrl to bounce db so there was no problem with > modified glogin.sql. > > Has anyone found a work-around in 9i so that sqlplus prompt displays > "username" and "hostname" when launched? I know there is a new > _CONNECT_IDENTIFIER in 9i, but that's not good enough. > > TIA. > > Guang > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Guang Mei > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Joan Hsieh INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: sqlplus prompt question in 9i
I had same problem with 9i, what I did is at the begining of the dbstarup or stop script, I mv the glogin.sql to _old, at end of scripts I mv back to the original name. Joan Guang Mei wrote: > > Hi: > > With Oracle 8i, I always modified $ORACLE_HOME/sqlplus/admin/glogin.sql, and > added > > set termout off > col site_name noprint new_value site_name_new > select 'SQL> ' site_name from dual; > selectuser >|| substr(proc.program, instr(proc.program,'@'), > instr(proc.program,' ') - instr(proc.program,'@')) >|| '-SQL> ' site_name > from v$process proc > where proc.pid = 2; > set sqlprompt '&&site_name_new' > set termout on > > so that when a user launches sqlplus, it would show something at prompt like > > [EMAIL PROTECTED]> > > instead of > > SQL> > > I found that doing this in 9i will prevent me starting up my instance, when > I use > > sqlplus '/as sysdba' > > In 8i, I always used svrmgrl to bounce db so there was no problem with > modified glogin.sql. > > Has anyone found a work-around in 9i so that sqlplus prompt displays > "username" and "hostname" when launched? I know there is a new > _CONNECT_IDENTIFIER in 9i, but that's not good enough. > > TIA. > > Guang > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Guang Mei > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Joan Hsieh INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: sqlplus prompt question in 9i
As a further FYI-
We do not allow anything to be placed in glogin.sql. It can screw up anything
supplied by oracle,
ie, upgrade scripts. A local login.sql is the best way to go.
Ron Thomas
Hypercom, Inc
[EMAIL PROTECTED]
Each new user of a new system uncovers a new class of bugs. -- Kernighan
[EMAIL PROTECTED]
ys.com To: [EMAIL PROTECTED]
Sent by: cc:
[EMAIL PROTECTED]Subject: Re: sqlplus prompt question
in 9i
.com
10/29/2003 10:14
AM
Please respond to
ORACLE-L
You could just use login.sql instead, on a per user basis.
If you don't want login.sql to be used, just edit or unset SQLPATH.
Maybe other options available for this in 9i. A perusal of the sqlplus
manual may prove useful.
Jared
"Guang Mei" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
10/29/2003 08:24 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject: sqlplus prompt question in 9i
Hi:
With Oracle 8i, I always modified $ORACLE_HOME/sqlplus/admin/glogin.sql,
and
added
set termout off
col site_name noprint new_value site_name_new
select 'SQL> ' site_name from dual;
selectuser
|| substr(proc.program, instr(proc.program,'@'),
instr(proc.program,' ') - instr(proc.program,'@'))
|| '-SQL> ' site_name
from v$process proc
where proc.pid = 2;
set sqlprompt '&&site_name_new'
set termout on
so that when a user launches sqlplus, it would show something at prompt
like
[EMAIL PROTECTED]>
instead of
SQL>
I found that doing this in 9i will prevent me starting up my instance,
when
I use
sqlplus '/as sysdba'
In 8i, I always used svrmgrl to bounce db so there was no problem with
modified glogin.sql.
Has anyone found a work-around in 9i so that sqlplus prompt displays
"username" and "hostname" when launched? I know there is a new
_CONNECT_IDENTIFIER in 9i, but that's not good enough.
TIA.
Guang
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Guang Mei
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Ron Thomas
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, sen
Re: sqlplus prompt question in 9i
You could just use login.sql instead, on a per user basis. If you don't want login.sql to be used, just edit or unset SQLPATH. Maybe other options available for this in 9i. A perusal of the sqlplus manual may prove useful. Jared "Guang Mei" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 10/29/2003 08:24 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject: sqlplus prompt question in 9i Hi: With Oracle 8i, I always modified $ORACLE_HOME/sqlplus/admin/glogin.sql, and added set termout off col site_name noprint new_value site_name_new select 'SQL> ' site_name from dual; select user || substr(proc.program, instr(proc.program,'@'), instr(proc.program,' ') - instr(proc.program,'@')) || '-SQL> ' site_name from v$process proc where proc.pid = 2; set sqlprompt '&&site_name_new' set termout on so that when a user launches sqlplus, it would show something at prompt like [EMAIL PROTECTED]> instead of SQL> I found that doing this in 9i will prevent me starting up my instance, when I use sqlplus '/as sysdba' In 8i, I always used svrmgrl to bounce db so there was no problem with modified glogin.sql. Has anyone found a work-around in 9i so that sqlplus prompt displays "username" and "hostname" when launched? I know there is a new _CONNECT_IDENTIFIER in 9i, but that's not good enough. TIA. Guang -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - 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).
sqlplus prompt question in 9i
Hi: With Oracle 8i, I always modified $ORACLE_HOME/sqlplus/admin/glogin.sql, and added set termout off col site_name noprint new_value site_name_new select 'SQL> ' site_name from dual; selectuser || substr(proc.program, instr(proc.program,'@'), instr(proc.program,' ') - instr(proc.program,'@')) || '-SQL> ' site_name from v$process proc where proc.pid = 2; set sqlprompt '&&site_name_new' set termout on so that when a user launches sqlplus, it would show something at prompt like [EMAIL PROTECTED]> instead of SQL> I found that doing this in 9i will prevent me starting up my instance, when I use sqlplus '/as sysdba' In 8i, I always used svrmgrl to bounce db so there was no problem with modified glogin.sql. Has anyone found a work-around in 9i so that sqlplus prompt displays "username" and "hostname" when launched? I know there is a new _CONNECT_IDENTIFIER in 9i, but that's not good enough. TIA. Guang -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
