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