SQL Auditing Help - Sol 10, 10.2.0.2

From: Newman, Christopher <cjnewman_at_uillinois.edu>
Date: Tue, 26 Jan 2010 16:07:24 -0600
Message-ID: <565F609E6D736D439837F1A1A797F341020CD1F8_at_ADMINMAIL1.ui.uillinois.edu>

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_at_DSTESTDB, dba_audit_trail_at_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

Received on Tue Jan 26 2010 - 16:07:24 CST