To check SQL Profile : select NAME,SIGNATURE ,STATUS,FORCE_MATCHING from dba_sql_profiles; ENABLE/DISABLE/DROP EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE(‘coe_5273fz2cqkk80_3455548535′,’STATUS’,’DISABLED’); exec dbms_sqltune.drop_sql_profile(‘coe_5273fz2cqkk80_3455548535’); SQL Profiles for a sql_id set lines 1000 pages 9999 col name for a30 col…
Read Moreone script to Identify all performace issue related to sql query Set echo off set trimspool on set define on column filename new_val filename select to_char(sysdate, ‘yyyymmdd-hh-mi-ss’ ) filename from…
Read MoreUndo Related Queries To check retention guarantee for undo tablespace select tablespace_name,status,contents,logging,retention from dba_tablespaces where tablespace_name like ‘%UNDO%’; To show ACTIVE/EXPIRED/UNEXPIRED Extents of Undo Tablespace select tablespace_name, status, count(extent_id) “Extent…
Read MoreLong running Concurrent Requests:- 1.How to Determine Which Manager Ran a Specific Concurrent Request? col USER_CONCURRENT_QUEUE_NAME for a100 select b.USER_CONCURRENT_QUEUE_NAME from fnd_concurrent_processes a, fnd_concurrent_queues_vl b, fnd_concurrent_requests c where a.CONCURRENT_QUEUE_ID =…
Read More/*Upload the customer data that needs to be updated in a temp table*/ CREATE TABLE scratch.RITM0669580 (account_number VARCHAR2(30), tax_payer_id VARCHAR2(20), tax_reg_num VARCHAR2(50), status VARCHAR2(1), message VARCHAR2(1000)); / set serveroutput on;…
Read MoreDECLARE x_return_status VARCHAR2 (200) := NULL; x_msg_count NUMBER := 0; x_msg_data VARCHAR2 (200) := NULL; t_output VARCHAR2 (200) := NULL; t_msg_dummy VARCHAR2 (200) := NULL; l_payee_upd_status iby_disbursement_setup_pub.ext_payee_update_tab_type; p_external_payee_tab_type iby_disbursement_setup_pub.external_payee_tab_type; p_ext_payee_id_tab_type…
Read MoreDECLARE x_return_status VARCHAR2 (200) := NULL; x_msg_count NUMBER := 0; x_msg_data VARCHAR2 (200) := NULL; t_output VARCHAR2 (200) := NULL; t_msg_dummy VARCHAR2 (200) := NULL; l_payee_upd_status iby_disbursement_setup_pub.ext_payee_update_tab_type; p_external_payee_tab_type iby_disbursement_setup_pub.external_payee_tab_type; p_ext_payee_id_tab_type…
Read Moreselect aia.INVOICE_NUM ,aia.INVOICE_AMOUNT ,aia.CREATION_DATE,aia.INVOICE_DATE,aia.SOURCE,aia.INVOICE_TYPE_LOOKUP_CODE INVOICE_TYPE, (select aca.STATUS_LOOKUP_CODE from apps.ap_invoice_payments_all aipa, apps.ap_checks_all aca where aipa.check_id = aca.check_id and nvl(aipa.reversal_flag,’N’) <> ‘Y’ and aipa.invoice_id = aia.invoice_id and aipa.org_id = aia.org_id and rownum…
Read MoreSELECT ( SELECT hou.name FROM apps.hr_operating_units hou WHERE hou.organization_id = aerpa.org_id ) operating_unit, aerpa.prompt expense_type, aerpa.flex_description gl_description, aerpa.flex_concactenated gl_code, aerpa.category_code, aerpa.end_date FROM apps.ap_expense_report_params_all aerpa WHERE aerpa.org_id IN ( 382, 402,…
Read Moreselect (select category_concat_segs from apps.mtl_item_categories_v where inventory_item_id = msib.inventory_item_id and organization_id = msib.organization_id and category_set_name = ‘Tax Category Code Set’) tax_category, msib.segment1 item_number, msib.enabled_flag active_status, msib.description item_description, msib.attribute4 form_factor, gcck.segment5…
Read More