FAQ
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

Search Discussions

  • Powell, Mark at Jan 27, 2010 at 2:29 pm


    I think it is very bad practice to use an Oracle reserved word as a table column name. I have had trouble querying a couple of Oracle views/base tables because the table had column names that were or had become key words. Having to modify SQL statements to place double quotes around the names is both a pain and makes for less readable SQL.

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org On Behalf Of Rich Jesse
    Sent: Tuesday, January 26, 2010 5:21 PM
    To: oracle-l@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
  • Newman, Christopher at Jan 28, 2010 at 4:42 pm
    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
    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
    On Behalf Of Rich Jesse
    Sent: Tuesday, January 26, 2010 4:21 PM
    To: oracle-l@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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedJan 26, '10 at 10:07p
activeJan 28, '10 at 4:42p
posts3
users2
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase