Re: Re[2]: Big Whoops

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
>
> 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.machine),
> instr(s.machine,'.') - 1)) ||
> '-SQL> '''
> FROM V$SESSION s, V$DATABASE D
> WHERE s.SID=1;
> 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.machine),
> instr(s.machine,'.') - 1)) ||
> '-SQL> '''
> FROM V$SESSION s, V$DATABASE D
> WHERE s.SID=1;
> 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
> > > > >To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> > > > >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
> > > > >>Reply-To: ORACLE-L_at_fatcity.com
> > > > >>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
> > > > >> >Reply-To: ORACLE-L_at_fatcity.com
> > > > >> >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!
> > > > >> >
> > > > >> >
> > > > >> >
> > > > >> >
> > > > >> >--
> > > > >> >Author:
> > > > >> > INET: Lisa_Koivu_at_gelco.com
> > > > >> >
> > > > >> >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: 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).
> > > > >>
> > > > >>________________________________________________________________________
> > > > >>Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com
> > > > >>
> > > > >>--
> > > > >>Author: Rachel Carmichael
> > > > >> INET: carmichr_at_hotmail.com
> > > > >>
> > > > >>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: 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).
> > > > >>--
> > > > >>Author:
> > > > >> INET: dgoulet_at_vicr.com
> > > > >>
> > > > >>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: 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).
> > > > >
> > > > >________________________________________________________________________
> > > > >Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com
> > > > >
> > > > >--
> > > > >Author: Steve Boyd
> > > > > INET: pimaco_oracle_at_hotmail.com
> > > > >
> > > > >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: 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).
> > > >
> > > > ________________________________________________________________________
> > > > Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com
> > > >
> > > > --
> > > > Author: Rachel Carmichael
> > > > INET: carmichr_at_hotmail.com
> > > >
> > > > 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: 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).
> > > >
> > >
> > > --
> > > Author: Ari D Kaplan
> > > INET: akaplan_at_interaccess.com
> > >
> > > 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: 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).
> > >
> >
> >
> > Jared Still
> > Certified Oracle DBA and Part Time Perl Evangelist ;-)
> > Regence BlueCross BlueShield of Oregon
> > jkstill_at_bcbso.com - Work - preferred address
> > jkstill_at_teleport.com - private
> >
> >
> > --
> > Author: Jared Still
> > INET: jkstill_at_bcbso.com
> >
> > 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: 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).
> >
>
> --
> Author: Ari D Kaplan
> INET: akaplan_at_interaccess.com
>
> 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: 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
Received on Mon May 29 2000 - 13:44:42 CDT