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
> ---------------------------------------------------------------------
> 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).
