Hi all
I have this script that keeps bugging me with an Oracle error ORA-00942
that should not be there. All the tables in the script are owned by
'DBREPORTER' who is running the script. I have executed the same SQL
commands directly with SQLPlus and they work fine. Even more, the same SQL
commands had been running within a PLSQL procedure for 1 year and they
worked fine. So there's something I am missing.
To be precise, I have also attached the error message. If anybody knows
what I am doing wrong I would greatly appreciate some help.
Regards,
Marc Torres Alvarez
################################################3
#### error message
/export/home/oracle>DBD::Oracle::db do failed: ORA-00942: table or view
does not exist (DBD ERROR: error possibly near <*> indicator at char 542 in
'INSERT INTO system_events
SELECT a.database,
sysdate,
a.event,
a.total_waits,
a.total_timeouts,
a.time_waited/100,
a.average_wait,
(a.total_waits - b.total_waits),
(a.total_timeouts - b.total_timeouts),
(a.time_waited/100 - b.time_waited),
(sysdate - b.time)*24*60*60
FROM t_se a,
(SELECT event, total_waits, total_timeouts,
time_waited, time
FROM dbreporter.<*>system_events
WHERE database = 'BISP.emea.fedex.com'
AND time = (SELECT MAX(time)
FROM system_events
WHERE database = 'BISP.emea.fedex.com')) b
WHERE a.event = b.event(+)
AND a.database
= 'BISP.emea.fedex.com' ') [for Statement "INSERT INTO system_events
SELECT a.database,
sysdate,
a.event,
a.total_waits,
a.total_timeouts,
a.time_waited/100,
a.average_wait,
(a.total_waits - b.total_waits),
(a.total_timeouts - b.total_timeouts),
(a.time_waited/100 - b.time_waited),
(sysdate - b.time)*24*60*60
FROM t_se a,
(SELECT event, total_waits,
total_timeouts, time_waited, time
FROM system_events
WHERE database = 'BISP.emea.fedex.com'
AND time = (SELECT
MAX(time)
FROM system_events
WHERE database = 'BISP.emea.fedex.com')) b
WHERE a.event = b.event
AND a.database = 'BISP.emea.fedex.com' "]
at ./.dbreporter/system_events2.pl line 34.
Can't call method "execute" on an undefined value at
./.dbreporter/system_events2.pl line 55.
############################################
#### script
#! /usr/local/bin/perl -w
##########################################################################################
# Script: system_events2.pl
#
# Task: this script is called by system_events.pl and connects to one
database to #
# pull out system_events related statistics.
#
##########################################################################################
# the DBI drivers are loaded
use DBI;
# make sure the ORACLE settings are visible from within the script
$ENV{"ORACLE_HOME"} = "/opt/oracle/product/9.2";
# handle the input (ORACLE_SID)
$SID = $ARGV[0];
# open the connection to the monitored and monitoring databases
$dbh1 = DBI->connect("dbi:Oracle:$SID", "dbreporter", "reporter1");
$dbh2 = DBI->connect("dbi:Oracle:DBSTATS", "dbreporter", "reporter1");
# extract the data from v_$system_event
$sth1 = $dbh1->prepare("SELECT event, total_waits, total_timeouts,
time_waited, average_wait
FROM sys.v_\$system_event ");
$sth1->execute();
while ( ($event, $total_waits, $total_timeouts, $time_waited,
$average_wait) = $sth1->fetchrow_array)
{
# insert the data into T_SE
$sth2 = $dbh2->do("INSERT INTO t_se
VALUES('$SID', '$event', $total_waits,
$total_timeouts, $time_waited, $average_wait) ");
}
# insert the data into SYSTEM_EVENTS
$sth1 = $dbh1->do("INSERT INTO system_events
SELECT a.database,
sysdate,
a.event,
a.total_waits,
a.total_timeouts,
a.time_waited/100,
a.average_wait,
(a.total_waits - b.total_waits),
(a.total_timeouts - b.total_timeouts),
(a.time_waited/100 - b.time_waited),
(sysdate - b.time)*24*60*60
FROM t_se a,
(SELECT event, total_waits, total_timeouts,
time_waited, time
FROM system_events
WHERE database = '$SID'
AND time = (SELECT MAX(time)
FROM system_events
WHERE database = '$SID')) b
WHERE a.event = b.event(+)
AND a.database = '$SID' ");
$sth1->execute();
# delete the records older than the number of days specified in
DATABASE_SETTINGS
$sth2 = $dbh2->do("DELETE FROM system_events
WHERE database = '$SID' AND time < (SELECT sysdate -
sysevents_days_keep
FROM
database_settings
WHERE database
= '$SID') ");
exit;
