更新子分类帐会计余额 & ORA-30006: 资源已被占用; 执行操作时出现 WAIT 超时

NOTE:1230404.1 – Open Period Balances Program(XLABAOPE) Fails with Error PLS-00306
NOTE:1285478.1 – ORA-30006 error in XLABABUP Subledger Accounting Balances Update program
NOTE:1354726.1 – XLABABUP module: Subledger Accounting Balances Update – Poor Performance or It Hangs
NOTE:1395849.1 – 13578004: R12 Xla_balances_calc_pkg.Massive_update_srs. Ora-30006: Resource Busy
NOTE:1578840.1 – Subledger Accounting Issue: Subledger Accounting Balances Update (XLABABUP) Completes with Error – ORA-30006: Resource Busy; Acquire with WAIT Timeout Expired
NOTE:1586430.1 – Subledger Accounting Balances Update Program Ended In Error ORA-30006: Resource Busy; Acquire with WAIT Timeout Expired
NOTE:1595203.1 – XLABABUP Subledger Accounting Balances Update Program Performance Issue
NOTE:1923784.1 – R12.1 : XLABABUP module Subledger Accounting Balances Update error ORA-30006
NOTE:2024116.1 – Performance Issues With Subledger Accounting Balances Update
NOTE:2026932.1 – R12: Troubleshooting Error : Update Subledger Accounting Balances Fail With ORA-30006: Resource Busy; Acquire With WAIT Timeout Expired

Subledger Accounting Balances Update (XLABABUP) 子分类帐会计余额更新 OLD one 
Update Subledger Accounting Balances (XLABAPUB) 更新子分类帐会计余额 NEW one 

更新子分类帐会计余额 & ORA-30006: 资源已被占用; 执行操作时出现 WAIT 超时

SLA 余额并行模式(SLA Balances Parallel mode)
Set the Profile: "SLA Balances Parallel mode" to either N or Y
Yes = the DB LEVEL PARALLELISM would be used
No = DB PARALLELISM would NOT be used.

set line 160 pages 9999
select INST_ID, SID, TYPE, ID1, ID2, LMODE, REQUEST, CTIME, BLOCK,
DECODE (BLOCK, 0, '', 'blocker') blocker,
DECODE (request, 0, '', 'waiter') waiter
from gv$lock where (ID1,ID2,TYPE) in
(select ID1,ID2,TYPE from gv$lock where request>0)
order by blocker; 

select b.bug_number, b.language, to_char(b.LAST_UPDATE_DATE, 'YYYY-MM-DD HH24:MI:SS') Last_update from apps.ad_bugs b where bug_number='&a';
set lines 160 pages 50000
Select distinct Bugs.Bug_Number as PATCH,
decode(Ad_Patch.Is_Patch_Applied('R12',-1,bugs.bug_Number),'EXPLICIT','APPLIED','NOT_APPLIED') as APPLIED
From 
(
 select '9524997'  as bug_number From Dual UNION ALL
 select '9679595'  as bug_number From Dual UNION ALL
 select '11666797' as bug_number From Dual UNION ALL
 select '12673914' as bug_number From Dual UNION ALL
 select '13840855' as bug_number From Dual UNION ALL
 select '14255667' as bug_number From Dual UNION ALL
 select '16358835' as bug_number From Dual UNION ALL
 select '16446642' as bug_number From Dual UNION ALL
 select '18706047' as bug_number From Dual
) Bugs order by 1;

9524997		APPLIED
9679595		APPLIED
11666797	APPLIED
12673914	APPLIED
13840855	NOT_APPLIED
14255667	APPLIED
16358835	NOT_APPLIED
16446642	NOT_APPLIED
18706047	NOT_APPLIED

alter session set nls_date_format ='yyyy-mm-dd HH24:MI:SS';
execute dbms_session.set_nls('NLS_DATE_FORMAT','''YYYY-MM-DD HH24:MI:SS''');
set lines 200 pages 50000 
col object_name format a30
col owner format a20
select owner, object_name, object_type,status from dba_objects where status = 'INVALID';

@?/rdbms/admin/utlprp 16
select count(*) from dba_objects where status = 'INVALID';

adstpall.sh apps/apps
sqlplus apps/apps @$AD_TOP/patch/115/sql/adsetmmd.sql ENABLE

export PATCH_TOP=/bzebs/patch
cd $PATCH_TOP
chmod -fR 777 *.zip

###13840855
adpatch workers=16 defaultsfile=$APPL_TOP/admin/$TWO_TASK/defaults.txt patchtop=$PATCH_TOP/13840855 driver=u13840855.drv logfile=u13840855_US.log
adpatch workers=16 defaultsfile=$APPL_TOP/admin/$TWO_TASK/defaults.txt patchtop=$PATCH_TOP/13840855_ZHS driver=u13840855.drv logfile=u13840855_ZHS.log

###16358835
adpatch workers=16 defaultsfile=$APPL_TOP/admin/$TWO_TASK/defaults.txt patchtop=$PATCH_TOP/16358835 driver=u16358835.drv logfile=u16358835_US.log
adpatch workers=16 defaultsfile=$APPL_TOP/admin/$TWO_TASK/defaults.txt patchtop=$PATCH_TOP/16358835_ZHS driver=u16358835.drv logfile=u16358835_ZHS.log

###16446642
adpatch workers=16 defaultsfile=$APPL_TOP/admin/$TWO_TASK/defaults.txt patchtop=$PATCH_TOP/16446642 driver=u16446642.drv logfile=u16446642_US.log
adpatch workers=16 defaultsfile=$APPL_TOP/admin/$TWO_TASK/defaults.txt patchtop=$PATCH_TOP/16446642_ZHS driver=u16446642.drv logfile=u16446642_ZHS.log

###18706047
adpatch workers=16 defaultsfile=$APPL_TOP/admin/$TWO_TASK/defaults.txt patchtop=$PATCH_TOP/18706047 driver=u18706047.drv logfile=u18706047_US.log
adpatch workers=16 defaultsfile=$APPL_TOP/admin/$TWO_TASK/defaults.txt patchtop=$PATCH_TOP/18706047_ZHS driver=u18706047.drv logfile=u18706047_ZHS.log

alter session set nls_date_format ='yyyy-mm-dd HH24:MI:SS';
execute dbms_session.set_nls('NLS_DATE_FORMAT','''YYYY-MM-DD HH24:MI:SS''');
set lines 200 pages 50000 
col object_name format a30
col owner format a20
select owner, object_name, object_type,status from dba_objects where status = 'INVALID';

@?/rdbms/admin/utlprp 16
select count(*) from dba_objects where status = 'INVALID';

sqlplus apps/apps @$AD_TOP/patch/115/sql/adsetmmd.sql DISABLE