Scripts for Apps DBA for daily operations.


--------------------------------------------------------
------OPERATION SCRIPTS TO HELP IN DAILY JOBS-----------
--------------------------------------------------------
1. Unlock application user
2. Currently Logged in users
3. Datafile shrink Report
4. Check DR Gap
5. Check DR current activity
6. Find SQL executed by a concurrent request
7. Unlock FNd user
8. Free Tablespace report
9. Get DDL of an object
10. List invalids with statement to compile them
11. Responsibilities assigned to a user.


--------------------------------------------------------
1. Unlock application user

BEGIN
FND_USER_PKG.EnableUser('A.STIGTER');
END;
/
--------------------------------------------------------
2. Currently Logged in users
SELECT
  fu.user_name User_Name,
  fu.email_address emailid,
  fu.description,
  TO_CHAR(IC.FIRST_CONNECT,'dd-Mon-rrrr HH24:mi:ss')"First Accessed",
  TO_CHAR(IC.LAST_CONNECT,'dd-Mon-rrrr HH24:mi:ss')"Last Accessed",
  ic.nls_territory,
  FVL.RESPONSIBILITY_NAME "RESPONSIBILITY NAME",
  fr.RESPONSIBILITY_KEY "Responsibility Key",
  ic.function_type,
  ic.time_out,
  fu.user_id,
  fu.employee_id,
  ic.responsibility_application_id,
  ic.responsibility_id,
  ic.org_id,
  ic.counter,
  fr.menu_id,
  ic.disabled_flag
FROM
  apps.fnd_user fu,
  apps.fnd_responsibility fr,
  apps.icx_sessions ic,
  apps.fnd_responsibility_VL FVL
WHERE
fu.user_id          = ic.user_id
AND FR.RESPONSIBILITY_KEY =FVL.RESPONSIBILITY_KEY
and fVl.responsibility_key != 'IRC_EXT_CANDIDATE'
AND fr.responsibility_id  = ic.responsibility_id
AND ic.disabled_flag      ='N'
AND ic.responsibility_id IS NOT NULL
AND (ic.last_connect + DECODE(apps.FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT'), NULL,limit_time, 0,limit_time,apps.FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT')/60)/24) > sysdate
and counter < ic.limit_connects
ORDER BY "Last Accessed" DESC;
--------------------------------------------------------
3. Datafile shrink Report

select /*+rule*/ 'alter database datafile '''||file_name||''' resize '||ceil( (nvl(hwm,1)*8192)/1024/1024)||'m autoextend on maxsize '||ceil( blocks*8192/1024/1024)||'m;' stat,
ceil( (nvl(hwm,1)*8192)/1024/1024 ) smallest,
ceil( blocks*8192/1024/1024) currsize,
ceil( blocks*8192/1024/1024) -
ceil( (nvl(hwm,1)*8192)/1024/1024 ) savings
from
   dba_data_files a,
   (select file_id, max(block_id+blocks-1) hwm
   from
      dba_extents
   group by file_id ) b
where a.file_id = b.file_id(+)
order by 4;
***NOTE: Autoexend on has to be enabled with relevant maxsize

--------------------------------------------------------
4. Check DR Gap

select a.*, b.*, (a."Archived" - b."Applied") "GAP"
from
(select thread#, max(sequence#) "Archived" from v$archived_log where archived='YES' group by thread# ) a,
(select thread#, max(sequence#) "Applied" from v$archived_log where applied='YES' group by thread# ) b where a.thread# = b.thread#
--------------------------------------------------------
5. Check DR current activity

SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY
--------------------------------------------------------
6. Find SQL executed by a concurrent request

SELECT
  B.sid,
  C.sql_text ,
  C.module
FROM APPS.fnd_concurrent_requests A ,
  gV$SESSION B ,
  gV$SQLAREA C
WHERE A.oracle_session_id = B.audsid
AND B.sql_hash_value      = C.hash_value
AND A.request_id          = 1451703
--------------------------------------------------------
7. Unlock FNd user

BEGIN
FND_USER_PKG.EnableUser('&username');
END;
/
--------------------------------------------------------
8. Free Tablespace report
select
  a.tablespace_name,
  round(sum(a.bytes/1024/1024),2) TOTAL_MB,  
  round(sum
  (
    ( case
    when autoextensible = 'YES' then
    (a.maxbytes - a.user_bytes) + nvl(b.bytes,0)
    when autoextensible = 'NO' then
    nvl(b.bytes,0)
    end
    )/1024/1024
  ),2) FREE_MB,
  (100 - round((round(sum(( case when autoextensible = 'YES' then (a.maxbytes - a.user_bytes) + nvl(b.bytes,0) when autoextensible = 'NO' then nvl(b.bytes,0) end)/1024/1024),2) *100) / round(sum(a.bytes/1024/1024),2))) "Percentage Used"
from
  dba_data_files a,
  (
    select file_id,
      sum(bytes) bytes
    from dba_free_space
    group by file_id
  ) b
where
  a.file_id = b.file_id(+)
group by a.tablespace_name
order by Total_mb desc
--------------------------------------------------------
9. Get DDL of an object

--select dbms_metadata.get_ddl( object_type=>'&OBJECT_TYPE',name=>'&OBJECT_NAME',schema=>'&SCHEMA_NAME') FROM DUAL ;

select dbms_metadata.get_ddl(object_type, object_name, owner)
from
(
    --Convert DBA_OBJECTS.OBJECT_TYPE to DBMS_METADATA object type:
    select
        owner,
        object_name,
        decode(object_type,
            'PACKAGE',      'PACKAGE_SPEC',
            'PACKAGE BODY', 'PACKAGE_BODY',
            'TYPE BODY', 'TYPE_BODY',
            object_type
        ) object_type
    from dba_objects
    where object_name = :object_name
        --These objects are included with other object types.
        and object_type not in ('INDEX PARTITION','LOB','LOB PARTITION','TABLE PARTITION')
        --Ignore system-generated types that support collection processing.
        and not (object_type like 'TYPE' and object_name like 'SYS_PLSQL_%')
)
order by owner, object_type, object_name;
--------------------------------------------------------
10. List invalids with statement to compile them

set linesize 200
set pagesize 200
col object_name for a40
col owner for a6
set echo off
set feedback off
set verify off
set heading off
SELECT '----------------' from dual;
SELECT 'List of Invalids' from dual;
SELECT '----------------' from dual;
set heading on
select owner, objecT_name, objecT_type, status from dba_objects where status!='VALID';
set heading off
SELECT '---------------------------------' from dual;
SELECT 'Execute Below to compile invalids' from dual;
SELECT '---------------------------------' from dual;
select 'alter ', object_type, owner, '.' ,object_name, 'compile ;' from dba_objects where status!='VALID' and object_type != 'PACKAGE BODY'
union ALL
select 'alter ', decode (OBJECT_TYPE, 'PACKAGE BODY', 'PACKAGE'), owner, '.' ,object_name, 'compile BODY ;' from dba_objects where status!='VALID' and object_type = 'PACKAGE BODY' ;
--------------------------------------------------------
11. Responsibilities assigned to a user.

Select b.user_name, c.responsibility_name, a.START_DATE, a.END_DATE
from fnd_user_resp_groups_direct a, fnd_user b, fnd_responsibility_tl c
where a.user_id = b.user_id
and a.responsibility_id = c.responsibility_id
and b.user_name='&username'