Pre Patch Analysis Output
SELECT a.bug_no,
app.application_name,
a.DIRECTORY,
a.filename,
a.old_version,
a.new_version,
DECODE (a.typeid,
'not applied',
'Unchanged File',
'upgrade',
'Changed File',
'new',
'New')
change_type
FROM apps.fnd_imp_psmaster_vl a, apps.fnd_application_vl app
WHERE a.bug_no in ('35538202')
AND app.application_short_name = a.app_short_name
ORDER BY DECODE (a.typeid,
'not applied',
'Unchanged File',
'upgrade',
'Changed File',
'new',
'New') ASC;
output:
How To Identify Sessions Which Cause PL/SQL LOCK TIMER Wait Event:
SELECT vs.sid,vs.sql_id, vs.inst_id,vs.osuser,vw.event,vw.p1,vw.p2,vw.p3 ,vt.sql_text , vs.program FROM gv$session_wait vw, gv$sqltext vt , gv$session vs
WHERE vw.event = 'PL/SQL lock timer'
AND vt.address=vs.sql_address
AND vs.inst_id = vw.inst_id
AND vs.sid = vw.sid;
How to Determine the SQL_ID for a SQL Statement:
a. How to identify the SQL_ID of a statement:
SELECT /* TARGET SQL - Ram */ * FROM dual;
SELECT sql_id, plan_hash_value, substr(sql_text,1,40) sql_text
FROM gv$sql
WHERE sql_text like 'SELECT /* TARGET SQL - Ram */%'
SELECT sql_id, plan_hash_value, SUBSTR(sql_text,1,40) Text
FROM gv$sql
WHERE sql_text LIKE '%TARGET SQL - Ram%'; -- An_Identifiable_String
c. If SQL is no longer available in gv$sql, you can find it in the AWR history views: DBA_HIST_SQLTEXT and DBA_HIST_SQLSTAT :
SELECT
s.sql_id,
s.plan_hash_value,
t.sql_text,
s.snap_id
FROM
dba_hist_sqlstat s,
dba_hist_sqltext t
WHERE s.dbid = t.dbid
AND s.sql_id = t.sql_id
AND sql_text LIKE '%Ram%'
ORDER BY
s.sql_id;
d. How to Determine the SQL_ID of a SQL Statement in a PL/SQL Block:
select sql_id, sql_text from gv$sql where sql_text like '%declare%fnd_concurrent.wait_for_request%';
EBS System Date Change - 19c (CDB/PDB):
1. MT level - CM logs backed up with CM down, then using 'mv' for the large directories.
2. DB level - turned on flashback, created restore point for the PDB.
3. Changed fixed_date at PDB level
ALTER SYSTEM SET fixed_date='2022-10-31:10:10:10' SCOPE=BOTH SID='*' PDB='SRN5I';
----
ALTER SYSTEM SET fixed_date='2022-10-31:10:10:10' SCOPE=BOTH SID='*';
ALTER SYSTEM SET fixed_date='2022-10-31:10:10:10' SCOPE=BOTH SID='*' PDB='SRN5I';
ALTER SYSTEM SET fixed_date='NONE' SCOPE=BOTH SID='*';
SQL> show con_name;
CON_NAME
------------------------------
CDB$ROOT
SQL> alter system set fixed_date =none scope=both sid='*';
System altered.
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fixed_date string NONE
SQL> select sysdate from dual;
SYSDATE
---------
16-JUN-22
alter session set container=SRN5I;
select sysdate from dual;
SYSDATE
---------
31-OCT-22
Every enterprise architect eventually gets that request from leadership: "Can we use AI on our data — without shipping it off to some ...
