Find any profile Values
======================
select PROFILE_OPTION_VALUE from FND_PROFILE_OPTION_VALUES where
PROFILE_OPTION_ID=(Select PROFILE_OPTION_ID from fnd_profile_options where PROFILE_OPTION_NAME ='WF_MAIL_WEB_AGENT');
This is the list of few profile options which Apps DBA use frequently. It is not necessary that you as Apps DBA must know all profile options,
it depends on your implemnetation.
Applications Help Web Agent
Applications Servlet Agent
Applications Web Agent
Concurrent: Active Request Limit
Concurrent: Hold Requests
Concurrent: Multiple Time Zones
Concurrent: Report Access Level
Concurrent: Report Copies
Concurrent: Request priority
Database Instance
Enable Security Group
FND: Debug Log Filename
FND: Debug Log Level
Forms Runtime Parameters
Gateway User ID
ICX: Discoverer Launcher
ICX: Forms Launcher
ICX: Report Launcher
ICX: Limit Connect
ICX: Limit time
ICX: Session Timeout
MO Operating Unit
Node Trust Level
RRA: Delete Temporary Files
RRA: Enabled
RRA: Service Prefix
RRA: Maximum Transfer Size
Self Service Personal Home Page Mode
Sign-On: Audit Level
Signon Password Failure Limit
Signon Password Hard to Guess
Signon Password Length
Signon Password No Reuse
Site Name
Socket Listener Port
TCF: Host
TCF: Port
TWO TASK
Spid.sql
select sid, b.serial#, osuser, b.username, status, process, paddr, spid
from v$process a,v$session b
where spid = '&spid'
and a.addr = b.paddr
and b.username != ' '
order by 1
/
Applied_Patches_11i.sql
REM
REM Applied_Patches_11i.sql - used to create 11i Applied_Patches_Report.txt
REM
set feedback off
set linesize 80
set pagesize 9000
column patch_name head Bug format a10
column application_short_name head Product format a7
column patch_type head Patch_Type format a16
column patch_level head Patch_Level format a12
column last_update_date head Applied format a11
column ReportDate head ReportDate format a20
prompt
prompt 11i Applied Patches by Most Recently Applied Date
SELECT sysdate ReportDate FROM dual;
SELECT distinct ap.patch_name,
decode(ab.application_short_name, null, mini.app_short_name,
ab.application_short_name) application_short_name,
ap.patch_type patch_type, mini.patch_level patch_level,
ap.last_update_date Applied
FROM applsys.ad_bugs ab, applsys.ad_applied_patches ap,
applsys.ad_patch_drivers pd, applsys.ad_patch_driver_minipks mini
WHERE ab.bug_number = ap.patch_name
and ap.applied_patch_id = pd.applied_patch_id
and pd.patch_driver_id = mini.patch_driver_id(+)
ORDER BY ap.last_update_date desc;
shrink_rbs.sql
@rseg
@rbs_info2
select 'alter rollback segment '||r.name ||' shrink ;'
from v$rollname r,
v$rollstat s
where s.usn = r.usn
and r.name != 'SYSTEM'
and r.name not in (
select r.name rname
from v$lock l,
v$process p,
v$rollname r,
v$rollstat s,
v$session s1
where l.sid = s1.sid (+)
and trunc(l.id1(+)/65536) = r.usn
and l.type(+) = 'TX'
and l.lmode (+) = 6
and s.usn = r.usn
and s1.paddr = p.addr
)
/
Rseg
select r.name rname,
s.rssize "size"
from v$rollname r,
v$rollstat s
where s.usn = r.usn
/
rbs_info2
set lines 80
set pages 68
col username format a8
col rname format a7
col sid format 9999
col userid format a8
select r.name rname ,
p.pid "PID",
p.spid "SPID",
NVL(p.username, 'NO TRANSACTION') "USERID",
p.terminal,
s.rssize "SIZE"
, s1.username "USERNAME"
, s1.sid
from v$lock l,
v$process p,
v$rollname r,
v$rollstat s,
v$session s1
where l.sid = s1.sid (+)
and trunc(l.id1(+)/65536) = r.usn
and l.type(+) = 'TX'
and l.lmode (+) = 6
and s.usn = r.usn
and s1.paddr = p.addr
order by r.name
/
user_sess.sql
Select distinct usr.user_name
from applsys.fnd_logins l, applsys.fnd_user usr, v$session sess
Where l.user_id =usr.user_id and l.spid = sess.process and l.end_time is null order by usr.user_name;
fnd_waiting_jobs.sql
REM $Header: afrqwait.sql 115.3 2000/01/10 12:25:25 pkm ship $
REM +======================================================================+
REM | Copyright (c) 1992 Oracle Corporation Belmont, California, USA |
REM | All rights reserved. |
REM +======================================================================+
REM FILENAME
REM pendingi.sql
REM DESCRIPTION
REM Selects all the Pending Requests with status Q
REM
REM Ram Bhoopalam Created
REM +======================================================================+
Set Pages 58
Set Linesize 80
Column Reqst Format 9999999
Column Orcl Format A8
Column Priority Format 99999
Column Program Format A25
Column Start_At Format A15
Column Req_Date Format A15
Column Reqst HEADING 'Request|ID'
Column Orcl HEADING 'Oracle|Name'
Column Priority HEADING 'Priority'
Column Program HEADING 'Program'
Column Req_Date HEADING 'Requested at'
Column Start_At HEADING 'To Start at'
Break On Report
Comput Count OF Reqst ON Report
TTITLE 'Pending for Conflict Resolution Manager'
select request_id Reqst,
User_Concurrent_Program_Name Program,
To_Char(Request_Date, 'MM-DD-YY HH24:MI') Req_Date,
To_Char(Requested_Start_Date, 'MM-DD-YY HH24:MI') Start_At
from fnd_concurrent_requests Fcr, Fnd_Concurrent_Programs_TL Fcp,
fnd_oracle_userid O
where Status_Code = 'Q'
And (
Fcr.Concurrent_Program_Id = Fcp.Concurrent_Program_Id And
Program_Application_ID = Application_ID )
And Hold_Flag = 'N'
And Fcr.Oracle_ID = O.Oracle_ID
And Requested_Start_Date <= Sysdate
Order By Requested_Start_Date Asc,
Decode(Priority, Null, 9999999, Priority) Asc,
Request_ID Asc
/
TTITLE 'Pending for Conflict Resolution Manager|( requested to run at a later time )'
select request_id Reqst,
User_Concurrent_Program_Name Program,
To_Char(Request_Date, 'MM-DD-YY HH24:MI') Req_Date,
To_Char(Requested_Start_Date, 'MM-DD-YY HH24:MI') Start_At
from fnd_concurrent_requests Fcr, Fnd_Concurrent_Programs_TL Fcp,
fnd_oracle_userid O
where Status_Code = 'Q'
And (
Fcr.Concurrent_Program_Id = Fcp.Concurrent_Program_Id And
Program_Application_ID = Application_ID )
And Hold_Flag = 'N'
And Fcr.Oracle_ID = O.Oracle_ID
And Requested_Start_Date > Sysdate
Order By Requested_Start_Date Asc,
Decode(Priority, Null, 9999999, Priority) Asc,
Request_ID Asc
/
TTITLE 'Pending for Conflict Resolution Manager|( on Hold )'
select request_id Reqst,
User_Concurrent_Program_Name Program,
To_Char(Request_Date, 'MM-DD-YY HH24:MI') Req_Date,
To_Char(Requested_Start_Date, 'MM-DD-YY HH24:MI') Start_At
from fnd_concurrent_requests Fcr, Fnd_Concurrent_Programs_TL Fcp,
fnd_oracle_userid O
where Status_Code = 'Q'
And (
Fcr.Concurrent_Program_Id = Fcp.Concurrent_Program_Id And
Program_Application_ID = Application_ID )
And Hold_Flag = 'Y'
And Fcr.Oracle_ID = O.Oracle_ID
Order By Requested_Start_Date Asc,
Decode(Priority, Null, 9999999, Priority) Asc,
Request_ID Asc
/
TTITLE 'Waiting Concurrent Requests'
select request_id Reqst,
User_Concurrent_Program_Name Program,
To_Char(Request_Date, 'MM-DD-YY HH24:MI') Req_Date,
To_Char(Requested_Start_Date, 'MM-DD-YY HH24:MI') Start_At
from fnd_concurrent_requests Fcr, Fnd_Concurrent_Programs_TL Fcp,
fnd_oracle_userid O
where Status_Code = 'A'
And (
Fcr.Concurrent_Program_Id = Fcp.Concurrent_Program_Id And
Program_Application_ID = Application_ID )
And Fcr.Oracle_ID = O.Oracle_ID
Order By Requested_Start_Date Asc,
Decode(Priority, Null, 9999999, Priority) Asc,
Request_ID Asc
/
TTITLE OFF
Set Pages 60
Set Linesize 80
Clear Breaks
Clear Computes
lock.sql
declare cursor c1 is
select * from v$lock where request != 0 order by id1, id2;
wid1 number := -999999;
wid2 number := -999999;
wholder_detail varchar2(200);
v_err_msg varchar2(80);
wsid number(5);
wstep number(2);
wtype varchar2(10);
wobject_name varchar2(180);
wobject_name1 varchar2(80);
wlock_type varchar2(50);
w_lastcallet varchar2(11);
h_lastcallet varchar2(11);
begin
for c1_rec in c1 loop
if c1_rec.id1 = wid1 and c1_rec.id2 = wid2 then
null;
else
wstep := 10;
select sid , type into wsid , wtype
from v$lock
where id1 = c1_rec.id1
and id2 = c1_rec.id2
and request = 0
and lmode != 4;
dbms_output.put_line(' ');
wstep := 20;
select 'Holder DBU: '||s.username ||' OSU: '||s.osuser ||' DBP:'||p.spid||' APP: '|| s.process ||
' SID:' || s.sid || ' Status: ' || s.status ||
' (' || floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60) ||
') Module:'|| module ||
' AppSrvr: ' || substr(replace(machine,'GEIPS-AMER\',null),1,15)
into wholder_detail
from v$session s, v$process p
where s.sid= wsid
and s.paddr = p.addr;
dbms_output.put_line(wholder_detail);
begin
select decode(wtype,'TX', 'Transaction',
'DL', 'DDL Lock',
'MR', 'Media Recovery',
'RT', 'Redo Thread',
'UN', 'User Name',
'TX', 'Transaction',
'TM', 'DML',
'UL', 'PL/SQL User Lock',
'DX', 'Distributed Xaction',
'CF', 'Control File',
'IS', 'Instance State',
'FS', 'File Set',
'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction',
'TS', 'Temp Segment',
'IV', 'Library Cache Invalida-tion',
'LS', 'Log Start or Switch',
'RW', 'Row Wait',
'SQ', 'Sequence Number',
'TE', 'Extend Table',
'TT', 'Temp Table',
'Un-Known Type of Lock')
into wlock_type
from dual;
declare
cursor c3 is
select object_id from v$locked_object
where session_id = wsid;
begin
wobject_name := '';
for c3_rec in c3 loop
select object_type||': '||owner||'.'||object_name
into wobject_name
from dba_objects
where object_id = c3_rec.object_id;
wobject_name := wobject_name ||' '||wobject_name1;
end loop;
exception
when others then
wobject_name := wobject_name ||' No Object Found';
end;
dbms_output.put_line('Lock Held: '||wlock_type||' for Object :'||wobject_name);
exception
when no_data_found then
dbms_output.put_line('Lock Held: '||wlock_type||' No object found in DBA Objects');
end;
end if;
wstep := 30;
select '.... Requestor DBU: '||s.username ||' OSU: '||s.osuser ||' DBP:'||p.spid||' APP: '|| s.process ||
' SID:' || s.sid || ' Status: ' || s.status ||
' (' || floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60) ||
') Module:'|| module ||
' AppSrvr: ' || substr(replace(machine,'GEIPS-AMER\',null),1,15)
into wholder_detail
from v$session s, v$process p
where s.sid= c1_rec.sid
and s.paddr = p.addr;
dbms_output.put_line(wholder_detail);
wid1 := c1_rec.id1;
wid2 := c1_rec.id2;
end loop;
if wid1 = -999999 then
wstep := 40;
dbms_output.put_line('No one requesting locks held by others');
end if;
exception
when others then
v_err_msg := (sqlerrm ||' '|| sqlcode||' step='||to_char(wstep));
DBMS_OUTPUT.PUT_LINE(v_err_msg);
end;
/
select 'Alter tablespace ' || tablespace_name || ' end backup' from dba_tablespaces;
lock_on_obj.sql
set lines 250
col OBJECT_NAME for a30
col MACHINE for a20
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;
crtime.sql
rem ********************************************************************
rem * ccmreq.sql scripts list all the running request .
rem ********************************************************************
set lines 155
col os form A7 head AppProc
col spid form a6 head DBProc
col program form A43 trunc
set pages 60
col time form 9999.99 head Elapsed
col "Req Id" form 9999999999
col "Parent" form a9
col "Prg Id" form 999999
col qname head "Concurrent Manager Queue" format a25 trunc
col sid format 99999 head SID
set recsep off
select q.concurrent_queue_name || ' - ' || target_node qname
,a.request_id "Req Id"
,decode(a.parent_request_id,-1,NULL,a.parent_request_id) "Parent"
,a.concurrent_program_id "Prg Id"
,a.phase_code,a.status_code
,b.os_process_id "OS"
,vs.sid
,vp.spid
,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 "Time"
,c.concurrent_program_name||' - '||
c2.user_concurrent_program_name "program"
from APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs_tl c2
,APPLSYS.fnd_concurrent_programs c
,v$session vs
,v$process vp
where a.controlling_manager = b.concurrent_process_id
and a.concurrent_program_id = c.concurrent_program_id
and a.program_application_id = c.application_id
and c2.concurrent_program_id = c.concurrent_program_id
and a.phase_code in ('I','P','R','T')
and b.queue_application_id = q.application_id
and b.concurrent_queue_id = q.concurrent_queue_id
and c2.language = 'US'
and vs.process (+) = b.os_process_id
and vs.paddr = vp.addr (+)
--order by 9,2,1
order by 9
/
inact.sql
select sid, b.serial#, osuser, b.username, status, process, paddr, spid, module, b.program
from v$process a,v$session b
where status = 'INACTIVE'
and a.addr = b.paddr
and b.username != ' '
order by 1
/
analyzereq.sql
conc_running.sql
SELECT f.request_id,
pt.user_concurrent_program_name user_concurrent_program_name,
f.actual_start_date actual_start_date,
f.actual_completion_date actual_completion_date,
Floor(((f.actual_completion_date - f.actual_start_date) * 24 * 60 * 60) / 3600)
||' HOURS '
||Floor((((f.actual_completion_date - f.actual_start_date) * 24 * 60 * 60) - Floor(((f.actual_completion_date - f.actual_start_date) * 24 * 60 * 60) / 3600) * 3600) / 60)
||' MINUTES '
||Round((((f.actual_completion_date - f.actual_start_date) * 24 * 60 * 60) - Floor(((f.actual_completion_date - f.actual_start_date) * 24 * 60 * 60) / 3600) * 3600 - (Floor((((f.actual_completion_date - f.actual_start_date) * 24 * 60 * 60) - Floor(((f.actual_completion_date - f.actual_start_date) * 24 * 60 * 60) / 3600) * 3600) / 60) * 60)))
||' SECS ' time_difference,
Decode(p.concurrent_program_name,'ALECDC',p.concurrent_program_name
||'['
||f.description
||']',
p.concurrent_program_name) concurrent_program_name,
Decode(f.phase_code,'R','Running',
'C','Complete',
f.phase_code) phase,
f.status_code
FROM apps.fnd_concurrent_programs p,
apps.fnd_concurrent_programs_tl pt,
apps.fnd_concurrent_requests f
WHERE f.concurrent_program_id = p.concurrent_program_id
AND f.program_application_id = p.application_id
AND f.concurrent_program_id = pt.concurrent_program_id
AND f.program_application_id = pt.application_id
AND pt.language = Userenv('Lang')
AND f.actual_start_date IS NOT NULL
AND f.phase_code = 'R'
ORDER BY f.actual_completion_date - f.actual_start_date DESC;
long.sql
select sid||','||serial# "Sess" , qcsid , username , opname , target , message from v$session_longops
where totalwork <>sofar;
ses.sql
select a.name, b.statistic#, b.value, b.sid
from v$sysstat a, v$sesstat b
where a.statistic# = b.statistic#
and b.sid = &1
and a.name like '%table%' ;
session_wait_info.sql
ttitle 'Events Sessions are Waiting For'
column event ON format a30
column p3text ON format a15
column p2text ON format a15
column p1text ON format a15
set linesize 132
set pagesize 54
select event, p1, p1text, p2, p2text, p3, p3text, count(*)
from v$session_wait
group by event, p1, p1text, p2, p2text, p3, p3text
order by count(*)
/
session_cache_hit_ratio.sql
select v$sess_io.* , 1-(PHYSICAL_READS/(BLOCK_GETS+CONSISTENT_GETS)) cache_hit_ratio, username
from v$sess_io, v$session
where physical_reads > 0
and 1-(PHYSICAL_READS/(BLOCK_GETS+CONSISTENT_GETS)) < .90
and (block_gets > 0 or consistent_gets > 0)
and v$session.sid = v$sess_io.sid
order by 1-(PHYSICAL_READS/(BLOCK_GETS+CONSISTENT_GETS))
/
sessdtl.sql
select s.sid , s.serial# , s.process , s.status ,
OSUser "osuser" , p.spid , s.USERNAME "user"
from v$process p, v$session s
where addr = paddr
and s.sid = &Session_ID
order by status , sid
/
sesswait.sql
select s.sid,s.serial#,
s.status,w.event,
w.p1,w.p2,w.state,
w.wait_time,
w.seconds_in_wait
from v$session s,v$session_wait w where
s.sid=w.sid
and w.seconds_in_wait >3600;
conc_det_new.sql
set line 80
set head off
set trimspool on
set verify off
col request format A80
ttitle center 'Concurrent Request Details' skip 2
SELECT 'Request Id: ' ||to_char(a.request_id) request,
'Parent Request Id: ' ||decode(to_char(parent_request_id),'-1',null,to_char(parent_request_id)) request,
'Program Name: ' ||substr(b.user_concurrent_program_name,1,50) request,
'Req Start Time: ' ||to_char(a.requested_start_date,'DD-MON-RR HH24:MI:SS') request,
'Start Time: ' ||to_char(a.actual_start_date,'DD-MON-RR HH24:MI:SS') request,
'End Time: ' ||to_char(a.actual_completion_date,'DD-MON-RR HH24:MI:SS') request,
'Execution Time: ' || to_char(round(trunc(((actual_completion_date-actual_start_date)*24*60*60)/60)+(((actual_completion_date-actual_start_date)*24*60*60)-(trunc(((actual_completion_date-actual_start_date)*24*60*60)/60)*60))/100,2)) request,
'User: ' || fu.user_name request,
'Phase: ' || decode(a.phase_code,'R','Running','P','Inactive','C','Completed', a.phase_code) request,
'Status: ' || decode(a.status_code,'E','Error', 'C','Normal', 'X','Terminated', 'Q','On Hold', 'D','Cancelled', 'G','Warning', 'R','Normal', 'W', 'Paused', a.status_code) request,
'Parameters: ' || substr(a.argument_text,1,55) request,
' ' || substr(a.argument_text,56,55) request,
' ' || substr(a.argument_text,111,55) request
FROM apps.fnd_user fu,
apps.fnd_concurrent_programs_vl b,
apps.fnd_concurrent_requests a
WHERE a.concurrent_program_id = b.concurrent_program_id
and fu.user_id = a.requested_by
AND a.request_id = &request_id
;
set pagesize 24
set verify on
set head on
ttitle off
conc_process.sql
select b.request_id,a.USER_CONCURRENT_PROGRAM_NAME
from apps.fnd_concurrent_programs_vl a, apps.fnd_concurrent_requests b
where a.concurrent_program_id = b.concurrent_program_id
and b.status_code = 'R'
and b.phase_code = 'R'
order by 1
/
Definition of any DDL
======================
select dbms_metadata.get_ddl('TABLE','FND_LOGINS','APPLSYS') from dual;
Find Product Patch Level / Version
==================================
select PATCH_LEVEL from fnd_product_installations where
APPLICATION_ID in (select APPLICATION_ID from fnd_application where APPLICATION_SHORT_NAME like '%AD%');
select PATCH_NAME from ad_applied_patches where PATCH_NAME='3453499';
Select name, name into: visible_option_value, :profile_option_value from v$database
select node_name,target_node from fnd_concurrent_queues;
insert into table apps.fnd_concurrent_requests (select * from fnd_concurrent_requests_bkp);
select LOGFILE_NAME, LOGFILE_NODE_NAME, OUTFILE_NAME, OUTFILE_NODE_NAME from fnd_concurrent_requests where REQUEST_ID=1091889;
select max_processes, running_processes, CONCURRENT_QUEUE_NAME from FND_CONCURRENT_QUEUES where CONCURRENT_QUEUE_NAME like 'XDP%' and CONCURRENT_QUEUE_NAME not like 'XDP_MANAGER';
select max_processes, running_processes, CONCURRENT_QUEUE_NAME from FND_CONCURRENT_QUEUES
where CONCURRENT_QUEUE_NAME like 'XDP%';
Validating a user Passowrd
===========================
select fnd_web_sec.validate_login('sysadmin','br3adman') from dual;
select fnd_web_sec.validate_login('bol-support','****************') from dual;
update wf_event_subscriptions set system_guid =wf_core.translate('WF_SYSTEM_GUID') where
system_guid <> wf_core.translate('WF_SYSTEM_GUID');
select name, status from wf_events
where guid in (select event_filter_guid from wf_event_subscriptions where
system_guid <> wf_core.translate('WF_SYSTEM_GUID'));
Select profile_option_value from
applsys.fnd_profile_option_values where profile_option_id in
(Select a.profile_option_id from applsys.fnd_profile_option_values A, applsys.fnd_profile_options B
where a.profile_option_id=b.profile_option_id
and profile_option_name like '%ICX%REPORT%SERVER');
select MESSAGE_NAME, MESSAGE_TEXT
from FND_NEW_MESSAGES, FND_APPLICATION
where FND_NEW_MESSAGES.LANGUAGE_CODE ='xx'
and FND_NEW_MESSAGES.APPLICATION_ID = FND_APPLICATION.APPLICATION_ID
and FND_APPLICATION.APPLICATION_SHORT_NAME = 'MSD'
and message_text like '%Search String%'
WF PARAMETERS
=============
SELECT
c.parameter_name,
a.parameter_value
FROM fnd_svc_comp_param_vals a,
fnd_svc_components b,
fnd_svc_comp_params_b c
WHERE b.component_id = a.component_id
AND b.component_type = c.component_type
AND c.parameter_id = a.parameter_id
AND c.encrypted_flag = 'N'
AND b.component_name = 'Workflow Notification Mailer'
ORDER BY c.parameter_name;
Query To Set Open notifications
==============================
Update wf_notifications set status = 'CLOSED', mail_status = 'SENT',end_date = sysdate; 372933.1
bash-2.05b$ diff htmlvars_apps.js .snapshot/AUTOHOTSNAP_auohscoar16_TCOA6I_CODE_20070128/htmlvars_apps.js
$APACHE_TOP/Apache/htdocs/discwb4/scripts/
Using the System Administrator responsibility, navigate to Globalpreferences, change the Workflow administrator field from SYSADMIN to
workflow administrator web applications (TAR # 4499451.993).
SQL> update wf_resources set text='FND_RESP|FND|FNDWF_ADMIN_WEB_NEW|STANDARD' where name = 'WF_ADMIN_ROLE';
select COMPONENT_TYPE,COMPONENT_NAME,COMPONENT_STATUS from FND_SVC_COMPONENTS;
update apps.fnd_concurrent_requests set hold_flag = 'Y', status_code = 'H', phase_code = 'I' where phase_code = 'P';
select user_end_date from wf_local_user_roles where user_name='SYSADMIN';
update wf_local_user_roles set USER_END_DATE=null where USER_NAME='SYSADMIN' and USER_END_DATE='28-OCT-06';
select effective_end_date from wf_local_user_roles where effective_end_date
='28-OCT-06';
update wf_local_user_roles set EFFECTIVE_END_DATE='31-MAR-12' where EFFECTIVE_END_DATE='28-OCT-06';
select tablespace_name,sum(maxbytes/1024/1024/1024) " size in GB " from dba_temp_files group by tablespace_name;
select tablespace_name,sum(maxbytes/1024/1024/1024) " size in GB " from dba_data_files group by tablespace_name;
Submit a concurrent request using CONCSUB
------------------------------------------
CONCSUB apps/BvGUWXhk SYSADMIN 'System Administrator' SYSADMIN CONCURRENT FND FNDCPPUR START="$PURGE_TIME" REPEAT_DAYS=1 REPEAT_INTERVAL_TYPE="START" "ALL" "Age" "30" '""' '""' '""' '""' '""' '""' '""' '""' "Yes" "Yes"
update alr_alerts set enabled_flag='N' , end_date_active = sysdate, last_update_date = sysdate
where alert_name like 'ALERTNAME'
COL USER_CONCURRENT_QUEUE_NAME FOR A35
COL CONCURRENT_QUEUE_NAME FOR A23
SELECT APPLSYS.FND_CONCURRENT_QUEUES_TL.USER_CONCURRENT_QUEUE_NAME,
APPLSYS.FND_CONCURRENT_QUEUES.CONCURRENT_QUEUE_NAME,
APPLSYS.FND_CONCURRENT_QUEUES.RUNNING_PROCESSES
FROM APPLSYS.FND_CONCURRENT_QUEUES, APPLSYS.FND_CONCURRENT_QUEUES_TL
WHERE APPLSYS.FND_CONCURRENT_QUEUES.CONCURRENT_QUEUE_NAME
IN ('FNDICM', 'STANDARD', 'FNDCRM', 'FNDSM_AUTHENTICATION',
'INVMGR', 'WFMLRSVC', 'WFWSSVC', 'WFALSNRSVC', 'XDP_Q_EVENT_SVC', 'FNDSCH',
'FNDSM_AUOHSF5NE01', 'FNDICM', 'INVTMRPM', 'PODAMGR', 'ZIPQM')
AND (APPLSYS.FND_CONCURRENT_QUEUES.CONCURRENT_QUEUE_ID=APPLSYS.FND_CONCURRENT_QUEUES_TL.CONCURRENT_QUEUE_ID)
ORDER BY APPLSYS.FND_CONCURRENT_QUEUES_TL.USER_CONCURRENT_QUEUE_NAME ASC
/
select profile_option_id, profile_option_name from fnd_profile_options where profile_option_name = 'SITENAME'
select * from fnd_profile_option_values where profile_option_id = 125;
Assign new value:
update fnd_profile_option_values
set profile_option_value = '&new_value '|| trunc(sysdate),
last_update_date = sysdate,
last_updated_by = (select user_id from fnd_user where user_name = 'SYSADMIN')
where profile_option_id = (select profile_option_id
from fnd_profile_options
where profile_option_name = 'SITENAME');
==================================================
