Hi there Oracle Gurus
I have tried this. It works fine in most cases.
I have inserted this into the glogin.sql.
My concern is when you connect in sqlplus to another database.
It will not pick up the entry in the glogin.sql for the new database.
It will still point to the old database which can be misleading.
Is there a work around???
Thanks and Regards
Suhen Pather
Oracle DBA
Telkom SA
>>> akaplan_at_interaccess.com 05/31/00 05:52PM >>>
Ruth,
In my reply I cut out a snippet of the entire program to explain about
machine names. The entire script must be run for this to work:
set heading off
set prompt off
spool make_prompt.sql
SELECT 'set sqlprompt '''|| d.name ||'@' ||
substr(s.machine,1,decode (instr(s.machine,'.'), 0, length(s.machine),
instr(s.machine,'.') - 1)) ||
'-SQL> '''
FROM V$SESSION s, V$DATABASE D
WHERE s.SID=3D1;
spool off
@make_prompt.sql
set heading on
set feedback on
-Ari Kaplan
www.arikaplan.com=20
On Wed, 31 May 2000, Ruth Gramolini wrote:
> Ari,
> I ran this script on my production databases. It give the sqlprompt at =
the
> end of the script but they comes back with the SQL> prompt. How can you
> make this permanent?
>=20
> Thanks!
> Ruth
> ----- Original Message -----
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> Sent: Monday, May 29, 2000 6:14 PM
>=20
>=20
> > I believe the example shows how to embed the machine name.
> >
> > SELECT 'set sqlprompt '''|| d.name ||'@' ||
> > substr(s.machine,1,decode (instr(s.machine,'.'), 0, length(s.machine=
),
> > instr(s.machine,'.') - 1)) ||
> > '-SQL> '''
> > FROM V$SESSION s, V$DATABASE D
> > WHERE s.SID=3D1;
> >
> > The output will look like:
> > set sqlprompt 'PHIS_at_survlpd-SQL> '
> >
> >
> > In the above example, "survlpd" is the machine name.
> >
> > -Ari
> > www.arikaplan.com=20
> > On Mon, 29 May 2000, Jared Still wrote:
> >
> > >
> > > In that case, maybe you could provide various methods
> > > of embedding the machine name in the SQL prompt as well.
> > >
> > > Jared
> > >
> > > On Sat, 27 May 2000, Ari D Kaplan wrote:
> > >
> > > > I do the SQL prompt change as well... for the listers benefit I am
> > > > providing part of my upcoming EOUG speech (how many of you really =
are
> > > > coming?) that explains how to do this...
> > > >
> > > > -Ari
> > > > www.arikaplan.com=20
> > > >
> > > > When you first get your Oracle CD-ROM, install and create your
> database,
> > > > and go to SQL*Plus, you are greeted with the familiar "SQL>" =
prompt.
> What
> > > > many Oracle professionals do not know is that this prompt can be
> changed.
> > > > It could be useful to have the prompt give the time, the username,
> which
> > > > machine you are on, or any other information particular to an
> application.
> > > > The prompt is most easily changed by modifying the global or local
> login
> > > > scripts. The global login script, which is the
> > > > $ORACLE_HOME/sqlplus/admin/glogin.sql file, gets run when anyone
> connects
> > > > to the database. It is here that you can put in SQL to control =
what
> > > > happens when a user invokes SQL*Plus. Common commands are =
formatting
> of
> > > > columns, setting PAGESIZE, putting in messages, changing optimizer
> goals,
> > > > or changing the prompt. For example:
> > > > set heading off
> > > > select 'Logged in as ' || username from user_users;
> > > > set heading on
> > > > will print "Logged in as SCOTT" when you connect as SCOTT.
> > > > If there is a file in $ORACLE_PATH called "login.sql", it is =
referred
> to
> > > > as a local login script. This will override the global login =
script.
> If
> > > > you use local or global login scripts, you can change the SQL =
prompt.
> > > > SELECT 'set sqlprompt '''|| d.name ||'@' ||
> > > > substr(s.machine,1,decode (instr(s.machine,'.'), 0, length(s.machin=
e),
> > > > instr(s.machine,'.') - 1)) ||
> > > > '-SQL> '''
> > > > FROM V$SESSION s, V$DATABASE D
> > > > WHERE s.SID=3D1;
> > > > The output will look like:
> > > > set sqlprompt 'PHIS_at_survlpd-SQL> '
> > > > This will use the "SET SQLPROMPT" command to be the instance name =
and
> the
> > > > server name. For this to work, you will need to spool the result =
of
> the
> > > > SELECT clause to a file, and then run the file. Putting it all
> together:
> > > > set heading off
> > > > set prompt off
> > > > spool make_prompt.sql
> > > > SELECT 'set sqlprompt '''|| d.name ||'@' ||
> > > > substr(s.machine,1,decode (instr(s.machine,'.'), 0, length(s.machin=
e),
> > > > instr(s.machine,'.') - 1)) ||
> > > > '-SQL> '''
> > > > FROM V$SESSION s, V$DATABASE D
> > > > WHERE s.SID=3D1;
> > > > spool off
> > > > @make_prompt.sql
> > > > set heading on
> > > > set feedback on
> > > > You will now have a nice prompt each time you connect. With =
multiple
> > > > windows open on your computer, this is a good way to keep track of
> which
> > > > database each window is connect to.
> > > >
> > > >
> > > > On Sat, 27 May 2000, Jared Still wrote:
> > > >
> > > > >
> > > > > > Since then I color-code my windows (light red on black for
> production
> > > > > > windows)...
> > > > >
> > > > > This seems to work for some people. A fellow DBA here at
> > > > > Blue Cross does this.
> > > > >
> > > > > It never seemed to work for me. Embedding the database name
> > > > > in the SQL prompt seems to work best, at least for me.
> > > > >
> > > > > Jared
> > > > >
> > > > > >
> > > > > > Steve, Rachel - you are not alone...
> > > > > >
> > > > > >
> > > > > > -Ari
> > > > > >
> > > > > > On Thu, 25 May 2000, Rachel Carmichael wrote:
> > > > > >
> > > > > > > me....
> > > > > > >
> > > > > > > logged into two terminals, one was production, one was test. =
In
> both the
> > > > > > > tablespaces had the same names. So I think I am going to =
drop
> the two
> > > > > > > tablespaces in test, so I can recreate from production.
> > > > > > >
> > > > > > > I do alter tablespace <xxx> offline;
> > > > > > > drop tablespace <xxx> including contents;
> > > > > > >
> > > > > > > and then scream as I realize I dropped a production =
tablespace.
> I go into my
> > > > > > > user's office and tell him he can kill me later, this is =
what I
> did, how
> > > > > > > does he want me to proceed to fix it. fortunately the
> production one was
> > > > > > > static data, and could be recreated. we do so.
> > > > > > >
> > > > > > > he then tells me I have used up all my screw-ups for the =
next 5
> years.
> > > > > > >
> > > > > > > everyone does stupid stuff. the trick is to admit it and =
know
> how to recover
> > > > > > > from it
> > > > > > >
> > > > > > > Rachel
> > > > > > >
> > > > > > >
> > > > > > > >From: "Steve Boyd" <pimaco_oracle_at_hotmail.com>
> > > > > > > >Reply-To: ORACLE-L_at_fatcity.com=20
> > > > > > > >To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.c=
om>
> > > > > > > >Subject: Re: Re[2]: Big Whoops
> > > > > > > >Date: Thu, 25 May 2000 07:38:38 -0800
> > > > > > > >
> > > > > > > >I'd like to post a question for everyone on the list.
> > > > > > > >
> > > > > > > >How many of you have ever dropped a table, or done =
something
> like that in
> > > > > > > >the wrong database(thinking you were connected to say the =
test
> DB), and
> > > > > > > >hosed production?
> > > > > > > >
> > > > > > > >Steve Boyd
> > > > > > > >
> > > > > > > >>From: dgoulet_at_vicr.com=20
> > > > > > > >>Reply-To: ORACLE-L_at_fatcity.com=20
> > > > > > > >>To: Multiple recipients of list ORACLE-L
> <ORACLE-L_at_fatcity.com>
> > > > > > > >>Subject: Re[2]: Big Whoops
> > > > > > > >>Date: Thu, 25 May 2000 06:40:46 -0800
> > > > > > > >>
> > > > > > > >>A couple of years ago I was trying to create a new =
instance on
> one of our
> > > > > > > >>Unix
> > > > > > > >>boxes to support an additional manufacturing line. Since =
the
> first
> > > > > > > >>instance was
> > > > > > > >>well tuned, I copied the init.ora file to a new name & did =
the
> little
> > > > > > > >>editing
> > > > > > > >>that I believed necessary, like a new db_name. What I =
forgot
> was the
> > > > > > > >>control_file line. You can guess what happen next, yes =
the
> control files
> > > > > > > >>got
> > > > > > > >>trashed and the original instance terminated with errors. =
We
> had to
> > > > > > > >>recover
> > > > > > > >>that one from tape since I hadn't backed up the control =
files
> to trace for
> > > > > > > >>some
> > > > > > > >>time, but I do now at every shutdown. We live & learn =
from
> our mistakes,
> > > > > > > >>hopefully NOT on a production server.
> > > > > > > >>
> > > > > > > >>
> > > > > > > >>BTW: My advice to any new DBA out there is that when an
> emergency hits
> > > > > > > >>you,
> > > > > > > >>or
> > > > > > > >>an AW SH%$. First step in the process is to step back, =
take a
> deep breath
> > > > > > > >>&
> > > > > > > >>calm down. Adrenaline and instant reaction are your worst
> enemies.
> > > > > > > >>
> > > > > > > >>Dick Goulet
> > > > > > > >>Senior Oracle DBA
> > > > > > > >>Vicor Corporation
> > > > > > > >>
> > > > > > > >>____________________Reply Separator____________________
> > > > > > > >>Author: "Rachel Carmichael" <carmichr_at_hotmail.com>
> > > > > > > >>Date: 5/24/00 6:16 PM
> > > > > > > >>
> > > > > > > >>Lisa,
> > > > > > > >>
> > > > > > > >>You have backups? Restore from backup. Otherwise..... =
recreate
> and import
> > > > > > > >>is
> > > > > > > >>the way to go.
> > > > > > > >>
> > > > > > > >>I gotta say, the fact that you did this, figured out how =
to
> fix it, and
> > > > > > > >>didn't freak out about it, would impress me MUCH more on =
an
> interview than
> > > > > > > >>the fact that you have your OCP :)
> > > > > > > >>
> > > > > > > >>Rachel
> > > > > > > >>
> > > > > > > >>
> > > > > > > >> >From: Lisa_Koivu_at_gelco.com=20
> > > > > > > >> >Reply-To: ORACLE-L_at_fatcity.com=20
> > > > > > > >> >To: Multiple recipients of list ORACLE-L
> <ORACLE-L_at_fatcity.com>
> > > > > > > >> >Subject: Big Whoops
> > > > > > > >> >Date: Wed, 24 May 2000 14:44:29 -0800
> > > > > > > >> >
> > > > > > > >> >This is by far the funniest thing I have ever done. I =
began
> running the
> > > > > > > >> >create
> > > > > > > >> >database scripts (ON A TEST MACHINE) when the $ORACLE_SID=
> was set to a
> > > > > > > >> >currently
> > > > > > > >> >running database. It's now completely and totally =
hosed.
> I'm just
> > > > > > > >> >wondering if
> > > > > > > >> >there is any possible way of recovering from this. I am
> just recreating
> > > > > > > >> >the
> > > > > > > >> >database and reimporting the data - it's not a big deal, =
but
> for future
> > > > > > > >> >reference, I wonder if this really does mean THE END OF =
THE
> DATABASE AS
> > > > > > > >>WE
> > > > > > > >> >KNOW
> > > > > > > >> >IT. My gut feel is YES.
> > > > > > > >> >
> > > > > > > >> >I just can't stop laughing. I finish my OCP exams and
> completely TRASH
> > > > > > > >>a
> > > > > > > >> >large
> > > > > > > >> >database in the same day (and dump pink ice cream on my
> white sweater
> > > > > > > >> >simultaneously)! Like Kirti said, Who *WANTS* to be a =
DBA?
> Are you out
> > > > > > > >>of
> > > > > > > >> >your
> > > > > > > >> >mind? you better be!
> > > > > > > >> >
> > > > > > > >> >
> > > > > > > >> >
> > > > > > > >> >
--=20
Author: Ari D Kaplan
INET: akaplan_at_interaccess.com=20
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
Received on Wed May 31 2000 - 10:44:38 CDT
