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'
