That's syntax for ya! Here's the "new" way to recompile invalid objects on and CDB / PDB in Oracle Database Server 19c (and above), a little trickier than just @utlrp.sql huh! ...
cd $ORACLE_HOME/rdbms/admin
$ORACLE_HOME/perl/bin/perl catcon.pl --n 1 --e --b utlrp --d '''.''' utlrp.sql
select * from dba_invalid_objects;
For reference:
This is a post from Gareth's blog at http://garethroberts.blogspot.com
Are there some little things in life that frustrate you?
If you're working in an environment where there are a lot of Business Intelligence Publisher (BIP) reports in an on-site OBIEE instance, sometimes you want to impact assess, or find usages of a certain database table or column. The following shell script is a very simple way to search the Data Model SQL in BI Publisher reports in the filesystem. Note of course this only works if you have an on-site OBIEE instance. For Oracle eBusiness Suite you'd be better to query the CLOBs in stored XML Publisher tables, and for Cloud based Oracle Analytics - you'll need to work that one out!
Change the CAT= like to store the location of the shared folder that you're catalog resides in.
Save the contents to a file called bipdmsearch and remember to chmod 755 bipdmsearch so you can execute it.
#!/bin/sh
if [ -z "$1" ]; then
echo "Usage: bipdmsearch <search string>"
exit 1
fi
if [ -z "$CAT" ]; then
CAT=/u01/app/obi12c/config12.2.1.2/bi/bidata/service_instances/ssi/metadata/content/catalog/root/shared
fi
echo "cd $CAT"
cd $CAT
echo "Search for $1"
find . -type f -name \*xdm\* -exec grep -il "$1" {} \;
echo "Done"
This is a post from Gareth's blog at http://garethroberts.blogspot.comWow - it has been a long time since I posted here! So much so that the world has changed - with an onslaught of content and apps and tools and technologies and Cloud! However that said I've found myself coming back to visit my old posts from time to time as technical references to especially fiddly things are often lost in the mix of content these days. I've always had a list of things to add here, and over the last few years I've been working in-depth with the Oracle Business Intelligence Suite and picked up a bunch of tips and tricks along the way, so the list is even longer now ;-)
Although there are more people documenting more things there are still plenty of techniques that are obscure but warrant being being referenced.
So, we'll start off with a couple of easy ones, maybe something using OBIEE back end catalog updates, then kick into some tougher ones .... maybe Google Earth KML output from Oracle SQL/BI Publisher or something like that :-)
Enjoy ...
This is a post from Gareth's blog at http://garethroberts.blogspot.comIf you have an HP Mini 210 or 100 and have had issues with disk running out of space, failures to boot, I feel for you. After a heap of hassle, I sorted my out problems without having to reinstall Windows, and so I'm sharing my experiences as I found many many people with the same issue, but no central resolution. The issues I've had are as follows
IF you get a failure to startup, screen with 3F0 Harddisk does not exist:
Hopefully now the computer will restart.
This netbook came preinstalled with Roxio BackOnTrack, which is the primary source of my woes. Recently I found I was running out of disk space and after deleting a heap of data, then finding I was running out again I installed WinDirStat to check what was going on. On inspection I found a super secret hidden folder System Rollback Data, which is not even visible with "show system files" switched on. Note this is not to be confused with Microsoft Windows System Restore. This folder contained 160Gb out of my 250Gb SSD !!
So after reading I found many pointers for Roxio BackOnTrack being the culprit of the System Restore Data directory size. Note that it seems Roxio BackOnTrack and this super secret directory may have a hook into the boot loader. So beware!
Anyway, without further ado here's what I hit and how to fix it.
c: cd windows\system32\drivers copy syscow32x.sys c:\temp del syscow32x.sys exit
After all that, my machine is running sweetly again. Hopefully this post saves someone from throwing a perfectly fine HP Mini 100 / 210 out the window !!
Catch ya!
Gareth
Not too sure what I changed on my laptop, possibly just installation of another product or perhaps Microsoft or similar updates, but when I went to edit a BI Publisher RTF template I got the following error message on clicking ANY button in the BI Publisher plugin for Microsoft Word.
--------------------------- Microsoft Visual Basic --------------------------- System Error &H8000FFFF (-2147418113). Catastrophic failure --------------------------- OK Help ---------------------------
After quite a bit of researching, I found the solution, for me it was the second of the following fixes:
1. Delete the EXD files. I attempted this in the root of my C:\ and D:\ drives, but it didn't fix the error.
DEL /S /A:H /A:-H *.EXD
2. Change security setting, and then reboot Windows.
2a. For Windows 32bit:
Regsvr32 "C:\Windows\System32\MSCOMCTL.OCX"
2b. For Windows 64bit:
Regsvr32 "C:\Windows\SysWOW64\MSCOMCTL.OCX"
Problem solved!
Catch ya!
Gareth
The New Zealand Oracle Users Group - NZOUG Conference 2013 is open for registrations and Earlybird pricing is still available! The agenda is now available.
NZOUG 2013 is being held in Wellington at the iconic Te Papa Museum on the beautiful waterfront on the 18th and 19th of March, with an additional Workshop Day on the 20th March.
The conference will see a wealth of brilliant speakers from around the world, including Tom Kyte, Graham Wood, Andrew Holdsworth, John Schiff and Nadia Bendjedou and of course top speakers from Middle Earth (NZ) as well. Tom, Andrew and Graham will be presenting their Real World Performance show, which will be a highlight of the conference.
Topics being covered cross a wide range of Oracle solutions, with tracks for Development, DBA, Cloud Computing, Oracle E-Business Suite, a full dedicated JD Edwards Day, Fusion Applications, EPM/BI, Middleware, Management, Infrastructure, Security, Hardware and Professional Development.
I'll be co-presenting one paper on utilizing the Oracle E-Business Suite Integrated SOA Gateway, tips, tricks and a demo, not too much this time around so that I can head along to as many of the other presentations as I can. Drop me an email if you want to catch up at the conference!
So, if you're around New Zealand in mid-March, perhaps following on from the NZ - England Cricket Test, and if you like getting out and networking often with fun consequences, then make sure you come to the New Zealand Oracle event of the year, not to be missed!
Catch ya!
Gareth
This is a post from Gareth's blog at http://garethroberts.blogspot.com
In the Oracle E-Business Suite (EBS) Release 12 the data model of Suppliers has become much more complex. The base tables have changed (Suppliers, Sites, Bank Accounts, Contacts) and some of the fields have become obsolete.
Here is a query to bring many of the Supplier attributes together, with focus on banks / bank accounts, payment methods, contacts, remittance delivery (email, notification method). Please post comments if you find any issues!
Adjust the WHERE clause on the first WITH query to return the suppliers that you need to report on. Hope this query helps someone out.
Add additional fields to the final query (or WITH queries as required.)
Update 29-Feb-2012: Outer join sites ss to payment methods pm.
with vendors as
(
select vendor_id
from ap_suppliers
where 1=1
/* COMMENT / UNCOMMENT and UPDATE THE NEXT 5 LINES AS YOU REQUIRE */
--and vendor_type_lookup_code = 'VENDOR'
--and upper( vendor_name ) like 'VIRTUATE%'
and creation_date between '01-JAN-2011' and '01-JAN-2012'
--and enabled_flag = 'Y'
)
, vend as
(
select pv.vendor_id vendor_id
, pv.vendor_name_alt vendor_name_alt
, pv.vendor_name vendor_name
, pv.segment1 vendor_number
, pv.vendor_type_lookup_code vendor_type_lookup_code
from ap_suppliers pv
where pv.vendor_id in (select v.vendor_id from vendors v)
)
, site as
(
select ss.vendor_id vendor_id
, ss.vendor_site_id vendor_site_id
, ss.vendor_site_code vendor_site_code
, ss.vendor_site_code_alt vendor_site_code_alt
, ss.vat_code tax_code
, ss.vat_registration_num vat_registration_num
, t.name terms_name
, ss.address_line1 ss_address_line1
, ss.address_line2 ss_address_line2
, ss.address_line3 ss_address_line3
, ss.zip ss_zip
, ss.city ss_city
, ss.state ss_state
, ss.country ss_country
, ss.area_code ss_area_code
, ss.phone ss_phone
, ss.fax_area_code ss_fax_area_code
, ss.fax ss_fax
, ss.telex ss_telex
, ss.pay_site_flag ss_pay_site_flag
, ss.primary_pay_site_flag ss_primary_pay_site_flag
, pm.remit_advice_delivery_method ss_remit_advice_deliv_meth
, pm.remit_advice_email ss_remit_advice_email
, pm.remit_advice_fax ss_remit_advice_fax
, pm.payment_method_code ss_payment_method_code
, ss.remittance_email ss_remittance_email
, ss.supplier_notif_method ss_supplier_notif_method
, ps.addressee ss_addressee
, ( select hcp.phone_area_code
from hz_contact_points hcp
where hcp.owner_table_id = ss.party_site_id
and hcp.owner_table_name = 'HZ_PARTY_SITES'
and hcp.phone_line_type = 'GEN'
and hcp.contact_point_type = 'PHONE'
--and hcp.created_by_module = 'AP_SUPPLIERS_API'
and rownum < 2 -- copied from OAF View Object
) ss_hcp_phone_area_code
, ( select hcp.phone_number
from hz_contact_points hcp
where hcp.owner_table_id = ss.party_site_id
and hcp.owner_table_name = 'HZ_PARTY_SITES'
and hcp.phone_line_type = 'GEN'
and hcp.contact_point_type = 'PHONE'
--and hcp.created_by_module = 'AP_SUPPLIERS_API'
and rownum < 2 -- copied from OAF View Object
) ss_hcp_phone_number
, ( select hcp.phone_area_code
from hz_contact_points hcp
where hcp.owner_table_id = ss.party_site_id
and hcp.owner_table_name = 'HZ_PARTY_SITES'
and hcp.phone_line_type = 'FAX'
and hcp.contact_point_type = 'PHONE'
--and hcp.created_by_module = 'AP_SUPPLIERS_API'
and rownum < 2 -- copied from OAF View Object
) ss_hcp_fax_area_code
, ( select hcp.phone_number
from hz_contact_points hcp
where hcp.owner_table_id = ss.party_site_id
and hcp.owner_table_name = 'HZ_PARTY_SITES'
and hcp.phone_line_type = 'FAX'
and hcp.contact_point_type = 'PHONE'
--and hcp.created_by_module = 'AP_SUPPLIERS_API'
and rownum < 2 -- copied from OAF View Object
) ss_hcp_fax_number
from ap_supplier_sites_all ss
, ap_suppliers sup
, ap_terms t
, (
select ss.vendor_site_id
, payee.remit_advice_delivery_method
, payee.remit_advice_email
, payee.remit_advice_fax
, pm.payment_method_code
from iby_external_payees_all payee
, iby_ext_party_pmt_mthds pm
, hz_party_sites ps
, ap_supplier_sites_all ss
where payee.payee_party_id = ps.party_id
and payee.payment_function = 'PAYABLES_DISB'
and payee.party_site_id = ss.party_site_id
and payee.supplier_site_id = ss.vendor_site_id
and payee.org_id = ss.org_id
and payee.org_type = 'OPERATING_UNIT'
and ss.party_site_id = ps.party_site_id
and payee.ext_payee_id = pm.ext_pmt_party_id (+)
and pm.primary_flag (+) = 'N'
and not exists
( select 1
from iby_ext_party_pmt_mthds pm2
where pm.ext_pmt_party_id = pm2.ext_pmt_party_id
and pm2.primary_flag = 'Y'
)
union all
select ss.vendor_site_id
, payee.remit_advice_delivery_method
, payee.remit_advice_email
, payee.remit_advice_fax
, pm.payment_method_code
from iby_external_payees_all payee
, iby_ext_party_pmt_mthds pm
, hz_party_sites ps
, ap_supplier_sites_all ss
where payee.payee_party_id = ps.party_id
and payee.payment_function = 'PAYABLES_DISB'
and payee.party_site_id = ss.party_site_id
and payee.supplier_site_id = ss.vendor_site_id
and payee.org_id = ss.org_id
and payee.org_type = 'OPERATING_UNIT'
and ss.party_site_id = ps.party_site_id
and pm.ext_pmt_party_id = payee.ext_payee_id
and pm.primary_flag = 'Y'
) pm
, hz_party_sites ps
where sup.vendor_id in (select vendor_id from vendors)
and sup.vendor_id = ss.vendor_id
and ss.vendor_site_id = pm.vendor_site_id (+)
and ss.party_site_id = ps.party_site_id (+)
and ss.terms_id = t.term_id (+)
)
, cont as
(
select pv.vendor_id vendor_id
, pvs.vendor_site_id vendor_site_id
, hp.party_id c_party_id
, hp.person_first_name c_first_name
, hp.person_last_name c_last_name
, hp.person_title c_person_title
, hcpe.email_address c_email_address
, hcpp.phone_area_code c_phone_area_code
, hcpp.phone_number c_phone_number
, hcpf.phone_area_code c_fax_area_code
, hcpf.phone_number c_fax_number
from hz_parties hp
, hz_relationships hzr
, hz_contact_points hcpp
, hz_contact_points hcpf
, hz_contact_points hcpe
, ap_suppliers pv
, ap_supplier_sites_all pvs
, hz_party_sites hps
where hp.party_id = hzr.subject_id
and hzr.relationship_type = 'CONTACT'
and hzr.relationship_code = 'CONTACT_OF'
and hzr.subject_type = 'PERSON'
and hzr.subject_table_name = 'HZ_PARTIES'
and hzr.object_type = 'ORGANIZATION'
and hzr.object_table_name = 'HZ_PARTIES'
and hzr.status = 'A'
and hcpp.owner_table_name(+) = 'HZ_PARTIES'
and hcpp.owner_table_id(+) = hzr.party_id
and hcpp.phone_line_type(+) = 'GEN'
and hcpp.contact_point_type(+) = 'PHONE'
and hcpf.owner_table_name(+) = 'HZ_PARTIES'
and hcpf.owner_table_id(+) = hzr.party_id
and hcpf.phone_line_type(+) = 'FAX'
and hcpf.contact_point_type(+) = 'PHONE'
and hcpe.owner_table_name(+) = 'HZ_PARTIES'
and hcpe.owner_table_id(+) = hzr.party_id
and hcpe.contact_point_type(+) = 'EMAIL'
and hcpp.status (+)='A'
and hcpf.status (+)='A'
and hcpe.status (+)='A'
and hps.party_id = hzr.object_id
and pvs.party_site_id = hps.party_site_id
and pv.vendor_id = pvs.vendor_id
and exists
( select 1
from ap_supplier_contacts ascs
where (ascs.inactive_date is null
or ascs.inactive_date > sysdate)
and hzr.relationship_id = ascs.relationship_id
and hzr.party_id = ascs.rel_party_id
and hps.party_site_id = ascs.org_party_site_id
and hzr.subject_id = ascs.per_party_id
)
and pv.vendor_id in (select vendor_id from vendors)
)
, bank as
(
select pv.vendor_id vendor_id
, ss.vendor_site_id vendor_site_id
, hopbank.bank_or_branch_number bank_number
, hopbranch.bank_or_branch_number branch_number
, eba.bank_account_num bank_account_num
, eba.bank_account_name bank_account_name
, piu.start_date bank_use_start_date
, piu.end_date bank_use_end_date
, piu.order_of_preference bank_priority
from iby_ext_bank_accounts eba
, iby_external_payees_all payee
, iby_pmt_instr_uses_all piu
, ap_supplier_sites_all ss
, ap_suppliers pv
, hz_organization_profiles hopbank
, hz_organization_profiles hopbranch
where 1=1
and eba.bank_id = hopbank.party_id
and eba.branch_id = hopbranch.party_id
and payee.payment_function = 'PAYABLES_DISB'
and payee.party_site_id = ss.party_site_id
and payee.supplier_site_id = ss.vendor_site_id
and payee.org_id = ss.org_id
and payee.org_type = 'OPERATING_UNIT'
and payee.ext_payee_id = piu.ext_pmt_party_id
and piu.payment_flow = 'DISBURSEMENTS'
and piu.instrument_type = 'BANKACCOUNT'
and piu.instrument_id = eba.ext_bank_account_id
and piu.start_date < sysdate
and ( piu.end_date is null or
piu.end_date > sysdate
)
and ss.vendor_id = pv.vendor_id
and pv.vendor_id in (select vendor_id from vendors)
)
-- select distinct v.*, s.*, c.*, b.*
select distinct v.vendor_id supplier_id
, v.vendor_number supplier_num
, v.vendor_name supplier_name
, v.vendor_type_lookup_code supplier_type
, s.terms_name terms_name
, s.tax_code invoice_tax_code
, s.vat_registration_num vat_registration_num
, s.vendor_site_code site_code
, s.ss_address_line1 address1
, s.ss_address_line2 address2
, s.ss_address_line3 address3
, s.ss_city suburb
, s.ss_state state
, s.ss_zip post_code
, s.ss_country country
, s.ss_payment_method_code payment_method
, b.bank_account_name bank_account_name
, b.bank_number bank_number
, b.branch_number branch_number
, b.bank_account_num bank_account_num
, s.ss_remit_advice_email remittance_email
, s.ss_remit_advice_deliv_meth notification_method
, c.c_first_name contact_first_name
, c.c_last_name contact_last_name
, c.c_person_title contact_title
, c.c_email_address contact_email
, c.c_phone_area_code contact_ph_area_code
, c.c_phone_number contact_ph_number
, c.c_fax_area_code contact_fax_area_code
, c.c_fax_number contact_fax_number
from vend v
, site s
, cont c
, bank b
where v.vendor_id = s.vendor_id (+)
and s.vendor_id = b.vendor_id (+)
and s.vendor_site_id = b.vendor_site_id (+)
and s.vendor_id = c.vendor_id (+)
and s.vendor_site_id = c.vendor_site_id (+)
and nvl(b.bank_priority,-1) = (select nvl(min(bank_priority),-1)
from bank b2
where b2.vendor_id = b.vendor_id
and b2.vendor_site_id = b.vendor_site_id)
order by 3,1,2,4,5,6,7,8,9,10,11,12,13;
Catch ya!
Gareth
This is a post from Gareth's blog at http://garethroberts.blogspot.com
I often get asked to take a look at an Oracle eBusiness Suite concurrent request to see what it is doing, this can come from a few different angles:
There are a number of strategies to track and trace where things are at for a running request, these include:
So without further ado, let's take a look at the following sweet query. UPDATE: 23-Aug-2012 fixed multi rows due to missing join on inst_id between gv$session and gv$process. Also note for non-RAC environments change gv$ to v$ and remove joins to sys.v_$active_instances:
set pages 9999 feed on lines 150
col user_concurrent_program_name format a40 head PROGRAM trunc
col elapsed format 9999
col request_id format 9999999 head REQUEST
col user_name format a12
col oracle_process_id format a5 head OSPID
col inst_name format a10
col sql_text format a30
col outfile_tmp format a30
col logfile_tmp format a30
REM *********************
REM **** RAC VERSION ****
REM *********************
select /*+ ordered */
fcp.user_concurrent_program_name
, fcr.request_id
, round(24*60*( sysdate - actual_start_date )) elapsed
, fu.user_name
, fcr.oracle_process_id
, sess.sid
, sess.serial#
, inst.inst_name
, sa.sql_text
, cp.plsql_dir || '/' || cp.plsql_out outfile_tmp
, cp.plsql_dir || '/' || cp.plsql_log logfile_tmp
from apps.fnd_concurrent_requests fcr
, apps.fnd_concurrent_programs_tl fcp
, apps.fnd_concurrent_processes cp
, apps.fnd_user fu
, gv$process pro
, gv$session sess
, gv$sqlarea sa
, sys.v_$active_instances inst
where fcp.concurrent_program_id = fcr.concurrent_program_id
and fcp.application_id = fcr.program_application_id
and fcr.controlling_manager = cp.concurrent_process_id
and fcr.requested_by = fu.user_id (+)
and fcr.oracle_process_id = pro.spid (+)
and pro.addr = sess.paddr (+)
and pro.inst_id = sess.inst_id (+)
and sess.sql_address = sa.address (+)
and sess.sql_hash_value = sa.hash_value (+)
and sess.inst_id = inst.inst_number (+)
and fcr.phase_code = 'R' /* only running requests */
;
REM *********************
REM ** NON-RAC VERSION **
REM *********************
select /*+ ordered */
fcp.user_concurrent_program_name
, fcr.request_id
, round(24*60*( sysdate - actual_start_date )) elapsed
, fu.user_name
, fcr.oracle_process_id
, sess.sid
, sess.serial#
, sa.sql_text
, cp.plsql_dir || '/' || cp.plsql_out outfile_tmp
, cp.plsql_dir || '/' || cp.plsql_log logfile_tmp
from apps.fnd_concurrent_requests fcr
, apps.fnd_concurrent_programs_tl fcp
, apps.fnd_concurrent_processes cp
, apps.fnd_user fu
, v$process pro
, v$session sess
, v$sqlarea sa
where fcp.concurrent_program_id = fcr.concurrent_program_id
and fcp.application_id = fcr.program_application_id
and fcr.controlling_manager = cp.concurrent_process_id
and fcr.requested_by = fu.user_id (+)
and fcr.oracle_process_id = pro.spid (+)
and pro.addr = sess.paddr (+)
and sess.sql_address = sa.address (+)
and sess.sql_hash_value = sa.hash_value (+)
and fcr.phase_code = 'R' /* only running requests */
;
PROGRAM REQUEST ELAPSED USER_NAME OSPID SID SERIAL# INST_NAME SQL_TEXT OUTFILE_TMP LOGFILE_TMP
---------------------------------------- -------- ------- ------------ ----- ---------- ---------- ---------- ------------------------------ ------------------------------ ------------------------------
Workflow Background Process 2960551 1 VIRTUATE 24814 130 29699 APPLPROD1 BEGIN WF_ENGINE.BACKGROUNDCONC /usr/tmp/o0068194.tmp /usr/tmp/l0068194.tmp
URRENT(:errbuf,:rc,:A0,:A1,:A2
,:A3,:A4,:A5); END;
1 row selected.
From the above we can see key information:
We can break out the above into a few queries and procedures to drill into specific information information from the core EBS tables and DBA v$ views
col user_concurrent_program_name format a40 head PROGRAM trunc col elapsed format 9999 col request_id format 9999999 head REQUEST col user_name format a12 col oracle_process_id format a5 head OSPID select fcp.user_concurrent_program_name , fcr.request_id , round(24*60*( sysdate - actual_start_date )) elapsed , fu.user_name , fcr.oracle_process_id from apps.fnd_concurrent_requests fcr , apps.fnd_concurrent_programs_tl fcp , apps.fnd_user fu where fcp.concurrent_program_id = fcr.concurrent_program_id and fcp.application_id = fcr.program_application_id and fu.user_id = fcr.requested_by and fcr.phase_code = 'R'; PROGRAM REQUEST ELAPSED USER_NAME OSPID ---------------------------------------- -------- ------- ------------ ----- Virtuate GL OLAP Data Refresh 2960541 5 VIRTUATE 21681
col inst_name format a10 col sql_text format a30 col module format a20 REM ********************* REM **** RAC VERSION **** REM ********************* select sess.sid , sess.serial# , sess.module , sess.inst_id , inst.inst_name , sa.fetches , sa.runtime_mem , sa.sql_text , pro.spid from gv$sqlarea sa , gv$session sess , gv$process pro , sys.v_$active_instances inst where sa.address = sess.sql_address and sa.hash_value = sess.sql_hash_value and sess.paddr = pro.addr and sess.inst_id = pro.inst_id and sess.inst_id = inst.inst_number (+) and pro.spid = &OSPID_from_running_request; REM ********************* REM ** NON-RAC VERSION ** REM ********************* select sess.sid , sess.serial# , sess.module , sa.fetches , sa.runtime_mem , sa.sql_text , pro.spid from v$sqlarea sa , v$session sess , v$process pro where sa.address = sess.sql_address and sa.hash_value = sess.sql_hash_value and sess.paddr = pro.addr and pro.spid = &OSPID_from_running_request;
If you're running something that has long SQL statements, get the full SQL Statement by selecting from v$sqltext_with_newlines as follows
select t.sql_text from v$sqltext_with_newlines t , v$session s where s.sid = &SID and s.sql_address = t.address order by t.piece
col outfile format a30 col logfile format a30 select cp.plsql_dir || '/' || cp.plsql_out outfile , cp.plsql_dir || '/' || cp.plsql_log logfile from apps.fnd_concurrent_requests cr , apps.fnd_concurrent_processes cp where cp.concurrent_process_id = cr.controlling_manager and cr.request_id = &request_id; OUTFILE LOGFILE ------------------------------ ------------------------------ /usr/tmp/PROD/o0068190.tmp /usr/tmp/PROD/l0068190.tmp REM Now tail log file on database node to see where it is at, near realtime REM tail -f /usr/tmp/l0068190.tmp
Then on the Database node you can tail -f the above plsql_out or plsql_log files to see where program is at. Combine this with good logging techniques (date/time stamp on each entry) and you'll be able to know where your program is at.
If locks are the potential problem, then drill into those:
set lines 150
col object_name format a32
col mode_held format a15
select /*+ ordered */
fcr.request_id
, object_name
, object_type
, decode( l.block
, 0, 'Not Blocking'
, 1, 'Blocking'
, 2, 'Global'
) status
, decode( v.locked_mode
, 0, 'None'
, 1, 'Null'
, 2, 'Row-S (SS)'
, 3, 'Row-X (SX)'
, 4, 'Share'
, 5, 'S/Row-X (SSX)'
, 6, 'Exclusive'
, to_char(lmode)
) mode_held
from apps.fnd_concurrent_requests fcr
, gv$process pro
, gv$session sess
, gv$locked_object v
, gv$lock l
, dba_objects d
where fcr.phase_code = 'R'
and fcr.oracle_process_id = pro.spid (+)
and pro.addr = sess.paddr (+)
and sess.sid = v.session_id (+)
and v.object_id = d.object_id (+)
and v.object_id = l.id1 (+)
;
REQUEST_ID OBJECT_NAME OBJECT_TYPE STATUS MODE_HELD
---------- -------------------------------- ------------------- ------------ ---------------
1070780 VIRTUATE_GL_OLAP_REFRESH TABLE Not Blocking Exclusive
So there you have it - enough tools to keep you happy Track n Tracing! Maybe next time we'll look at tracing with bind / waits or PL/SQL Profiling concurrent programs
Catch ya!From time to time I create enhancement requests or bugs, commonly on Oracle eBusiness Suite. Additionally I come across enhancement requests that I think deserve more visibility. This page is dedicated to that cause. If you have an Oracle Enhancement Request (ER) or Bug you would like to raise the visibility on, please feel free to comment or contact me.
If you like the looks of one of the Enhancement Requests noted below, log a Service Request (SR) on My Oracle Support (MOS) and ask for your MOS Customer record to be attached to the ER / Bug.
| Created | Logged By | Bug / ER | Status | Product | Description |
|---|---|---|---|---|---|
| 2015/02/22 | See ER | 9130428 | Open | AR | Need a Standard Way to Export the Aging 7 Buckets By Account Report Output to Excel |
| 2013/10/07 | See ER | 17069048 | Open | BIP | Enable use of recursive with clause |
| 2013/06/05 | See ER | 12872320 | Open | BIP | Request to add autofilter functionality to native Excel template (binary) |
| 2012/01/22 | Gareth | 13616572 | Open | BIP | BI Publisher RTF unable to show a fixed number of lines for a word wrapped cell |
| 2011/05/13 | See ER | 12423249 | Open | BIP | Adding support of SMTP username/password to "XAPI:EMAIL" bursting for E-Business Suite (EBS) 12.1.X |
| 2010/11/09 | See ER | 8208646 | Open | AP/IBY | R12 Unable to send Separate Remittance Advice as email attachment in PDF |
| 2010/08/11 | Gareth | 10019593 | Rejected | AP/IBY | ARXRWMAI - In Receipts Summary form, cannot query by payment server id - PSON (Payment Server Order Number) |
| 2010/07/14 | See ER | 9908713 | Open | BIP | Need the DeliveryManager API to have a trackback variable for bounced emails |
| 2010/07/18 | See ER | 9918364 | Open | BIP | Need email body to be set to UTF-8 charset when using East Asian language emails |
| 2010/07/08 | Gareth | 9891120 | Open | eAM | Need hook to extend EAM Maintenance Work Order Detail Report |
| 2010/06/19 | Gareth | 9834226 | Open | AP | Need to embed email images Payables/Payments "Send Separate Remittance Advices" |
| 2009/01/07 | See ER | 7694052 | Open | AP | Does the Supplier Open Interface API Support Updates to Supplier Information? |
| 2005/03/25 | See ER | 4262148 | Open | AP | Need Public API to manage custom AP Invoice Holds |
| Status Code | Definition |
|---|---|
| 11 | Code/Hardware Bug (Response/Resolution) |
| 12 | To External (User Group) Review |
| 15 | Enhancement Req. Internal(Oracle) Review |
| 19 | Approved for User Group Voting |
| 20 | To Requestor, Need More Info |
| 22 | Approved for Future Release |
| 23 | Scheduled for Future Release |
| 50 | ER Waiting for Base ER Fix |
| 82 | QA/PM to Eval ER, Chg Delivered Internal |
| 97 | Closed, Enhancement Rejected |
| 98 | Closed, Enhancement Implemented |
Catch ya!
Gareth
This is a post from Gareth's blog at http://garethroberts.blogspot.com
