Home » RDBMS Server » Security » Problem on Job (Oracle 9.2.0.1.0 Windows Server 2000)
Problem on Job [message #360751] Sun, 23 November 2008 22:22 Go to next message
SilverChi
Messages: 17
Registered: September 2006
Junior Member
Hi guys..

I have a weird situation in my machine. I need to copy the audit records from dba_audit_trail to other table as backup; periodically (every 6 AM). To do that i have created a backup table (i named it DBA_AUDIT_TRAIL_BAK) which have exactly same structure of dba_audit_trail, i also create a procedure and job sequence. Here is my procedure

CREATE OR REPLACE PROCEDURE BACKUP_AUDIT AS
LTIME DATE;
BEGIN
SELECT SYSDATE INTO LTIME FROM SYS.DUAL;

INSERT INTO DBA_AUDIT_TRAIL_BAK
(SELECT OS_USERNAME, USERNAME, USERHOST, TERMINAL, TIMESTAMP, OWNER,
OBJ_NAME, ACTION, ACTION_NAME, NEW_OWNER, NEW_NAME, OBJ_PRIVILEGE,
SYS_PRIVILEGE, ADMIN_OPTION, GRANTEE, AUDIT_OPTION, SES_ACTIONS,
LOGOFF_TIME, LOGOFF_LREAD, LOGOFF_PREAD, LOGOFF_LWRITE, LOGOFF_DLOCK,
COMMENT_TEXT, SESSIONID, ENTRYID, STATEMENTID, RETURNCODE, PRIV_USED,
CLIENT_ID, SESSION_CPU
FROM SYS.DBA_AUDIT_TRAIL WHERE TIMESTAMP<LTIME);
COMMIT;
DELETE SYS.AUD$ WHERE TIMESTAMP#<LTIME;
COMMIT;
END;
/


I didn't see any problem when i execute the procedure manually, but it return error when executed by job. Here is the error

ORA-12012: error on auto execute of job 67
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 6


is there something i've missed?

Regards..
Re: Problem on Job [message #360800 is a reply to message #360751] Mon, 24 November 2008 01:10 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Which one is line 6?

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and use code tags.
Use the "Preview Message" button to verify.

Use SQL*Plus and copy and paste your session.

Regards
Michel

[Updated on: Mon, 24 November 2008 01:11]

Report message to a moderator

Re: Problem on Job [message #360837 is a reply to message #360800] Mon, 24 November 2008 02:08 Go to previous messageGo to next message
SilverChi
Messages: 17
Registered: September 2006
Junior Member
Oops.. my bad, i forgot the indentation format

Here is my procedure :
CREATE OR REPLACE PROCEDURE BACKUP_AUDIT AS
  LTIME DATE;
BEGIN
  SELECT SYSDATE INTO LTIME FROM SYS.DUAL;

  INSERT INTO DBA_AUDIT_TRAIL_BAK 
   (SELECT OS_USERNAME, USERNAME, USERHOST, TERMINAL, TIMESTAMP,
    OWNER, OBJ_NAME, ACTION, ACTION_NAME, NEW_OWNER, NEW_NAME, 
    OBJ_PRIVILEGE, SYS_PRIVILEGE, ADMIN_OPTION, GRANTEE, 
    AUDIT_OPTION, SES_ACTIONS, LOGOFF_TIME, LOGOFF_LREAD, 
    LOGOFF_PREAD, LOGOFF_LWRITE, LOGOFF_DLOCK, COMMENT_TEXT, 
    SESSIONID, ENTRYID, STATEMENTID, RETURNCODE, PRIV_USED, 
    CLIENT_ID, SESSION_CPU 
    FROM SYS.DBA_AUDIT_TRAIL WHERE TIMESTAMP<LTIME);
  COMMIT;
  DELETE SYS.AUD$ WHERE TIMESTAMP#<LTIME;
  COMMIT;
END;
/
Re: Problem on Job [message #360849 is a reply to message #360837] Mon, 24 November 2008 02:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
We still don't know which one is line 6.
More, I doubt the error comes from this procedure unless your dual is screwed up.
Also don't commit between the 2 statements, they are the 2 parts of the same transaction.

Regards
Michel
Re: Problem on Job [message #360861 is a reply to message #360849] Mon, 24 November 2008 02:54 Go to previous message
SilverChi
Messages: 17
Registered: September 2006
Junior Member
that's made me confused too.., there's no problem when i execute the procedure manually. it's only became problem when execute by the job
Previous Topic: use aud$ table
Next Topic: How to trace activities of all users on the database
Goto Forum:
  


Current Time: Thu Mar 28 07:34:52 CDT 2024