Posts Tagged ‘Oracle DBA’

Oracle EBS Find Where Debug Is On

May 15th, 2018, posted in Oracle Queries
Share

Oracle EBS Find Where Debug Is On

If you have Debug is on at Site level then it will Impact the performance of the Instance, here is the script which will help you to quickly check your EBS Instance if ‘Debug’ is on (all level) of the EBS.

 

set linesize 160
col USER_PROFILE_OPTION_NAME for a30
col CONTEXT for a30
col VALUE for a30
col NAME for a30
 
SELECT po.profile_option_name "NAME",
po.USER_PROFILE_OPTION_NAME,
decode(to_char(pov.level_id),
'10001', 'SITE',
'10002', 'APP',
'10003', 'RESP',
'10005', 'SERVER',
'10006', 'ORG',
'10004', 'USER', '???') "LEV",
decode(to_char(pov.level_id),
'10001', '',
'10002', app.application_short_name,
'10003', rsp.responsibility_key,
'10005', svr.node_name,
'10006', org.name,
'10004', usr.user_name,
'???') "CONTEXT",
pov.profile_option_value "VALUE"
FROM FND_PROFILE_OPTIONS_VL po,
FND_PROFILE_OPTION_VALUES pov,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
WHERE po.user_profile_option_name like '%Debug%'
AND pov.profile_option_value='Y'
AND pov.application_id = po.application_id
AND pov.profile_option_id = po.profile_option_id
AND usr.user_id (+) = pov.level_value
AND rsp.application_id (+) = pov.level_value_application_id
AND rsp.responsibility_id (+) = pov.level_value
AND app.application_id (+) = pov.level_value
AND svr.node_id (+) = pov.level_value
AND org.organization_id (+) = pov.level_value;

Sample ouptut:

 

NAME                           USER_PROFILE_OPTION_NAME       LEV      CONTEXT                        VALUE
---------------------------    ----------------------------   ------   ---------------------------    ---------------
AFLOG_ENABLED                  FND: Debug Log Enabled         USER     SA-USER1                       Y
FND_CONC_ALLOW_DEBUG           Concurrent: Allow Debugging    RESP     XXXX_CE_SUP_MY-MYR-GE          Y
PRINT_DEBUG                    FA: Print Debug                USER     SA-USER2                       Y
PO_SET_DEBUG_WORKFLOW_ON       PO: Set Debug Workflow ON      USER     SA-USER3                       Y
PA_DEBUG_MODE                  PA: Debug Mode                 USER     SA-USER4                       Y
SO_DEBUG                       OE: Debug                      SITE                                    Y
MSC_JAVA_DEBUG                 MSC: Enable Java Debug         SITE                                    Y
 
7 rows selected.
Share

ORA-01450: maximum key length (6398) exceeded

May 13th, 2018, posted in Oracle Queries
Share

ORA-01450: maximum key length (6398) exceeded,ORA 01450,Ora error,ORA 01450 errors,Oracle ERP,Oracle ,ERP,Oracle ERP Application,Oracle Application,Oracle DBA,Oracle Forms,racle Apps,Apps DBA,ERP Application,Repository Creation Utility - Warning | ORA-01450: maximum key length (6398) exceed,Repository Creation Utility Warning,ORA-01450: maximum key length (6398) exceed,Warning ORA-01450: maximum key length (6398) exceed,

Cause 

the errors seen above are encountered because the NLS_LENGTH_SEMANTICS initialisation parameter has been set to CHAR. This is not a supported setting for this parameter in an Oracle Fusion Middle ware installation. This is documented in the Release Notes for each platform.

Solution 

To remove the ORA-01450 errors when creating the MDS and SOAINFRA components ensure that the requirement for NLS_LENGTH_SEMANTICS=BYTE is met.

To check the current setting of NLS_LENGTH_SEMANTICS, login to the target database as SYSDBA and issue the following command:

SQL> show parameter NLS_LENGTH_SEMANTICS

NAME                     TYPE        VALUE
———————— ———– ——-
nls_length_semantics     string      CHAR

SQL> alter system set nls_length_semantics=BYTE;

System altered.

SQL> show parameter NLS_LENGTH_SEMANTICS

NAME                     TYPE        VALUE
———————— ———– ——-
nls_length_semantics     string      BYTE

After resetting the parameter you should restart your database for the change to take effect.

Share

Find Concurrent Manager Jobs By Query

May 6th, 2018, posted in Oracle Queries
Share

Query :

 

select sess.sid,
 sess.sql_hash_value,
 oracle_process_id OS_PROCESS_ID,
 fusr.description user_name ,
 fcp.user_concurrent_program_name progName,
 to_char(actual_Start_date,'DD-MON-YYYY HH24:MI:SS') StartDate,
 request_id RequestId,
 (sysdate - actual_start_date)*24*60 ETime_in_Mins
from apps.fnd_concurrent_requests fcr,
 apps.fnd_concurrent_programs_tl fcp,
 apps.fnd_user fusr,
 v$session sess
where fcp.concurrent_program_id = fcr.concurrent_program_id
 and fcr.program_application_id = fcp.application_id
 and fcp.language = 'US'
 and fcr.phase_code = 'R'
 and fcr.status_code = 'R'
 and fcr.requested_by = fusr.user_id
 and fcr.oracle_session_id = sess.audsid (+)
-- and p.addr = sess.paddr
 order by 6 DESC
Share

RECEIPT APPLICATION ERROR: APP-FND-00531

April 28th, 2018, posted in Oracle Queries
Share
제품 : FIN_AR
작성날짜 : 2003-11-18
RECEIPT APPLICATION ERROR: APP-FND-00531
========================================
PURPOSE
-------
Receipt Application 시 발생하는 error를 효과적으로 해결한다.
Problem Description
-------------------
Applications 11.0.3:
Transaction을 생성 한 후 Receipt 과 Apply 할 때 다음과 같은 error 가 발생하였다.
APP-FND-00531 Amount Cannot Be Correctly Formatted
The Amount XXX.XXX Could Not Be Formally Formatted.
The Correct Format Is 999.99 And -999.99

Workaround
----------
1. 해당 data 를 bakcup 받아 놓는다.
2. 다음 update script을 수행한다.
Update ar_payment_schedules_all
set amount_due_original = round(amount_due_original,2),
amount_due_remaining= round(amount_due_remaining,2),
acctd_amount_due_remaining = round (acctd_amount_due_remaining, 2)
where customer_trx_id=xxx
update ra_customer_trx_lines_all
set extended_amount=round(extended_amount,2)
where customer_trx_id=xxx and line_type='TAX'
update ra_cust_trx_line_gl_dist_all
set amount=round(amount,2)
where customer_trx_id=xxx

Solution Description
--------------------------
Applications 11.0.3에서 Tax / Freight Defaultation Program이 rounding 처리를 잘 못하여 ra_cust_trx_line_gl_dist_all 에 데이타가 corrupted 된 것으로 보인다.
잘 못된 data를 rounding 처리하여 저장해준다.
Reference Documents
-------------------
Note 247018.1
Share

Select Number And Convert Number From English To Arabic In Oracle

April 23rd, 2018, posted in Oracle Queries
Share
CREATE FUNCTION numToEasternArabic(
  in_value IN NUMBER
) RETURN NVARCHAR2 DETERMINISTIC
IS
  p_num  VARCHAR2(100) := TO_CHAR( in_value );
  p_char CHAR(1);
  o_str  NVARCHAR2(100);
BEGIN
  FOR i IN 1 .. LENGTH( p_num ) LOOP
    p_char := SUBSTR( p_num, i, 1 );
    o_str := o_str
          || CASE p_char
             WHEN '.'
             THEN N'.'
             ELSE UNISTR(
                    '\' || TO_CHAR(
                             TO_NUMBER( p_char ) + 660,
                             'FM0000'
                           )
                  )
             END;
  END LOOP;
  RETURN o_str;
END;
/


Query 1:

SELECT numToEasternArabic( 1438 )
FROM   DUAL

Results:

| NUMTOEASTERNARABIC(1438) |
|--------------------------|
|                     ١٤٣٨ |
 
Share