?
Solved

Problem with DBMS_AUDIT_MGMT Syntax

Posted on 2011-10-18
13
Medium Priority
?
2,100 Views
Last Modified: 2012-08-13
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_ARCHIVE_TIMESTAMP(
   audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
   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_ARCHIVE_TIMESTAMP(
   audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
   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_TRAIL_ALL' must be declared
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_AUDIT_TRAIL(
   audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
   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.
0
Comment
Question by:Roxanne25
  • 8
  • 5
13 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36988622
you might not be able to see the value.

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,
...


 
0
 

Author Comment

by:Roxanne25
ID: 36988657
Nope, now it says this:
PLS-00201: identifier 'DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP' must be declared

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.  
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36988818
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.
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

Author Comment

by:Roxanne25
ID: 36995121
Grumble, I got execute permissions on the package but I still can't get it to work... gives the same error.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36995232
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.
0
 

Author Comment

by:Roxanne25
ID: 36995276
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.
0
 

Author Comment

by:Roxanne25
ID: 37000024
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. :)
0
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 37000120
>> I'm wondering if its only capable of running under sysdba.

It is very likely that you need to be a privileged user to run this.  I'm not sure and cannot test  this for you.  Have someone that can access Oracle Support ask for you.  Support can confirm this pretty easily.

>>I'm hoping/wishing someone else would chime in with help

Posts in a question can only be seen by the Experts participating in the question.

The correct way to obtain more assistance is click the 'Request Attention' link above and have a Moderator look into it.
0
 

Author Comment

by:Roxanne25
ID: 37000180
Ah ok, I thought the issues still showed up in unresolved status for everyone to read.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37000227
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.
0
 

Author Comment

by:Roxanne25
ID: 37000257
Gotchya... thanks for explaining that. :)
0
 

Author Comment

by:Roxanne25
ID: 37093705
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.
0
 

Author Closing Comment

by:Roxanne25
ID: 37093723
Accepting this answer.  I *believe* it was a permission issue as was suggested but I have no way to verify.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to recover a database from a user managed backup
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question