following SQL output failed logins in previous 30 minutes.
SELECT TO_CHAR(current_timestamp AT TIME ZONE 'GMT', 'YYYY-MM-DD HH24:MI:SS TZD') AS curr_timestamp, COUNT(username) AS failed_count
FROM sys.dba_audit_session
WHERE returncode != 0
AND TO_CHAR(timestamp, 'YYYY-MM-DD HH24:MI:SS') >= TO_CHAR(current_timestamp - TO_DSINTERVAL('0 0:30:00'), 'YYYY-MM-DD HH24:MI:SS')
TO_DSINTERVAL
Syntax
to_dsinterval::=
Description of the illustration to_dsinterval.gif
Purpose
TO_DSINTERVAL converts a character string of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to an INTERVAL DAY TO SECOND value.
char is the character string to be converted.
The only valid nlsparam you can specify in this function is NLS_NUMERIC_CHARACTERS. This argument can have the form:
NLS_NUMERIC_CHARACTERS = "dg"
where d and g represent the decimal character and group separator respectively.
Examples
The following example selects from the employees table the employees who had worked for the company for at least 100 days on January 1, 1990:
SELECT employee_id, last_name FROM employees
WHERE hire_date + TO_DSINTERVAL('100 10:00:00')
<= DATE '1990-01-01';
EMPLOYEE_ID LAST_NAME
----------- ---------------
100 King
101 Kochhar
200 Whalen
