| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Re[2]: Big Whoops
Ari,
There is no such a thing to refresh the prompt after user the CONNECT command in SQLPLUS.
Like Jared, I have a connect.sql file in the bin directory which prompt for username, password and dbstring. And I will just call that script by @connect and enter all the value at prompt.
The connect.sql script got exactly the same settings as the glogin.sql for the prompt.
Winnie
Jared Still <jkstill_at_bcbso.com> on 06/05/2000 10:11:31 AM
Please respond to ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> cc: (bcc: Winnie Liu/HQ/ISC)
Subject: RE: Re[2]: Big Whoops
Ari,
I created a script 'c.sql' to do my connects, and got in the habit of using '@c scott/tiger_at_mydb' rather than using 'connect' directly.
c.sql calls 'setprompt.sql', and anything else needed.
Jared
On Mon, 29 May 2000, Ari D Kaplan wrote:
> Thanks Kirti for providing this in Oracle7.
>
> One problem with this is if you are already connected and issue:
>
> connect username/password_at_DB_LINK;
>
> I don't know how to get around that... if anyone out there knows how to
> refresh the prompt when you issue "connect" that would be great.
>
>
> Why are we doing this on a holiday? Well, I already had my corn on the
> cob, and the email is always on, so.....
>
> Regards,
>
> -Ari
> www.arikaplan.com
>
> On Mon, 29 May 2000, Deshpande, Kirti wrote:
>
> > 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).
> > > > > > > > >
> > > > > > > > >
> > >
Jared Still
Certified Oracle DBA and Part Time Perl Evangelist ;-)
Received on Mon Jun 05 2000 - 12:47:42 CDT
 |
 |