RE: SQL Auditing Help - Sol 10, 10.2.0.2

  • From: "Newman, Christopher" <cjnewman@xxxxxxxxxxxxx>
  • To: "Newman, Christopher" <cjnewman@xxxxxxxxxxxxx>, <rjoralist@xxxxxxxxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 28 Jan 2010 10:42:11 -0600

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@CJNTEST (select name,os_username,
timestamp,username, userhost,
  2  terminal from V$DATABASE@DB1, dba_audit_trail@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@DB1, dba_audit_trail@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@CJNTEST (select name,os_username,
timestamp,username, userhost,
terminal from V\$DATABASE@$1, dba_audit_trail@$1 where timestamp <
timestamp -30);
exit
!
echo "insert into oracle.master_audit@CJNTEST (select name,os_username,
timestamp,username, userhost, terminal from V\$DATABASE@$1,
dba_audit_trail@$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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Newman, Christopher
Sent: Wednesday, January 27, 2010 1:44 PM
To: rjoralist@xxxxxxxxxxxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Rich Jesse
Sent: Tuesday, January 26, 2010 4:21 PM
To: oracle-l@xxxxxxxxxxxxx
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)
> (


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


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


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

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 01-2010