Thursday 7 December 2023

Oracle Objects Being Accessed by Programs/Users

Objects Being Accessed:

select client_identifier,program,state,object,module, action,s.* 

from gv$session s , gv$access a 

where status='ACTIVE'

and a.sid = s.sid and a.inst_id = s.inst_id
and object in ('Object_name');


Objects are Locked by DDL:

select * from
DBA_DDL_LOCKS
where name = :name_object;

Wednesday 2 February 2022

DB Lock in the Oracle Objects

 SELECT object_name, machine, s.sid, s.serial#, s.*

FROM gv$locked_object l, dba_objects o, gv$session s  

WHERE l.object_id = o.object_id

AND l.session_id = s.sid;


Release SESSION SQL:  
--alter system kill session 'sid, serial#';  
ALTER system kill session '23, 1647';

Tuesday 6 April 2021

Flexfields server-side validation package report error. virtual_value_set() exception : ORA-06502: PLSQL numeric or value error: number precision too large column#x

First of all we need to understand that this issue is coming from a seeded package. i.e. fnd_flex_server1

In this package you can search with the procedure name you are seeing in the error message to confirm where the error is coming from.

The issue might be due to either KFF or DFF as the same package is being used for both the flex fields.

In my case the below query was returning the record and it was failing while iterating the data from the cursor.

You need to pass table_id, application id, flex field name, context to get the records using the cursor.

Query:

SELECT g.end_user_column_name, g.application_column_name,

              c.column_type, c.width, g.required_flag,

              g.security_enabled_flag, g.concatenation_description_len,

              g.default_type, g.default_value, g.flex_value_set_id,

              g.runtime_property_function,

              NULL

       FROM fnd_descr_flex_column_usages g, fnd_columns c

      WHERE g.application_id = descstruct.application_id

        AND g.descriptive_flexfield_name = descstruct.desc_flex_name

        AND g.descriptive_flex_context_code = descstruct.desc_flex_context

        AND g.enabled_flag = 'Y'

        AND c.application_id = t_apid

        AND c.table_id = t_id

        AND c.column_name = g.application_column_name

      ORDER BY g.column_seq_num;

 



Sunday 21 February 2021

Check Rollback Blocks Post Concurrent Request is Cancelled

  SELECT inst_id,

         usn,

         state,

         undoblockstotal                     "Total",

         undoblocksdone                      "Done",

         undoblockstotal - undoblocksdone    "ToDo",

         inst_id,

         xid,

         rcvservers,

         DECODE (

             cputime,

             0, 'unknown',

               SYSDATE

             + (  (  (undoblockstotal - undoblocksdone)

                   / (undoblocksdone / DECODE (cputime, 0, 1)))

                / 86400))                    "Estimated time to complete"

    FROM gv$fast_start_transactions

   WHERE state != 'RECOVERED'

ORDER BY 1, 2;

Wednesday 28 October 2020

End Date a Responsibility For an FND User(Backend Script)

 DECLARE

    v_user_name             VARCHAR2 (100) := 'ROHAN_RAJPUT';

    v_responsibility_name   VARCHAR2 (100) := 'AR XX Superuser';  -- Resp name

    v_application_name      VARCHAR2 (100) := NULL;

    v_responsibility_key    VARCHAR2 (100) := NULL;

    v_security_group        VARCHAR2 (100) := NULL;

BEGIN

    SELECT fa.application_short_name,

           fr.responsibility_key,

           frg.security_group_key

      INTO v_application_name, v_responsibility_key, v_security_group

      FROM fnd_responsibility     fr,

           fnd_application        fa,

           fnd_security_groups    frg,

           fnd_responsibility_tl  frt

     WHERE     fr.application_id = fa.application_id

           AND fr.data_group_id = frg.security_group_id

           AND fr.responsibility_id = frt.responsibility_id

           AND frt.LANGUAGE = USERENV ('LANG')

           AND frt.responsibility_name = v_responsibility_name;


    fnd_user_pkg.delresp (username         => v_user_name,

                          resp_app         => v_application_name,

                          resp_key         => v_responsibility_key,

                          security_group   => v_security_group);


    COMMIT;


    DBMS_OUTPUT.put_line (

           'Responsiblity '

        || v_responsibility_name

        || ' is removed from the user '

        || v_user_name

        || ' Successfully');

EXCEPTION

    WHEN OTHERS

    THEN

        DBMS_OUTPUT.put_line (

               'Error encountered while deleting responsibilty from the user and the error is '

            || SQLERRM);

END;

/

Friday 11 September 2020

ORA-04061: existing state of has been invalidated ORA-04061: existing state of package body "package name" has been invalidated

Ideally this error comes when the package in question is Invalid but sometime we see this error even if our package is Valid.


Instead of getting into too much details and debugging just request your DBAs to recompile the package or do it yourself if you are a confident developer with apps access ;) 


To compile both of sepc and body :-

                                Alter package pkg_name compile;


To compile only Body :-

                                Alter package pkg_name compile body;

If its recompiled without any error then just retest.

That's it.



Thursday 2 April 2020

Check Scheduled Concurrent Programs in Oracle Apps

SELECT fcr.request_id,
       DECODE (fcpt.user_concurrent_program_name,
               'Report Set', 'Report Set:' || fcr.description,
               fcpt.user_concurrent_program_name)
          CONC_PROG_NAME,
       argument_text PARAMETERS,
       NVL2 (fcr.resubmit_interval,
             'PERIODICALLY',
             NVL2 (fcr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE'))
          PROG_SCHEDULE_TYPE,
       DECODE (
          NVL2 (fcr.resubmit_interval,
                'PERIODICALLY',
                NVL2 (fcr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')),
          'PERIODICALLY',    'EVERY '
                          || fcr.resubmit_interval
                          || ' '
                          || fcr.resubmit_interval_unit_code
                          || ' FROM '
                          || fcr.resubmit_interval_type_code
                          || ' OF PREV RUN',
          'ONCE',    'AT :'
                  || TO_CHAR (fcr.requested_start_date, 'DD-MON-RR HH24:MI'),
          'EVERY: ' || fcrc.class_info)
          PROG_SCHEDULE,
       fu.user_name USER_NAME,
       requested_start_date START_DATE
  FROM apps.fnd_concurrent_programs_tl fcpt,
       apps.fnd_concurrent_requests fcr,
       apps.fnd_user fu,
       apps.fnd_conc_release_classes fcrc
 WHERE     fcpt.application_id = fcr.program_application_id
       AND fcpt.concurrent_program_id = fcr.concurrent_program_id
       AND fcr.requested_by = fu.user_id
       AND fcr.phase_code = 'P'
       AND fcr.requested_start_date > SYSDATE
       AND fcpt.LANGUAGE = 'US'
       AND fcrc.release_class_id(+) = fcr.release_class_id
       AND fcrc.application_id(+) = fcr.release_class_app_id;