Tuesday, November 15, 2022

 Find Client IP Address logged in to EBS 12

Below query will provide the list of all application users connected to EBS with their client IP-Addresses.

SELECT distinct 

        d.user_name

       ,c.CLIENTIP

FROM fnd_logins c, 

       fnd_user d

WHERE d.user_id = c.user_id

AND c.end_time IS NULL;

Friday, February 18, 2022

 Execute/Run Tuning Advisor from backend

step1 : login as sysdba or with user with tuning advisor priv

Step 2: Identify the sql for which you want to run tuning advisor

e.g I am using sql-id cxa9cy8vz51gw

step 3: Create Tuning Task

BEGIN

l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (

sql_id => 'cxa9cy8vz51gw',

scope => DBMS_SQLTUNE.scope_comprehensive,

time_limit => 500,

task_name => 'cxa9cy8vz51gw_tuning_task11',

description => 'Tuning task1 for statement cxa9cy8vz51gw');

DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

END;

/ 

Step 4:Execute Tuning task:

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'cxa9cy8vz51gw_tuning_task11');

Step 5: Get the Tuning advisor report.

set long 65536

set longchunksize 65536

set linesize 100

select dbms_sqltune.report_tuning_task('cxa9cy8vz51gw_tuning_task11') from dual;



Wednesday, January 15, 2020

huge archive log genaration -Cause

Huge archive log generation .. what to do? what is causing it
Step 1:

Know which table / object has more number of changes during the problematic window:

SELECT to_char(begin_interval_time,'YYYY_MM_DD HH24:MI') snap_time,
        dhsso.object_name,
        sum(db_block_changes_delta) as maxchages
  FROM dba_hist_seg_stat dhss,
         dba_hist_seg_stat_obj dhsso,
         dba_hist_snapshot dhs
  WHERE dhs.snap_id = dhss.snap_id
    AND dhs.instance_number = dhss.instance_number
    AND dhss.obj# = dhsso.obj#
    AND dhss.dataobj# = dhsso.dataobj#
    AND begin_interval_time BETWEEN to_date('2013_05_22 17','YYYY_MM_DD HH24')
                                           AND to_date('2013_05_22 21','YYYY_MM_DD HH24')
  GROUP BY to_char(begin_interval_time,'YYYY_MM_DD HH24:MI'),
           dhsso.object_name order by maxchages asc;
  
The below objects have max number of changes in them during the high archive generation period.

SNAP_TIME        OBJECT_NAME                     MAXCHAGES
---------------- ------------------------------ ----------
2013_05_22 17:00 MGMT_CURRENT_AVAILABILITY_PK         7104
2013_05_22 17:00 MGMT_AVAIL_MARKER_PK                 7312
2013_05_22 18:00 MGMT_METRICS_1HOUR_PK_INT           20112
2013_05_22 17:00 MGMT_METRICS_1HOUR_PK_INT           20864
2013_05_22 20:00 MGMT_METRICS_1HOUR_PK_INT           21152
2013_05_22 19:01 MGMT_METRICS_1HOUR_PK_INT           23088
2013_05_22 17:00 MGMT_COLLECTIONS                    95392
2013_05_22 20:00 MGMT_COLLECTIONS                   110080
2013_05_22 17:00 MGMT_CURRENT_METRICS_PK            110512
2013_05_22 20:00 MGMT_CURRENT_METRICS_PK            110992
2013_05_22 18:00 MGMT_CURRENT_METRICS_PK            112304
2013_05_22 19:01 MGMT_CURRENT_METRICS_PK            113584
2013_05_22 19:01 MGMT_COLLECTIONS                   114656
2013_05_22 18:00 MGMT_COLLECTIONS                   114752
2013_05_22 20:00 MGMT_METRICS_RAW_PK_INT            127744
2013_05_22 18:00 MGMT_METRICS_RAW_PK_INT            129392
2013_05_22 17:00 MGMT_METRICS_RAW_PK_INT            129888
2013_05_22 19:01 MGMT_METRICS_RAW_PK_INT            130512
2013_05_22 17:00 MGMT_POLICY_ASSOC                  292576
2013_05_22 17:00 MGMT_POLICY_ASSOC_CFG              294128
2013_05_22 18:00 MGMT_POLICY_ASSOC                  367312
2013_05_22 20:00 MGMT_POLICY_ASSOC_CFG              368544
2013_05_22 20:00 MGMT_POLICY_ASSOC                  368560
2013_05_22 19:01 MGMT_POLICY_ASSOC_CFG              370432
2013_05_22 18:00 MGMT_POLICY_ASSOC_CFG              371504
2013_05_22 19:01 MGMT_POLICY_ASSOC                  377728
2013_05_22 17:00 MGMT_POLICY_ASSOC_CFG_PARAMS       434720
2013_05_22 18:00 MGMT_POLICY_ASSOC_CFG_PARAMS       551968
2013_05_22 20:00 MGMT_POLICY_ASSOC_CFG_PARAMS       554816
2013_05_22 19:01 MGMT_POLICY_ASSOC_CFG_PARAMS       560944
2013_05_22 17:00 MGMT_POLICY_ASSOC_CFG_PK_IDX       587168
2013_05_22 19:01 MGMT_POLICY_ASSOC_CFG_PK_IDX       737296
2013_05_22 20:00 MGMT_POLICY_ASSOC_CFG_PK_IDX       739120
2013_05_22 18:00 MGMT_POLICY_ASSOC_CFG_PK_IDX       742656

Step 2:

Once you know the objects ,get the SQL information realted to those objects:

The below are the SQLs causing more changes to MGMT_POLICY_ASSOC_CFG%.

SELECT to_char(begin_interval_time,'YYYY_MM_DD HH24:MI'),
         dbms_lob.substr(sql_text,4000,1),
         dhss.instance_number,
         dhss.sql_id,executions_delta,rows_processed_delta
  FROM dba_hist_sqlstat dhss,
         dba_hist_snapshot dhs,
         dba_hist_sqltext dhst
  WHERE upper(dhst.sql_text) LIKE '%MGMT_POLICY_ASSOC_CFG%'
    AND dhss.snap_id=dhs.snap_id
    AND dhss.instance_Number=dhs.instance_number
AND begin_interval_time BETWEEN to_date('2013_05_22 17','YYYY_MM_DD HH24')
                                           AND to_date('2013_05_22 21','YYYY_MM_DD HH24')
    AND dhss.sql_id = dhst.sql_id;




INSERT INTO MGMT_POLICY_ASSOC_CFG(CLEAR_MESSAGE, CLEAR_MESSAGE_NLSID, COLL_NAME, CONDITION_OPERATOR, EVAL_ORDER, FIXIT_J
OB, IS_PUSH, KEY_OPERATOR, KEY_VALUE, MESSAGE, MESSAGE_NLSID, NO_CLEAR_ON_NULL, NUM_OCCURRENCES, OBJECT_GUID, POLICY_GUI
D) VALUES ( :1, :2, NVL(:3, ' '), :4, :5, :6, :7, NVL(:8, 0), NVL(:9, ' '), :10, :11, :12, :13, :14, :15)
              1 71t5d196acgr9           186462                    0

2013_05_22 18:00
UPDATE MGMT_POLICY_ASSOC_CFG SET CLEAR_MESSAGE= :1 , CLEAR_MESSAGE_NLSID= :2 , CONDITION_OPERATOR= :3 , EVAL_ORDER= :4 ,
 FIXIT_JOB= :5 , IS_PUSH= :6 , MESSAGE= :7 , MESSAGE_NLSID= :8 , NO_CLEAR_ON_NULL= :9 , NUM_OCCURRENCES= :10  WHERE COLL
_NAME= NVL(:11, ' ') AND KEY_VALUE= NVL(:12, ' ') AND POLICY_GUID= HEXTORAW(:13) AND KEY_OPERATOR= NVL(:14, 0) AND OBJEC
T_GUID= HEXTORAW(:15)
              1 5xmfm787py0a3           186462               186462

2013_05_22 18:00
UPDATE MGMT_POLICY_ASSOC_CFG_PARAMS SET CRIT_THRESHOLD= :1 , WARN_THRESHOLD= :2  WHERE COLL_NAME= NVL(:3, ' ') AND KEY_V
ALUE= NVL(:4, ' ') AND POLICY_GUID= HEXTORAW(:5) AND KEY_OPERATOR= NVL(:6, 0) AND OBJECT_GUID= HEXTORAW(:7) AND PARAM_NA
ME= NVL(:8, ' ')
              1 dxdzv7ymv7vrk           186487               186487



2013_05_22 17:00
INSERT INTO MGMT_POLICY_ASSOC_CFG_PARAMS(COLL_NAME, CRIT_THRESHOLD, KEY_OPERATOR, KEY_VALUE, OBJECT_GUID, POLICY_GUID, W
ARN_THRESHOLD) VALUES ( NVL(:1, ' '), :2, NVL(:3, 0), NVL(:4, ' '), :5, :6, :7)
              1 a8d5br4z9m251           147609                    0


Step 3: Once you know the SQL ID , get the program and userid who is running it and intimate the user to take care of this query / Program.

As a DBA , you can increase the archive log backup frequency of the database / once you get the permission to kill the program /sql you can kill them as well.

Know which program is causing huge redo using the SQL IDs.

 SELECT instance_number, to_char(sample_time,'yyyy_mm_dd hh24:mi:ss'),
         user_id,
         program
  FROM dba_hist_active_sess_history
  WHERE sql_id in ('5xmfm787py0a3','bq28gpz5w5qqg','71t5d196acgr9','a8d5br4z9m251')
    AND snap_id BETWEEN 31856 AND 31860  2    3    4    5    6  ;

INSTANCE_NUMBER TO_CHAR(SAMPLE_TIME    USER_ID PROGRAM
--------------- ------------------- ---------- ----------------------------------------------------------------
              1 2013_05_22 17:26:22         25 OMS
              1 2013_05_22 18:20:22         25 OMS
              1 2013_05_22 18:40:31         25 OMS
              1 2013_05_22 19:56:41         25 OMS
              1 2013_05_22 19:34:41         25 OMS
              1 2013_05_22 19:14:11         25 OMS
              1 2013_05_22 20:49:00         25 OMS
              1 2013_05_22 20:36:20         25 OMS
              1 2013_05_22 20:25:50         25 OMS

Wednesday, June 12, 2019

FInd sid SERIAL# OF PID

Below query can be used to find details of PID runing on Server and its associated SID,Serial# at database level


col sid format 999999
col username format a20
col osuser format a15
select b.spid,a.sid, a.serial#,a.username, a.osuser
from v$session a, v$process b
where a.paddr= b.addr
and b.spid='&spid'
order by b.spid;


Thursday, February 21, 2019

Processes consuming Swap on Linux/Server


Processes consuming swap on Server

## Get swap space in Linux using bash for loop ##
for file in /proc/*/status ; do awk '/VmSwap|Name/{printf $2 " " $3}END{ print ""}' $file; done | sort -k 2 -n -r | less

## bash for loop ##
for file in /proc/*/status ; do awk '/VmSwap|Name/{printf $2 " " $3}END{ print ""}' $file; done


Script to find swap usage:

SUM=0
OVERALL=0
for DIR in `find /proc/ -maxdepth 1 -type d -regex "^/proc/[0-9]+"`
do
    PID=`echo $DIR | cut -d / -f 3`
    PROGNAME=`ps -p $PID -o comm --no-headers`
    for SWAP in `grep VmSwap $DIR/status 2>/dev/null | awk '{ print $2 }'`
    do
        let SUM=$SUM+$SWAP
    done
    if (( $SUM > 0 )); then
        echo "PID=$PID swapped $SUM KB ($PROGNAME)"
    fi
    let OVERALL=$OVERALL+$SUM
    SUM=0
done
echo "Overall swap used: $OVERALL KB"


Script to find swap usage of runing processes.
the below script should be run with root user to get the correct results

SCRIPT_NAME=`basename $0`;
SORT="kb";                 # {pid|kB|name} as first parameter, [default: kb]
[ "$1" != "" ] && { SORT="$1"; }

[ ! -x `which mktemp` ] && { echo "ERROR: mktemp is not available!"; exit; }
MKTEMP=`which mktemp`;
TMP=`${MKTEMP} -d`;
[ ! -d "${TMP}" ] && { echo "ERROR: unable to create temp dir!"; exit; }

>${TMP}/${SCRIPT_NAME}.pid;
>${TMP}/${SCRIPT_NAME}.kb;
>${TMP}/${SCRIPT_NAME}.name;

SUM=0;
OVERALL=0;
    echo "${OVERALL}" > ${TMP}/${SCRIPT_NAME}.overal;

for DIR in `find /proc/ -maxdepth 1 -type d -regex "^/proc/[0-9]+"`;
do
    PID=`echo $DIR | cut -d / -f 3`
    PROGNAME=`ps -p $PID -o comm --no-headers`

    for SWAP in `grep Swap $DIR/smaps 2>/dev/null| awk '{ print $2 }'`
    do
        let SUM=$SUM+$SWAP
    done

    if (( $SUM > 0 ));
    then
        echo -n ".";
        echo -e "${PID}\t${SUM}\t${PROGNAME}" >> ${TMP}/${SCRIPT_NAME}.pid;
        echo -e "${SUM}\t${PID}\t${PROGNAME}" >> ${TMP}/${SCRIPT_NAME}.kb;
        echo -e "${PROGNAME}\t${SUM}\t${PID}" >> ${TMP}/${SCRIPT_NAME}.name;
    fi
    let OVERALL=$OVERALL+$SUM
    SUM=0
done
echo "${OVERALL}" > ${TMP}/${SCRIPT_NAME}.overal;
echo;
echo "Overall swap used: ${OVERALL} kB";
echo "========================================";
case "${SORT}" in
    name )
        echo -e "name\tkB\tpid";
        echo "========================================";
        cat ${TMP}/${SCRIPT_NAME}.name|sort -r;
        ;;

    kb )
        echo -e "kB\tpid\tname";
        echo "========================================";
        cat ${TMP}/${SCRIPT_NAME}.kb|sort -rh;
        ;;

    pid | * )
        echo -e "pid\tkB\tname";
        echo "========================================";
        cat ${TMP}/${SCRIPT_NAME}.pid|sort -rh;
        ;;
esac
rm -fR "${TMP}/";

Wednesday, December 12, 2018

Tuning Advisor on sql from backend



below are the steps to run Tuning Advisor on sql_id

DECLARE
  my_task_name VARCHAR2(30);
begin
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => '&sql_id',scope => 'COMPREHENSIVE',time_limit =>7200,task_name => 'STA:1t05z5qh0u707',description => 'description_ax');
end;

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'STA:1t05z5qh0u707');

SET LONG 10000
SET PAGESIZE 1000
SET LINESIZE 200
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('STA:1t05z5qh0u707') from dual;

Index on Table

Index on Table

select
b.uniqueness, a.index_name, a.table_name, a.column_name
from all_ind_columns a, all_indexes b
where a.index_name=b.index_name
and a.table_name = upper('&table_name')
order by a.table_name, a.index_name, a.column_position;

Monday, December 10, 2018

Close WF_notifications as part of clone process


Close WF_notifications as part of clone process

use the below commands to close open notification so that emails are not sent out of the non-prod environments

create table wf_notifications_bkp as select * from wf_notifications;
update applsys.wf_notifications set status ='CLOSED';
update applsys.wf_notifications set mail_status ='SENT' where mail_status ='MAIL';
commit;

Wednesday, November 14, 2018

determine which concurrent manager ran a specific concurrent request?


Determine which concurrent manager ran a specific concurrent request?

login as apps/****

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 = b.CONCURRENT_QUEUE_ID
and a.CONCURRENT_PROCESS_ID = c.controlling_manager
and c.request_id = '&Request_id';



References:
Concurrent Processing – ANALYZEREQ.SQL Detailed Analysis of One Concurrent Request (Release 11 and up) [ID 134035.1]
How to Determine Which Manager Ran a Specific Concurrent Request? [ID 343575.1]
How Do I Find Out Which Concurrent Manager Processed A Concurrent Request/Program [ID 344011.1]

Wednesday, November 7, 2018

sga/pga memory usages of an Oracle instance

Query to find the the hourly  sga/pga memory usages of an Oracle instance..

select sn.INSTANCE_NUMBER, sga.allo sga, pga.allo pga,(sga.allo+pga.allo) tot,trunc(SN.END_INTERVAL_TIME,'mi') time
  from
(select snap_id,INSTANCE_NUMBER,round(sum(bytes)/1024/1024/1024,3) allo
   from DBA_HIST_SGASTAT
  group by snap_id,INSTANCE_NUMBER) sga
,(select snap_id,INSTANCE_NUMBER,round(sum(value)/1024/1024/1024,3) allo
    from DBA_HIST_PGASTAT where name = 'total PGA allocated'
   group by snap_id,INSTANCE_NUMBER) pga
, dba_hist_snapshot sn
where sn.snap_id=sga.snap_id
  and sn.INSTANCE_NUMBER=sga.INSTANCE_NUMBER
  and sn.snap_id=pga.snap_id
  and sn.INSTANCE_NUMBER=pga.INSTANCE_NUMBER
order by sn.snap_id desc, sn.INSTANCE_NUMBER;

Friday, April 27, 2018

Analyze Indexes


ANALYZE INDEX PA.PA_TASKS_N1 COMPUTE STATISTICS;
ANALYZE INDEX PA.PA_TASKS_N1 VALIDATE STRUCTURE;
SELECT name, height, lf_rows, del_lf_rows, (del_lf_rows/lf_rows)*100 as ratio FROM INDEX_STATS;


Knowing when to Rebuild Indexes
We must first get an idea of the current state of the index by using the ANALYZE INDEX VALIDATE STRUCTURE command.
The VALIDATE STRUCTURE command can be safely executed without affecting the optimizer. The VALIDATE STRUCTURE command populates the SYS.INDEX_STATS table only. The SYS.INDEX_STATS table can be accessed with the public synonym INDEX_STATS. The INDEX_STATS table will only hold validation information for one index at a time. You will need to query this table before validating the structure of the next index.
There are two rules of thumb to help determine if the index needs to be rebuilt.

1)     If the index has height greater than four, rebuild the index.
2)     if the ratio of del_lf_row/lf_row*100 is greater than 20% then rebuild the index

Indexes on Tables


Find all Indexes on a Table

SELECT index_name, column_name, column_position
FROM dba_ind_columns
WHERE table_name like upper('&table_name')
ORDER BY index_name, column_position;


Friday, March 16, 2018

Find Active Users with responsibilities assigned in R122


Below Query can be used to find all active users and all the responsibilities assigned to them in Oracle EBusiness Suite R122


SELECT   fu.user_name, fu.start_date user_start_date,fu.description,
         fu.end_date user_end_date, per.GLOBAL_NAME employee_name,
         per.effective_start_date employee_start_date,
         per.effective_end_date employee_end_date, resp.responsibility_name,
         resp.start_date responsibility_start_date,
         resp.end_date responsibility_end_date
    FROM apps.fnd_user fu,
         (SELECT *
            FROM (SELECT x.*,
                         ROW_NUMBER () OVER (PARTITION BY person_id ORDER BY effective_end_date DESC)
                                                                            r
                    FROM apps.per_all_people_f x)
           WHERE r = 1) per,
         (SELECT fr.responsibility_name, fur.user_id, fur.start_date,
                 fur.end_date
            FROM apps.fnd_user_resp_groups_direct fur,
                 apps.fnd_responsibility_tl fr
           WHERE fur.responsibility_id = fr.responsibility_id
          UNION
          SELECT fr.responsibility_name, fur.user_id, fur.start_date,
                 fur.end_date
            FROM apps.fnd_user_resp_groups_indirect fur,
                 apps.fnd_responsibility_tl fr
           WHERE fur.responsibility_id = fr.responsibility_id) resp
   WHERE fu.employee_id = per.person_id(+)
     AND resp.user_id = fu.user_id
  --   and fu.user_name in ('XYZ','PQR')
 --    and fu.description like '%XYZ%'
     and  nvl(fu.end_date, sysdate+1) > Sysdate order by fu.user_name

Thursday, May 18, 2017

Script to End Data all Application Users in Oracle EBS

take backup of fnd _user table
conn apps/***
create table fnd_user_bkp as select * from fnd_user;

the below script will end date all application users except some system accounts and few application users who needs to be active in the cloned environment or test instance

DECLARE
   CURSOR usercur  
   IS
    SELECT fu.user_name
      FROM apps.fnd_user fu
     WHERE NVL(fu.end_date, SYSDATE + 1) >= SYSDATE
       AND fu.user_name NOT IN -- EXCLUDE SYSTEM ACCOUNTS ** THIS IS NOT A COMPREHENSIVE LIST **
        (
            'AME_INVALID_APPROVER',
            'APPSMGR',
            'ASGADM',
            'ASGUEST',
            'AUTOINSTALL',
            'GUEST',
            'IBE_ADMIN',
            'IBE_GUEST',
            'IBEGUEST',
            'IEXADMIN',
            'IRC_EMP_GUEST',
            'IRC_EXT_GUEST',
            'MOBADM',
            'MOBDEV',
            'MOBILEADM',
            'OP_CUST_CARE_ADMIN',
            'OP_SYSADMIN',
            'PORTAL_PROD',
            'PRODAPOID',
            'PRODOIDAP',
            'SYSADMIN',
            'WIZARD'
        ) -- you can use this list to exclude staff in central support team if required:
       AND fu.user_name NOT IN('TEST1',
                               'TEST2',
                               'TEST3',
                               'TEST4',
      'TEST5',
      'TEST6',
      'TEST7');

BEGIN
   FOR myuser IN usercur
   LOOP
      fnd_user_pkg.updateuser(
         x_user_name                 => myuser.user_name,
         x_owner                     => 'CUST',
         x_end_date                  => TRUNC(SYSDATE-1)
      );
   END LOOP;
END;
/
COMMIT;
EXIT

Sunday, January 29, 2017

SQL to check Stale statistics


SQL to check Stale statistics

---Using Anonymous PL/SQL block 
SQL> SET SERVEROUTPUT ON
  DECLARE
ObjList dbms_stats.ObjectTab;
BEGIN
dbms_stats.gather_database_stats(objlist=>ObjList, options=>'LIST STALE');
FOR i in ObjList.FIRST..ObjList.LAST
LOOP
dbms_output.put_line(ObjList(i).ownname || '.' || ObjList(i).ObjName || ' ' || ObjList(i).ObjType || ' ' || ObjList(i).partname);
END LOOP;
END;
/

---Oracle 10g Onwards. 
col TABLE_NAME for a30
TABLE_NAME        STALE_STATS


BEGIN
DBMS_STATS.SET_GLOBAL_PREFS(‘AUTOSTATS_TARGET’,’ ORACLE’);
BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL); 


col PARTITION_NAME for a20
col SUBPARTITION_NAME for a20
select OWNER,TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,NUM_ROWS,LAST_ANALYZED from dba_TAB_STATISTICS where STALE_STATS='YES';

-- Oracle 12c

Statistics on a table are considered stale when more than STALE_PERCENT (default 10%) of the rows are changed (total number of inserts, deletes, updates) in the table. Oracle monitors the DML activity for all tables and records it in the SGA. The monitoring information is periodically flushed to disk, and is exposed in the *_TAB_MODIFICATIONS view. 

select table_name,inserts,updates,deletes
from USER_TAB_MODIFICATIONS
where table_name='PRODUCTS2';

TABLE_NAME   INSERTS  UPDATES  DELETES
PRODUCTS2      766             1532             32

Querying USER_TAB_MODIFICATIONS view to check DML activity on the PRODUCTS2 table
It is possible to manually flush this data by calling the procedure 

DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO if you want to get up-to-date information at query time (internally the monitoring data is flushed before all statistics collection operations). You can then see which tables have stale statistics by querying the STALE_STATS column in the USER_TAB_STATISTICS view.
sql> conn hr/hr
Connected
sql> select table_name,stale_stats
from user_tab_statistics;

COUNTRIES            NO
DEPARTMENTS       NO
JOBS                           NO
EMPLOYEES
PRODUCTS2              YES
LOCATIONS              NO

Querying USER_TAB_STATISTICS to see if any tables have stale statistics


Tables where STALE_STATS is set to NO, have up to date statistics. Tables where STALE_STATS is set toYES,havestalestatistics.Tables where STALE_STATS is not  set are missing statistics altogether. 


If you already have a well-established statistics gathering procedure, or if for some other reason you want to disable automatic statistics gathering for your main application schema, consider leaving it on for the dictionary tables. You can do this by changing the value of AUTOSTATS_TARGET to ORACLE instead of AUTO using DBMS_STATS.SET_GLOBAL_PREFS procedure.
END; /
To disable the task altogether:
END; / 


Tuesday, January 10, 2017

Check if profile option is enabled or not

Query to check the value of any profile option
like FND: Diagnostics or FND: Debug log etc.


login as apps user and run the below query. upon prompt enter the profile option name

SELECT fpo.profile_option_name SHORT_NAME,
         fpot.user_profile_option_name NAME,
         DECODE (fpov.level_id,
                 10001, 'Site',
                 10002, 'Application',
                 10003, 'Responsibility',
                 10004, 'User',
                 10005, 'Server',
                 'UnDef')
            LEVEL_SET,
         DECODE (TO_CHAR (fpov.level_id),
                 '10001', '',
                 '10002', fap.application_short_name,
                 '10003', frsp.responsibility_key,
                 '10005', fnod.node_name,
                 '10006', hou.name,
                 '10004', fu.user_name,
                 'UnDef')
            "CONTEXT",
         fpov.profile_option_value VALUE
    FROM fnd_profile_options fpo,
         fnd_profile_option_values fpov,
         fnd_profile_options_tl fpot,
         fnd_user fu,
         fnd_application fap,
         fnd_responsibility frsp,
         fnd_nodes fnod,
         hr_operating_units hou
   WHERE     fpo.profile_option_id = fpov.profile_option_id(+)
         AND fpo.profile_option_name = fpot.profile_option_name
         AND fu.user_id(+) = fpov.level_value
         AND frsp.application_id(+) = fpov.level_value_application_id
         AND frsp.responsibility_id(+) = fpov.level_value
         AND fap.application_id(+) = fpov.level_value
         AND fnod.node_id(+) = fpov.level_value
         AND hou.organization_id(+) = fpov.level_value
         AND fpot.user_profile_option_name like('&User_Profile_Option_Name')
ORDER BY short_name;

Check trace enabled at concurrent program level

How to check if trace is enabled for Concurrent program

Option 1:

you can login to oracle Ebusiness Suite
go to System Administrator responsibility.
Concurrent--> Program
Query for the concurrent pogram name
check if the check box enable trace for trace enabled or not.


Option 2:

login as apps user. Run the below query . I will show all concurrent programs that has trace enabled.

SELECT A.CONCURRENT_PROGRAM_NAME "Program Name",
SUBSTR(A.USER_CONCURRENT_PROGRAM_NAME,1,40) "User Program Name",
SUBSTR(B.USER_NAME,1,15) "Last Updated By",
SUBSTR(B.DESCRIPTION,1,25) DESCRIPTION
FROM APPS.FND_CONCURRENT_PROGRAMS_VL A, APPLSYS.FND_USER B
WHERE A.ENABLE_TRACE='Y'
AND A.LAST_UPDATED_BY=B.USER_ID;



Thursday, December 22, 2016

Concurrent Request on hold


Put all Concurrent requests on hold.

UPDATE fnd_concurrent_requests
SET hold_flag = 'Y'
WHERE phase_code = 'P'
and status_code in ('Q','I');

unhold all requests submitted/submitted by sysadmin --

UPDATE fnd_concurrent_requests
SET hold_flag = 'N'
WHERE phase_code = 'P'
and status_code in ('Q','I')
and REQUESTED_BY='0';

Saturday, December 17, 2016

grant select to APPS object on different schema without causing invalids in database.



Query to grant select privs on all apps objects to a given schema XYZ without causing invalid objects


select 'exec ad_zd.grant_privs(''SELECT'||''''||','||''''||object_name||''''||','||''''||'XYZ'||''''||')'||';'
from dba_objects where object_type in ('TABLE','VIEW') and owner ='APPS';

find Indexes created on table


Query to find the list of all indexes created on a table.

SELECT index_name, column_name, column_position
FROM dba_ind_columns
WHERE table_name like upper('&table_name')
ORDER BY index_name, column_position;