Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In today’s article, we will learn how to see users who access Oracle Database or users who get an error while accessing.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | SELECT username AS "DB USER", os_username AS "OS USER", userhost AS "HOSTNAME", TO_CHAR (timestamp, 'DD/MM/YY HH24:MI:SS')"TIMESTAMP", DECODE ( returncode, 00911, 'INCORRECT CHARACTER INPUT', 01004, 'ACCESS DISABLED', 01005, 'NULL PASSWORD ENTERED', 01017, 'WRONG USER OR PASSWORD', 01031, 'UNAUTHORIZED TRIAL', 01045, 'TRIAL WITHOUT CREATE SESSION AUTHORITY', 01918, 'NON-DB USER', 01920, 'NON-DB ROLE', 28000, 'LOCKED ACCOUNT TRIAL', 28001, 'PASSWORD EXPIRED', 28009, 'SYSDBA OR SYSOPER TRIAL') AS return code FROM SYS.DBA_AUDIT_SESSION WHERE returncode != 0 AND -- timestamp > SYSDATE - 1 of 9 -- AND returncode IN (1017, 28000) ORDER BY extended_timestamp DESC |
