Roxanne25
asked on
Problem with DBMS_AUDIT_MGMT Syntax
Hi, I'm trying to perform a cleanup of our audit trail db in our oracle system. I thought I had the code correct to do this but I'm having issues getting the syntax correct.
I tried to run this (to set the last archive date):
BEGIN
DBMS_AUDIT_MGMT.SET_LAST_A
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAI
last_archive_time => to_timestamp('2010/OCT/01 0:00:00', 'YYYY/MON/DD HH24:MI:SS'),
rac_instance_number => 1 /* single instance database */);
END;
But I got the following error message:
Error starting at line 1 in command:
BEGIN
DBMS_AUDIT_MGMT.SET_LAST_A
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAI
last_archive_time => to_timestamp('2010/OCT/01 0:00:00', 'YYYY/MON/DD HH24:MI:SS'),
rac_instance_number => 1 /* single instance database */);
END;
Error report:
ORA-06550: line 3, column 24:
PLS-00201: identifier 'DBMS_AUDIT_MGMT.AUDIT_TRA
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
I'm not sure why its telling me that the audit trail type value should be declared. After I set the last date, I then want to clear out the records up to that date. Which I was hoping would work with this code:
BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDI
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAI
use_last_arch_timestamp => TRUE);
END;
Can anybody give me some direction with this? I'm not an oracle person so getting this set up is quite foreign to me. We are using Oracle 11g.
I tried to run this (to set the last archive date):
BEGIN
DBMS_AUDIT_MGMT.SET_LAST_A
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAI
last_archive_time => to_timestamp('2010/OCT/01 0:00:00', 'YYYY/MON/DD HH24:MI:SS'),
rac_instance_number => 1 /* single instance database */);
END;
But I got the following error message:
Error starting at line 1 in command:
BEGIN
DBMS_AUDIT_MGMT.SET_LAST_A
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAI
last_archive_time => to_timestamp('2010/OCT/01 0:00:00', 'YYYY/MON/DD HH24:MI:SS'),
rac_instance_number => 1 /* single instance database */);
END;
Error report:
ORA-06550: line 3, column 24:
PLS-00201: identifier 'DBMS_AUDIT_MGMT.AUDIT_TRA
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
I'm not sure why its telling me that the audit trail type value should be declared. After I set the last date, I then want to clear out the records up to that date. Which I was hoping would work with this code:
BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDI
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAI
use_last_arch_timestamp => TRUE);
END;
Can anybody give me some direction with this? I'm not an oracle person so getting this set up is quite foreign to me. We are using Oracle 11g.
ASKER
Nope, now it says this:
PLS-00201: identifier 'DBMS_AUDIT_MGMT.SET_LAST_
I'm thinking that this may be a permission thing? Can only the sysdba execute the audit commands? Or can the permission be granted to a regular user? In our environment, I do not have sysdba login but my login has administrator rights.
PLS-00201: identifier 'DBMS_AUDIT_MGMT.SET_LAST_
I'm thinking that this may be a permission thing? Can only the sysdba execute the audit commands? Or can the permission be granted to a regular user? In our environment, I do not have sysdba login but my login has administrator rights.
Likely a permission issue.
I'm not an Expert on auditing but you should be able to be granted execute on the package.
I'm just not sure if that single package has everything you need.
I'm not an Expert on auditing but you should be able to be granted execute on the package.
I'm just not sure if that single package has everything you need.
ASKER
Grumble, I got execute permissions on the package but I still can't get it to work... gives the same error.
One last try: Were you explicitly granted to you or were they granted to you through a role?
I'm not an Expert on auditing. It might be that it takes a privileged user to execute this. Oracle Support can confirm what privs you will need to execute this successfully.
I'm not an Expert on auditing. It might be that it takes a privileged user to execute this. Oracle Support can confirm what privs you will need to execute this successfully.
ASKER
I'm not sure... I logged a help desk ticket to our production support people and they said they granted me the privledges but I'm not sure if it was explicitly or not. Having very little control over the environmnet when you're on the development team is very trying! :D
I don't have access to Oracle support, but I did send an email to the production folks to say YO, why can't I do anything with this package. :)
We'll see what they say.
I don't have access to Oracle support, but I did send an email to the production folks to say YO, why can't I do anything with this package. :)
We'll see what they say.
ASKER
He says he granted it explicitly but it still doesn't recognize it as a valid package. I am going to have them run it under sysdba and see if it works or not. I'm wondering if its only capable of running under sysdba.
I'm hoping/wishing someone else would chime in with help on this package... seems lately only one person replies to my tickets. :)
I'm hoping/wishing someone else would chime in with help on this package... seems lately only one person replies to my tickets. :)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ah ok, I thought the issues still showed up in unresolved status for everyone to read.
They remain in the list of questions awaiting answers but the older the question is, the farther down in the list they are and the less likely Experts are to see them.
Posting in them does not move them to the top of the list.
Posting in them does not move them to the top of the list.
ASKER
Gotchya... thanks for explaining that. :)
ASKER
Ok, I am going to close this ticket ... I moved on to another job, so I'll let them deal with this. Thanks for trying to help though.
ASKER
Accepting this answer. I *believe* it was a permission issue as was suggested but I have no way to verify.
Per the docs:
http://download.oracle.com/docs/cd/E11062_01/admin.1023/e11059/avadm_app_d_audit_mgmt.htm#BABCAJBB
AUDIT_TRAIL_ALL is 15
try:
...
audit_trail_type => 15,
...