Re: Re[2]: Big Whoops

Oops. I didn't scroll down and read the entire message. Sorry about that.

What I had in mind, but didn't say, ( the SEND key is faster than the brain ) was in regards to version 7 databases.

I used to pull the machine name out of MTS configuration info, but we don't use MTS here.

Any idea how to do this in 7.x databases?

Thanks

Jared

P.S. Why are we doing this on a holiday?

On Mon, 29 May 2000, Ari D Kaplan wrote:

> 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=1;
>
> The output will look like:
> set sqlprompt 'PHIS_at_survlpd-SQL> '
>
>
> In the above example, "survlpd" is the machine name.
>
> -Ari
> www.arikaplan.com
> 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
> > >
> > > 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
> > > also send the HELP command for other information (like subscribing).
> > >
> >
> >
> > Jared Still
> > Certified Oracle DBA and Part Time Perl Evangelist ;-)
Received on Mon May 29 2000 - 17:15:21 CDT