Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Re[2]: Big Whoops

RE: Re[2]: Big Whoops

From: Deshpande, Kirti <Kirti.Deshpande_at_gtedc.gte.com>
Date: Mon, 29 May 2000 18:49:35 -0500
Message-Id: <10512.107041@fatcity.com> 





Here is one way to do this in Oracle 7 that I know.. It shows the machine
name and sid name just before the SQL> prompt....
However, the prompt won't change for remote connections made from local the
local connection. 



set termout off


col myprompt noprint new_value myprompt_new
SELECT 'SQL> ' myprompt from dual;


SELECT substr(proc.program,

       instr(proc.program,'@')+1,
       instr(proc.program,' ') - instr(proc.program,'@')) ||
       '[' || param.value ||
       '] SQL> ' myprompt




FROM v$process proc, v$parameter param


WHERE param.name = 'db_name'


  AND proc.pid = 2;


set sqlprompt '&&myprompt_new'


/


set termout on    



HTH..



- Kirti




> -----Original Message-----

> From:	Jared Still [SMTP:jkstill_at_bcbso.com]

> Sent:	Monday, May 29, 2000 6:24 PM

> To:	Multiple recipients of list ORACLE-L

> Subject:	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  ;-)

> > > Regence BlueCross BlueShield of Oregon

> > > jkstill_at_bcbso.com - Work - preferred address

> > > jkstill_at_teleport.com - private

> > > 

> > > 

> > 

> 

> 

> 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

Received on Mon May 29 2000 - 18:49:35 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US