Scripts / commands to detect failed DB login attempts and querying of unusual large amount of data

Auditing is not required to trap failed login attempts.  It can be done with a trigger.  The basic idea is this:

create or replace trigger error_audit
after servererror
on database
begin

  if (is_servererror(1017)) then
    -- Insert information into a table here
  end if;

end;
/

Select all Open in new window

Most of the information you probably want to insert is in SYS_CONTEXT

It is true that you can use a custom trigger like the one johnsone suggested to detect failed login attempts.  But, the easiest way to report failed login attempts is to use Oracle's auditing features.  If the only activity that you audit is failed login attempts, you probably will not have to worry about the performance impact of this (assuming those are rare) or space usage for this data.  But to be safe, before you turn on auditing, check the tablespace that your SYS.AUD$ table is in and make sure that is not the SYSTEM tablespace.  In Oracle11.2 and later the default for this now is SYSAUX, but if your database was upgraded from an earlier version of Oracle, this table may be in the SYSTEM tablespace.  If it is there yet, you can move it.  Yes, Oracle supports DBAs moving this SYS-owned table. (Just don't move or modify other tables owned by SYS.)

No, Oracle does not provide or include a built-in way to rename the SYS.AUD$ table each day.  You could create a custom table with a structure similar to SYS.AUD$, that you partition by day (or week or month) and set up a custom PL\SQL job to run just after midnight and copy all of the contents (or at least the contents you want) from the SYS.AUD$ table to your custom table, then delete these rows from SYS.AUD$.

If you use a custom trigger to write your records to a custom table, you simply write your query to retrieve them based on whatever time period you want to retrieve.  Or, you can set up the table to be range-partitioned by day, then write your query to retrieve the records for the partition you want.

And yes, as Johnson said, logins for SYS (or anyone as SYSDBA) will not be tracked by a trigger.

The trigger to capture 1017 errors is an example, you can add whatever you want.  I'm not sure what other login errors there can be though.

As far as removing data from a custom audit trail table goes, you have full control.  A trigger wouldn't remove data.  Whatever reporting process you come up with can remove the data.  If you are really doing this for regulatory purposes, I would think you have to keep the data around for quite some time.

I'm not aware of a way to audit amount of data, but I was never asked to do it, so it may be out there.  We used to have a job that ran every 10 minutes or so that would look for queries that had been running for more than a specified amount of time.  But, that has nothing to do with data.  As I recall, you can set up a resource profile to stop people from running queries that do too many reads.

The documentation is your friend.  Look at CREATE PROFILE

The limit you are probably looking for is LOGICAL_READS_PER_CALL.