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 |
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 |
|
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
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 |
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 #360861 is a reply to message #360849] |
Mon, 24 November 2008 02:54 |
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
|
|
|
Goto Forum:
Current Time: Thu Feb 06 20:55:37 CST 2025
|