RE: SQL Auditing Help - Sol 10, 10.2.0.2

From: Newman, Christopher <cjnewman_at_uillinois.edu>
Date: Thu, 28 Jan 2010 10:42:11 -0600
Message-ID: <565F609E6D736D439837F1A1A797F341020CD20A_at_ADMINMAIL1.ui.uillinois.edu>

I think it must be an issue with the shell script, not the actual SQL. Running manually from 'central' db:

Running manually from 10.2.0.4 home:

SQL> insert into oracle.master_audit_at_CJNTEST (select name,os_username, timestamp,username, userhost,
  2 terminal from V$DATABASE_at_DB1, dba_audit_trail_at_DB1 where timestamp > timestamp -30);

229841 rows created.

When running from the shell script though, I'm getting the following:

SQL> 2 terminal from V$DATABASE_at_DB1, dba_audit_trail_at_DB1 where timestamp < timestamp -30)  

*
ERROR at line 2:
ORA-02070: database does not support in this context

I'm echoing the sql statement in the shell script to verify it's correct and being passed in its entirety. Here's what the script looks like (test.log being a list of service names). Connectivity isn't the issue, rather the query problem.

#!/bin/ksh

export actionlog="chris.txt"
export ORACLE_HOME=/u01/app/oracle/product/10.2.0.4

set `cat test.log`
while [ $# -gt 0 ]; do

echo "Database   :: $1 " >> $actionlog
echo "Start time :: `date '+%D %T'`" >> $actionlog
echo "--------------------------------------------------" >> $actionlog

$ORACLE_HOME/bin/sqlplus oracle/passhere <<! >>$actionlog insert into oracle.master_audit_at_CJNTEST (select name,os_username, timestamp,username, userhost,
terminal from V\$DATABASE_at_$1, dba_audit_trail@$1 where timestamp < timestamp -30);
exit
!
echo "insert into oracle.master_audit_at_CJNTEST (select name,os_username, timestamp,username, userhost, terminal from V\$DATABASE_at_$1, dba_audit_trail_at_$1 where timestamp < timestamp -30);" shift
done

Any idea why running from the shell would produce the error, while running manually from sqlplus on the central server works?

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Newman, Christopher Sent: Wednesday, January 27, 2010 1:44 PM To: rjoralist_at_society.servebeer.com; oracle-l_at_freelists.org Subject: RE: SQL Auditing Help - Sol 10, 10.2.0.2

Good thought, but I've also tried with the ntimestamp# column from sys.aud$, same result as below. Any thoughts?

Thanks- Chris

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Rich Jesse Sent: Tuesday, January 26, 2010 4:21 PM
To: oracle-l_at_freelists.org
Subject: Re: SQL Auditing Help - Sol 10, 10.2.0.2

Might it be something silly like using a keyword ("TIMESTAMP") as a column
name?

Just a knee jerk to what I see. I know, I know, Oracle Corp does it. For
this particular, I use "TIME_STAMP". Not sure that helps, but it's a shot...

Rich

> create table oracle.master_audit
> (DBNAME varchar2(10),
> OS_USERNAME varchar2(255),
> TIMESTAMP date,
> USERNAME VARCHAR2(30),
> USERHOST VARCHAR2(128),
> TERMINAL VARCHAR2(255))
> Partition by range (TIMESTAMP)
> (

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jan 28 2010 - 10:42:11 CST