Create a view to check Locked objects.

------------DROP VIEW APPS.XXALL_LOCKED_OBJECTS;
/* Formatted on 6/25/2015 11:10:04 AM (QP5 v5.256.13226.35510) */
CREATE OR REPLACE FORCE VIEW APPS.XXALL_LOCKED_OBJECTS
(
   OWNER,
   OBJECT_NAME,
   OBJECT_TYPE,
   SID,
   SERIAL#,
   STATUS,
   OSUSER,
   MACHINE
)
   BEQUEATH DEFINER
AS
   SELECT c.owner,
          c.object_name,
          c.object_type,
          b.SID,
          b.serial#,
          b.status,
          b.osuser,
          b.machine
     FROM v$locked_object a, v$session b, dba_objects c
    --TO ALTER AND KILL THE LOCKED OBJECTS
    ----  ALTER SYSTEM KILL SESSION 'SID,serial#' IMMEDIATE;
    ----  ALTER SYSTEM KILL SESSION '408,19512' IMMEDIATE;
    WHERE b.SID = a.session_id AND a.object_id = c.object_id;


===============================================================
How to find the locked objects and Kill the Session in Oracle
===========================================
Step-1 Run the following SQL query to find out the list of objects that has been locked

SELECT aob.object_name
,aob.object_id
,b.process
,b.session_id
FROM all_objects aob, v$locked_object b
WHERE aob.object_id = b.object_id

OR

SELECT aob.object_name
,aob.object_id
,b.process
,b.session_id
FROM all_objects aob, v$locked_object b
,V$session a
WHERE aob.object_id = b.object_id
and a.sid=b.session_id ;


Step-2 Now run the following SQL query with session id (from step-1)

SELECT SID, SERIAL#  FROM v$session WHERE SID = <SESSION_ID>
Note <SID> <SERIAL#>


Step-3 Run the following Query to kill the session with session_id and Serial no (from step-2)

ALTER SYSTEM KILL SESSION '<SID> ,<SERIAL#>';

Ref:--
http://appsdbaclass.blogspot.com/2011/05/how-to-find-locked-objects-and-kill_5619.html


Another eg:
=========
CREATE VIEW scott.empview
(
   ename,
   enumber
)
   BEQUEATH DEFINER
AS
   SELECT ename,enumber FROM scott.emp;

 now give grant to another user
======================
GRANT SELECT ON scott.empVIEW TO tom;

as tom user(fyi)
========
CREATE OR REPLACE SYNONYM tom.empVIEW  FOR scott.empview;

Query to check all application sessions along with their sid & pid:
================================================================

select /*+ ORDERED */

p.spid db_pid

, s.process mt_pid

, s.username||':'||SUBSTR(s.sid||','||s.serial#,1,15 ) sid_serial

, fl.start_time apps_logon_time

, fu.user_name apps_user

, s.machine||'.'||s.osuser mt_detail

--, logon_time mt_start_time

, substr(module,1,10) module, action

from fnd_logins fl

, fnd_user fu

, v$process p

, v$session s

where  decode('&&1','web',fl.spid,1)=decode('&&1','web',s .process,1)

and fl.pid=p.pid

and fl.process_spid=p.spid

and fl.serial#=p.serial#

and s.process is not null

and s.paddr = p.addr

and fl.user_id=fu.user_id

and fl.end_time is null

query to check locked objects
=====================
 SELECT c.owner,
       c.object_name,
       c.object_type,
       b.SID,
       b.serial#,
       b.status,
       b.osuser,
       b.machine
  FROM v$locked_object a, v$session b, dba_objects c
 WHERE b.SID = a.session_id AND a.object_id = c.object_id;


 ALTER SYSTEM KILL SESSION '428,4729'

Sysadmin queries.

find users who have SYSADMIN responsibility

SELECT fu.USER_NAME,fu.DESCRIPTION,furgd.start_date,furgd.end_date,frvl.responsibility_name

FROM fnd_user_resp_groups_direct furgd, fnd_responsibility_vl frvl, fnd_user fu
WHERE furgd.responsibility_id = frvl.responsibility_id
AND fu.user_id = furgd.user_id
AND(to_char(furgd.end_date) is null
OR furgd.end_date > sysdate)
AND frvl.end_date is null
AND frvl.responsibility_name = 'System Administrator';

Another Script
==============
SELECT u.USER_NAME,u.START_DATE,u.END_DATE
       ,tr.RESPONSIBILITY_NAME
       ,r.START_DATE RESP_START_DATE,r.END_DATE RESP_END_DATE
    FROM fnd_user u,
         fnd_user_resp_groups_all ur,
         fnd_responsibility r,
         fnd_responsibility_tl tr,
         fnd_security_groups_vl s,
         fnd_application fa
  WHERE  1=1
  AND ur.responsibility_application_id = r.application_id
  AND ur.responsibility_id = r.responsibility_id
  AND tr.responsibility_id = r.responsibility_id
  AND u.user_id = ur.user_id
  AND ur.security_group_id = s.security_group_id
  AND r.application_id = fa.application_id
  AND trunc(sysdate) between  ur.start_date and nvl(ur.end_date,sysdate)
  AND trunc(sysdate) between  u.start_date and nvl(u.end_date,sysdate)
  AND tr.LANGUAGE='US'
  and RESPONSIBILITY_NAME='System Administrator'


With all details.
===========
SELECT fu.*
FROM fnd_user_resp_groups_direct furgd, fnd_responsibility_vl frvl, fnd_user fu
WHERE furgd.responsibility_id = frvl.responsibility_id
AND fu.user_id = furgd.user_id
AND(to_char(furgd.end_date) is null
OR furgd.end_date > sysdate)
AND frvl.end_date is null
AND frvl.responsibility_name = 'System Administrator';


-------------------------------------------------------------------------------
-- Query to find all responsibilities of a user
-------------------------------------------------------------------------------
SELECT fu.user_name                "User Name",
       frt.responsibility_name     "Responsibility Name",
       furg.start_date             "Start Date",
       furg.end_date               "End Date",     
       fr.responsibility_key       "Responsibility Key",
       fa.application_short_name   "Application Short Name"
  FROM fnd_user_resp_groups_direct        furg,
       applsys.fnd_user                   fu,
       applsys.fnd_responsibility_tl      frt,
       applsys.fnd_responsibility         fr,
       applsys.fnd_application_tl         fat,
       applsys.fnd_application            fa
 WHERE furg.user_id             =  fu.user_id
   AND furg.responsibility_id   =  frt.responsibility_id
   AND fr.responsibility_id     =  frt.responsibility_id
   AND fa.application_id        =  fat.application_id
   AND fr.application_id        =  fat.application_id
   AND frt.language             =  USERENV('LANG')
   AND UPPER(fu.user_name)      =  UPPER('SYSADMIN')  -- <change it>
   -- AND (furg.end_date IS NULL OR furg.end_date >= TRUNC(SYSDATE))
 ORDER BY frt.responsibility_name;


Another query to remove duplicates..
===========================
SELECT fu.user_id, fu.user_name, fur.responsibility_id,
fr.responsibility_name
FROM fnd_user fu, fnd_user_resp_groups fur, fnd_responsibility_vl fr
WHERE fu.user_id = fur.user_id
AND fr.application_id = fur.responsibility_application_id
AND fr.responsibility_id = fur.responsibility_id
AND TRUNC (SYSDATE) BETWEEN TRUNC (fr.start_date)
AND TRUNC (NVL ((fr.end_date - 1), SYSDATE))
AND TRUNC (SYSDATE) BETWEEN TRUNC (fur.start_date)
AND TRUNC (NVL ((fur.end_date - 1), SYSDATE))
and user_name like 'AHMED' --- for all user or for perticular user
-- AND fur.responsibility_application_id = 275 -- to check users for perticular responsibility
order by user_name


query to check list of users with last login date and end date
============================================
SQL>select USER_NAME,START_DATE,END_DATE,DESCRIPTION,LAST_LOGON_DATE
from fnd_user
where CREATED_BY<>1;

SYSAUX consuming more space

SYSAUX consuming more space
========================

Tips regarding 
SYSAUX Space Usage:
================

• Make sure the SYSAUX tablespace is set to AUTOEXTEND off     -- This allows storage to be re-used vs. appended to
• Confirm the STATISTICS_LEVEL value **                                    --  ALL is known to potentially be resource intensive while Basic and Typical are typically not
• Check for the usage of advisors, baselines or sql tuning sets:   -- These require trapping information which is retained in snapshots even if the snapshot range was scheduled to be dropped
• Run awrinfo.sql scripts on each instance                                    -- To better report and verify which objects are consuming the most space in the SYSAUX tablespace.
• Run queries against sysaux_occupants                                      -- A simple method to get general information on SYSAUX storage consumption

A nicely formated report can be generated by running the awrinfo.sql script

SQL> connect / as sysdba
SQL> @?/rdbms/admin/awrinfo.sql                   --- For Windows - The script ( = @ ) was executed from the Oracle_Home (= ? ) if set

In the report you may tend to see the following or similar tables are consuming the most storage space

1. WRH$_ACTIVE_SESSION_HISTORY     
2. WRI$_OPTSTAT_HISTGRM_HISTORY
3. WRI$_OPTSTAT_HISTHEAD_HISTORY

@?/rdbms/admin/awrinfo.sql

In my case the culprit was

Space usage by non-AWR components (> 500K)
**********************************

COMPONENT MB SEGMENT_NAME SEGMENT_TYPE
--------- --------- --------------------------------------------------------------------- ---------------
NON_AWR 743.0 SYS.I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST INDEX
NON_AWR 512.0 SYS.I_WRI$_OPTSTAT_HH_ST INDEX
NON_AWR 375.0 SYS.WRI$_OPTSTAT_OPR TABLE
NON_AWR 155.0 SYS.WRI$_OPTSTAT_OPR_TASKS TABLE
NON_AWR 144.0 SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY.SYS_P11840 TABLE PARTITION
| Occupant Name Schema Name Space Usage
| -------------------- -------------------- ----------------
| SM/OPTSTAT SYS 3,260.1 MB

Resolution
======
exec DBMS_STATS.PURGE_STATS(DBMS_STATS.PURGE_ALL);
this will truncate all stats history tables .

changed the value to 4 days as it will purge every 31 days

exec dbms_stats.alter_stats_history_retention(4);

You can query DBA_AUTOTASK_CLIENT_HISTORY
DBA_AUTOTASK_CLIENT_HISTORY displays a per-window history of job execution counts for each automated maintenance task. In the history, you can see if the job has run successfully in the past and when it stopped running succesfully.


SQL> SELECT client_name, window_name, jobs_created, jobs_started, jobs_completed, window_start_time,window_end_time FROM dba_autotask_client_history WHERE client_name like '%stats%'; 

How long old stats are kept

select dbms_stats.get_stats_history_retention from dual;
Set retention of old stats to 10 days

exec dbms_stats.alter_stats_history_retention(10);
Purge stats older than 10 days (best to do this in stages if there is a lot of data (sysdate-30,sydate-25 etc)

exec DBMS_STATS.PURGE_STATS(SYSDATE-10);
Show available stats that have not been purged

select dbms_stats.get_stats_history_availability from dual;
Show how big the tables are and rebuild after stats have been purged

col Mb form 9,999,999
col SEGMENT_NAME form a40
col SEGMENT_TYPE form a6
set lines 120
select sum(bytes/1024/1024) Mb, segment_name,segment_type from dba_segments
where  tablespace_name = 'SYSAUX'
and segment_name like 'WRI$_OPTSTAT%'
and segment_type='TABLE'
group by segment_name,segment_type order by 1 asc
        MB SEGMENT_NAME                             SEGMEN
---------- ---------------------------------------- ------
         0 WRI$_OPTSTAT_OPR                         TABLE
         0 WRI$_OPTSTAT_AUX_HISTORY                 TABLE
        88 WRI$_OPTSTAT_TAB_HISTORY                 TABLE
       126 WRI$_OPTSTAT_IND_HISTORY                 TABLE
       158 WRI$_OPTSTAT_HISTGRM_HISTORY             TABLE
     4,482 WRI$_OPTSTAT_HISTHEAD_HISTORY            TABLE
Show how big the indexes are ready for a rebuild after stats have been purged

col Mb form 9,999,999
col SEGMENT_NAME form a40
col SEGMENT_TYPE form a6
set lines 120
select sum(bytes/1024/1024) Mb, segment_name,segment_type from dba_segments
where  tablespace_name = 'SYSAUX'
and segment_name like '%OPT%'
and segment_type='INDEX'
group by segment_name,segment_type order by 1 asc
/
        MB SEGMENT_NAME                             SEGMEN
---------- ---------------------------------------- ------
         0 WRH$_OPTIMIZER_ENV_PK                    INDEX
         0 I_WRI$_OPTSTAT_OPR_STIME                 INDEX
         0 I_WRI$_OPTSTAT_AUX_ST                    INDEX
        88 I_WRI$_OPTSTAT_TAB_ST                    INDEX
       105 I_WRI$_OPTSTAT_IND_ST                    INDEX
       105 I_WRI$_OPTSTAT_H_ST                      INDEX
       195 I_WRI$_OPTSTAT_TAB_OBJ#_ST               INDEX
       213 I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST           INDEX
       214 I_WRI$_OPTSTAT_IND_OBJ#_ST               INDEX
     2,055 I_WRI$_OPTSTAT_HH_ST                     INDEX
     3,883 I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST            INDEX
Note that you cannot enable row movement and shrink the tables as the indexes are function based

alter table WRI$_OPTSTAT_IND_HISTORY enable row movement;
alter table WRI$_OPTSTAT_IND_HISTORY shrink space;
*
ERROR at line 1:
ORA-10631: SHRINK clause should not be specified for this object

select 'alter table '||segment_name||'  move tablespace SYSAUX;' from dba_segments where tablespace_name = 'SYSAUX'
and segment_name like '%OPT%' and segment_type='TABLE'
Run the rebuild table commands – note that this does cause any gather_stats jobs to fail

alter table WRI$_OPTSTAT_TAB_HISTORY  move tablespace sysaux;
alter table WRI$_OPTSTAT_IND_HISTORY  move tablespace sysaux;
alter table WRI$_OPTSTAT_HISTHEAD_HISTORY  move tablespace sysaux;
alter table WRI$_OPTSTAT_HISTGRM_HISTORY  move tablespace sysaux;
alter table WRI$_OPTSTAT_AUX_HISTORY  move tablespace sysaux;
alter table WRI$_OPTSTAT_OPR  move tablespace sysaux;
alter table WRH$_OPTIMIZER_ENV  move tablespace sysaux;
Script to generate rebuild statements

select 'alter index '||segment_name||'  rebuild online parallel (degree 14);' from dba_segments where tablespace_name = 'SYSAUX'
and segment_name like '%OPT%' and segment_type='INDEX'
Once completed it is best to check that the indexes (indices) are usable

select  di.index_name,di.index_type,di.status  from  dba_indexes di , dba_tables dt
where  di.tablespace_name = 'SYSAUX'
and dt.table_name = di.table_name
and di.table_name like '%OPT%'
order by 1 asc
/
SQL>
INDEX_NAME                     INDEX_TYPE                  STATUS
------------------------------ --------------------------- --------
I_WRI$_OPTSTAT_AUX_ST          FUNCTION-BASED NORMAL       VALID
I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST  FUNCTION-BASED NORMAL       VALID
I_WRI$_OPTSTAT_HH_ST           FUNCTION-BASED NORMAL       VALID
I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST FUNCTION-BASED NORMAL       VALID
I_WRI$_OPTSTAT_H_ST            FUNCTION-BASED NORMAL       VALID
I_WRI$_OPTSTAT_IND_OBJ#_ST     FUNCTION-BASED NORMAL       VALID
I_WRI$_OPTSTAT_IND_ST          FUNCTION-BASED NORMAL       VALID
I_WRI$_OPTSTAT_OPR_STIME       FUNCTION-BASED NORMAL       VALID
I_WRI$_OPTSTAT_TAB_OBJ#_ST     FUNCTION-BASED NORMAL       VALID
I_WRI$_OPTSTAT_TAB_ST          FUNCTION-BASED NORMAL       VALID
WRH$_OPTIMIZER_ENV_PK          NORMAL                      VALID
Finally lets see what space has been saved with a retention date of 1 day and a gather schema stats for the SYSASDM schema

exec dbms_stats.alter_stats_history_retention(1);
select dbms_stats.get_stats_history_retention from dual;
        MB SEGMENT_NAME                             SEGMEN
---------- ---------------------------------------- ------
         0 WRI$_OPTSTAT_OPR                         TABLE
         0 WRI$_OPTSTAT_AUX_HISTORY                 TABLE
         3 WRI$_OPTSTAT_TAB_HISTORY                 TABLE
         4 WRI$_OPTSTAT_IND_HISTORY                 TABLE
         8 WRI$_OPTSTAT_HISTGRM_HISTORY             TABLE
       104 WRI$_OPTSTAT_HISTHEAD_HISTORY            TABLE

        MB SEGMENT_NAME                             SEGMEN
---------- ---------------------------------------- ------
         0 WRH$_OPTIMIZER_ENV_PK                    INDEX
         0 I_WRI$_OPTSTAT_OPR_STIME                 INDEX
         0 I_WRI$_OPTSTAT_AUX_ST                    INDEX
         2 I_WRI$_OPTSTAT_IND_ST                    INDEX
         2 I_WRI$_OPTSTAT_TAB_ST                    INDEX
         3 I_WRI$_OPTSTAT_TAB_OBJ#_ST               INDEX
         4 I_WRI$_OPTSTAT_IND_OBJ#_ST               INDEX
         5 I_WRI$_OPTSTAT_H_ST                      INDEX
         9 I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST           INDEX
        41 I_WRI$_OPTSTAT_HH_ST                     INDEX
        96 I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST            INDEX

Ref
====
For more information : FAQ: Automatic Statistics Collection ( Doc ID 1233203.1 )
Suggestions if Your SYSAUX Tablespace Grows Rapidly or Too Large (Doc ID 1292724.1)
https://jhdba.wordpress.com/tag/dbms_stats-purge_stats/