| 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
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------_=_NextPart_001_01BFCCF3.5D0A6B90
Content-Type: text/plain;
charset="iso-8859-1"
The problem with this approach (as was posted before) is that only users
with access to v$session and v$database
can use it.
I created PL/SQL function owned by sys (or any other user with select privilege for v$session and v$database) and with execute given to public which return sqlprompt string. For this script to work database should have such function. I have a check if such synonym exist. This is the part of glogin.sql
set termout off
define user_prompt=''
variable sql_prompt varchar2(50)
declare
v_count number;
v_sql_prompt varchar2(50);
begin
select count(*) into v_count from all_synonyms where synonym_name =
'GET_SQLPROMPT_FS';
:sql_prompt := 'SQL->';
if v_count > 0 then
execute immediate 'select rtrim(get_sqlprompt_fs) from dual' into
v_sql_prompt;
:sql_prompt := v_sql_prompt;
end if;
end;
/
column x new_value user_prompt
select :sql_prompt x from dual;
set sqlprompt "&user_prompt"
set termout on
I also saved this in the c.sql ( after line connect &1 ) and put it into directory $SQLPATH (in registry on NT)
Instead of connect aaa/bbb/@ddd I use @c aaa/bbb/@ddd and this will change SQL prompt.
This function produce sqlprompt as
hostname:dbname:user_name:session_id:serial#->
I such synonym does not exist it will produce sqlprompt as SQL->
Here is the code of function:
create or replace function get_sqlprompt_f
return varchar2
is
v_host_name varchar2(12); v_dbname varchar2(10); v_session_id varchar2(6); v_serial_nm varchar2(6); v_user_name varchar2(10);
begin
SELECT d.name into v_dbname from v$database d;
select substr(s.machine,1,decode (instr(s.machine,'.'), 0, length(s.machine), instr(s.machine,'.') - 1)) into v_host_name FROM V$SESSION s WHERE s.SID=1;
select rtrim(to_char(sid)), rtrim(to_char(serial#)) into v_session_id,
v_serial_nm
FROM v$session WHERE audsid = userenv('SESSIONID');
select user into v_user_name from dual;
return
v_host_name||':'||v_dbname||':'||v_user_name||':'||v_session_id||':'||v_seri
al_nm||'->';
end get_sqlprompt_f;
/
show errors;
Also you need to give execute on this function to PUBLIC and create public synonym get_sqlprompt_fs for this function.
Alex Hillman
-----Original Message-----
From: Ari D Kaplan [mailto:akaplan_at_interaccess.com]
Sent: Wednesday, May 31, 2000 11:52 AM
To: Multiple recipients of list ORACLE-L
Subject: Re: Re[2]: Big Whoops
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=1;
spool off
@make_prompt.sql
set heading on
set feedback on
-Ari Kaplan
www.arikaplan.com
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? > > Thanks! > Ruth > ----- Original Message ----- > To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> > Sent: Monday, May 29, 2000 6:14 PM > > > > 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 reallyare
> > > > 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 isreferred
> 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 SQLprompt.
> > > > 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 nameand
> 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 productiontablespace.
> 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 next5
> 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 thetest
> 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 instanceon
> one of our > > > > > > > >>Unix > > > > > > > >>boxes to support an additional manufacturing line. Sincethe
> first > > > > > > > >>instance was > > > > > > > >>well tuned, I copied the init.ora file to a new name & didthe
> little > > > > > > > >>editing > > > > > > > >>that I believed necessary, like a new db_name. What Iforgot
> 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 controlfiles
> 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, takea
> 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. Ibegan
> 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
> 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 aDBA?
> Are you out > > > > > > > >>of > > > > > > > >> >your > > > > > > > >> >mind? you better be! > > > > > > > >> > > > > > > > > >> > > > > > > > > >> > > > > > > > > >> >
--
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).
------_=_NextPart_001_01BFCCF3.5D0A6B90
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Diso-8859-1">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
5.5.2448.0">
<TITLE>RE: Re[2]: Big Whoops</TITLE>
</HEAD>
<BODY>
<P><FONT SIZE=3D2>The problem with this approach (as was posted before) =
is that only users with access to v$session and v$database</FONT>
<BR><FONT SIZE=3D2>can use it.</FONT>
</P>
<P><FONT SIZE=3D2>I created PL/SQL function owned by sys (or any other =
user with select privilege for v$session and v$database) and with =
execute given to public which return sqlprompt string. For this script =
to work database should have such function. I have a check if such =
synonym exist. This is the part of glogin.sql</FONT></P>
<P><FONT SIZE=3D2>set termout off</FONT>
<BR><FONT SIZE=3D2>define user_prompt=3D''</FONT>
</P>
<P><FONT SIZE=3D2>variable sql_prompt varchar2(50)</FONT>
</P>
<P><FONT SIZE=3D2>declare</FONT>
<BR><FONT SIZE=3D2> v_count number;</FONT>
<BR><FONT SIZE=3D2> v_sql_prompt varchar2(50);</FONT>
<BR><FONT SIZE=3D2>begin</FONT>
<BR><FONT SIZE=3D2> select count(*) into v_count from all_synonyms =
where synonym_name =3D 'GET_SQLPROMPT_FS';</FONT>
<BR><FONT SIZE=3D2> :sql_prompt :=3D 'SQL->';</FONT>
<BR><FONT SIZE=3D2> if v_count > 0 then</FONT>
<BR><FONT SIZE=3D2> execute immediate 'select =
rtrim(get_sqlprompt_fs) from dual' into v_sql_prompt;</FONT>
<BR><FONT SIZE=3D2> :sql_prompt :=3D v_sql_prompt;</FONT>
<BR><FONT SIZE=3D2> end if;</FONT>
<BR><FONT SIZE=3D2>end;</FONT>
<BR><FONT SIZE=3D2>/</FONT>
</P>
<P><FONT SIZE=3D2>column x new_value user_prompt</FONT>
</P>
<P><FONT SIZE=3D2>select :sql_prompt x from dual;</FONT>
</P>
<P><FONT SIZE=3D2>set sqlprompt "&user_prompt"</FONT>
</P>
<P><FONT SIZE=3D2>set termout on </FONT>
</P>
<BR>
<P><FONT SIZE=3D2>I also saved this in the c.sql ( after line connect =
&1 )</FONT>
<BR><FONT SIZE=3D2>and put it into directory $SQLPATH (in registry on =
NT) </FONT>
</P>
<P><FONT SIZE=3D2>Instead of connect aaa/bbb/@ddd I use @c aaa/bbb/@ddd =
and this will change SQL prompt.</FONT>
</P>
<P><FONT SIZE=3D2>This function produce sqlprompt as =
hostname:dbname:user_name:session_id:serial#-></FONT>
<BR><FONT SIZE=3D2>I such synonym does not exist it will produce =
sqlprompt as SQL-></FONT>
</P>
<BR>
<P><FONT SIZE=3D2>Here is the code of function:</FONT>
</P>
<P><FONT SIZE=3D2>create or replace function get_sqlprompt_f</FONT>
<BR><FONT SIZE=3D2>return varchar2</FONT>
<BR><FONT SIZE=3D2>is</FONT>
</P>
<P><FONT SIZE=3D2>v_host_name varchar2(12);</FONT>
<BR><FONT SIZE=3D2>v_dbname varchar2(10);</FONT>
<BR><FONT SIZE=3D2>v_session_id varchar2(6);</FONT>
<BR><FONT SIZE=3D2>v_serial_nm varchar2(6);</FONT>
<BR><FONT SIZE=3D2>v_user_name varchar2(10);</FONT>
</P>
<P><FONT SIZE=3D2>begin</FONT>
</P>
<P><FONT SIZE=3D2>SELECT d.name into v_dbname from v$database d;</FONT>
</P>
<P><FONT SIZE=3D2>select substr(s.machine,1,decode =
(instr(s.machine,'.'), 0, length(s.machine), instr(s.machine,'.') - 1)) =
into v_host_name</FONT></P>
<P><FONT SIZE=3D2>FROM V$SESSION s WHERE s.SID=3D1;</FONT>
</P>
<P><FONT SIZE=3D2>select rtrim(to_char(sid)), rtrim(to_char(serial#)) =
into v_session_id, v_serial_nm</FONT>
<BR><FONT SIZE=3D2>FROM v$session WHERE audsid =3D =
userenv('SESSIONID');</FONT>
</P>
<P><FONT SIZE=3D2>select user into v_user_name from dual;</FONT>
</P>
<P><FONT SIZE=3D2>return =
v_host_name||':'||v_dbname||':'||v_user_name||':'||v_session_id||':'||v_=
serial_nm||'->';</FONT>
</P>
<P><FONT SIZE=3D2>end get_sqlprompt_f;</FONT>
<BR><FONT SIZE=3D2>/</FONT>
<BR><FONT SIZE=3D2>show errors;</FONT>
</P>
<BR>
<P><FONT SIZE=3D2>Also you need to give execute on this function to =
PUBLIC and create public synonym get_sqlprompt_fs for this =
function.</FONT>
</P>
<BR>
<P><FONT SIZE=3D2>Alex Hillman</FONT>
</P>
<P><FONT SIZE=3D2>-----Original Message-----</FONT>
<BR><FONT SIZE=3D2>From: Ari D Kaplan [<A =
HREF=3D"mailto:akaplan_at_interaccess.com">mailto:akaplan_at_interaccess.com</=
A>]</FONT>
<BR><FONT SIZE=3D2>Sent: Wednesday, May 31, 2000 11:52 AM</FONT>
<BR><FONT SIZE=3D2>To: Multiple recipients of list ORACLE-L</FONT>
<BR><FONT SIZE=3D2>Subject: Re: Re[2]: Big Whoops</FONT>
</P>
<BR>
<P><FONT SIZE=3D2>Ruth,</FONT>
</P>
<P><FONT SIZE=3D2>In my reply I cut out a snippet of the entire program =
to explain about</FONT>
<BR><FONT SIZE=3D2>machine names. The entire script must be run for =
this to work:</FONT>
</P>
<P><FONT SIZE=3D2>set heading off</FONT>
<BR><FONT SIZE=3D2>set prompt off</FONT>
<BR><FONT SIZE=3D2>spool make_prompt.sql</FONT>
<BR><FONT SIZE=3D2>SELECT 'set sqlprompt '''|| d.name ||'@' ||</FONT>
<BR><FONT SIZE=3D2>substr(s.machine,1,decode (instr(s.machine,'.'), 0, =
length(s.machine),</FONT>
<BR><FONT SIZE=3D2>instr(s.machine,'.') - 1)) ||</FONT>
<BR><FONT SIZE=3D2>'-SQL> '''</FONT>
<BR><FONT SIZE=3D2>FROM V$SESSION s, V$DATABASE D</FONT>
<BR><FONT SIZE=3D2>WHERE s.SID=3D1;</FONT>
<BR><FONT SIZE=3D2>spool off</FONT>
<BR><FONT SIZE=3D2>@make_prompt.sql</FONT>
<BR><FONT SIZE=3D2>set heading on</FONT>
<BR><FONT SIZE=3D2>set feedback on</FONT>
</P>
<P><FONT SIZE=3D2>-Ari Kaplan</FONT>
<BR><FONT SIZE=3D2>www.arikaplan.com</FONT>
</P>
<P><FONT SIZE=3D2>On Wed, 31 May 2000, Ruth Gramolini wrote:</FONT>
</P>
<P><FONT SIZE=3D2>> Ari,</FONT>
<BR><FONT SIZE=3D2>> I ran this script on my production =
databases. It give the sqlprompt at the</FONT>
<BR><FONT SIZE=3D2>> end of the script but they comes back with the =
SQL> prompt. How can you</FONT>
<BR><FONT SIZE=3D2>> make this permanent?</FONT>
<BR><FONT SIZE=3D2>> </FONT>
<BR><FONT SIZE=3D2>> Thanks!</FONT>
<BR><FONT SIZE=3D2>> Ruth</FONT>
<BR><FONT SIZE=3D2>> ----- Original Message -----</FONT>
<BR><FONT SIZE=3D2>> To: Multiple recipients of list ORACLE-L =
<ORACLE-L_at_fatcity.com></FONT>
<BR><FONT SIZE=3D2>> Sent: Monday, May 29, 2000 6:14 PM</FONT>
<BR><FONT SIZE=3D2>> </FONT>
<BR><FONT SIZE=3D2>> </FONT>
<BR><FONT SIZE=3D2>> > I believe the example shows how to embed =
the machine name.</FONT>
<BR><FONT SIZE=3D2>> ></FONT>
<BR><FONT SIZE=3D2>> > SELECT 'set sqlprompt '''|| d.name ||'@' =
||</FONT>
<BR><FONT SIZE=3D2>> > =
substr(s.machine,1,decode (instr(s.machine,'.'), 0, =
length(s.machine),</FONT>
<BR><FONT SIZE=3D2>> > instr(s.machine,'.') - =
1)) ||</FONT>
<BR><FONT SIZE=3D2>> > '-SQL> '''</FONT>
<BR><FONT SIZE=3D2>> > FROM V$SESSION s, V$DATABASE D</FONT>
<BR><FONT SIZE=3D2>> > WHERE s.SID=3D1;</FONT>
<BR><FONT SIZE=3D2>> ></FONT>
<BR><FONT SIZE=3D2>> > The output will look like:</FONT>
<BR><FONT SIZE=3D2>> > set sqlprompt 'PHIS_at_survlpd-SQL> =
'</FONT>
<BR><FONT SIZE=3D2>> ></FONT>
<BR><FONT SIZE=3D2>> ></FONT>
<BR><FONT SIZE=3D2>> > In the above example, "survlpd" =
is the machine name.</FONT>
<BR><FONT SIZE=3D2>> ></FONT>
<BR><FONT SIZE=3D2>> > -Ari</FONT>
<BR><FONT SIZE=3D2>> > www.arikaplan.com</FONT>
<BR><FONT SIZE=3D2>> > On Mon, 29 May 2000, Jared Still =
wrote:</FONT>
<BR><FONT SIZE=3D2>> ></FONT>
<BR><FONT SIZE=3D2>> > ></FONT>
<BR><FONT SIZE=3D2>> > > In that case, maybe you could provide =
various methods</FONT>
<BR><FONT SIZE=3D2>> > > of embedding the machine name in the =
SQL prompt as well.</FONT>
<BR><FONT SIZE=3D2>> > ></FONT>
<BR><FONT SIZE=3D2>> > > Jared</FONT>
<BR><FONT SIZE=3D2>> > ></FONT>
<BR><FONT SIZE=3D2>> > > On Sat, 27 May 2000, Ari D Kaplan =
wrote:</FONT>
<BR><FONT SIZE=3D2>> > ></FONT>
<BR><FONT SIZE=3D2>> > > > I do the SQL prompt change as =
well... for the listers benefit I am</FONT>
<BR><FONT SIZE=3D2>> > > > providing part of my upcoming =
EOUG speech (how many of you really are</FONT>
<BR><FONT SIZE=3D2>> > > > coming?) that explains how to do =
this...</FONT>
<BR><FONT SIZE=3D2>> > > ></FONT>
<BR><FONT SIZE=3D2>> > > > -Ari</FONT>
<BR><FONT SIZE=3D2>> > > > www.arikaplan.com</FONT>
<BR><FONT SIZE=3D2>> > > ></FONT>
<BR><FONT SIZE=3D2>> > > > When you first get your Oracle =
CD-ROM, install and create your</FONT>
<BR><FONT SIZE=3D2>> database,</FONT>
<BR><FONT SIZE=3D2>> > > > and go to SQL*Plus, you are =
greeted with the familiar "SQL>" prompt.</FONT>
<BR><FONT SIZE=3D2>> What</FONT>
<BR><FONT SIZE=3D2>> > > > many Oracle professionals do not =
know is that this prompt can be</FONT>
<BR><FONT SIZE=3D2>> changed.</FONT>
<BR><FONT SIZE=3D2>> > > > It could be useful to have the =
prompt give the time, the username,</FONT>
<BR><FONT SIZE=3D2>> which</FONT>
<BR><FONT SIZE=3D2>> > > > machine you are on, or any other =
information particular to an</FONT>
<BR><FONT SIZE=3D2>> application.</FONT>
<BR><FONT SIZE=3D2>> > > > The prompt is most easily =
changed by modifying the global or local</FONT>
<BR><FONT SIZE=3D2>> login</FONT>
<BR><FONT SIZE=3D2>> > > > scripts. The global login =
script, which is the</FONT>
<BR><FONT SIZE=3D2>> > > > =
$ORACLE_HOME/sqlplus/admin/glogin.sql file, gets run when anyone</FONT>
<BR><FONT SIZE=3D2>> connects</FONT>
<BR><FONT SIZE=3D2>> > > > to the database. It is here that =
you can put in SQL to control what</FONT>
<BR><FONT SIZE=3D2>> > > > happens when a user invokes =
SQL*Plus. Common commands are formatting</FONT>
<BR><FONT SIZE=3D2>> of</FONT>
<BR><FONT SIZE=3D2>> > > > columns, setting PAGESIZE, =
putting in messages, changing optimizer</FONT>
<BR><FONT SIZE=3D2>> goals,</FONT>
<BR><FONT SIZE=3D2>> > > > or changing the prompt. For =
example:</FONT>
<BR><FONT SIZE=3D2>> > > > set heading off</FONT>
<BR><FONT SIZE=3D2>> > > > select 'Logged in as ' || =
username from user_users;</FONT>
<BR><FONT SIZE=3D2>> > > > set heading on</FONT>
<BR><FONT SIZE=3D2>> > > > will print "Logged in as =
SCOTT" when you connect as SCOTT.</FONT>
<BR><FONT SIZE=3D2>> > > > If there is a file in =
$ORACLE_PATH called "login.sql", it is referred</FONT>
<BR><FONT SIZE=3D2>> to</FONT>
<BR><FONT SIZE=3D2>> > > > as a local login script. This =
will override the global login script.</FONT>
<BR><FONT SIZE=3D2>> If</FONT>
<BR><FONT SIZE=3D2>> > > > you use local or global login =
scripts, you can change the SQL prompt.</FONT>
<BR><FONT SIZE=3D2>> > > > SELECT 'set sqlprompt '''|| =
d.name ||'@' ||</FONT>
<BR><FONT SIZE=3D2>> > > > substr(s.machine,1,decode =
(instr(s.machine,'.'), 0, length(s.machine),</FONT>
<BR><FONT SIZE=3D2>> > > > instr(s.machine,'.') - 1)) =
||</FONT>
<BR><FONT SIZE=3D2>> > > > '-SQL> '''</FONT>
<BR><FONT SIZE=3D2>> > > > FROM V$SESSION s, V$DATABASE =
D</FONT>
<BR><FONT SIZE=3D2>> > > > WHERE s.SID=3D1;</FONT>
<BR><FONT SIZE=3D2>> > > > The output will look =
like:</FONT>
<BR><FONT SIZE=3D2>> > > > set sqlprompt =
'PHIS_at_survlpd-SQL> '</FONT>
<BR><FONT SIZE=3D2>> > > > This will use the "SET =
SQLPROMPT" command to be the instance name and</FONT>
<BR><FONT SIZE=3D2>> the</FONT>
<BR><FONT SIZE=3D2>> > > > server name. For this to work, =
you will need to spool the result of</FONT>
<BR><FONT SIZE=3D2>> the</FONT>
<BR><FONT SIZE=3D2>> > > > SELECT clause to a file, and =
then run the file. Putting it all</FONT>
<BR><FONT SIZE=3D2>> together:</FONT>
<BR><FONT SIZE=3D2>> > > > set heading off</FONT>
<BR><FONT SIZE=3D2>> > > > set prompt off</FONT>
<BR><FONT SIZE=3D2>> > > > spool make_prompt.sql</FONT>
<BR><FONT SIZE=3D2>> > > > SELECT 'set sqlprompt '''|| =
d.name ||'@' ||</FONT>
<BR><FONT SIZE=3D2>> > > > substr(s.machine,1,decode =
(instr(s.machine,'.'), 0, length(s.machine),</FONT>
<BR><FONT SIZE=3D2>> > > > instr(s.machine,'.') - 1)) =
||</FONT>
<BR><FONT SIZE=3D2>> > > > '-SQL> '''</FONT>
<BR><FONT SIZE=3D2>> > > > FROM V$SESSION s, V$DATABASE =
D</FONT>
<BR><FONT SIZE=3D2>> > > > WHERE s.SID=3D1;</FONT>
<BR><FONT SIZE=3D2>> > > > spool off</FONT>
<BR><FONT SIZE=3D2>> > > > @make_prompt.sql</FONT>
<BR><FONT SIZE=3D2>> > > > set heading on</FONT>
<BR><FONT SIZE=3D2>> > > > set feedback on</FONT>
<BR><FONT SIZE=3D2>> > > > You will now have a nice prompt =
each time you connect. With multiple</FONT>
<BR><FONT SIZE=3D2>> > > > windows open on your computer, =
this is a good way to keep track of</FONT>
<BR><FONT SIZE=3D2>> which</FONT>
<BR><FONT SIZE=3D2>> > > > database each window is connect =
to.</FONT>
<BR><FONT SIZE=3D2>> > > ></FONT>
<BR><FONT SIZE=3D2>> > > ></FONT>
<BR><FONT SIZE=3D2>> > > > On Sat, 27 May 2000, Jared Still =
wrote:</FONT>
<BR><FONT SIZE=3D2>> > > ></FONT>
<BR><FONT SIZE=3D2>> > > > ></FONT>
<BR><FONT SIZE=3D2>> > > > > > Since then I =
color-code my windows (light red on black for</FONT>
<BR><FONT SIZE=3D2>> production</FONT>
<BR><FONT SIZE=3D2>> > > > > > windows)...</FONT>
<BR><FONT SIZE=3D2>> > > > ></FONT>
<BR><FONT SIZE=3D2>> > > > > This seems to work for some =
people. A fellow DBA here at</FONT>
<BR><FONT SIZE=3D2>> > > > > Blue Cross does =
this.</FONT>
<BR><FONT SIZE=3D2>> > > > ></FONT>
<BR><FONT SIZE=3D2>> > > > > It never seemed to work for =
me. Embedding the database name</FONT>
<BR><FONT SIZE=3D2>> > > > > in the SQL prompt seems to =
work best, at least for me.</FONT>
<BR><FONT SIZE=3D2>> > > > ></FONT>
<BR><FONT SIZE=3D2>> > > > > Jared</FONT>
<BR><FONT SIZE=3D2>> > > > ></FONT>
<BR><FONT SIZE=3D2>> > > > > ></FONT>
<BR><FONT SIZE=3D2>> > > > > > Steve, Rachel - you =
are not alone...</FONT>
<BR><FONT SIZE=3D2>> > > > > ></FONT>
<BR><FONT SIZE=3D2>> > > > > ></FONT>
<BR><FONT SIZE=3D2>> > > > > > -Ari</FONT>
<BR><FONT SIZE=3D2>> > > > > ></FONT>
<BR><FONT SIZE=3D2>> > > > > > On Thu, 25 May 2000, =
Rachel Carmichael wrote:</FONT>
<BR><FONT SIZE=3D2>> > > > > ></FONT>
<BR><FONT SIZE=3D2>> > > > > > > me....</FONT>
<BR><FONT SIZE=3D2>> > > > > > ></FONT>
<BR><FONT SIZE=3D2>> > > > > > > logged into two =
terminals, one was production, one was test. In</FONT>
<BR><FONT SIZE=3D2>> both the</FONT>
<BR><FONT SIZE=3D2>> > > > > > > tablespaces had =
the same names. So I think I am going to drop</FONT>
<BR><FONT SIZE=3D2>> the two</FONT>
<BR><FONT SIZE=3D2>> > > > > > > tablespaces in =
test, so I can recreate from production.</FONT>
<BR><FONT SIZE=3D2>> > > > > > ></FONT>
<BR><FONT SIZE=3D2>> > > > > > > I do alter =
tablespace <xxx> offline;</FONT>
<BR><FONT SIZE=3D2>> > > > > > > drop tablespace =
<xxx> including contents;</FONT>
<BR><FONT SIZE=3D2>> > > > > > ></FONT>
<BR><FONT SIZE=3D2>> > > > > > > and then scream =
as I realize I dropped a production tablespace.</FONT>
<BR><FONT SIZE=3D2>> I go into my</FONT>
<BR><FONT SIZE=3D2>> > > > > > > user's office and =
tell him he can kill me later, this is what I</FONT>
<BR><FONT SIZE=3D2>> did, how</FONT>
<BR><FONT SIZE=3D2>> > > > > > > does he want me =
to proceed to fix it. fortunately the</FONT>
<BR><FONT SIZE=3D2>> production one was</FONT>
<BR><FONT SIZE=3D2>> > > > > > > static data, and =
could be recreated. we do so.</FONT>
<BR><FONT SIZE=3D2>> > > > > > ></FONT>
<BR><FONT SIZE=3D2>> > > > > > > he then tells me =
I have used up all my screw-ups for the next 5</FONT>
<BR><FONT SIZE=3D2>> years.</FONT>
<BR><FONT SIZE=3D2>> > > > > > ></FONT>
<BR><FONT SIZE=3D2>> > > > > > > everyone does =
stupid stuff. the trick is to admit it and know</FONT>
<BR><FONT SIZE=3D2>> how to recover</FONT>
<BR><FONT SIZE=3D2>> > > > > > > from it</FONT>
<BR><FONT SIZE=3D2>> > > > > > ></FONT>
<BR><FONT SIZE=3D2>> > > > > > > Rachel</FONT>
<BR><FONT SIZE=3D2>> > > > > > ></FONT>
<BR><FONT SIZE=3D2>> > > > > > ></FONT>
<BR><FONT SIZE=3D2>> > > > > > > >From: =
"Steve Boyd" <pimaco_oracle_at_hotmail.com></FONT>
<BR><FONT SIZE=3D2>> > > > > > > >Reply-To: =
ORACLE-L_at_fatcity.com</FONT>
<BR><FONT SIZE=3D2>> > > > > > > >To: Multiple =
recipients of list ORACLE-L <ORACLE-L_at_fatcity.com></FONT>
<BR><FONT SIZE=3D2>> > > > > > > >Subject: Re: =
Re[2]: Big Whoops</FONT>
<BR><FONT SIZE=3D2>> > > > > > > >Date: Thu, 25 =
May 2000 07:38:38 -0800</FONT>
<BR><FONT SIZE=3D2>> > > > > > > ></FONT>
<BR><FONT SIZE=3D2>> > > > > > > >I'd like to =
post a question for everyone on the list.</FONT>
<BR><FONT SIZE=3D2>> > > > > > > ></FONT>
<BR><FONT SIZE=3D2>> > > > > > > >How many of =
you have ever dropped a table, or done something</FONT>
<BR><FONT SIZE=3D2>> like that in</FONT>
<BR><FONT SIZE=3D2>> > > > > > > >the wrong =
database(thinking you were connected to say the test</FONT>
<BR><FONT SIZE=3D2>> DB), and</FONT>
<BR><FONT SIZE=3D2>> > > > > > > >hosed =
production?</FONT>
<BR><FONT SIZE=3D2>> > > > > > > ></FONT>
<BR><FONT SIZE=3D2>> > > > > > > >Steve =
Boyd</FONT>
<BR><FONT SIZE=3D2>> > > > > > > ></FONT>
<BR><FONT SIZE=3D2>> > > > > > > >>From: =
dgoulet_at_vicr.com</FONT>
<BR><FONT SIZE=3D2>> > > > > > > >>Reply-To: =
ORACLE-L_at_fatcity.com</FONT>
<BR><FONT SIZE=3D2>> > > > > > > >>To: =
Multiple recipients of list ORACLE-L</FONT>
<BR><FONT SIZE=3D2>> <ORACLE-L_at_fatcity.com></FONT>
<BR><FONT SIZE=3D2>> > > > > > > >>Subject: =
Re[2]: Big Whoops</FONT>
<BR><FONT SIZE=3D2>> > > > > > > >>Date: =
Thu, 25 May 2000 06:40:46 -0800</FONT>
<BR><FONT SIZE=3D2>> > > > > > > >></FONT>
<BR><FONT SIZE=3D2>> > > > > > > >>A couple =
of years ago I was trying to create a new instance on</FONT>
<BR><FONT SIZE=3D2>> one of our</FONT>
<BR><FONT SIZE=3D2>> > > > > > > =
>>Unix</FONT>
<BR><FONT SIZE=3D2>> > > > > > > >>boxes to =
support an additional manufacturing line. Since the</FONT>
<BR><FONT SIZE=3D2>> first</FONT>
<BR><FONT SIZE=3D2>> > > > > > > >>instance =
was</FONT>
<BR><FONT SIZE=3D2>> > > > > > > >>well =
tuned, I copied the init.ora file to a new name & did the</FONT>
<BR><FONT SIZE=3D2>> little</FONT>
<BR><FONT SIZE=3D2>> > > > > > > =
>>editing</FONT>
<BR><FONT SIZE=3D2>> > > > > > > >>that I =
believed necessary, like a new db_name. What I forgot</FONT>
<BR><FONT SIZE=3D2>> was the</FONT>
<BR><FONT SIZE=3D2>> > > > > > > =
>>control_file line. You can guess what happen next, yes =
the</FONT>
<BR><FONT SIZE=3D2>> control files</FONT>
<BR><FONT SIZE=3D2>> > > > > > > >>got</FONT>=
<BR><FONT SIZE=3D2>> > > > > > > >>trashed =
and the original instance terminated with errors. We</FONT>
<BR><FONT SIZE=3D2>> had to</FONT>
<BR><FONT SIZE=3D2>> > > > > > > =
>>recover</FONT>
<BR><FONT SIZE=3D2>> > > > > > > >>that one =
from tape since I hadn't backed up the control files</FONT>
<BR><FONT SIZE=3D2>> to trace for</FONT>
<BR><FONT SIZE=3D2>> > > > > > > =
>>some</FONT>
<BR><FONT SIZE=3D2>> > > > > > > >>time, but =
I do now at every shutdown. We live & learn from</FONT>
<BR><FONT SIZE=3D2>> our mistakes,</FONT>
<BR><FONT SIZE=3D2>> > > > > > > >>hopefully =
NOT on a production server.</FONT>
<BR><FONT SIZE=3D2>> > > > > > > >></FONT>
<BR><FONT SIZE=3D2>> > > > > > > >></FONT>
<BR><FONT SIZE=3D2>> > > > > > > >>BTW: My =
advice to any new DBA out there is that when an</FONT>
<BR><FONT SIZE=3D2>> emergency hits</FONT>
<BR><FONT SIZE=3D2>> > > > > > > =
>>you,</FONT>
<BR><FONT SIZE=3D2>> > > > > > > >>or</FONT>
<BR><FONT SIZE=3D2>> > > > > > > >>an AW =
SH%$. First step in the process is to step back, take a</FONT>
<BR><FONT SIZE=3D2>> deep breath</FONT>
<BR><FONT SIZE=3D2>> > > > > > > =
>>&</FONT>
<BR><FONT SIZE=3D2>> > > > > > > >>calm =
down. Adrenaline and instant reaction are your worst</FONT>
<BR><FONT SIZE=3D2>> enemies.</FONT>
<BR><FONT SIZE=3D2>> > > > > > > >></FONT>
<BR><FONT SIZE=3D2>> > > > > > > >>Dick =
Goulet</FONT>
<BR><FONT SIZE=3D2>> > > > > > > >>Senior =
Oracle DBA</FONT>
<BR><FONT SIZE=3D2>> > > > > > > >>Vicor =
Corporation</FONT>
<BR><FONT SIZE=3D2>> > > > > > > >></FONT>
<BR><FONT SIZE=3D2>> > > > > > > =
>>____________________Reply Separator____________________</FONT>
<BR><FONT SIZE=3D2>> > > > > > > >>Author: =
"Rachel Carmichael" <carmichr_at_hotmail.com></FONT>
<BR><FONT SIZE=3D2>> > > > > > > =
>>Date: 5/24/00 6:16 =
PM</FONT>
<BR><FONT SIZE=3D2>> > > > > > > >></FONT>
<BR><FONT SIZE=3D2>> > > > > > > =
>>Lisa,</FONT>
<BR><FONT SIZE=3D2>> > > > > > > >></FONT>
<BR><FONT SIZE=3D2>> > > > > > > >>You have =
backups? Restore from backup. Otherwise..... recreate</FONT>
<BR><FONT SIZE=3D2>> and import</FONT>
<BR><FONT SIZE=3D2>> > > > > > > >>is</FONT>
<BR><FONT SIZE=3D2>> > > > > > > >>the way =
to go.</FONT>
<BR><FONT SIZE=3D2>> > > > > > > >></FONT>
<BR><FONT SIZE=3D2>> > > > > > > >>I gotta =
say, the fact that you did this, figured out how to</FONT>
<BR><FONT SIZE=3D2>> fix it, and</FONT>
<BR><FONT SIZE=3D2>> > > > > > > >>didn't =
freak out about it, would impress me MUCH more on an</FONT>
<BR><FONT SIZE=3D2>> interview than</FONT>
<BR><FONT SIZE=3D2>> > > > > > > >>the fact =
that you have your OCP :)</FONT>
<BR><FONT SIZE=3D2>> > > > > > > >></FONT>
<BR><FONT SIZE=3D2>> > > > > > > =
>>Rachel</FONT>
<BR><FONT SIZE=3D2>> > > > > > > >></FONT>
<BR><FONT SIZE=3D2>> > > > > > > >></FONT>
<BR><FONT SIZE=3D2>> > > > > > > >> =
>From: Lisa_Koivu_at_gelco.com</FONT>
<BR><FONT SIZE=3D2>> > > > > > > >> =
>Reply-To: ORACLE-L_at_fatcity.com</FONT>
<BR><FONT SIZE=3D2>> > > > > > > >> >To: =
Multiple recipients of list ORACLE-L</FONT>
<BR><FONT SIZE=3D2>> <ORACLE-L_at_fatcity.com></FONT>
<BR><FONT SIZE=3D2>> > > > > > > >> =
>Subject: Big Whoops</FONT>
<BR><FONT SIZE=3D2>> > > > > > > >> =
>Date: Wed, 24 May 2000 14:44:29 -0800</FONT>
<BR><FONT SIZE=3D2>> > > > > > > >> =
></FONT>
<BR><FONT SIZE=3D2>> > > > > > > >> >This =
is by far the funniest thing I have ever done. I began</FONT>
<BR><FONT SIZE=3D2>> running the</FONT>
<BR><FONT SIZE=3D2>> > > > > > > >> =
>create</FONT>
<BR><FONT SIZE=3D2>> > > > > > > >> =
>database scripts (ON A TEST MACHINE) when the $ORACLE_SID</FONT>
<BR><FONT SIZE=3D2>> was set to a</FONT>
<BR><FONT SIZE=3D2>> > > > > > > >> =
>currently</FONT>
<BR><FONT SIZE=3D2>> > > > > > > >> =
>running database. It's now completely and totally =
hosed.</FONT>
<BR><FONT SIZE=3D2>> I'm just</FONT>
<BR><FONT SIZE=3D2>> > > > > > > >> =
>wondering if</FONT>
<BR><FONT SIZE=3D2>> > > > > > > >> =
>there is any possible way of recovering from this. I =
am</FONT>
<BR><FONT SIZE=3D2>> just recreating</FONT>
<BR><FONT SIZE=3D2>> > > > > > > >> =
>the</FONT>
<BR><FONT SIZE=3D2>> > > > > > > >> =
>database and reimporting the data - it's not a big deal, but</FONT>
<BR><FONT SIZE=3D2>> for future</FONT>
<BR><FONT SIZE=3D2>> > > > > > > >> =
>reference, I wonder if this really does mean THE END OF THE</FONT>
<BR><FONT SIZE=3D2>> DATABASE AS</FONT>
<BR><FONT SIZE=3D2>> > > > > > > >>WE</FONT>
<BR><FONT SIZE=3D2>> > > > > > > >> =
>KNOW</FONT>
<BR><FONT SIZE=3D2>> > > > > > > >> =
>IT. My gut feel is YES.</FONT>
<BR><FONT SIZE=3D2>> > > > > > > >> =
></FONT>
<BR><FONT SIZE=3D2>> > > > > > > >> >I =
just can't stop laughing. I finish my OCP exams and</FONT>
<BR><FONT SIZE=3D2>> completely TRASH</FONT>
<BR><FONT SIZE=3D2>> > > > > > > >>a</FONT>
<BR><FONT SIZE=3D2>> > > > > > > >> =
>large</FONT>
<BR><FONT SIZE=3D2>> > > > > > > >> =
>database in the same day (and dump pink ice cream on my</FONT>
<BR><FONT SIZE=3D2>> white sweater</FONT>
<BR><FONT SIZE=3D2>> > > > > > > >> =
>simultaneously)! Like Kirti said, Who *WANTS* to be a =
DBA?</FONT>
<BR><FONT SIZE=3D2>> Are you out</FONT>
<BR><FONT SIZE=3D2>> > > > > > > >>of</FONT>
<BR><FONT SIZE=3D2>> > > > > > > >> =
>your</FONT>
<BR><FONT SIZE=3D2>> > > > > > > >> =
>mind? you better be!</FONT>
<BR><FONT SIZE=3D2>> > > > > > > >> =
></FONT>
<BR><FONT SIZE=3D2>> > > > > > > >> =
></FONT>
<BR><FONT SIZE=3D2>> > > > > > > >> =
></FONT>
<BR><FONT SIZE=3D2>> > > > > > > >> =
Received on Fri Jun 02 2000 - 19:33:39 CDT
 |
 |