I just turned on auditing for an Oracle 11g database. I'm interested in getting more information about each event; for example, when I see a CREATE_INDEX
event, I'd like to know which field was indexed. Is there any way to do that?
1 Answer
Sure, use one of the below settings:
- db, extended
Performs all actions of AUDIT_TRAIL=db, and also populates the SQL bind and SQL text CLOB-type columns of the SYS.AUD$ table, when available. These two columns are populated only when this parameter is specified.
- xml, extended
Performs all actions of AUDIT_TRAIL=xml, and includes SQL text and SQL bind information in the audit trail.
alter system set AUDIT_TRAIL=db, extended scope=spfile;
shutdown immediate
startup
-
2Might want to add a warning that executing your code will shut the database down. Jan 22, 2018 at 21:50
-
1@MguerraTorres - one would hope that the command "shutdown immediate" would be a pretty strong clue that the db will get shut down. But unfortunately, there are people out there who will blindly copy and execute code from the internet without actually thinking about what it is doing. Jan 23, 2018 at 22:33
-
@EdStevens agreed. That was the main reason for my comment. It seems as "harmless" as a RECONFIGURE command in SQL Server but it (SHUTDOWN) soooo much worse. learn.microsoft.com/en-us/sql/t-sql/language-elements/… Jan 23, 2018 at 23:01