Find out who's locking the accounts

set lines 200
set pages 200

column USERNAME format a12
column OS_USERNAME format a12
column USERHOST format a25
column EXTENDED_TIMESTAMP format a40

SELECT USERNAME, OS_USERNAME, USERHOST, EXTENDED_TIMESTAMP
FROM SYS.DBA_AUDIT_SESSION WHERE returncode != 0 and username = '&Account_Locked'
and EXTENDED_TIMESTAMP > (systimestamp-1) order by 4 desc
/

Query to find HISTOGRAMS

select owner,table_name,histogram from DBA_TAB_COL_STATISTICS where
owner='SCOTT' and table_name='EMPLOYEE'

Default STATS Collection in 11g

- The GATHER_STATS_JOB Oracle’s default stats collection job does not exist in
11g (the name does not exist) as it was there in 10g. Instead it has been
included in Automatic Maintenance Tasks

- How to check, Oracle’s default stats collection job is enable or disabled


SQL> select CLIENT_NAME,status from DBA_AUTOTASK_CLIENT;

CLIENT_NAME STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection DISABLED
auto space advisor ENABLED
sql tuning advisor ENABLED

- How to disable if it is enabled (run below query to disable it). Below PL/SQL block has to be executed by SYS

BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
END;