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;

db session

 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’

AND st.statistic# = sn.statistic#

AND st.sid = s.sid

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

AND stcpu.statistic# = sncpu.statistic#

AND stcpu.sid = st.sid

AND s.paddr = p.addr

AND s.last_call_et < 1800

AND s.logon_time > (SYSDATE - 240/1440)

AND st.value > 0;

invalid Object table & Compare

 create table GETSBACK.pre1226invalids as select * from dba_objects where status='INVALID';

select count(*) from GETSBACK.pre1226invalids;

select owner,object_name,object_type,status from dba_objects where status='INVALID' and object_name not in (select object_name from getsback.pre1226invalids where status='INVALID');

 - Apply EBS Patches (US)

export PATCH=20128107

export PATCH_HOME=/orasoft/oraApps/OEL6/cfs/R122/patches/patch_${PATCH}

 

time adpatch defaultsfile=$APPL_TOP/admin/$TWO_TASK/adalldefaults.txt \

logfile=adp${PATCH}.log patchtop=${PATCH_HOME}/${PATCH} \

driver=u${PATCH}.drv workers=64 interactive=yes

 

- Verify applied patches

sqlplus / as sysdba

SELECT DISTINCT bug_number,language,creation_date

      FROM apps.ad_bugs

      WHERE bug_number IN ('20128107')

ORDER BY bug_number,language,creation_date

/

 

- Disable “Maintenance Mode”

On Primary Application Node:

sqlplus -s apps/`tellme apps` @$AD_TOP/patch/115/sql/adsetmmd.sql DISABLE

DB Parameters Validate

 

1.1.1.1         Verify StandBy and FlasBack Parameters

On Primary Database Node as oracle,

$ sqlplus / as sysdba

select NAME ||'='||VALUE from v$parameter

 where NAME in ('db_unique_name',

                'log_archive_config',

                'log_archive_dest_2',

                'log_archive_min_succeed_dest',

                'log_archive_dest_state_1',

                'log_archive_dest_state_2',

                'remote_login_passwordfile',

                'fal_server',

                'standby_file_management');

 

SQL>alter database flashback on;

SQL> select NAME,FLASHBACK_ON from v$database;

SQL> archive log list

select NAME ||'='||VALUE from v$parameter

 where NAME in ('undo_retention',

                'undo_management',

                'db_flashback_retention_target',

                'db_recovery_file_dest',

                'db_recovery_file_dest_size');

Review some Init Parameters

 sqlplus / as sysdba

COLUMN name FORMAT a35 HEADING 'Parameter Name'

COLUMN value FORMAT a110 HEADING 'Value'

SET PAGESIZE 50000 LINES 200

select distinct name,value

from gv$parameter

where name in

('service_names',

'resource_manager_plan',

'shared_pool_size',

'shared_pool_reserved_size',

'job_queue_processes',

'utl_file_dir',

'timed_statistics')

order by 1;

Invalid Objects

 select object_type

, object_name
, owner
from dba_objects
where status = 'INVALID'
and owner = 'DEMANTRA'
order by object_type, object_name;

SQL> set pagesize 200 linesize 132
SQL> SELECT owner, object_type, object_name, status
     FROM all_objects
     WHERE status = 'INVALID'
     ORDER BY owner;

SELECT CASE WHEN object_type = 'SYNONYM' AND owner = 'PUBLIC' THEN
    'alter ' || owner || ' ' || DECODE(object_type, 'PACKAGE BODY', 'PACKAGE', object_type) || ' ' || object_name || ' ' || DECODE(object_type, 'PACKAGE BODY', 'COMPILE BODY', 'COMPILE') || ';'
    ELSE
    'alter ' || DECODE(object_type, 'PACKAGE BODY', 'PACKAGE', object_type) || ' ' || owner || '.' || object_name || ' ' || DECODE(object_type, 'PACKAGE BODY', 'COMPILE BODY', 'COMPILE') || ';'
    END "SQL_COMMANDS"
    FROM dba_objects
    WHERE object_type IN ('PACKAGE', 'PACKAGE BODY', 'VIEW', 'PROCEDURE', 'FUNCTION', 'TRIGGER', 'SYNONYM')
    AND status = 'INVALID' 
    ORDER BY DECODE(object_type, 'TRIGGER', '99', '00');










column owner format A9
column object_name format A31
column object_type format A15
column last_ddl_time format A10
spool invalids.lst
select OWNER, OBJECT_NAME,OBJECT_TYPE,LAST_DDL_TIME
from dba_objects where status='INVALID'
order by owner;

Oracle upgrade high level

 

You can generate and rebuild all Unusable indexes with the following script

 select 'alter index '||owner||'.'||index_name||' rebuild parallel 8;' from dba_indexes where status = 'UNUSABLE'

union all
select 'alter index '||owner||'.'||index_name||' noparallel;' from dba_indexes where status = 'UNUSABLE';

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

 

Oracle E-Business Suite Release 12  Release-Specific Database Initialization Parameters for 19c
S.No	Release-Specific Database Initialization Parameters for Oracle 19c	Required Parameters	Information about the Parameter
1	compatible = 19.0.0 #MP	EBS
2	optimizer_adaptive_plans = TRUE #MP	EBS
3	optimizer_adaptive_statistics = FALSE #MP	EBS
4	pga_aggregate_limit = 0 #MP	EBS
5	temp_undo_enabled = FALSE	EBS
6	_pdb_name_case_sensitive = TRUE #MP	EBS
7	event='10946 trace name context forever, level 8454144' #MP	EBS
8	_optimizer_gather_stats_on_conventional_dml = FALSE #MP	EBS
9	_optimizer_use_stats_on_conventional_dml = FALSE #MP	EBS
10	optimizer_real_time_statistics = FALSE #MP	EBS
Additional Database Initialization Parameters For Oracle E-Business Suite Release 12.2
11	recyclebin = off #MP	EBS
12	service_names=%s_dbSid%, <s_patch_service_name or ebs_patch>  # Based on AD/TXK Code level	EBS
13	local_listener=%s_dbSid%_LOCAL	EBS
14	result_cache_max_size = 600M	EB                S

egrep 'compatible|optimizer_adaptive_plans|optimizer_adaptive_statistics|pga_aggregate_limit|temp_undo_enabled|_pdb_name_case_sensitive|event|_optimizer_gather_stats_on_conventional_dml|_optimizer_use_stats_on_conventional_dml|optimizer_real_time_statistics|recyclebin|service_names|local_listener|result_cache_max_size' intiSID.ora