Oracle Home inventory is corrupted LsInventorySession failed: OracleHome Inventory[oratest@slctest01 OPatch]$ opatch lsinventoryOracle Interim Patch Installer version 11.2.0.3.3Copyright (c) 2012, Oracle Corporation. All rights reserved.Oracle Home : /u01/oracle/testdb/11.2.0Central Inventory : /u01/oraInventory ...
Oracle E-Business Suite Current patch level
SELECT ABBREVIATION,
NAME,
TYPE,
CODELEVEL,
BASELINE
FROM AD_TRACKABLE_ENTITIES
WHERE ABBREVIATION...
Defragment Concurrent Tables
--DEFRAGMENT
---Defragment the tables periodically to reclaim unused space / improve performance
alter table APPLSYS.FND_CONCURRENT_REQUESTS move;
alter table APPLSYS.FND_CRM_HISTORY move;
alter table APPLSYS.FND_CONCURRENT_PROCESSES move;
alter table APPLSYS.FND_CRM_HISTORY move;
alter table APPLSYS.FND_ENV_CONTEXT move;
alter table APPLSYS.FND_TEMP_FILES...
Creating ACL for UTL_SMTP
SELECT *
FROM dba_network_acls;
-- Create ACL and privilege
begin
dbms_network_acl_admin.create_acl (
acl => 'utl_mail.xml',
description => 'Allow mail to be send',
principal => 'APPS',
is_grant => TRUE,
...
Cost Manager Status
SELECT x.process_type "Name",
DECODE(
(SELECT '1' FROM fnd_concurrent_requests cr,
fnd_concurrent_programs_vl cp,
fnd_application a WHERE cp.concurrent_program_id = cr.concurrent_program_id
AND cp.concurrent_program_name = x.process_name
...
Cost Manager Datafix
select * from mtl_material_transactions where costed_flag = 'E';
select *
from org_acct_periods where acct_period_id=13481
--where period_name like 'JUL-14'
select transaction_id,creation_date,organization_id,transaction_date,acct_period_id
from mtl_material_transactions where costed_flag = 'E';
UPDATE mtl_material_transactions
SET...
Core HR SQL Queries
--Organization through Organization Hierarchy
SELECT NAME, TYPE, organization_id
FROM (SELECT hou.NAME, TYPE, organization_id
FROM hr_all_organization_units hou
...
Concurrent Request Statistics
--- Concurrent Request ran in 24 Hours
SELECT fcr.request_id "Request ID",
fcp.user_concurrent_program_name "Program",
-- fcr.argument_text " Parameters",
fu.user_name "Username",
fr.responsibility_name "Responsbility",
fcr.actual_start_date...
Concurrent Program Statestics
select CONC.USER_CONCURRENT_PROGRAM_NAME,
CONC.CONCURRENT_PROGRAM_NAME,
CONC.ENABLED_FLAG,
stat.LAST_RUN_DATE,
stat.LAST_RUN_REQUEST_ID,
stat.OUTCOME,
stat.SUCCESSFUL_COMPLETION,
...
Concurrent Managers Status
/* Checking concurrent programs running currently with Details of Processed time and Start Date */
SELECT DISTINCT
c.USER_CONCURRENT_PROGRAM_NAME,
ROUND ( ( (SYSDATE - a.actual_start_date) * 24 * 60 * 60 / 60), 2)
AS Process_time,
...
Compile Forms command line
TEST
f60gen module=/u5/applupg2/uatappl/au/11.5.0/forms/US/APXINWKB.fmb userid=APPS/APPS
output_file=/u5/applupg2/uatappl/ap/11.5.0/forms/US/APXINWKB.fmx
module_type=form
batch=no
compile_all=special
PROD
f60gen module=/u3/applprd/prdappl/au/11.5.0/forms/US/APXINWKB.fmb userid=APPS/APPS
output_file=/u3/applprd/prdappl/ap/11.5.0/forms/US/APX...
Concurrent Manager Cleanup Script
====================================================================
REM
REM FILENAME
REM cmclean.sql
REM DESCRIPTION
REM Clean out the concurrent manager tables
REM NOTES
REM Usage: sqlplus <apps_user/apps_passwd> @cmclean
REM
REM
REM $Id: cmclean.sql,v 1.4 2001/04/07 15:55:07 pferguso Exp $
REM
REM
REM...
Check Materialized view Refresh
SELECT owner, mview_name, last_refresh_date
FROM all_mviews
where mview_name like 'MATERIALIZED_VIEW_NAME...
Check Database Locks
--To check database locks----[Kill Inactive Session] ----------------------------------------------------
SELECT * FROM dba_locks WHERE blocking_others='Blocking';
select LOCK_TYPE,SESSION_ID,BLOCKING_OTHERS from dba_locks where BLOCKING_OTHERS !='Not Blocking';
select process,sid, blocking_session from v$session where blocking_session is not null;
--Locked...
Cancel Scheduled Concurrent requests
--How to Cancel a Concurrent Request Stuck in the Queue? [ID 749748.1]
--CCM.sql Diagnostic Script for Concurrent Manager [ID 171855.1]
--CMCLEAN.SQL - Non Destructive Script to Clean Concurrent Manager Tables [ID 134007.1]
--How to Clear a Request Set Stuck in RUNNING / PAUSED Status [ID 1081912.6]
--STATUS_CODE Column:
--A Waiting B Resuming...
Assigning Responsibility using FND_USER_PKG
BEGIN
fnd_user_pkg.addresp
(username => UPPER
('MUQTHIYAR.PASHA'),
resp_app => 'FND',
resp_key => 'APPLICATION_DEVELOPER',
security_group => 'STANDARD',
description => NULL,
start_date => SYSDATE,
end_date => NULL
);
COMMIT;
DBMS_OUTPUT.put_line ('Responsibility Added Successfully');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line...
Archive Log Statastics
select * from V$LOG_HISTORY
select * from V$ARCHIVE_DEST
select trunc(COMPLETION_TIME) TIME, SUM(BLOCKS * BLOCK_SIZE)/1024/1024 SIZE_MB from V$ARCHIVED_LOG group by trunc (COMPLETION_TIME) order by 1;
select trunc(COMPLETION_TIME) TIME, SUM(BLOCKS * BLOCK_SIZE)/1024/1024 SIZE_MB from V$ARCHIVED_LOG group by trunc (COMPLETION_TIME) order by 1;
select...
Application Check List
Application Information Gathering
=================================
Existing Operating Units
========================
select ORGANIZATION_ID, NAME
from hr_operating_units
order by ORGANIZATION_ID;
Registered Applications
=======================
select application_id, application_short_name, basepath
from fnd_application
order by application_id;
Registered...
Adding Report to Request Group using API
/**********************************************************************
*PURPOSE: To Add a Concurrent Program to a Request Group from backend *
**********************************************************************/
--
DECLARE
...
