SQL script to find SQL ID's having more than one hash plan

 

SQL script to find SQL ID's having more than one hash plan

This will be for the last 7 days.

Script:

SELECT

    sql_id, COUNT(DISTINCT plan_hash_value)

   FROM

    dba_hist_sqlstat  stat,

    dba_hist_snapshot ss

WHERE

        stat.snap_id = ss.snap_id

    AND ss.dbid = stat.dbid

    AND ss.instance_number = stat.instance_number

    AND ss.begin_interval_time >= sysdate - 7

    AND ss.end_interval_time <= sysdate

    AND stat.plan_hash_value <> 0

    AND stat.executions_delta > 0

    AND stat.parsing_schema_name NOT IN ( 'SYS', 'SYSTEM' )

 GROUP BY

    sql_id

having COUNT(DISTINCT plan_hash_value) >1

ORDER BY

    1;

PL/SQL to Compare if they are the same or different

Script:

SET SERVEROUTPUT ON

DECLARE

    v_count number := 0;

    CURSOR SQLID IS

SELECT

    sql_id,

    COUNT(DISTINCT plan_hash_value) cnt

FROM

    dba_hist_sqlstat  stat,

    dba_hist_snapshot ss

WHERE

        stat.snap_id = ss.snap_id

    AND ss.dbid = stat.dbid

    AND ss.instance_number = stat.instance_number

    AND ss.begin_interval_time >= sysdate - 7

    AND ss.end_interval_time <= sysdate

    AND stat.plan_hash_value <> 0

    AND stat.executions_delta > 0

    AND stat.parsing_schema_name NOT IN ( 'SYS', 'SYSTEM' )

GROUP BY

    sql_id

ORDER BY

    1;

BEGIN

    FOR I IN SQLID

    loop

    DBMS_OUTPUT.PUT_LINE ('~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~');

        IF I.cnt > 1 THEN

            DBMS_OUTPUT.PUT_LINE ('Multiple plan for this sql :-'||I.sql_id||'. Checked for last 7 days. Please login to DB and do action item as needed ');

       ELSE

             DBMS_OUTPUT.PUT_LINE ('This sqlid '||I.sql_id||' has more than one plan, but the plan was not changed since last 7days'); 

        END IF;

        

    end loop;

END;

/

sqlflip2.sql

 set lines 155

----------------------------------------------------------------------------------------

--

-- File name:   sqlflip2.sql

--

-- Purpose:     Attempts to find SQL statements with plan instability.

--

---------------------------------------------------------------------------------------

col execs for 999,999,999

col etime for 999,999,999.9

col avg_etime for 999,999.999

col avg_cpu_time for 999,999.999

col avg_lio for 999,999,999.9

col avg_pio for 9,999,999.9

col begin_interval_time for a30

col node for 99999

break on plan_hash_value on startup_time skip 1

select sql_id, plan_hash_value, 

sum(execs) execs, 

-- sum(etime) etime, 

sum(etime)/sum(execs) avg_etime, 

sum(cpu_time)/sum(execs) avg_cpu_time,

sum(lio)/sum(execs) avg_lio, 

sum(pio)/sum(execs) avg_pio

from (

select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,

nvl(executions_delta,0) execs,

elapsed_time_delta/1000000 etime,

(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,

buffer_gets_delta lio,

disk_reads_delta pio,

cpu_time_delta/1000000 cpu_time,

(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio,

(cpu_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta)) avg_cpu_time

from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS

where sql_id = nvl('&sql_id','4dqs2k5tynk61')

and ss.snap_id = S.snap_id

and ss.instance_number = S.instance_number 

-- and executions_delta > 0

)

group by sql_id, plan_hash_value

order by 5

/

sqlflip1.sql

 ----------------------------------------------------------------------------------------

--

-- File name:   sqlflip1.sql

--

-- Purpose:     Provides list of all PHVs their execution count and resource consumption

--

---------------------------------------------------------------------------------------

set lines 155

col execs for 999,999,999

col min_etime for 999,999.99

col max_etime for 999,999.99

col avg_etime for 999,999.999

col avg_lio for 999,999,999.9

col norm_stddev for 999,999.9999

col begin_interval_time for a30

col node for 99999

break on plan_hash_value on startup_time skip 1

select * from (

select sql_id, sum(execs), min(avg_etime) min_etime, max(avg_etime) max_etime, stddev_etime/min(avg_etime) norm_stddev

from (

select sql_id, plan_hash_value, execs, avg_etime,

stddev(avg_etime) over (partition by sql_id) stddev_etime 

from (

select sql_id, plan_hash_value,

sum(nvl(executions_delta,0)) execs,

(sum(elapsed_time_delta)/decode(sum(nvl(executions_delta,0)),0,1,sum(executions_delta))/1000000) avg_etime

-- sum((buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta))) avg_lio

from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS

where ss.snap_id = S.snap_id

and ss.instance_number = S.instance_number 

and executions_delta > 0

and elapsed_time_delta > 0

and s.snap_id > nvl('&earliest_snap_id',0)

group by sql_id, plan_hash_value

)

)

group by sql_id, stddev_etime

)

where norm_stddev > nvl(to_number('&min_stddev'),2)

and max_etime > nvl(to_number('&min_etime'),.1)

order by norm_stddev

/

validation

 col directory_path  for a100

set pages 1000 lines 150            

select directory_name,directory_path from dba_directories;

SET LINESIZE 150

COLUMN owner FORMAT A20
COLUMN directory_name FORMAT A25
COLUMN directory_path FORMAT A80

SELECT *
FROM   dba_directories
ORDER BY owner, directory_name;

+++++++++

SQL:

 

select 'CREATE OR REPLACE DIRECTORY '||directory_name||' as '''|| directory_path||''';' from dba_directories;

                                      

OR

 

update dir$ set OS_PATH=replace(OS_PATH,'&OLD_VALUE','&NEW_VALUE') where OS_PATH like '%&OLD_VALUE%';

+++

Update Site Name:

 

Update the Below function. Change the value according to the instance

 

update fnd_form_functions_tl

set user_function_name = ' from PROD (BKP 16-MAY-16)'

where function_id in (select function_id from fnd_form_functions where function_name = 'FWK_HOMEPAGE_BRAND') and LANGUAGE='US';

 

 

set pages 1000 lines 150

col user_function_name for a80

select LANGUAGE,FUNCTION_ID,user_function_name  from fnd_form_functions_tl where function_id in (select function_id from fnd_form_functions where function_name = 'FWK_HOMEPAGE_BRAND') and LANGUAGE='US';

 +++

Once REGOID is completed, we need to follow the attached mail steps to populate the data in OID tables.

 SQL>  execute fnd_oid_plug.setPlugin;

 

SQL> SELECT * FROM fnd_user_preferences WHERE user_name='#INTERNAL' AND module_name='OID_CONF';

Oracle E-Business Suite Release 12 Release-Specific Database Initialization Parameters for 19c

 

Release-Specific Database Initialization Parameters for 19c

 ####################################################################

#
# Oracle E-Business Suite Release 12
# Release-Specific Database Initialization Parameters for 19c
#
####################################################################

compatible = 19.0.0 #MP
optimizer_adaptive_plans = TRUE #MP
optimizer_adaptive_statistics = FALSE #MP
pga_aggregate_limit = 0 #MP
temp_undo_enabled = FALSE
_pdb_name_case_sensitive = TRUE #MP
event='10946 trace name context forever, level 8454144' #MP
_optimizer_gather_stats_on_conventional_dml = FALSE #MP
_optimizer_use_stats_on_conventional_dml = FALSE #MP
optimizer_real_time_statistics = FALSE #MP


###############################################################################
#
# End of Release-Specific Database Initialization Parameters Section for 19c
#
###############################################################################

Parameter Removal List for Oracle Database 19c

 egrep -i '_kks_use_mutex_pin|_shared_pool_reserved_min_alloc|_sqlexec_progression_cost|exafusion_enabled|exclude_seed_cdb_view|global_context_pool_size|max_enabled_roles|o7_dictionary_accessibility|olap_page_pool_size|optimizer_adaptive_features|optimizer_features_enable|parallel_automatic_tuning|parallel_degree_level|parallel_io_cap_enabled|parallel_server|parallel_server_instances|plsql_compiler_flags|plsql_native_library_dir|plsql_native_library_subdir_count|plsql_optimize_level|standby_archive_dest|timed_statistics|use_indirect_data_buffers|utl_file_dir' init19c.ora

++++++++++++

 Parameter Removal List for Oracle Database 19c

If they exist, you should remove the following parameters from your database initialization parameters file for Oracle Database 19c.

Note: Parameters may appear on a "removal list" because they are obsolete; because the default value is required and no other value may be set; or to cater for certain special cases where a non-default value has to be set to meet specific needs (currently, there is only one such case, which is described in Section 6).

_kks_use_mutex_pin
_shared_pool_reserved_min_alloc
_sqlexec_progression_cost
exafusion_enabled
exclude_seed_cdb_view
global_context_pool_size
max_enabled_roles
o7_dictionary_accessibility
olap_page_pool_size
optimizer_adaptive_features
optimizer_features_enable
parallel_automatic_tuning
parallel_degree_level
parallel_io_cap_enabled
parallel_server
parallel_server_instances
plsql_compiler_flags
plsql_native_library_dir
plsql_native_library_subdir_count
plsql_optimize_level
standby_archive_dest
timed_statistics
use_indirect_data_buffers
utl_file_dir

 

Important day to day scripts

 

–FIND DATABASE SIZE

 select 'DATA_n_INDEX: ' || sum(bytes)/1024/1024/1024 || ' GBytes' DATABASE_SIZE from dba_data_files

union

select 'TEMP: ' || sum(bytes)/1024/1024/1024 || ' GBytes' from dba_temp_files

union

select 'REDO LOGS: ' || sum(bytes)/1024/1024/1024 || ' GBytes' from v$log

union

select 'CONTROLFILE: ' || sum(FILE_SIZE_BLKS*BLOCK_SIZE)/1024/1024 || ' MBytes' from v$controlfile;

–CREATE NEW DATABASE USER

create user <username> identified by <password> default tablespace <tablespace_name> temporary tablespace <tablespace_name> profile <profile_name>;

 

grant create session to <username>;

 

alter user <username> password expire;

 

–CREATE DYNAMIC SQL SCRIPTS TO COMPILE INVALID OBJECTS

spool recompile_list.sql;

select 'ALTER '||

decode(substr(object_type,1,4),'PACK','PACKAGE ',object_Type||' ')||

owner || '.' || decode(object_type,'JAVA CLASS','"') || object_name ||

decode(object_type,'JAVA CLASS','" RESOLVE ','INDEX','REBUILD',' COMPILE ') ||

DECODE(object_type,'PACKAGE BODY','BODY', NULL)||';' OBJECTS_TO_COMPILE

from dba_objects

where object_type in ('PACKAGE','PROCEDURE','PACKAGE BODY','FUNCTION',

'TRIGGER','VIEW','JAVA CLASS','INDEX')

and status = 'INVALID'

order by object_name, object_type;

spool off;

 

–FIND ALL ACTIVE DATABASE SESSIONS

select event, program, module, sid, SECONDS_IN_WAIT

from v$session

where status = 'ACTIVE'

and module is not null

order by 2 desc;

OR

select sid, serial#, username, osuser, logon_time, program, module

from v$session where username is not null and status = 'ACTIVE'

 

–FIND EBS USER NAME FROM DATABASE SESSION ID

column "User Name" format a10

column "OS_PID" format a8

 

select d.user_name "User Name",

b.sid SID,b.serial# "Serial#", c.spid "srvPID", a.SPID "OS_PID",

to_char(START_TIME,'DD-MON-YY HH:MM:SS') "STime"

from fnd_logins a, v$session b, v$process c, fnd_user d

where b.paddr = c.addr

and a.pid=c.pid

and a.spid = b.process

and d.user_id = a.user_id

and (d.user_name = 'USER_NAME' OR 1=1)

and b.sid = &sid;

 

–FIND ORPHAN PROCESSES

select spid from v$process where not exists (select 1 from v$session where paddr=addr)

 

–FIND DETAILS WHEN CONCURRENT PROGRAM WAS RUN IN LAST 10 DAYS

select REQUEST_ID,

REQUEST_DATE,

REQUESTED_START_DATE,

RESUBMIT_END_DATE,

ACTUAL_START_DATE,

ACTUAL_COMPLETION_DATE

from fnd_concurrent_requests

where CONCURRENT_PROGRAM_ID = &Concurrent_Prog_ID

and ACTUAL_START_DATE between trunc(sysdate) - 10 and sysdate order by REQUESTED_START_DATE;

 

–FIND ALL FORMS RELATED SESSIONS

col CLIENT_IDENTIFIER format a10

col MODULE format a25

col MACHINE format a10

 

select sid, serial#, logon_time, client_identifier, module, status, machine, seconds_in_wait

from gv$session

where program like 'frmweb%'

order by logon_time;

 

–FIND USER_ID FROM USER_NAME

select USER_ID, USER_NAME

from apps.FND_USER

where USER_NAME = upper('&1');

 

–FIND EBS USER CONNECTION RELATED DETAILS FROM USER_ID

select SESSION_ID, CREATION_DATE, TIME_OUT, LIMIT_TIME, LIMIT_CONNECTS, COUNTER, FIRST_CONNECT, LAST_CONNECT

from apps.ICX_SESSIONS

where USER_ID = &user_id

order by CREATION_DATE;

 

–FIND EBS USER CONNECTION RELATED DETAILS FROM SESSION_ID

SELECT SESSION_ID, XSID, TIME_OUT, LIMIT_TIME, LIMIT_CONNECTS, COUNTER, TO_CHAR(SYSDATE,'DD-MM-YYYY HH24:MI:SS'), TO_CHAR(FIRST_CONNECT,'DD-MM-YYYY HH24:MI:SS'),TO_CHAR(LAST_CONNECT,'DD-MM-YYYY HH24:MI:SS'), DISABLED_FLAG

FROM apps.ICX_SESSIONS

WHERE SESSION_ID = '&session_id';

 

–FIND PID,SPID FROM SID

select p.PID,p.SPID,s.SID

from v$process p,v$session s

where s.paddr = p.addr

and s.sid = &sid;

 

–FIND WORKFLOW MAIL PREFRENCES FROM DISLAY_NAME

col DISPLAY_NAMEformata30

col name format a30

set lines 132

 

select NAME, DISPLAY_NAME, NOTIFICATION_PREFERENCE

from wf_users

where DISPLAY_NAME like upper('%&NAME%');

 

–FIND ORACLE DATABASE HIDDEN PARAMETER VALUE

set lines 132

column ksppinm format a50

column ksppstvl format a30

 

select a.ksppinm, b.ksppstvl

from x$ksppi a,x$ksppsv b

where a.indx=b.indx

and substr(a.ksppinm,1,1) = '_'

and lower(a.ksppinm) like '%&hidden_parameter%'

order by ksppinm;

 

–FIND WORKFLOW NAME AND DISPLAY_NAME FROM EMAIL ADDRESS

select name, display_name

from apps.WF_LOCAL_ROLES

where EMAIL_ADDRESS = '<email_address>';

 

–FIND EBS APPLICATION USER DETAILS FROM SID

rem to_char(max(l.start_time),'mm/dd/yy hh:mi:ssAM') startedat,

set lines 132

set verify off

col user_name head "Apps Signon" format a12 trunc

col description head "Real Name" format a25 trunc

rem col user_form_name head "Form Name" format a30 trunc

col forminfo head "Form Name" format a40 trunc

col time head "Elapsed Time" format a10

col zoom_level head "Zoom Level"

col startedat head "Logon At" format a19

col lastcallet format a11

accept trgtsid number prompt 'What is the SID : '

select /*+ rule */

to_char(s.logon_time,'mm/dd/yy hh:mi:ssAM') startedat,

a.time,

floor(s.last_call_et/3600)||':'||

floor(mod(s.last_call_et,3600)/60)||':'||

mod(mod(s.last_call_et,3600),60) "LastCallET",

u.user_name, u.description ,

s.module || ' - ' || a.user_form_name forminfo

from applsys.fnd_logins l,

applsys.fnd_user u,

apps.fnd_signon_audit_view a,

v$process p,

v$session s

where s.sid = &trgtsid

and s.paddr = p.addr

and p.pid = l.pid

and l.end_time is null

and l.spid = s.process

and l.start_time is not null

-- and l.start_time = u.last_logon_date

-- and l.session_number = u.session_number

and l.user_id = u.user_id

and u.user_id = a.user_id

and p.pid = a.pid

and l.start_time = (select max(l2.start_time)

from applsys.fnd_logins l2

where l2.pid = l.pid)

group by to_char(s.logon_time,'mm/dd/yy hh:mi:ssAM'),

floor(s.last_call_et/3600)||':'||

floor(mod(s.last_call_et,3600)/60)||':'||

mod(mod(s.last_call_et,3600),60),

u.user_name, u.description,a.time,s.module || ' - ' || a.user_form_name

order by to_char(s.logon_time,'mm/dd/yy hh:mi:ssAM'),a.time;

 

–FIND VALUE OF PROFILE OPTION FROM DATABASE

set serveroutput on

set echo on

set timing on

set feedback on

set long 10000

set linesize 120

set pagesize 132

column SHORT_NAME format A30

column NAME format A40

column LEVEL_SET format a15

column CONTEXT format a30

column VALUE format A60 wrap

 

select p.profile_option_name SHORT_NAME,

n.user_profile_option_name NAME,

decode(v.level_id,

10001, 'Site',

10002, 'Application',

10003, 'Responsibility',

10004, 'User',

10005, 'Server',

10007, 'SERVRESP',

'UnDef') LEVEL_SET,

decode(to_char(v.level_id),

'10001', '',

'10002', app.application_short_name,

'10003', rsp.responsibility_key,

'10005', svr.node_name,

'10006', org.name,

'10004', usr.user_name,

'10007', 'Serv/resp',

'UnDef') "CONTEXT",

v.profile_option_value VALUE

from fnd_profile_options p,

fnd_profile_option_values v,

fnd_profile_options_tl n,

fnd_user usr,

fnd_application app,

fnd_responsibility rsp,

fnd_nodes svr,

hr_operating_units org

where p.profile_option_id = v.profile_option_id (+)

and p.profile_option_name = n.profile_option_name

and (upper(n.user_profile_option_name) like upper('&user_rofile_name')

or upper(n.user_profile_option_name) like upper('&user_profle_name'))

and usr.user_id (+) = v.level_value

and rsp.application_id (+) = v.level_value_application_id

and rsp.responsibility_id (+) = v.level_value

and app.application_id (+) = v.level_value

and svr.node_id (+) = v.level_value

and org.organization_id (+) = v.level_value

order by short_name, level_set;

 

–FIND USER DETAILS FROM USER_NAME

select USER_GUID,START_DATE,END_DATE,LAST_LOGON_DATE,EMAIL_ADDRESS

from fnd_user

where user_name = '&USER_NAME';

 

–FIND ALL RUNNING CONCURRENT REQUESTS WHICH ARE CURRENTLY RUNNING

set linesize 120

col program for a34

col requestor for a9

SELECT REQUEST_ID "Req. ID",

to_char(REQUESTED_START_DATE,'YYYY-MM-DD HH24:MI:SS') "REQ START DATE",

to_char(ACTUAL_START_DATE,'YYYY-MM-DD HH24:MI:SS') "ACT START DATE",

to_char(ACTUAL_COMPLETION_DATE,'YYYY-MM-DD HH24:MI:SS') "ACT COMP DATE",

PHASE_CODE "P-CODE",STATUS_CODE "S-CODE",

PROGRAM, REQUESTOR

FROM

APPS.FND_CONC_REQ_SUMMARY_V WHERE PHASE_CODE = 'R'

order by actual_start_date DESC;

 

– UPDATE PROFILE OPTION FROM BACKEND

DECLARE

stat boolean;

BEGIN

dbms_output.disable;

dbms_output.enable(100000);

stat := FND_PROFILE.SAVE('XX_PROFILE_NAME', 'XX_NEW_VALUE', 'SITE');

IF stat THEN

dbms_output.put_line( 'Stat = TRUE - profile updated' );

ELSE

dbms_output.put_line( 'Stat = FALSE - profile NOT updated' );

END IF;

commit;

END;

 

— APPLICATION USER DEFINITION

rem fnduser.sql

rem

set linesize 132

rem

ttitle 'Application User Definitions'

rem

col user_name format a15 heading 'USER NAME'

col user_id format 99999 heading 'USER|ID'

col employee_flag format a1 heading 'E'

col description format a35 heading 'DESCRIPTION'

col start_date format a9 heading 'START|DATE'

col end_date format a9 heading 'END|DATE'

col last_logon_date format a9 heading 'LAST|LOGON|DATE'

col last_logon_time format a17 heading 'LAST LOGON|TIMESTAMP'

col last_logon_days format 9999 heading 'LAST|LOGON|DAYS'

col f format a1 heading 'F'

col p format a1 heading 'P'

col password_lifespan_days format 9999 heading 'PW|EXPIRE|DAYS'

col password_date format a9 heading 'PASSWORD|DATE'

col password_change_days format 9999 heading 'PW|CHANGE|DAYS'

rem

break on report

compute count of start_date end_date last_logon_time last_logon_days on report

rem

select user_name,

 user_id,

 decode( employee_id, null, null, 'E' ) employee_flag,

 description,

 start_date,

 end_date,

 decode( end_date, least( end_date, sysdate ), null, '+' ) f,

 to_char( last_logon_date, 'mm/dd/yy hh24:mi:ss' ) last_logon_time,

 decode( end_date, null, sysdate - last_logon_date ) last_logon_days,

 decode( end_date, null, decode( 30, greatest( 30, trunc(sysdate-last_logon_date) ), null, '*' ) ) p,

 password_lifespan_days,

 password_date,

 decode( end_date, null, sysdate - password_date ) password_change_days

 from applsys.fnd_user u

 where user_name like upper('%&userid%')

 order by description, user_name;

rem

set linesize 80

 

–LONG RUNNING EBS FORM SESSION

rem long_run_form.sql

rem

set linesize 132

set pagesize 60

set tab off

rem

ttitle 'Long Running Active Form Sessions'

rem

col email_address format a26 heading 'EMAIL ADDRESS'

col user_name format a10 heading 'USER NAME'

col pid format 9999 heading 'PID'

col spid format a6 heading 'SERVER|PID'

col inst_id format 9 heading 'I'

col sid format 9999 heading 'SID'

col serial# format 99999 heading 'SERIAL'

col process format a6 heading 'CLIENT|PID'

col osuser format a8 heading 'OS|USERNAME'

col log_per_sec format 99999 heading 'LOG|PER|SEC'

col logical format b999999999 heading 'LOGICAL|READS'

col phy_per_sec format b9999 heading 'PHY|PER|SEC'

col physical_reads format b99999999 heading 'PHYSICAL|READS'

col audsid format b9999999 heading 'AUDIT|SESSION'

col program format a30 heading 'PROGRAM NAME'

col module format a12 heading 'FORM NAME'

col logon_time format a8 heading 'LOGON|TIME'

col duration format a8 heading 'DURATION'

col last_call_min format 999 heading 'LAST|CALL|MIN'

col orcl_user format a6 heading 'ORACLE|USERID'

col status format a1 heading 'S'

col enqueue format a1 heading 'E'

rem

select distinct user_name,

decode( s.status, 'ACTIVE', '*', 'INACTIVE', null, 'KILLED', 'K', '?' ) status,

decode( s.lockwait, null, null, 'E' ) enqueue,

s.last_call_et/60 last_call_min,

s.module,

s.inst_id,

s.sid,

s.serial#,

-- s.username orcl_usr,

-- s.osuser osuser,

s.process,

p.spid,

to_char( trunc(sysdate) + ( sysdate - s.logon_time ), 'hh24:mi:ss' ) duration,

( i.block_gets + i.consistent_gets ) /

( ( sysdate - s.logon_time ) * 86400 ) log_per_sec,

i.block_gets + i.consistent_gets logical,

physical_reads /

( ( sysdate - s.logon_time ) * 86400 ) phy_per_sec,

i.physical_reads,

-- s.action,

email_address

from applsys.fnd_logins l,

applsys.fnd_user u,

gv$session s,

gv$sess_io i,

gv$process p

where l.user_id = u.user_id

and s.sid = i.sid

and s.inst_id = i.inst_id

and p.pid = l.pid

-- and s.process = l.spid

and p.spid = l.process_spid(+)

and l.end_time is null

and s.paddr = p.addr(+)

and s.inst_id = p.inst_id(+)

and ( s.module in ( 'FNDATTCH', 'FNDSCSGN' )

or substr( s.action, 1, 5 ) = 'FRM::' )

and s.last_call_et >= 300

and s.status in ( 'ACTIVE', 'KILLED' )

order by last_call_min desc;

rem

set linesize 80

set pagesize 24

set tab on

 

—FIND WHICH RESPONSIBILITIES CAN RUN SPECIFIC PROGRAM

 SELECT fcp.concurrent_program_name

 ,fr.responsibility_name

 ,frg.request_group_name

 ,fcp.user_concurrent_program_name

 FROM

 fnd_request_group_units frgup

 ,fnd_concurrent_programs_vl fcp

 ,fnd_request_groups frg

 ,fnd_responsibility_vl fr

 WHERE 1=1

 AND fr.request_group_id = frg.request_group_id

 AND frg.request_group_id = frgup.request_group_id

 AND frgup.request_unit_type = 'P'

 AND frgup.request_unit_id = fcp.concurrent_program_id

 AND fcp.concurrent_program_name LIKE '%&concurrent_program_name%';

 

– FIND WHICH SID IS BLOCKING OTHER SID

select s1.username || '@' || s1.machine

 || ' ( SID=' || s1.sid || ' ) is blocking '

 || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status

 from v$lock l1, v$session s1, v$lock l2, v$session s2

 where s1.sid=l1.sid and s2.sid=l2.sid

 and l1.BLOCK=1 and l2.request > 0

 and l1.id1 = l2.id1

 and l2.id2 = l2.id2 ;

 

–FIND DETAILS OF A SID

rem vusersid.sql

 rem

 set linesize 132

 rem

 ttitle 'User Session Statistics'

 rem

 col pid format 9999 heading 'PID'

 col spid format a6 heading 'SERVER|PID'

 col sid format 9999 heading 'SID'

 col serial# format 99999 heading 'SERIAL'

 col process format a6 heading 'CLIENT|PID'

 col osuser format a8 heading 'OS|USERNAME'

 col username format a10 heading 'ORACLE|USERNAME'

 col log_per_sec format 999999 heading 'LOG|PER|SEC'

 col logical format b9999999999 heading 'LOGICAL|READS'

 col phy_per_sec format b9999 heading 'PHY|PER|SEC'

 col physical_reads format b99999999 heading 'PHYSICAL|READS'

 col audsid format b9999999 heading 'AUDIT|SESSION'

 col program format a32 heading 'PROGRAM NAME'

 col module format a32 heading 'MODULE'

 col logon_time format a8 heading 'LOGON|TIME'

 col duration format a8 heading 'DURATION'

 col last_call_min format 9999 heading 'LAST|CALL|MIN'

 col status format a1 heading 'S'

 rem

 select s.process,

 p.spid,

 /*

 p.pid,

 */

 s.sid,

 s.serial#,

 s.osuser,

 s.username,

 ( i.block_gets + i.consistent_gets ) /

 ( ( sysdate - s.logon_time ) * 86400 ) log_per_sec,

 i.block_gets + i.consistent_gets logical,

 physical_reads /

 ( ( sysdate - s.logon_time ) * 86400 ) phy_per_sec,

 i.physical_reads,

 /*

 s.audsid,

 */

 to_char( trunc(sysdate) + ( sysdate - s.logon_time ), 'hh24:mi:ss' ) duration,

 s.last_call_et/60 last_call_min,

 decode( s.status, 'ACTIVE', '*', 'INACTIVE', null, 'KILLED', 'K', '?' ) status,

 decode( s.action, 'Concurrent Request', 'CM/' || s.module, s.module ) module

 from v$process p, v$session s, v$sess_io i

 where i.sid = s.sid

 and s.paddr = p.addr

 and s.sid = &sid;

 rem

 set linesize 80

 

–FIND DETAILS OF SQL OF A SID

rem vusersidsql.sql

 rem

 set linesize 132

 rem

 ttitle 'User Session Statistics'

 rem

 col pid format 9999 heading 'PID'

 col spid format a6 heading 'SERVER|PID'

 col sid format 9999 heading 'SID'

 col serial# format 99999 heading 'SERIAL'

 col process format a6 heading 'CLIENT|PID'

 col osuser format a8 heading 'OS|USERNAME'

 col username format a10 heading 'ORACLE|USERNAME'

 col log_per_sec format 999999 heading 'LOG|PER|SEC'

 col logical format b9999999999 heading 'LOGICAL|READS'

 col phy_per_sec format b9999 heading 'PHY|PER|SEC'

 col physical_reads format b99999999 heading 'PHYSICAL|READS'

 col audsid format b9999999 heading 'AUDIT|SESSION'

 col program format a32 heading 'PROGRAM NAME'

 col module format a32 heading 'MODULE'

 col logon_time format a8 heading 'LOGON|TIME'

 col duration format a8 heading 'DURATION'

 col last_call_min format 9999 heading 'LAST|CALL|MIN'

 col status format a1 heading 'S'

 col hide_sql_hash_value noprint new_value _sql_hash_value

 rem

 select s.process,

 p.spid,

 /*

 p.pid,

 */

 s.sid,

 s.serial#,

 s.osuser,

 s.username,

 ( i.block_gets + i.consistent_gets ) /

 ( ( sysdate - s.logon_time ) * 86400 ) log_per_sec,

 i.block_gets + i.consistent_gets logical,

 physical_reads /

 ( ( sysdate - s.logon_time ) * 86400 ) phy_per_sec,

 i.physical_reads,

 /*

 s.audsid,

 */

 to_char( trunc(sysdate) + ( sysdate - s.logon_time ), 'hh24:mi:ss' ) duration,

 s.last_call_et/60 last_call_min,

 decode( s.status, 'ACTIVE', '*', 'INACTIVE', null, 'KILLED', 'K', '?' ) status,

 decode( s.action, 'Concurrent Request', 'CM/' || s.module, s.module ) module,

 s.sql_hash_value hide_sql_hash_value

 from v$process p, v$session s, v$sess_io i

 where i.sid = s.sid

 and s.paddr = p.addr

 and s.sid = &sid;

 rem

 set linesize 80

 rem

 ttitle 'SQL Text'

 rem

 col sql_text format a64 heading 'SQL TEXT'

 rem

 select sql_text

 from v$sqltext

 where hash_value = &_sql_hash_value

 and &_sql_hash_value <> 0

 order by piece;

 

–SCRIPT TO CHECK WHEN EBS USER LOG IN AND THE RESPONSIBILITY ACCESSED BY THE USER

NOTE: ‘Sign-On:Audit Level’ profile must be enabled in the EBS instance for the data to be collected.

 Choose the scope of your audit and who to audit by setting the user profile level at the user, responsibility, application, or site profile levels. Also remember, you should consider the additional system overhead required to precisely monitor and audit your users as they access Oracle Applications. The more users you audit and the higher the level of auditing, the greater the likelihood of incurring additional system overhead.

SELECT UNIQUE fu.user_id,

 fu.user_name user_name,

 fr.responsibility_key responsibility,

 fg.START_DATE respo_start_date,

 flr.start_time respo_last_access_date

 FROM fnd_user fu,

 fnd_user_resp_groups_all fg,

 fnd_application_tl fa,

 fnd_responsibility fr,

 fnd_login_responsibilities flr,

 fnd_logins fl

 WHERE fg.user_id = fu.user_id

 AND fg.responsibility_application_id = fa.application_id

 AND fa.application_id = fr.application_id

 AND fg.responsibility_id = fr.responsibility_id

 AND fa.LANGUAGE='US'

 AND fl.login_id = flr.login_id

 AND fu.user_id = fl.user_id

 AND fg.responsibility_id = flr.responsibility_id

 AND fg.end_date is Null

 and flr.start_time in (select max(start_time) from fnd_login_responsibilities group by RESPONSIBILITY_ID)

 ORDER BY fu.user_id,

 fr.responsibility_key,

 flr.start_time desc;

DB Context File

 egrep -i 's_undo_tablespace|s_db_oh|s_dbhost|s_dbSid|s_pdb_name|s_cdb_name|s_base|s_dbuser|s_dbgroup|s_dbhome1|s_display|s_dbCluster|s_isDBCluster|s_dbport|s_port_pool' $CONTEXT_FILE

Gather Information for IMP Performance Issues

 REM srdc_imp_performance.sql - Gather Information for IMP Performance Issues

define SRDCNAME = 'IMP_PERFORMANCE'

SET MARKUP HTML ON PREFORMAT ON

set TERMOUT off FEEDBACK off verify off TRIMSPOOL on HEADING off

set lines 132 pages 10000

COLUMN SRDCSPOOLNAME NOPRINT NEW_VALUE SRDCSPOOLNAME

select 'SRDC_'||upper ('&&SRDCNAME')||'_'||upper (instance_name)||'_'||to_char (sysdate, 'YYYYMMDD_HH24MISS') SRDCSPOOLNAME from v$instance;

set TERMOUT on MARKUP html preformat on 

REM

spool &&SRDCSPOOLNAME..htm

select '+----------------------------------------------------+' from dual

union all

select '| Diagnostic-Name: '||'&&SRDCNAME' from dual

union all

select '| Timestamp:       '||to_char (systimestamp, 'YYYY-MM-DD HH24:MI:SS TZH:TZM') from dual

union all

select '| Machine:         '||host_name from v$instance

union all

select '| Version:         '||version from v$instance

union all

select '| DBName:          '||name from v$database

union all

select '| Instance:        '||instance_name from v$instance

union all

select '+----------------------------------------------------+' from dual

/

set HEADING on MARKUP html preformat off

REM === -- end of standard header -- ===

set concat "#"

SET PAGESIZE 9999

SET LINESIZE 256

SET TRIMOUT ON

SET TRIMSPOOL ON

Column sid format 99999 heading "SESS|ID"

Column serial# format 9999999 heading "SESS|SER|#"

Column session_id format 99999 heading "SESS|ID"

Column session_serial# format 9999999 heading "SESS|SER|#"

Column event format a50

Column total_waits format 9,999,999,999 heading "TOTAL|TIME|WAITED|MICRO"

Column pga_used_mem format 9,999,999,999 

Column pga_alloc_mem format 9,999,999,999

Column status heading 'Status' format a20

Column timeout heading 'Timeout' format 999999

Column error_number heading 'Error Number' format 999999

Column error_msg heading 'Message' format a44 

Column sql_text heading 'Current SQL statement' format a50 

Column Number_of_objects format 99999999

Column object_type format a35

ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

SET MARKUP HTML ON PREFORMAT ON

--====================Retrieve sid, serial#, PGA details for the active import process(es)===========================

SET HEADING OFF 

SELECT '==================================Determine sid, serial#, PGA details for the active import process(es):=========================================' FROM dual;

SET HEADING ON 

set feedback on

SELECT sid, s.serial#, p.PGA_USED_MEM, p.PGA_ALLOC_MEM

FROM   v$process p, v$session s

WHERE  p.addr = s.paddr AND 

       UPPER (s.program) LIKE 'IMP%';

SET HEADING OFF 

set feedback off

SELECT '=================================================================================================================================================' FROM dual;

--====================Retrieve the number of objects per object_type===========================

SET HEADING OFF 

SELECT '==================================Determine the number of objects per object_type:===============================================================' FROM dual;

SET HEADING ON

set feedback on

SELECT count(*) number_of_objects, object_type 

FROM   dba_objects

group  by object_type order by number_of_objects desc;

SET HEADING OFF 

set feedback off

SELECT '=================================================================================================================================================' FROM dual;

--====================Retrieve all wait events and time in wait for the running import process(es)====================

SET HEADING OFF

SELECT '==================================Retrive all wait events and time in wait for the running import process(es):===================================' FROM dual;

SET HEADING ON

set feedback on

select session_id, session_serial#, Event, sum(time_waited) total_waits

from   v$active_session_history

where  sample_time > sysdate - 1 and 

       UPPER (program) LIKE 'IMP%' and 

       session_id in (select sid 

                      from   v$session where UPPER (program) LIKE 'IMP%') and 

       session_state = 'WAITING' and 

       time_waited > 0

group  by session_id, session_serial#, Event

order  by session_id, session_serial#, total_waits desc;

SET HEADING OFF 

set feedback off

SELECT '=================================================================================================================================================' FROM dual;

--====================Import progress - retrieve current sql id and statement====================

SET HEADING OFF

SELECT '==================================Import progress - retrieve current sql id and statement:=======================================================' FROM dual;

SET HEADING ON

set feedback on

select sysdate, a.sid, a.sql_id, a.event, b.sql_text

from   v$session a, v$sql b

where  a.sql_id = b.sql_id and 

       UPPER (a.program) LIKE 'IMP%'

order  by a.sid desc;

SET HEADING OFF 

set feedback off

SELECT '=================================================================================================================================================' FROM dual;

SET HEADING OFF MARKUP HTML OFF

SET SERVEROUTPUT ON FORMAT WRAP

declare

  CURSOR c_fix IS select v.KSPPSTVL value 

                  FROM   x$ksppi n, x$ksppsv v 

                  WHERE  n.indx = v.indx and 

                         n.ksppinm = 'fixed_date';  

  v_long_op_flag Number := 0 ;                        

  v_target varchar2(100); 

  v_sid number;

  v_totalwork Number;    

  v_opname varchar2(200);

  v_sofar Number;                        

  v_time_remain Number;  

  stmt varchar2(2000);

  v_fix c_fix%ROWTYPE; 

begin

  stmt := 'select count(*) from v$session_longops where sid in (select sid from v$session where UPPER(program) LIKE '||

          '''IMP%'')'||' and totalwork <> sofar';

  DBMS_OUTPUT.PUT_LINE('<pre>');

  dbms_output.put_line('==================================Check v$session_longops - Import pending work:=================================================================');

  dbms_output.put_line(chr(10));

  execute immediate stmt into v_long_op_flag;

  if (v_long_op_flag > 0 ) then      

    dbms_output.put_line ('The number of long running import processes is:   '||v_long_op_flag);

    dbms_output.put_line (chr (10));                        

    for longop in (select sid, target,opname, sum (totalwork) totwork, sum (sofar) sofar, sum (totalwork-sofar) blk_remain, Round (sum (time_remaining / 60), 2) time_remain

                   from   v$session_longops 

                   where  sid in (select sid 

                                  from   v$session 

                                  where  UPPER (program) LIKE 'IMP%') and 

                          opname NOT LIKE '%aggregate%' and 

                          totalwork <> sofar 

                   group  by sid, target, opname)                        

    loop  

      dbms_output.put_line (Rpad ('Import SID', 40, ' ')||chr (9)||':'||chr (9)||longop.sid); 

      dbms_output.put_line (Rpad ('Object being read', 40, ' ')||chr (9)||':'||chr (9)||longop.target);      

      dbms_output.put_line (Rpad ('Operation being executed', 40, ' ')||chr (9)||':'||chr (9)||longop.opname); 

      dbms_output.put_line (Rpad ('Total blocks to be read', 40, ' ')||chr (9)||':'||chr (9)||longop.totwork);                        

      dbms_output.put_line (Rpad ('Total blocks already read', 40, ' ')||chr (9)||':'||chr (9)||longop.sofar);                        

      dbms_output.put_line (Rpad ('Remaining blocks to be read', 40, ' ')||chr (9)||':'||chr (9)||longop.blk_remain);                        

      dbms_output.put_line (Rpad ('Estimated time remaining for the process', 40, ' ')||chr (9)||':'||chr (9)||longop.time_remain||' Minutes'); 

      dbms_output.put_line('=================================================================================================================================================');

  dbms_output.put_line (chr (10));

    end Loop;

  else

    DBMS_OUTPUT.PUT_LINE('No import session is found in v$session_longops');

dbms_output.put_line('=================================================================================================================================================');

    dbms_output.put_line (chr (10)); 

  end If;

  dbms_output.put_line('==================================Is the fixed_date parameter set?:==============================================================================');

  dbms_output.put_line(chr(10));

  open c_fix; 

  fetch c_fix into v_fix;

  if nvl (to_char (v_fix.value), '1') = to_char ('1') then

    DBMS_OUTPUT.PUT_LINE ('No value is found for fixed_date parameter');

  else

    DBMS_OUTPUT.PUT_LINE ('The fixed_date parameter is set for this database and the value is: '||v_fix.value);

  end if;

  dbms_output.put_line('=================================================================================================================================================');

  DBMS_OUTPUT.PUT_LINE('</pre>');

end;

/

spool off

PROMPT

PROMPT

PROMPT REPORT GENERATED : &SRDCSPOOLNAME..htm

exit

check_patchesi_19.sql

 -- -----------------------------------------------------------------------------------

-- File Name    : https://MikeDietrichDE.com/wp-content/scripts/19/

-- Author       : Mike Dietrich

-- Description  : Displays contents of the patches (BP/PSU) registry and history

-- Requirements : Access to the DBA role.

-- Call Syntax  : @check_patches_19.sql

-- Last Modified: 24/03/2020

-- Database Rel.: Oracle 19c

-- -----------------------------------------------------------------------------------

SET LINESIZE 500

SET PAGESIZE 1000

SET SERVEROUT ON

SET LONG 2000000

COLUMN action_time FORMAT A20

COLUMN action FORMAT A10

COLUMN status FORMAT A10

COLUMN description FORMAT A40

COLUMN source_version FORMAT A10

COLUMN target_version FORMAT A10

alter session set "_exclude_seed_cdb_view"=FALSE;

spool check_patches_19.txt

 select CON_ID,

        TO_CHAR(action_time, 'YYYY-MM-DD') AS action_time,

        PATCH_ID,

        PATCH_TYPE,

        ACTION,

        DESCRIPTION,

        SOURCE_VERSION,

        TARGET_VERSION

   from CDB_REGISTRY_SQLPATCH

  order by CON_ID, action_time, patch_id;

spool off

Orace DBA scripts: wait event scripts

 Orace DBA scripts: wait event scripts

-- sessions with highest CPU consumption

SELECT s.sid, s.serial#, p.spid as "OS PID",s.username, s.module, st.value/100 as "CPU sec"

FROM v$sesstat st, v$statname sn, v$session s, v$process p

WHERE sn.name = 'CPU used by this session' -- CPU

AND st.statistic# = sn.statistic#

AND st.sid = s.sid

AND s.paddr = p.addr

AND s.last_call_et < 3600 -- active within last 1/2 hour

AND s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours

-- sessions with the highest time for a certain wait

SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, se.time_waited

FROM v$session_event se, v$session s, v$process p

WHERE se.event = '&event_name'

AND s.last_call_et < 1800 -- active within last 1/2 hour

AND s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours

AND se.sid = s.sid

AND s.paddr = p.addr

ORDER BY se.time_waited;

-- sessions with highest DB Time usage

SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, st.value/100 as "DB Time (sec)"

, stcpu.value/100 as "CPU Time (sec)", round(stcpu.value / st.value * 100,2) as "% CPU"

FROM v$sesstat st, v$statname sn, v$session s, v$sesstat stcpu, v$statname sncpu, v$process p

WHERE sn.name = 'DB time' -- CPU

AND st.statistic# = sn.statistic#

AND st.sid = s.sid

AND  sncpu.name = 'CPU used by this session' -- CPU

AND stcpu.statistic# = sncpu.statistic#

AND stcpu.sid = st.sid

AND s.paddr = p.addr

AND s.last_call_et < 1800 -- active within last 1/2 hour

AND s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours

AND st.value > 0

--session statistics for a particular session :

 select  s.sid,s.username,st.name,se.value

from v$session s, v$sesstat se, v$statname st

where s.sid=se.SID and se.STATISTIC#=st.STATISTIC#

--and st.name ='CPU used by this session'

and s.username='&USERNAME'

order by s.sid,se.value desc

If non DBA user want to see its own statistics then it should grants as below:

grant select on sys.V_$SESSION to username;

grant select on sys.V_$STATNAME to username;

grant select on sys.V_$MYSTAT to username;

and replace view v$sesstat by v$mystat

another useful grants :

grant select on sys.V_$PROCESS to username;

grant select on sys.DBA_2PC_PENDING to username;

grant select on sys.v_$SQLTEXT to username;

grant select on sys.v_$SQL to username;

++++++++++++

Performance issue find highest resource consumption

Session with highest CPU consumption

SELECT s.sid, s.serial#, p.spid as "OS PID",s.username, s.module, st.value/100 as

"CPU sec"

FROM v$sesstat st, v$statname sn, v$session s, v$process p

WHERE sn.name = 'CPU used by this session' -- CPU

AND st.statistic# = sn.statistic#

AND st.sid = s.sid

AND s.paddr = p.addr

AND s.last_call_et (SYSDATE - 240/1440) -- sessions logged on within 4 hours

ORDER BY st.value;

Sessions with the highest time for a certain wait

SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, se.time_waited

FROM v$session_event se, v$session s, v$process p

WHERE se.event = '&event_name'

AND s.last_call_et (SYSDATE - 240/1440) -- sessions logged on within 4 hours

AND se.sid = s.sid

AND s.paddr = p.addr

ORDER BY se.time_waited;

Sessions with highest DB Time usage

SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, st.value/100 as

"DB Time (sec)"

, stcpu.value/100 as "CPU Time (sec)", round(stcpu.value / st.value * 100,2) as "%

CPU"

FROM v$sesstat st, v$statname sn, v$session s, v$sesstat stcpu, v$statname sncpu, v

$process p

WHERE sn.name = 'DB time' -- CPU

AND st.statistic# = sn.statistic#

AND st.sid = s.sid

AND sncpu.name = 'CPU used by this session' -- CPU

AND stcpu.statistic# = sncpu.statistic#

AND stcpu.sid = st.sid

AND s.paddr = p.addr

AND s.last_call_et (SYSDATE - 240/1440) -- sessions logged on within 4 hours

AND st.value > 0;

++++++++++++

O CHECK FOR TABLE LOCKS

set pagesize 400

set linesize 600

col USERNAME for a15

col OS_USER_NAME for a15

col TERMINAL for a15

col OBJECT_NAME for a30

SELECT a.sid,a.serial#, a.username,c.os_user_name,a.terminal, b.object_id,substr(b.object_name,1,40) object_name

from v$session a, dba_objects b, v$locked_object c where a.sid = c.session_id and b.object_id = c.object_id;

Steps for releasing lock on a table:

Finding Locks

select session_id "sid",SERIAL# "Serial",

substr(object_name,1,20) "Object",

substr(os_user_name,1,10) "Terminal",

substr(oracle_username,1,10) "Locker",

nvl(lockwait,'active') "Wait",

decode(locked_mode,

2, 'row share',

3, 'row exclusive',

4, 'share',

5, 'share row exclusive',

6, 'exclusive', 'unknown') "Lockmode",

OBJECT_TYPE "Type"

FROM

SYS.V_$LOCKED_OBJECT A,

SYS.ALL_OBJECTS B,

SYS.V_$SESSION c

WHERE

A.OBJECT_ID = B.OBJECT_ID AND

C.SID = A.SESSION_ID

ORDER BY 1 ASC, 5 Desc

Finding Blocking sessions :

select l1.sid, ' IS BLOCKING ', l2.sid

from v$lock l1, v$lock l2 where l1.block =1 and l2.request > 0

and l1.id1=l2.id1 and l1.id2=l2.id2

select s1.username '@' s1.machine ' ( SID=' s1.sid ' ) is blocking '

s2.username '@' s2.machine ' ( SID=' s2.sid ' ) ' AS blocking_status

from v$lock l1, v$session s1, v$lock l2, v$session s2 where s1.sid=l1.sid and s2.sid=l2.sid

and l1.BLOCK=1 and l2.request > 0 and l1.id1 = l2.id1 and l2.id2 = l2.id2 ;

Sessions with highest CPU consumption :

SELECT s.sid, s.serial#, p.spid as "OS PID",s.username, s.module, st.value/100 as "CPU sec"

FROM v$sesstat st, v$statname sn, v$session s, v$process p

WHERE sn.name = 'CPU used by this session' -- CPU

AND st.statistic# = sn.statistic#

AND st.sid = s.sid

AND s.paddr = p.addr

AND s.last_call_et <> (SYSDATE - 240/1440) -- sessions logged on within 4 hours

ORDER BY st.value;

Sessions with the highest time for a certain wait  :

SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, se.time_waited

FROM v$session_event se, v$session s, v$process p

WHERE se.event = '&event_name'

AND s.last_call_et <> (SYSDATE - 240/1440) -- sessions logged on within 4 hours

AND se.sid = s.sid

AND s.paddr = p.addr

ORDER BY se.time_waited;

Sessions with highest DB Time usage :

SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, st.value/100 as "DB Time (sec)"

, stcpu.value/100 as "CPU Time (sec)", round(stcpu.value / st.value * 100,2) as "% CPU"

FROM v$sesstat st, v$statname sn, v$session s, v$sesstat stcpu, v$statname sncpu, v$process p

WHERE sn.name = 'DB time' -- CPU

AND st.statistic# = sn.statistic#

AND st.sid = s.sid

AND sncpu.name = 'CPU used by this session' -- CPU

AND stcpu.statistic# = sncpu.statistic#

AND stcpu.sid = st.sid

AND s.paddr = p.addr

AND s.last_call_et <> (SYSDATE - 240/1440) -- sessions logged on within 4 hours

AND st.value > 0; 

 

Step1:To verify the lock object Here is the import query:

---------------------------------------------------------------

SELECT o.owner, o.object_name, o.object_type, o.last_ddl_time, o.status, l.session_id, l.oracle_username, l.locked_mode

FROM dba_objects o, gv$locked_object l

WHERE o.object_id = l.object_id;

Step 2: Find the serial# for the sessions holding the lock:

SQL> select SERIAL# from v$session where SID=667;

SERIAL#

----------

21091

SQL> alter system kill session '667,21091';

System altered.

How to release a lock in Oracle

This mostly comes from two other sources Killing Oracle Session and What’s blocking my lock?

This involves the system tables v$lock and v$session, and using the ‘ALTER SYSTEM’ statement. This can all be done via sqlplus as the system user (you don’t need to be sysdba).

First, determine who’s holding the lock:

select

 s1.username || '@' || s1.machine

 || ' ( SID,S#=' || s1.sid || ',' || s1.serial# || ' )  is blocking '

 || s2.username || '@' || s2.machine

 || ' ( SID,S#=' || s2.sid || ',' || s2.serial# || ' )'

  AS blocking_status

from

 v$lock l1,

 v$session s1,

 v$lock l2,

 v$session s2

where

 s1.sid = l1.sid

 and s2.sid = l2.sid

 and l1.BLOCK = 1

 and l2.request > 0

 and l1.id1 = l2.id1

 and l2.id2 = l2.id2;

 

 

The result set will look like this:

BLOCKING_STATUS

--------------------------------------------------------------------------------

BEN@INTWAREPOD2145 ( SID,S#=134,11102 )  is blocking BEN@INTWAREPOD2145 ( SID,S#=128,30076 )

 

 

Now that we know which session is doing the blocking, (134,11102) in this case, we can kill it.

SQL> alter system kill session '134,11102';

 

Note, the alter system statement is powerful stuff, so take care with it.

=++++++

Database Important Scripts

Hit Ratios

select Round(100*(cg.value+db.value-pr.value)/(cg.value+db.value),2) "Buffer Hit Ratio"

from v$sysstat db, v$sysstat cg, v$sysstat pr

where db.name = 'db block gets'

and cg.name = 'consistent gets'

and pr.name = 'physical reads'

/

Check the active process

ps -fu applmgr

Kill the Active process

kill -9 `ps -u applmgr -o "pid="`

Clear the whole content and remove all lines inside the file then :

Code:

cat /dev/null > your_file

Total TBS Size

SELECT space.tablespace_name, space.total_space, free.total_free,

ROUND(free.total_free/space.total_space*100) as pct_free,

ROUND((space.total_space-free.total_free),2) as total_used,

ROUND((space.total_space-free.total_free)/space.total_space*100) as pct_used,

free.max_free, next.max_next_extent

FROM

(SELECT tablespace_name, SUM(bytes)/1024/1024 total_space

FROM dba_data_files

GROUP BY tablespace_name) space,

(SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024,2) total_free, ROUND(MAX(bytes)/1024/1024,2) max_free

FROM dba_free_space

GROUP BY tablespace_name) free,

(SELECT tablespace_name, ROUND(MAX(next_extent)/1024/1024,2) max_next_extent FROM dba_segments

GROUP BY tablespace_name) NEXT

WHERE space.tablespace_name = free.tablespace_name (+)

AND space.tablespace_name = next.tablespace_name (+)

AND (ROUND(free.total_free/space.total_space*100)<> free.max_free)

order by pct_used desc

/

Free Space Size

select tablespace_name, bytes/1024/1024 from dba_free_space

/

Last Analyzed

select max(last_analyzed) from dba_tables

/

TEMP TBS Size

select file_name, sum(bytes)/(1024*1024) from dba_temp_files

group by file_name

/

select tablespace_name, sum(bytes)/(1024*1024) TEMPSIZE from dba_temp_files

group by tablespace_name

/

Script: Listing Memory Used By All Sessions

select se.sid,n.name,

max(se.value) maxmem

from v$sesstat se,

v$statname n

where n.statistic# = se.statistic#

and n.name in ('session pga memory','session pga memory max',

'session uga memory','session uga memory max')

group by n.name,se.sid

order by 3

/

SCRIPT: How to Determine the Number of Disk Sorts vs Memory Sorts

select 'INIT.ORA sort_area_size: 'value

from v$parameter

where name like 'sort_area_size'

/

select a.name, value

from v$statname a, v$sysstat

where a.statistic# = v$sysstat.statistic#

and a.name in ('sorts (disk)', 'sorts (memory)', 'sorts (rows)')

/

This script lists all jobs that are currently running in the local database.

select

djr.sid sess,

djr.job jid,

dj.log_user subu,

dj.priv_user secd,

dj.what proc,

to_char(djr.last_date,'MM/DD') lsd,

substr(djr.last_sec,1,5) lst,

to_char(djr.this_date,'MM/DD') nrd,

substr(djr.this_sec,1,5) nrt,

djr.failures fail

from

sys.dba_jobs dj,

sys.dba_jobs_running djr

where

djr.job = dj.job

/

Lists all jobs that have been submitted to run in the local database job queue.

select

job jid,

log_user subu,

priv_user secd,

what proc,

to_char(last_date,'MM/DD') lsd,

substr(last_sec,1,5) lst,

to_char(next_date,'MM/DD') nrd,

substr(next_sec,1,5) nrt,

failures fail,

decode(broken,'Y','N','Y') ok

from

sys.dba_jobs

/

Compile

for

set heading off;

set pagesize 500;

spool c:\dba\compile.sql;

select 'alter ' object_type ' ' OBJECT_NAME ' compile ' ';' from dba_objects where object_type in ('FUNCTION','PACKAGE','PROCEDURE','TRIGGER','PACKAGE BODY','VIEW') AND STATUS ='INVALID';

spool off;

for running the script

@c:\dba\compile.sql

Section – A

This scripts is used to take a index rebuild

The following tables we have to recreate it, (FND_CONCURRENT_REQUESTS, FND_FILE_TEMP)

1) This scripts is used to take a index rebuild on following tables

(FND_CONCURRENT_REQUESTS, FND_FILE_TEMP)

(spool the following output)

select 'ALTER INDEX ' OWNER '.' INDEX_NAME ' REBUILD NOLOGGING;'

FROM DBA_INDEXES a

WHERE

a.TABLE_NAME in('FND_CONCURRENT_REQUESTS','FND_FILE_TEMP')

and partitioned= 'NO'

union

select 'ALTER INDEX ' a.OWNER '.' b.INDEX_NAME ' REBUILD PARTITION 'b.partition_name' NOLOGGING;'

from dba_indexes a,dba_ind_partitions b

where a.indeX_name = b.index_name

and a.TABLE_NAME in ('FND_CONCURRENT_REQUESTS','FND_FILE_TEMP')

and partitioned= 'YES'

2) ALTER TABLE APPLSYS.FND_CONCURRENT_REQUESTS move;

3) ALTER TABLE APPLSYS.FND_FILE_TEMP move;

3) run the index rebuild outuput scripts.

4) run the gather table statistics for mentioned tables

(FND_CONCURRENT_REQUESTS, FND_FILE_TEMP)

TABLE

select segment_name, owner, extents, max_extents

from dba_segments

where segment_type = 'TABLE'

and (extents +1) >= max_extents;

ALTER TABLE .table STORAGE ( MAXEXTENTS x);

where x is greater than max_extents and lesser than unlimited

(2147483645);

ALTER TABLE .table STORAGE ( MAXEXTENTS UNLIMITED);

INDEX

select segment_name, owner, extents, max_extents

from dba_segments

where segment_type = 'INDEX' and

(extents +1) >= max_extents;

ALTER INDEX .index STORAGE ( MAXEXTENTS integer);

ALTER INDEX .index STORAGE ( MAXEXTENTS UNLIMITED);

Section B

The following script's output is used to run a index rebuild on weekly basis

select 'ALTER INDEX ' OWNER '.' INDEX_NAME ' REBUILD NOLOGGING;'

FROM DBA_INDEXES a

WHERE OWNER NOT IN ('SYS','SYSTEM')

AND a.INDEX_TYPE='NORMAL'

AND NOT EXISTS (SELECT INDEX_NAME FROM DBA_IND_PARTITIONS

WHERE INDEX_OWNER NOT IN ('SYS','SYSTEM')

AND INDEX_NAME = a.INDEX_NAME)

AND TABLE_NAME NOT IN (SELECT TABLE_NAME from dba_tables c

where TEMPORARY='Y'

and a.table_name = c.table_name)

ORDER BY OWNER,INDEX_NAME;

/

for

set heading off;

set pagesize 500;

spool c:\dba\compile.sql;

select 'alter ' object_type ' ' OBJECT_NAME ' compile ' ';' from dba_objects where object_type in ('FUNCTION','PACKAGE','PROCEDURE','TRIGGER','VIEW') AND STATUS ='INVALID';

spool off;

for running the script

select 'alter ' 'PACKAGE ' OBJECT_NAME ' compile body' ';' from dba_objects where object_type in ('PACKAGE BODY') AND STATUS ='INVALID';

/

spool runts.sql

select 'alter database datafile '''file_name''''' autoextend on;' from dba_data_files;

/

@runts

The biggest portion of a database's size comes from the datafiles. To find out how many megabytes are allocated to ALL datafiles:

select sum(bytes)/1024/1024 "Meg" from dba_data_files;

To get the size of all TEMP files:

select nvl(sum(bytes),0)/1024/1024 "Meg" from dba_temp_files;

To get the size of the on-line redo-logs:

select sum(bytes)/1024/1024 "Meg" from sys.v_$log;

Putting it all together into a single query:

select a.data_size+b.temp_size+c.redo_size "total_size"

from ( select sum(bytes) data_size

from dba_data_files ) a,

( select nvl(sum(bytes),0) temp_size

from dba_temp_files ) b,

( select sum(bytes) redo_size

from sys.v_$log ) c

/

select to_char(creation_time, 'RRRR Month') "Month",

sum(bytes)/1024/1024 "Growth in Meg"

from sys.v_$datafile

where creation_time > SYSDATE-365

group by to_char(creation_time, 'RRRR Month')

/

SESSION_WAITS

SELECT NVL(s.username, '(oracle)') AS username,

s.sid,

s.serial#,

sw.event,

sw.wait_time,

sw.seconds_in_wait,

sw.state

FROM v$session_wait sw,

v$session s

WHERE s.sid = sw.sid

ORDER BY sw.seconds_in_wait DESC;

/

To select the username and the process status

select a.requested_start_date,a.last_update_date,a.status_code,b.user_name

from fnd_concurrent_requests a,fnd_user b where a.requested_by = b.user_id and a.request_id = 677224

selecta.requested_start_date,a.last_update_date,a.status_code,b.user_name ,a.argument_text from fnd_concurrent_requests a,fnd_user b where a.requested_by = b.user_id and a.request_id = 677224

To select the username,process,status,Terminal name using SID

select a.status,p.spid, a.sid, a.serial#, a.username, a.terminal,

a.osuser, c.Consistent_Gets, c.Block_Gets, c.Physical_Reads,

(100*(c.Consistent_Gets+c.Block_Gets-c.Physical_Reads)/

(c.Consistent_Gets+c.Block_Gets)) HitRatio, c.Physical_Reads, b.sql_text

from v$session a, v$sqlarea b, V$SESS_IO c,v$process p

where a.sql_hash_value = b.hash_value

and a.SID = c.SID

and p.addr = a.paddr

and (c.Consistent_Gets+c.Block_Gets)>0

and a.Username is not null

Order By a.status asc, c.Consistent_Gets desc , c.Physical_Reads desc;

/

To see the currently updated archive log files

SQL>select name from v$archived_log where trunc(completion_time) >= trunc(sysdate)-5;

To find the BDUMP,UDUMP directory

select value from v$parameter where name = 'background_dump_dest'

select value from v$parameter where name = 'user_dump_dest'

select value from v$parameter where name in ('background_dump_dest','user_dump_dest', 'log_archive_dest')

/

Identify the user and session ID for a UNIX process

This quick process identifies the Oracle user and session ID of a UNIX process that is using up a large amount of CPU. It can also be used to help find inefficient queries. This query is placed inside of a script that I pass the UNIX process ID to.

select s.sid, s.username, s.osuser, s.serial#

from v$session s, v$process p

where s.paddr = p.addr

and p.spid = &1;

/

Tracing an Oracle session by SID

This code accepts an Oracle session ID [SID] as a parameter and will show you what SQL statement is running in that session and what event the session is waiting for. You simply create a SQL file of the code and run it from the SQL prompt.

prompt Showing running sql statements ...........................

select addr from v$process where spid='8419'

select * from v$session where PADDR='00000003B29F9388'

select a.sid Current_SID, a.last_call_et ,b.sql_text

from v$session a

,v$sqltext b

where a.sid = 14

and a.username is not null

and a.status = 'ACTIVE'

and a.sql_address = b.address

order by a.last_call_et,a.sid,b.piece;

prompt Showing what sql statement is doing.....................

select a.sid, a.value session_cpu, c.physical_reads,

c.consistent_gets,d.event,

d.seconds_in_wait

from v$sesstat a,v$statname b, v$sess_io c, v$session_wait d

where a.sid= 14

and b.name = 'CPU used by this session'

and a.statistic# = b.statistic#

and a.sid=c.sid

and a.sid=d.sid;

/

Check all active processes, the latest SQL, and the SQL hit ratio

select a.status, a.sid, a.serial#, a.username, a.terminal,

a.osuser, c.Consistent_Gets, c.Block_Gets, c.Physical_Reads,

(100*(c.Consistent_Gets+c.Block_Gets-c.Physical_Reads)/

(c.Consistent_Gets+c.Block_Gets)) HitRatio, c.Physical_Reads, b.sql_text

from v$session a, v$sqlarea b, V$SESS_IO c

where a.sql_hash_value = b.hash_value

and a.SID = c.SID

and (c.Consistent_Gets+c.Block_Gets)>0

and a.Username is not null

and a.status = 'ACTIVE'

Order By a.status asc, c.Consistent_Gets desc , c.Physical_Reads desc;

Monitoring Oracle processes

select p.spid "Thread ID", b.name "Background Process", s.username

"User Name",

s.osuser "OS User", s.status "STATUS", s.sid "Session ID",

s.serial# "Serial No.",

s.program "OS Program"

from v$process p, v$bgprocess b, v$session s

where s.paddr = p.addr and b.paddr(+) = p.addr

order by s.status,1;

/

Displays concurrent requests that have run times longer than one hour (3600 seconds)

SELECT REQUEST_ID,

TO_CHAR(ACTUAL_START_DATE,'MM/DD/YY HH:MI:SS') starttime,

TO_CHAR(ACTUAL_COMPLETION_DATE,'MM/DD/YY HH:MI:SS') endtime,

ROUND((ACTUAL_COMPLETION_DATE - ACTUAL_START_DATE)*(60*24),2) rtime,

OUTCOME_CODE,phase_code,status_code,

printer,print_style,description,

SUBSTR(completion_text,1,20) compl_txt

FROM fnd_concurrent_requests

WHERE to_date(ACTUAL_START_DATE,'DD-MON-RRRR') = to_date(sysdate,'DD-

MON-RRRR')

ORDER BY 2 desc

/

This script will map concurrent manager process information about current concurrent managers.

SELECT proc.concurrent_process_id concproc,

SUBSTR(proc.os_process_id,1,6) clproc,

SUBSTR(LTRIM(proc.oracle_process_id),1,15) opid,

SUBSTR(vproc.spid,1,10) svrproc,

DECODE(proc.process_status_code,'A','Active',

proc.process_status_code) cstat,

SUBSTR(concq.concurrent_queue_name,1,30) qnam,

-- SUBSTR(proc.logfile_name,1,20) lnam,

SUBSTR(proc.node_name,1,10) nnam,

SUBSTR(proc.db_name,1,8) dbnam,

SUBSTR(proc.db_instance,1,8) dbinst,

SUBSTR(vsess.username,1,10) dbuser

FROM fnd_concurrent_processes proc,

fnd_concurrent_queues concq,

v$process vproc,

v$session vsess

WHERE proc.process_status_code = 'A'

AND proc.queue_application_id = concq.application_id

AND proc.concurrent_queue_id = concq.concurrent_queue_id

AND proc.oracle_process_id = vproc.pid(+)

AND vproc.addr = vsess.paddr(+)

ORDER BY proc.queue_application_id,

proc.concurrent_queue_id

Show currently running concurrent requests

SELECT SUBSTR(LTRIM(req.request_id),1,15) concreq,

SUBSTR(proc.os_process_id,1,15) clproc,

SUBSTR(LTRIM(proc.oracle_process_id),1,15) opid,

SUBSTR(look.meaning,1,10) reqph,

SUBSTR(look1.meaning,1,10) reqst,

SUBSTR(vsess.username,1,10) dbuser,

SUBSTR(vproc.spid,1,10) svrproc,

vsess.sid sid,

vsess.serial# serial#

FROM fnd_concurrent_requests req,

fnd_concurrent_processes proc,

fnd_lookups look,

fnd_lookups look1,

v$process vproc,

v$session vsess

WHERE req.controlling_manager = proc.concurrent_process_id(+)

AND req.status_code = look.lookup_code

AND look.lookup_type = 'CP_STATUS_CODE'

AND req.phase_code = look1.lookup_code

AND look1.lookup_type = 'CP_PHASE_CODE'

AND look1.meaning = 'Running'

AND proc.oracle_process_id = vproc.pid(+)

AND vproc.addr = vsess.paddr(+);

/

To find the CPU consumption

select ss.sid,w.event,command,ss.value CPU ,se.username,se.program, wait_time, w.seq#, q.sql_text,command

from

v$sesstat ss, v$session se,v$session_wait w,v$process p, v$sqlarea q

where ss.statistic# in

(select statistic#

from v$statname

where name = 'CPU used by this session')

and se.sid=ss.sid

and ss.sid>6

and se.paddr=p.addr

and se.sql_address=q.address

order by ss.value desc,ss.sid

/

Script to show problem tablespaces

SELECT space.tablespace_name, space.total_space, free.total_free,

ROUND(free.total_free/space.total_space*100) as pct_free,

ROUND((space.total_space-free.total_free),2) as total_used,

ROUND((space.total_space-free.total_free)/space.total_space*100) as pct_used,

free.max_free, next.max_next_extent

FROM

(SELECT tablespace_name, SUM(bytes)/1024/1024 total_space

FROM dba_data_files

GROUP BY tablespace_name) space,

(SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024,2) total_free, ROUND(MAX(bytes)/1024/1024,2) max_free

FROM dba_free_space

GROUP BY tablespace_name) free,

(SELECT tablespace_name, ROUND(MAX(next_extent)/1024/1024,2) max_next_extent FROM dba_segments

GROUP BY tablespace_name) NEXT

WHERE space.tablespace_name = free.tablespace_name (+)

AND space.tablespace_name = next.tablespace_name (+)

AND (ROUND(free.total_free/space.total_space*100)<> free.max_free)

order by pct_used desc

Oracle space monitoring scripts table space wise

This scripts gives warning indicator for all tablespaces that have less then 90% free space in them (with an asterisk in the last column).

select tbs.tablespace_name,

tot.bytes/(1024*1024) "Total Space in MB",

round(tot.bytes/(1024*1024)- sum(nvl(fre.bytes,0))/(1024*1024),2) "Used in MB",

round(sum(nvl(fre.bytes,0))/(1024*1024),2) "Free in MB",

round((1-sum(nvl(fre.bytes,0))/tot.bytes)*100,2) Pct,

decode(

greatest((1-sum(nvl(fre.bytes,0))/tot.bytes)*100, 90),

90, '', '*'

) Pct_warn

from dba_free_space fre,

(select tablespace_name, sum(bytes) bytes

from dba_data_files

group by tablespace_name) tot,

dba_tablespaces tbs

where tot.tablespace_name = tbs.tablespace_name

and fre.tablespace_name(+) = tbs.tablespace_name

group by tbs.tablespace_name, tot.bytes/(1024*1024), tot.bytes

order by 5 desc, 1 ;

Oracle space monitoring scripts (grand total table space)

select

sum(tot.bytes/(1024 *1024))"Total size",

sum(tot.bytes/(1024*1024)-sum(nvl(fre.bytes,0))/(1024*1024)) Used,

sum(sum(nvl(fre.bytes,0))/(1024*1024)) Free,

sum((1-sum(nvl(fre.bytes,0))/tot.bytes)*100) Pct

from dba_free_space fre,

(select tablespace_name, sum(bytes) bytes

from dba_data_files

group by tablespace_name) tot,

dba_tablespaces tbs

where tot.tablespace_name = tbs.tablespace_name

and fre.tablespace_name(+) = tbs.tablespace_name

group by tbs.tablespace_name, tot.bytes/(1024*1024), tot.bytes

/

What's holding up the system?

Poorly written SQL is another big problem. Use the following SQL to determine the UNIX pid:

Select

p.pid, s.sid, s.serial#,s.status, s.machine,s.osuser, p.spid, t.sql_text

From

v$session s,

v$sqltext t,

v$process p

Where

s.sql_address = t.address and

s.paddr = p.addr and

s.sql_hash_value = t.hash_value and

s.sid > 7 and

s.audsid != userenv ('SESSIONID')

Order By s.status,s.sid, s.osuser, s.process, t.piece ;

/

Script to display status of all the Concurrent Managers

select distinct Concurrent_Process_Id CpId, PID Opid,

Os_Process_ID Osid, Q.Concurrent_Queue_Name Manager,

P.process_status_code Status,

To_Char(P.Process_Start_Date, 'MM-DD-YYYY HH:MI:SSAM') Started_At

from Fnd_Concurrent_Processes P, Fnd_Concurrent_Queues Q, FND_V$Process

where Q.Application_Id = Queue_Application_ID

and Q.Concurrent_Queue_ID = P.Concurrent_Queue_ID

and Spid = Os_Process_ID

and Process_Status_Code not in ('K','S')

order by Concurrent_Process_ID, Os_Process_Id, Q.Concurrent_Queue_Name

Get current SQL from SGA

select sql_text

from V$session s , V$sqltext t

where s.sql_address=t.address

and sid=

order by piece;

You can find the SID from V$session.

What SQL is running and who is running it?

select a.sid,a.serial#,a.username,b.sql_text

from v$session a,v$sqltext b

where a.username is not null

and a.status = 'ACTIVE'

and a.sql_address = b.address

order by 1,2,b.piece;

---

select decode(sum(decode(s.serial#,l.serial#,1,0)),0,'No','Yes') " ",

s.sid "Session ID",s.status "Status",

s.username "Username", RTRIM(s.osuser) "OS User",

b.spid "OS Process ID",s.machine "Machine Name",

s.program "Program",c.sql_text "SQL text"

from v$session s, v$session_longops l,v$process b,

(select address,sql_text from v$sqltext where piece=0) c

where (s.sid = l.sid(+)) and s.paddr=b.addr and s.sql_address = c.address

group by s.sid,s.status,s.username,s.osuser,s.machine,

s.program,b.spid, b.pid, c.sql_text order by s.status,s.sid

TO FIND THE SORTING DETAILS

SELECT a.sid,a.value,b.name from

V$SESSTAT a, V$STATNAME b

WHERE a.statistic#=b.statistic#

AND b.name LIKE 'sort%'

ORDER BY 1;

/

Long running SQL statements

SELECT s.rows_processed, s.loads, s.executions, s.buffer_gets,

s.disk_reads, t.sql_text,s.module, s.ACTION

FROM v$sql /*area*/ s,

v$sqltext t

WHERE s.address = t.address

AND ((buffer_gets > 10000000) or

(disk_reads > 1000000) or

(executions > 1000000))

ORDER BY ((s.disk_reads * 100) + s.buffer_gets) desc, t.address, t.piece

/

Move a table from one tablespace to another

There are many ways to move a table from one tablespace to another. For example, you can create a duplicate table with dup_tab as select * from original_tab; drop the original table and rename the duplicate table as the original one.

The second option is exp table, drop it from the database and import it back. The third option (which is the one I am most interested in) is as follows.

Suppose you have a dept table in owner scott in the system tablespace and you want to move in Test tablespace.

connect as sys

SQL :> select table_name,tablespace_name from dba_tables where table_name='DEPT' and owner='SCOTT';

TABLE_NAME TABLESPACE_NAME

------------------------------ ------------------------------

DEPT SYSTEM

Elapsed: 00:00:00.50

You want to move DEPT table from system to say test tablespace.

SQL :> connect scott/tiger

Connected.

SQL :> alter table DEPT move tablespace TEST;

Table altered.

SQL :> connect

Enter user-name: sys

Enter password:

Connected.

SQL :> select table_name,tablespace_name from dba_tables where table_name='DEPT' and owner='SCOTT';

TABLE_NAME TABLESPACE_NAME

------------------------------ ------------------------------

DEPT TEST

/

To compile the procedure

Alter PROCEDURE JA_IN_BULK_PO_QUOTATION_TAXES compile

To compile the form

F60gen userid=apps/metroapps@dev module=

.fmb

output_file=/forms/US/form name.fmx

module_type=form batch=no compile_all=special

/

Run this sql statement to get package version :

select text from user_source where name='&package_name'

and text like '%$Header%';

prompt asks you the package name, in return it gives you two lines

corresponding to specifications and body creation files

You can also get pls version on database by running:

select name , text

from dba_source

where text like '%.pls%'

and line <>views

Sometimes version information is available in view definition.

Try the following sql statement :

col TEXT for a40 head "TEXT"

select VIEW_NAME, TEXT

from USER_VIEWS

where VIEW_NAME = '&VIEW_NAME';

workflow

Run wfver.sql (see §5.e) to get version of workflow packages and views.

Finding active and inactive sessions :

set linesize 132

col started format a15

col osuser format a10

col username format a13

col sid format 99999

select d.physical_reads, p.spid,a.sid, a.serial#, a.username, a.osuser,

TO_CHAR(a.logon_time, 'MM/DD HH24:MI') started, a.sql_hash_value,status

from sys.v_$session a, sys.v_$process p, sys.v_$sess_io d

where a.sid = d.sid and a.paddr = p.addr and a.type <> 'BACKGROUND'

and a.status = 'INACTIVE'

order by a.username,a.logon_time

Select 'alter system kill session '''sid','serial#''';' from

V$session where status='INACTIVE';

select p.spid,s.status,s.username,s.machine,s.sid,s.serial#,s.program,

s.osuser,s.sql_address from v$process p,v$session s

where s.paddr = p.addr and s.sid in(&sid)

Finding Locks

select session_id "sid",SERIAL# "Serial",

substr(object_name,1,20) "Object",

substr(os_user_name,1,10) "Terminal",

substr(oracle_username,1,10) "Locker",

nvl(lockwait,'active') "Wait",

decode(locked_mode,

2, 'row share',

3, 'row exclusive',

4, 'share',

5, 'share row exclusive',

6, 'exclusive', 'unknown') "Lockmode",

OBJECT_TYPE "Type"

FROM

SYS.V_$LOCKED_OBJECT A,

SYS.ALL_OBJECTS B,

SYS.V_$SESSION c

WHERE

A.OBJECT_ID = B.OBJECT_ID AND

C.SID = A.SESSION_ID

ORDER BY 1 ASC, 5 Desc

Finding Blocking sessions :

select l1.sid, ' IS BLOCKING ', l2.sid

from v$lock l1, v$lock l2 where l1.block =1 and l2.request > 0

and l1.id1=l2.id1 and l1.id2=l2.id2

select s1.username '@' s1.machine ' ( SID=' s1.sid ' ) is blocking '

s2.username '@' s2.machine ' ( SID=' s2.sid ' ) ' AS blocking_status

from v$lock l1, v$session s1, v$lock l2, v$session s2 where s1.sid=l1.sid and s2.sid=l2.sid

and l1.BLOCK=1 and l2.request > 0 and l1.id1 = l2.id1 and l2.id2 = l2.id2 ;

-- sessions with highest CPU consumption

SELECT s.sid, s.serial#, p.spid as "OS PID",s.username, s.module, st.value/100 as "CPU sec"

FROM v$sesstat st, v$statname sn, v$session s, v$process p

WHERE sn.name = 'CPU used by this session' -- CPU

AND st.statistic# = sn.statistic#

AND st.sid = s.sid

AND s.paddr = p.addr

AND s.last_call_et <> (SYSDATE - 240/1440) -- sessions logged on within 4 hours

ORDER BY st.value;

-- sessions with the highest time for a certain wait

SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, se.time_waited

FROM v$session_event se, v$session s, v$process p

WHERE se.event = '&event_name'

AND s.last_call_et <> (SYSDATE - 240/1440) -- sessions logged on within 4 hours

AND se.sid = s.sid

AND s.paddr = p.addr

ORDER BY se.time_waited;

-- sessions with highest DB Time usage

SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, st.value/100 as "DB Time (sec)"

, stcpu.value/100 as "CPU Time (sec)", round(stcpu.value / st.value * 100,2) as "% CPU"

FROM v$sesstat st, v$statname sn, v$session s, v$sesstat stcpu, v$statname sncpu, v$process p

WHERE sn.name = 'DB time' -- CPU

AND st.statistic# = sn.statistic#

AND st.sid = s.sid

AND sncpu.name = 'CPU used by this session' -- CPU

AND stcpu.statistic# = sncpu.statistic#

AND stcpu.sid = st.sid

AND s.paddr = p.addr

AND s.last_call_et <> (SYSDATE - 240/1440) -- sessions logged on within 4 hours

AND st.value > 0;

db file scattered read

Systemwide Waits:

If the TIME spent waiting for multiblock reads is significant then it can be helpful to determine which segment/s Oracle is performing the reads against. The files where the reads are occuring can be found by looking at where BLKS_READ / READS > 1 . (A ratio greater than 1 indicates there are some multiblock reads occuring).

It can also be useful to see which sessions are performing scans and trace them to see if the scans are expected or not. This statement can be used to see which sessions may be worth tracing:

SELECT sid, total_waits, time_waited FROM v$session_event WHERE event='db file scattered read' and total_waits>0 ORDER BY 3,2;

One can also look at:

Statements with high DISK_READS in Sessions with high table scans blocks gotten in

db file sequential read

Systemwide Waits:IO is a normal activity so you are really interested in unnecessary or slow IO activity. If the TIME spent waiting for IOs is significant then we can determine which segment/s Oracle has to go to disk for. See the "Tablespace IO" and "File IO" sections of the ESTAT or STATSPACK reports to get information on which tablespaces / files are servicing the most IO requests, and to get an indication of the speed of the IO subsystem. If the TIME spent waiting for reads is significant then it can be helpful to determine which segment/s Oracle is performing the reads against. The files where the reads are occuring can be found by looking at .

It can also be useful to see which sessions are performing reads and trace them to see if the IOs are expected or not. This statement can be used to see which sessions may be worth tracing:

SELECT sid, total_waits, time_waited FROM v$session_event WHERE event='db file sequential read' and total_waits>0 ORDER BY 3,2;

One can also look at:

Statements with high DISK_READS in Sessions with high "physical reads" in

Undo Tablespace Check

set linesize 150

col username format a18

col sid format 99999

col object_name format a18

select s.username,s.sid,rn.name,rs.rssize/1024/1024 "UsedSize",rs.status,t.used_ublk,t.used_urec,do.object_name

from V$TRANSACTION t,V$SESSION s,V$ROLLNAME rn,V$ROLLSTAT rs,V$LOCKED_OBJECT lo,DBA_OBJECTS do

where t.addr = s.taddr

and t.xidusn = rn.usn

and rn.usn = rs.usn

and t.xidusn = lo.xidusn(+)

and do.object_id = lo.object_id;

Temp Tablespace Check

prompt

prompt +----------------------------------------------------+

prompt TEMP TABLESPACE USAGE BY SESSION

prompt +----------------------------------------------------+

--Temp TS usage by each session:

select b.tablespace

,a.sid,

sum(round(((b.blocks*p.value)/1024/1024),2)) size_mb

from v$session a

,v$sort_usage b

,v$process c

,v$parameter p

where p.name='db_block_size' and a.saddr = b.session_addr and

a.paddr=c.addr

group by b.tablespace,a.sid

order by sum(round(((b.blocks*p.value)/1024/1024),2)) desc

Extract the DDL Scripts for the existing database links:

SELECT

'create 'DECODE(U.NAME,'PUBLIC','public ')'database link 'CHR(10)

DECODE(U.NAME,'PUBLIC',Null, U.NAME'.') L.NAMEchr(10)

'connect to ' L.USERID ' identified by '''

L.PASSWORD''' using ''' L.host ''''

chr(10)';' TEXT

FROM sys.link$ L,

sys.user$ U

WHERE L.OWNER# = U.USER# ;

Please change the oracle directories appropriately:

select 'create or replace directory 'OWNER'.'DIRECTORY_NAME ' as ' ''''DIRECTORY_PATH''''';' from DBA_DIRECTORIES;

How to check database bit like 32bit or 64bit

SELECT distinct('This is a ' (length(addr)*4) '-bit database') "WordSize" FROM v$process;

select PLATFORM_ID, PLATFORM_NAME from v$database;

Check the PROFILE OPTIONS VALUE

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.profile_option_name LIKE ‘%&&profile%’

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

AND 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,

ORDER BY "NAME", pov.level_id, "VALUE";

***************

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.profile_option_name LIKE 'ICX_FORMS_LAUNCHER'

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

*********

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.profile_option_name LIKE '%&&profile%'

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

AND 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,

'???') LIKE '%&&username%'

ORDER BY "NAME", pov.level_id, "VALUE";

Find Concurrent manager for a particular concurrent request

select

c.user_name,

a.Request_id,

to_char(a.actual_start_date,'DD-MON-YYYY HH24:MI:SS') "Start",

decode(a.status_code,

'A','Waiting',

'B', 'Resuming',

'C', 'Normal',

'D', 'Cancelled',

'E', 'Error',

'G', 'Warning',

'H', 'On Hold',

'I', 'Normal',

'M', 'No Manager',

'P', 'Scheduled',

'Q', 'Standby',

'R', 'Normal',

'S', 'Suspended',

'T', 'Terminating',

'U', 'Disabled',

'W', 'Paused',

'X', 'Terminated',

'Z', 'Waiting') status_code,

b.user_concurrent_queue_name' - 'b.target_node "queue_name",

a.user_concurrent_program_name

from fnd_concurrent_worker_requests a,

fnd_concurrent_queues_vl b,

fnd_user c

where a.concurrent_queue_id=b.concurrent_queue_id

and a.phase_code='R'

and a.requested_by=c.user_id

and c.user_name='RMANI'

Order by 5;