Site Navigation
Feedback: May 2003, Published: June 2003
If you have a question regarding any of the articles in this journal, or some comments please send them in. If there are any general questions about Unix or Database Administration, I will attempt to answer them.
Hints for this month:
- Postpone DBMS jobs in Oracle
- Do you need to predeclare a subroutine?
- Binary files appear as a string of strange characters in browser.
- Oracle is starting up with the wrong parameter file (PFILE) in Windows.
Spam Diaries:
Postpone DBMS jobs in Oracle
Date: Tue, 13 May 2003 00:59:29 +1000
From: Gerry Patterson
When changing a DBMS job, the CHANGE procedure can be used. However if
the only change is to be the time of the next run, this can be changed
with the NEXT_DATE Procedure which has the following syntax:
DBMS_JOB.NEXT_DATE (
job IN BINARY_INTEGER,
next_date IN DATE);
Parameters:
job = Number of the job being run.
next_date = Date when the job will be automatically run, assuming
there are background processes attempting to run it.
This becomes tedious when there are large number to change. The
following script can be used to change a large number of them. It will
change the next_date of jobs due to run in the next 24 hours (or
whatever -- you choose.)
To use it you specify the user, password, SID and number of hours.
e.g. in a batchfile put the following code:
sqlplus scott/tiger@ORCL @postpone 8
Where:
scott = The user who owns the jobs
tiger = Scott's password
ORCL = The oracle SID of the database
8 = The number of hours to add to the next_date of the jobs
When used to call the SQL script given below, this would add eight hours
to the next date of all unbroken jobs that are owned by Scott and due to
run within the next 24 hours. The code for postpone_jobs.sql is as
follows:
/* ------------------------------------------------------------------------ */
/* postpone_jobs.sql */
/* ----------------- */
/* */
/* Postpone jobs scheduled within the next 24 hrs by given number of hrs */
/* */
/* Call with: Arg 1 = Number of hours to postpone */
/* */
/* G. Patterson, May 2003 */
/* */
/* ------------------------------------------------------------------------ */
DECLARE
cursor c1 is
SELECT job,
next_date
FROM user_jobs
WHERE broken != 'Y'
AND next_date between sysdate and sysdate + 1
ORDER BY job;
n_job number;
n_offset number;
d_ndate date;
BEGIN
-- divide by 24 = date offset in hours
n_offset := &&1/24;
OPEN c1;
IF c1%ISOPEN then
null;
ELSE
goto exit_2;
END IF;
LOOP
FETCH c1 into n_job, d_ndate;
IF c1%NOTFOUND then
goto exit_1;
END IF;
dbms_job.next_date(n_job,d_ndate+n_offset);
END LOOP;
<<exit_1>>
close c1;
<<exit_2>>
null;
END;
/
-- Show the next start times of all jobs owned by this user
alter session set nls_date_format = 'dd/mm/yyyy hh24:mi';
col what format a30 trunc
select next_date,job,what from user_jobs order by next_date,job;
-- exit when finished, so it can be called from a shell script
exit
/* ------------------------------------------------------------------------ */
/* End postpone_jobs.sql */
/* ------------------------------------------------------------------------ */
Do you need to predeclare a subroutine?
Date: Thu, 15 May 2003 04:50:19 +1000
From: Gerry Patterson
When a subroutine (e.g. "MySub") is declared in a script which is
included in another script (using the "require" command), perl will not
treat references to it as a subroutine, unless you enclose the parameter
string in brackets (which forces perl to consider it as a subroutine).
This can be disconcerting if your original script contains lots of
references without brackets. Omitting brackets is quite a common
practice for perl programmers.
The error message may look something like this:
String found where operator expected at /path/progname.pl line 343, near "MySub "$p1/blah""
(Do you need to predeclare MySub?)
You can fix this by enclosing the parameter in brackets i.e.
MySub("$p1/blah");
Still if you have lots of them, it could be a lot of changes.
Alternatively you can predeclare the subroutine. Just precede the call
with this:
sub MySub;
(It's probably better to put these declarations at the very top of your
script -- for readability -- but that is a matter of personal
preference).
Re: Perl install
From: "Dan Byrnes" Date: Sat, 17 May 2003 15:41:33 +1000 Very badly, Gerry, very confused, I've downloaded the Windows installer thing V2.0 - then tried your own page for the Perl, and what came into Netscape was one long stream of code, and like, where does it go when it's finished, it wasn't an install thing, just a long read-out. No idea, here, cheers, Dan. Ed: Yes, it does appear as an incomprehensible string of bytes in most browsers. This is because my server is sending it as plain text. And browsers other than MSIE can't grok .msi files. Thanks for telling me about this. I shall fix it. (Webmasters, see the hint below)
Binary files appear as a string of strange characters in browser.
Date: Sat, 17 May 2003 16:50:35 +1000 From: Gerry Patterson This is caused by the server outputting the file as text. If you are a web site owner/operator and you receive a complaint about binary files appearing as a string of strange characters in various browsers, you may need to consider changing the default type of the files. If a certain folder contains mostly binary data, you can alter the default type for that particular folder by adding the following statement to the .htacess file in that folder: DefaultType application/octet-stream
Oracle is starting up with the wrong parameter file (PFILE) in Windows.
Date: Tue, 31 May 2003 11:30:35 +1000 From: Gerry Patterson This can occur when you have changed the location of the initial parameters file. You can just give and make sure that you continue using the original location. Or else, you can change the entry in the Windows registry. For example, if you had two oracle SIDs. You might discover the following keys in the registry key Software\ORACLE\HOME0: Name Type Data ---- ------ ---- ID REG_SZ 0 MS_HELP_TOOLS REG_SZ D:\oracle\ora81\MSHELP NLS_LANG REG_SZ AMERICAN_AMERICA.WE8ISO8859P1 ORA_SID1_AUTOSTART REG_EXPAND_SZ TRUE ORA_SID1_PFILE REG_EXPAND_SZ D:\oracle\ora81\DATABASE\initSID1.ora ORA_SID1_SHUTDOWN REG_EXPAND_SZ TRUE ORA_SID1_SHUTDOWN_TIMEOUT REG_EXPAND_SZ 30 ORA_SID1_SHUTDOWNTYPE REG_EXPAND_SZ i ORA_SID2_AUTOSTART REG_EXPAND_SZ TRUE ORA_SID2_PFILE REG_EXPAND_SZ E:\oldpath\pfile\initsid2.ora ORA_SID2_SHUTDOWN REG_EXPAND_SZ TRUE ORA_SID2_SHUTDOWN_TIMEOUT REG_EXPAND_SZ 30 ORA_SID2_SHUTDOWNTYPE REG_EXPAND_SZ i ORACLE_BASE REG_SZ D:\oracle ORACLE_GROUP_NAME REG_SZ Oracle - OraHome81 ORACLE_HOME REG_SZ D:\oracle\ora81 ORACLE_HOME_KEY REG_SZ Software\ORACLE\HOME0 ORACLE_HOME_NAME REG_SZ OraHome81 ORACLE_SID REG_SZ SID1 OWAST REG_SZ D:\oracle\ora81\assistants\owast OWASTDBS REG_SZ OFF OWASTHOME REG_SZ OracleWebAssistant0 RDBMS_ARCHIVE REG_SZ D:\oracle\ora81\DATABASE\ARCHIVE RDBMS_CONTROL REG_SZ D:\oracle\ora81\DATABASE SQLPATH REG_SZ D:\oracle\ora81\SQL TNSADMIN REG_SZ D:\oracle\ora81\network\ADMIN You can just change these entries with the regedit tool. Alternatively if you have the REG command line tool (from one of the Windows resource kits), you can do it on the command line. For example to change the pfile key for SID2, you might use the REG tool as follows: D:\>reg query Software\ORACLE\HOME0\ORA_SID2_PFILE EXPAND_SZ ORA_SID2_PFILE E:\oldpath\pfile\initsid2.ora (This tells you what the current value is) D:\>reg update Software\ORACLE\HOME0\ORA_SID2_PFILE=D:\oracle\ora81\DATABASE\initSID2.ora The operation completed successfully. (This has changed the value)
SPAM: Sniff! ... Smells like SPAM!
From pr@hosting.commandprompt.com Tue May 6 05:47:15 2003 Return-Path: <pr@hosting.commandprompt.com> Received: from hosting.commandprompt.com ([207.173.200.199]) by pgts04.xxxx.com.au (8.11.6/8.11.6) with ESMTP id h45JlDP63511 for <gerry@xxxx.com.au>; Tue, 6 May 2003 05:47:14 +1000 (EST) (envelope-from pr@hosting.commandprompt.com) Received: (from pr@localhost) by hosting.commandprompt.com (8.11.6/8.11.6) id h45JEg809854 for gerry@xxxx.com.au; Mon, 5 May 2003 12:14:42 -0700 Date: Mon, 5 May 2003 12:14:42 -0700 From: "Command Prompt PR." <pr@hosting.commandprompt.com> Message-Id: <200305051914.h45JEg809854@hosting.commandprompt.com> To: gerry@xxxx.com.au Subject: Consultants Guide Terms and Conditions Status: RO Content-Length: 1362 Lines: 44 Hello, We recently sent out an email about some of our products. There were a couple of people that felt that this was SPAM. In an effort to quell some of the dissidents we have drafted the following document: --- Linux Consultants Guide/Database Terms and Conditions 1. Command Prompt, Inc. will provide at zero cash cost a listing in the Linux Consultants Guide/Database to any Linux consultant. 2. Command Prompt, Inc. will continue world wide distribution of the document and related links (to the database) via the LDP and theLinuxReview.Com website. 3. Command Prompt, Inc. reserves the right to communicate with the listed consultants via email up to 12 (TWELVE) TIMES PER YEAR. The communication will always be about Open Source, or Command Prompt's products and services. 4. Command Prompt, Inc. will not sell your name or generate compiled lists for external companies. If any of these conditions are unacceptable to companies listed in the database, send an email to support@commandprompt.com with your full company information and you will be removed from the database. --- We feel that this is a fair arrangement. We understand how people feel about spam. We don't like it either. However, we don't feel that the occassional email from Command Prompt is too much to ask for the service. Sincerely, Joshua D. Drake
SPAM: Looks like SPAM ... TASTES like SPAM!
Good thing we don't step in it! ... Actually Joshua, if that really is your name, I don't really have to taste it in order to recognise it ... the faked address in the header is enough.
From pr@hosting.commandprompt.com Thu May 15 05:30:32 2003
Return-Path: <pr@hosting.commandprompt.com>
Received: from hosting.commandprompt.com ([207.173.200.199])
by pgts04.xxxx.com.au (8.11.6/8.11.6) with ESMTP id h4EJUUP81156
for <gerry@xxxx.com.au>; Thu, 15 May 2003 05:30:31 +1000 (EST)
(envelope-from pr@hosting.commandprompt.com)
Received: (from pr@localhost)
by hosting.commandprompt.com (8.11.6/8.11.6) id h4EIxFg07840
for gerry@xxxx.com.au; Wed, 14 May 2003 11:59:15 -0700
Date: Wed, 14 May 2003 11:59:15 -0700
From: "Command Prompt PR." <pr@hosting.commandprompt.com>
Message-Id: <200305141859.h4EIxFg07840@hosting.commandprompt.com>
To: gerry@xxxx.com.au
Subject: Consultants Guide Update May
Status: RO
Content-Length: 2235
Lines: 67
Reply to: surveyhelp-2320mt@surveysite.com
Hello All,
As usual, we are sending out a brief update on the Consultants Guide.
In case you haven't noticed we released a new version about a month ago.
You ou may want to take a look.
Also, the survey people are back, and have asked me to forward this
the message below along. We would really appreciate it, if you folks
would fill out the survey. We get 5.00 for everyone of you that completes
the survey, which helps us support the Consultants Guide quite a bit.
Thanks for all your support in the past and we hope you are having
a great season.
Sincerely,
Joshua Drake
Dear Linux Developer:
On behalf of The Linux Review and SurveySite, a third-party independent
marketing research firm, we would like to invite you to take a few
moments to participate in an online Web survey regarding Linux
communities. The survey should take less than 5 minutes to complete.
If you complete the survey, we would be pleased to send you a copy of
the results when the study is done. Simply enter your e-mail address at
the end of the survey to receive a copy of the results.
Please note that your answers to this survey will remain private and
confidential and you will not be contacted further.
If you would like to participate in this important survey, please click
on the following link:
http://www2.survey-poll.com/email/2320mth.html
If your Web browser does not support automatic linking, please type the
URL above into the address bar of your browser.
Thank you for your input. Your opinion counts!
Sincerely,
The Linux Review
Please direct questions about completing this survey to:
surveyhelp-2320mt@surveysite.com
Ed: The IP address of hosting.commandprompt.com is actually
207.173.200.143.
207.173.200.199 is in a netblock owned by Spire Technologies. According
to whois the information for Spire Technologies is:
Spire Technologies, Inc.
2140 SW Jefferson Street, Suite #300
Portland, OR 97201 US
Phone: 503-222-3086
Fax: 503-224-7460