SQL Auditing Help - Sol 10, 10.2.0.2

Hello,   

I have a centralized auditing table that I am trying to update based on
date values (field timestamp from dba_audit_trail, datatype is date)
from other databases.  I'm trying to grab entries 30 days old or newest
and put them into my 'master' table.  My insert statement is thus:

  insert into oracle.master_audit (select name ,os_username,
timestamp,username, userhost, terminal
  from V$DATABASE, dba_audit_trail where timestamp > timestamp - 30)

1459 rows created.

However when attempting to run the same sql across a DB link, I'm
getting the following:


SQL>   2  terminal from V$DATABASE@DSTESTDB, dba_audit_trail@DSTESTDB
where timestamp < timestamp -30)
 
*
ERROR at line 2:
ORA-02070: database  does not support  in this context
02070, 00000, "database %s%s does not support %s in this context"
// *Cause: The remote database does not support the named capability in
//         the context in which it is used.
// *Action: Simplify the SQL statement.


My 'master' table is just a subset of  columns in the dba_audit_trail
view and looks like this:

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)
(
  partition auditq1 values less than (to_date('01-APR-2009
00:00:00','dd-MON-yyyy HH24:MI:SS'))  tablespace auditq1,
  partition auditq2 values less than (to_date('01-JUN-2009
00:00:00','dd-MON-yyyy HH24:MI:SS'))tablespace auditq2,
  partition auditq3 values less than (to_date('01-AUG-2009
00:00:00','dd-MON-yyyy HH24:MI:SS')) tablespace auditq3,
  partition auditq4 values less than (to_date('01-DEC-2009
00:00:00','dd-MON-yyyy HH24:MI:SS')) tablespace auditq4,
  partition auditq5 values less than (to_date('01-FEB-2010
00:00:00','dd-MON-yyyy HH24:MI:SS')) tablespace auditq5,
  partition auditq6 values less than (to_date('01-APR-2010
00:00:00','dd-MON-yyyy HH24:MI:SS')) tablespace auditq6,
  partition auditq7 values less than (to_date('01-JUN-2010
00:00:00','dd-MON-yyyy HH24:MI:SS')) tablespace auditq7,
  partition auditq8 values less than (to_date('01-AUG-2010
00:00:00','dd-MON-yyyy HH24:MI:SS')) tablespace auditq8,
  partition auditcurrent values less than (maxvalue) tablespace
auditcurrent
);


The error message isn't much help to me; I think the statement is pretty
simple as is.

Thanks- Chris

Chris Newman
Database Specialist
AITS, University of Illinois
217-333-5429

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

Other related posts: