Welcome to My Oracle World

Hi All,

Welcome to My Oracle World. In this blog, I’ll try to add new solutions for some problems and provide briefly examples. Moreover, I’m planning periodically to analyze some administration features of Oracle database

Unsuccessful Login Attempts of FND user

select count (ul.user_id)
from fnd_unsuccessful_logins ul, fnd_user u
where ul.user_id = u.user_id
and ul.attempt_time > NVL (u.last_logon_date, u.last_update_date)
and ul.user_id = u.user_id
and u.user_name = '&username'; 

How to send mail using sender name

#!/bin/ksh
SendTo=achintam@domain.com
SendFrom=Linga\<lavula@domain.com\>
cat /dev/null >/tmp/test.txt
TESTF=/tmp/test.txt
echo "To: $SendTo" >>$TESTF
echo "From: $SendFrom" >>$TESTF
echo "Subject: Testing" >>$TESTF
/usr/sbin/sendmail $SendTo <$TESTF
exit

Currently Running Jobs

set pages 66
set line 132
-- spool progs1.lst
col     user_name       format a20 word_wrapped
column  ProgName        format a25 word_wrapped
column  requestId       format 9999999999
column  StartDate       format a20 word_Wrapped
column  OS_PROCESS_ID   format a6
column  ETime           format 99999999 word_Wrapped
col     sid             format 99999 word_Wrapped

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

DB Locks

To check the Locks on Database


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 l1.id2 = l2.id2;

Blocking Session Information 

col sid format 9999

col username format a10

col osuser format a10

col program format a25

col process format 9999999

col spid format 999999

col logon_time format a13

set lines 150

set heading off

set verify off

set feedback off

undefine sid_number

undefine spid_number

rem accept sid_number number prompt "pl_enter_sid:"

col sid NEW_VALUE sid_number noprint

col spid NEW_VALUE spid_number noprint

         select  s.sid   sid,

                p.spid  spid

--              ,decode(count(*), 1,'null','No Session Found with this info') " "

         FROM v$session s,

              v$process p

         WHERE s.sid LIKE NVL('&sid', '%')

         AND p.spid LIKE NVL ('&OS_ProcessID', '%')

         AND s.process LIKE NVL('&Client_Process', '%')

         AND s.paddr = p.addr

--       group by s.sid, p.spid;

PROMPT Session and Process Information

PROMPT -------------------------------

col event for a30

select '    SID                         : '||v.sid      || chr(10)||

       '    Serial Number               : '||v.serial#  || chr(10) ||

       '    Oracle User Name            : '||v.username         || chr(10) ||

       '    Client OS user name         : '||v.osuser   || chr(10) ||

       '    Client Process ID           : '||v.process  || chr(10) ||

       '    Client machine Name         : '||v.machine  || chr(10) ||

       '    Oracle PID                  : '||p.pid      || chr(10) ||

       '    OS Process ID(spid)         : '||p.spid     || chr(10) ||

       '    Session''s Status           : '||v.status   || chr(10) ||

       '    Logon Time                  : '||to_char(v.logon_time, 'MM/DD HH24:MIpm')   || chr(10) ||

       '    Program Name                : '||v.program  || chr(10)

from v$session v, v$process p

where v.paddr = p.addr

and v.serial# > 1

and p.background is null

and p.username is not null

and sid = &sid_number

order by logon_time, v.status, 1

/

PROMPT Sql Statement

PROMPT --------------

select sql_text

from v$sqltext , v$session

where v$sqltext.address = v$session.sql_address

and sid = &sid_number

order by piece

/

PROMPT

PROMPT Event Wait Information

PROMPT ----------------------

select '   SID '|| &sid_number ||' is waiting on event  : ' || x.event || chr(10) ||

       '   P1 Text                      : ' || x.p1text || chr(10) ||

       '   P1 Value                     : ' || x.p1 || chr(10) ||

       '   P2 Text                      : ' || x.p2text || chr(10) ||

       '   P2 Value                     : ' || x.p2 || chr(10) ||

       '   P3 Text                      : ' || x.p3text || chr(10) ||

       '   P3 Value                     : ' || x.p3

from v$session_wait x

where x.sid= &sid_number

/

PROMPT

PROMPT Session Statistics

PROMPT ------------------

select        '     '|| b.name  ||'             : '||decode(b.name, 'redo size', round(a.value/1024/1024,2)||' M', a.value)

from v$session s, v$sesstat a, v$statname b

where a.statistic# = b.statistic#

and name in ('redo size', 'parse count (total)', 'parse count (hard)', 'user commits')

and s.sid = &sid_number

and a.sid = &sid_number

--order by b.name

order by decode(b.name, 'redo size', 1, 2), b.name

/

COLUMN USERNAME FORMAT a10

COLUMN status FORMAT a8

column RBS_NAME format a10

PROMPT

PROMPT Transaction and Rollback Information

PROMPT ------------------------------------

select        '    Rollback Used                : '||t.used_ublk*8192/1024/1024 ||' M'          || chr(10) ||

              '    Rollback Records             : '||t.used_urec        || chr(10)||

              '    Rollback Segment Number      : '||t.xidusn           || chr(10)||

              '    Rollback Segment Name        : '||r.name             || chr(10)||

              '    Logical IOs                  : '||t.log_io           || chr(10)||

              '    Physical IOs                 : '||t.phy_io           || chr(10)||

              '    RBS Startng Extent ID        : '||t.start_uext       || chr(10)||

              '    Transaction Start Time       : '||t.start_time       || chr(10)||

              '    Transaction_Status           : '||t.status

FROM v$transaction t, v$session s, v$rollname r

WHERE t.addr = s.taddr

and r.usn = t.xidusn

and s.sid = &sid_number

/

PROMPT

PROMPT Sort Information

PROMPT ----------------

column username format a20

column user format a20

column tablespace format a20

SELECT        '    Sort Space Used(8k block size is asssumed    : '||u.blocks/1024*8 ||' M'             || chr(10) ||

              '    Sorting Tablespace                           : '||u.tablespace       || chr(10)||

              '    Sort Tablespace Type                 : '||u.contents || chr(10)||

              '    Total Extents Used for Sorting               : '||u.extents

FROM v$session s, v$sort_usage u

WHERE s.saddr = u.session_addr

AND s.sid = &sid_number

/

set heading on

set verify on

clear column