100 scripts you should know as a DBA – Part 2
In this multipart dba scripts post , we are going to learn the important scripts that a DBA should know in order to do the day to day database administration. This post in entirely dedicated to aud$.
7. what is AUD$ in Oracle Database 11g
The data dictionary of every Oracle database has a table named SYS.AUD$, commonly referred to as the database audit trail. Depending on configuration choices, this table can reside in different schema, such as the traditional SYS schema in the SYSTEM tablespace.
The database audit trail is a single table named SYS.AUD$ in the SYS schema of each Oracle Database data dictionary. Several predefined views are provided to help you use the information in this table, such as DBA_AUDIT_TRAIL.
If you want to filter and analyze your audit data from AUD$ table you need to know ACTION# column.
There are several ACTION# types available and we can view the action types by querying
SELECT action, name FROM audit_actions ORDER BY 1;
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 |
<span style="font-size: 14pt;">SYS> SYS> SELECT action, name FROM audit_actions ORDER BY 1; ACTION NAME ---------- ---------------------------- 0 UNKNOWN 1 CREATE TABLE 2 INSERT 3 SELECT 4 CREATE CLUSTER 5 ALTER CLUSTER 6 UPDATE 7 DELETE 8 DROP CLUSTER 9 CREATE INDEX 10 DROP INDEX 11 ALTER INDEX 12 DROP TABLE 13 CREATE SEQUENCE 14 ALTER SEQUENCE 15 ALTER TABLE 16 DROP SEQUENCE 17 GRANT OBJECT 18 REVOKE OBJECT 19 CREATE SYNONYM 20 DROP SYNONYM 21 CREATE VIEW 22 DROP VIEW 23 VALIDATE INDEX 24 CREATE PROCEDURE 25 ALTER PROCEDURE 26 LOCK 27 NO-OP 28 RENAME 29 COMMENT 30 AUDIT OBJECT 31 NOAUDIT OBJECT 32 CREATE DATABASE LINK 33 DROP DATABASE LINK 34 CREATE DATABASE 35 ALTER DATABASE 36 CREATE ROLLBACK SEG 37 ALTER ROLLBACK SEG 38 DROP ROLLBACK SEG 39 CREATE TABLESPACE 40 ALTER TABLESPACE 41 DROP TABLESPACE 42 ALTER SESSION 43 ALTER USER 44 COMMIT 45 ROLLBACK 46 SAVEPOINT 47 PL/SQL EXECUTE 48 SET TRANSACTION 49 ALTER SYSTEM 50 EXPLAIN 51 CREATE USER 52 CREATE ROLE 53 DROP USER 54 DROP ROLE 55 SET ROLE 56 CREATE SCHEMA 57 CREATE CONTROL FILE 59 CREATE TRIGGER 60 ALTER TRIGGER 61 DROP TRIGGER 62 ANALYZE TABLE 63 ANALYZE INDEX 64 ANALYZE CLUSTER 65 CREATE PROFILE 66 DROP PROFILE 67 ALTER PROFILE 68 DROP PROCEDURE 70 ALTER RESOURCE COST 71 CREATE MATERIALIZED VIEW LOG 72 ALTER MATERIALIZED VIEW LOG 73 DROP MATERIALIZED VIEW LOG 74 CREATE MATERIALIZED VIEW 75 ALTER MATERIALIZED VIEW 76 DROP MATERIALIZED VIEW 77 CREATE TYPE 78 DROP TYPE 79 ALTER ROLE 80 ALTER TYPE 81 CREATE TYPE BODY 82 ALTER TYPE BODY 83 DROP TYPE BODY 84 DROP LIBRARY 85 TRUNCATE TABLE 86 TRUNCATE CLUSTER 88 ALTER VIEW 91 CREATE FUNCTION 92 ALTER FUNCTION 93 DROP FUNCTION 94 CREATE PACKAGE 95 ALTER PACKAGE 96 DROP PACKAGE 97 CREATE PACKAGE BODY 98 ALTER PACKAGE BODY 99 DROP PACKAGE BODY 100 LOGON 101 LOGOFF 102 LOGOFF BY CLEANUP 103 SESSION REC 104 SYSTEM AUDIT 105 SYSTEM NOAUDIT 106 AUDIT DEFAULT 107 NOAUDIT DEFAULT 108 SYSTEM GRANT 109 SYSTEM REVOKE 110 CREATE PUBLIC SYNONYM 111 DROP PUBLIC SYNONYM 112 CREATE PUBLIC DATABASE LINK 113 DROP PUBLIC DATABASE LINK 114 GRANT ROLE 115 REVOKE ROLE 116 EXECUTE PROCEDURE 117 USER COMMENT 118 ENABLE TRIGGER 119 DISABLE TRIGGER 120 ENABLE ALL TRIGGERS 121 DISABLE ALL TRIGGERS 122 NETWORK ERROR 123 EXECUTE TYPE 128 FLASHBACK 129 CREATE SESSION 130 ALTER MINING MODEL 131 SELECT MINING MODEL 133 CREATE MINING MODEL 134 ALTER PUBLIC SYNONYM 135 DIRECTORY EXECUTE 136 SQL*LOADER DIRECT PATH LOAD 137 DATAPUMP DIRECT PATH UNLOAD 157 CREATE DIRECTORY 158 DROP DIRECTORY 159 CREATE LIBRARY 160 CREATE JAVA 161 ALTER JAVA 162 DROP JAVA 163 CREATE OPERATOR 164 CREATE INDEXTYPE 165 DROP INDEXTYPE 166 ALTER INDEXTYPE 167 DROP OPERATOR 168 ASSOCIATE STATISTICS 169 DISASSOCIATE STATISTICS 170 CALL METHOD 171 CREATE SUMMARY 172 ALTER SUMMARY 173 DROP SUMMARY 174 CREATE DIMENSION 175 ALTER DIMENSION 176 DROP DIMENSION 177 CREATE CONTEXT 178 DROP CONTEXT 179 ALTER OUTLINE 180 CREATE OUTLINE 181 DROP OUTLINE 182 UPDATE INDEXES 183 ALTER OPERATOR 192 ALTER SYNONYM 197 PURGE USER_RECYCLEBIN 198 PURGE DBA_RECYCLEBIN 199 PURGE TABLESPACE 200 PURGE TABLE 201 PURGE INDEX 202 UNDROP OBJECT 204 FLASHBACK DATABASE 205 FLASHBACK TABLE 206 CREATE RESTORE POINT 207 DROP RESTORE POINT 208 PROXY AUTHENTICATION ONLY 209 DECLARE REWRITE EQUIVALENCE 210 ALTER REWRITE EQUIVALENCE 211 DROP REWRITE EQUIVALENCE 212 CREATE EDITION 213 ALTER EDITION 214 DROP EDITION 215 DROP ASSEMBLY 216 CREATE ASSEMBLY 217 ALTER ASSEMBLY 218 CREATE FLASHBACK ARCHIVE 219 ALTER FLASHBACK ARCHIVE 220 DROP FLASHBACK ARCHIVE 225 ALTER DATABASE LINK 305 ALTER PUBLIC DATABASE LINK 181 rows selected.</span> |
Now, if we want to know the auditing information of logoff and logon, we can querying
select statement,timestamp#, userid, action#, returncode, logoff$time, comment$text from aud$ where action# in (100,101);
|
1 2 3 4 5 6 7 8 9 10 11 12 |
<span style="font-size: 14pt;">SQL> select statement,timestamp#, userid, action#, returncode, logoff$time, comment$text from aud$ where action# in (100,101); STATEMENT TIMESTAMP# USERID ACTION# RETURNCODE LOGOFF$TIME COMMENT$TEXT ---------- -------------------- ---------- ---------- ---------- -------------------- ---------------------------------------- 1 SYSTEM 100 1017 Authenticated by: DATABASE 1 SYSTEM 100 0 Authenticated by: DATABASE 1 SYSTEM 101 0 04-apr-2018 13:44:24 1 SYSTEM 100 0 Authenticated by: DATABASE 1 SYSTEM 101 0 04-apr-2018 13:44:57 1 TESTUSER 100 0 Authenticated by: DATABASE;Client address : (ADDRESS=(PROTOCOL=tcp)(HOST=192.148.1.118)(PORT=2061))</span> |
Few more useful queries you can try for
select statement ,timestamp#, userid, userhost,terminal, action#, returncode, logoff$time, comment$text from aud$ where action# in (100,101);
select a.timestamp#, a.userid, a.userhost,a.terminal, b.action,b.name, a.returncode, a.logoff$time, a.comment$text from aud$ a, audit_actions b
where a.action# = b.action ;
SELECT * FROM sys.aud$ a, audit_actions b WHERE a.action# = b.action AND TO_CHAR(TIMESTAMP#,’mm/yyyy’) = ’06/2018′ AND ROWNUM < 300 ORDER BY TIMESTAMP# DESC ;
select userid, terminal, spare1, substr(s1,1,instr(s1,’)’)-1) IP,
returncode, timestamp#
from (select A.*, substr(comment$text,instr(comment$text,’HOST=’)+5,100) s1
from aud$ a where action# in (100,101) )
order by IP;
Also read,
100 scripts you should know as a DBA – Part 1
Words from DBAPATH
Thank you for giving your valuable time to read the above information. I hope the content served your purpose in reaching out the blog.
Suggestions for improvement of the blog are highly appreciable. Please contact us for any information/suggestion/feedback.
If you want to be updated with all our articles
please follow us on Facebook | Twitter
Please subscribe to our newsletter.