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-----
Sent: Wednesday, October 29, 2003 6:19 PM To: Multiple recipients of list ORACLE-L

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 <gmei_at_incyte.com>:

> 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
> >
> > username_at_hostname-SQL>
> >
> > 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: gmei_at_incyte.com
> >
> > 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: ListGuru_at_fatcity.com (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: joan.hsieh_at_tufts.edu
>
> 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: ListGuru_at_fatcity.com (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: gmei_at_incyte.com
>
> 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: ListGuru_at_fatcity.com (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: joan.hsieh_at_tufts.edu

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: ListGuru_at_fatcity.com (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: david hill
  INET: david.hill_at_lechateau.ca

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: ListGuru_at_fatcity.com (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).