Oracle Scripts by Brian Lomasky and David C. Kreines NOTE: For updated scripts, please return to the book catalog page at http://www.oreilly.com/catalog/9781565924383/ and check the "Updated & Missing Scripts" link. Unconfirmed error reports are from readers. They have not yet been approved or disproved by the author or editor and represent solely the opinion of the reader. If you have any technical questions or error reports, you can send them to booktech@oreilly.com. (Please specify the printing date of your copy.) This page was last updated August 29, 2002 Here's a key to the markup: [page-number]: serious technical mistake {page-number}: minor technical mistake : important language/formatting problem (page-number): language change or minor formatting problem ?page-number?: reader question or request for clarification UNCONFIRMED errors and suggestions from readers: {5} The second line of the first code sample now reads: read sid Should read: read mysid ?6? Regarding the first and second paragraphs: I am an AIX user. I work for IBM Global Services. The purpose of this reply is to clarify whether or not the CD ROM you supply can be read by an AIX system, as a mounted CD ROM file system. [Chapter 6, pg 72] backup scripts; I make use of the backup scripts (with some modifications to handle things like Veritas Quick I/O files, handling already compressed archive log files, etc) for my hot backups. I've come across what appears to be a serious problem when running a hot backup to disk during a period of rapid archive log generation. In a nutshell, the problem is that between the time when archive logs are copied to the backup directory and the time when archive logs are deleted, one or more archive logs may be produced. The delarch and delarch2 scripts delete archive logs based upon the sequence number of the current log sequence (as calculated in delarch). Unfortunately, this number may very well be greater than the sequence number of archive logs previously copied to the archive log directory from backarch. As a result, delarch/delarch2 will delete archive logs that are not placed in the backup directory and one or more archive logs will be completely lost making complete recovery impossible. [96] Script delarch2; This is a followup to my previous submission. Narrowed this down. Script delarch2 has the following line that is supposed to extract the log sequence number from a filename: seqn=`basename $1 $3 | $4 '{ i = match($0, "[0-9]+") printf "%d\n", substr($0,i,RLENGTH) }' -` Where the variables are defined as follows: $1 = Archive log filespec - i.e. /arc02/oradata/pbl1/arch_1_1197.arc $2 = Maximum redo log sequence number that is not CURRENT - i.e. 1206 $3 = LOG_ARCHIVE_FORMAT specification (less any variables) - i.e. .arc $4 = awk or nawk Replacing the variables as below, the resulting value assigned to seqn is 1, NOT 1197. seqn=`basename /arc02/oradata/pbl1/arch_1_1197.arc .arc | nawk '{ i = match($0, "[0- 9]+") printf "%d\n", substr($0,i,RLENGTH) }' -` Because of this, the following test: if [ $seqn -lt $2 ] resolves to [ 1 -lt 1206 ] is ALWAYS true and all archive log files are deleted. The value of $3 (LOG_ARCHIVE_FORMAT) originates in script delarch where v$parameter is queried for value log_archive_format. In my case, this value is arch_%t_%s.arc. Obviously the presence of the thread number (via %t) results in the match command assigning the wrong value (1 vs. 1197) to seqn. Even if this were corrected, for a highly active system with lots (perhaps too many!) log switches, it might be preferable to capture the log sequence number at the time just preceeding archive log copying to the backup location rather than after the fact. {113} 113-117; crdb script while creating for 8.1.7 doesnt create tablespaces because it fails while assigning owner_name. owner_name when changed to varchar2(8) works like charm. {120} in script; First, the book and scripts have already made my work considerably easier. If I need to do something I am now reaching for your book right away...many routine tasks are elegantly handled through your scripts. I really appreciate all your work. It means less work for me :-) Now to the problem I've come across. The create triggers sql doesn't appear to handle INSTEAD OF triggers, and maybe from that it the FOR EACH ROW is not being caught. Here is an example of something that is being missed by the crtrigs.sql script: CREATE OR REPLACE TRIGGER "ZZZZZ".ZZZZZ_itd INSTEAD OF DELETE ON ZZZZZZ FOR EACH ROW BEGIN ZZZZZZZ_dml.delete_ZZZZZZZZZ (:OLD.ZZZZZZZZZZ_sk ,:OLD.ZZZZ_sk); END; >From the crtrigs.sql I get this: create trigger ZZZZZ.ZZZZZ_ITD after DELETE on ZZZZZZZ.ZZZZZZZZZZ BEGIN ZZZZZZZZ_dml.delete_ZZZZZZZ (:OLD.ZZZZZZZZZZ_sk ,:OLD.ZZZZZ_sk); END; [119] Script CRSEQS.SQL; [121] Script CRSEQS.SQL; This script does not work correctly if the sequence contains no cache value ( Value 0 is not allowed! ) Current code version: ===================== a_lin := wri(a_lin, ' cache ' || to_char(lv_cache_size), 0); New code version: ================= if (nvl(lv_cache_size,0) <> 0) then a_lin := wri(a_lin, ' cache ' || to_char(lv_cache_size), 0); else a_lin := wri(a_lin, ' nocache', 0); end if; [121] Script CRVIEWS.SQL; This script (i.e. the parser for view lines) does not work correctly if the views contain inline comments ("-- comment") at the end of each line. (Script version with last Change 10/09/98 by Brian Lomasky) [129] free.sql description; The free.sql script does not report tablespaces with NO (0) free space. If the tablespace_name does not exist in dba_free_space, due to the tablespace being full, it does not mention it. An outer join is needed. [159] days_old; I have 2 questions about the script itself. The first question is about the line: if (int($8/4)*$8 == $8 && ($6 == "Jan" || $6 == "Feb")) dold = dold +1 Is it possible for any value, other than 4, to be divided by 4 and then multiplied by itself and still equal itself? The second question is in 2 parts: (a) If there was a leapyear between the date of the file and today, but neither the year of the file or this year is that leapyear, will this script account for that leapyear? (b) If there are more than one leapyear between the date of the file and today, not counting the year of the file and this year, will this script account for all the leapyears? [162] allmemry.c and freemem.c; You warned me that some items were HP-UX specific, but perhaps, if you are able, you would be so kind as to offer some guidance, please. The situation is that Oracle is running here on SGI Irix 6.4 machines. Both allmemry.c and freemem.c contain an #include for which unfortunately is not found on an SGI Irix 6.4 machine. That didn't supprise me too much, but then it is not found on a Unisys Unix SVR5 1.4 machine, or a Red Hat Linux 6.2, either. I did however find it on a Caldera OpenLinux 2.4 machine in the path: /usr/scr/linux-2.2.14/include/asm-sparc64/pstat.h Any suggestions would be most welcome, and appreciated. [CD] backup script; Line 775 of the backup script reads: echo "/" > xport_$mysid.dat This doesn't work with Oracle 8.1.6. The correct syntax is: echo "USERID=/" I don't know about other versions of Oracle, but the Oracle Database Administration book's 4/1999 printing has the correct syntax for the export parm file. [CD] crroles.sql script; Line 6 of the crroles.sql script says 'type' when it should say 'type#'.