Some Regular Day to Day SQL Queries Used By DBA

DB SIZE
-----------

select 
( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) +
( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) +
( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) +
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) "Size in GB"
from
dual

Important Queries for Apps DBA
***************************
--Blocking Sessions--

Connect as sysdba and execute below Query to find out blocking sessions

set lines 130
set pages 200
column module format a20
column program format a20
column username format a15

select s.sid,s.serial#,s.status,p.spid,s.module,s.action,s.program
from v$session s,v$process p
where s.sid in (select session_id from dba_locks where blocking_others='Blocking')
and s.paddr=p.addr

--Note: Kill only INACTIVE blocking sessions. For Inactive blocking sessions,
--Event will be as shown below:

SQL> select EVENT from gv$session_wait where sid=&sid;
Enter value for sid: 19
old   1: select EVENT from gv$session_wait where sid=&sid
new   1: select EVENT from gv$session_wait where sid=19

EVENT
----------------------------------------------------------------
SQL*Net message from client

Locks in Specific Modules
Locks in AP Module
column object_name format a40
SELECT a.object_id, a.session_id, b.object_name
FROM v$locked_object a, dba_objects b
WHERE a.object_id = b.object_id
AND b.owner in ('AP')

--Another Query with more details

select s.sid sid_no,s.serial#,p.spid,s.action  action,a.user_name user_name,
to_char(b.start_time,'DD-MON-YYYY HH24:MI') stime
from fnd_user a, fnd_logins b, v$session s, v$process p
  where a.user_id = b.user_id
  and p.addr = s.paddr
  and s.process = b.spid
  and b.end_time is null
  and b.start_time > trunc(sysdate) - 1
  and a.user_name = '&login_id'
  and s.module like 'AP%'


--Runaway Forms Sessions

set lines 130
set pages 200
column userinfo heading "ORACLE/OS User" format a25
column terminal heading "Term" format a6
column process heading "Parent|Process ID" format a10
column "sid (audsid)" format a20
column spid heading "Shadow|Process ID" format a10
column event heading "Waiting" format a30

select s.username||' '||s.osuser||' ('||decode(s.terminal,'unknown','?','UNKNOWN','?',s.terminal) ||')' userinfo,
s.sid||','|| s.serial# ||' ('|| s.audsid ||')' "sid (audsid)",   p.spid, w.event , w.p1
from v$session s, v$process p ,v$session_wait w
where p.addr = s.paddr and s.sid = w.sid and s.process = '&form_process_id' ;

--Sample Output:

Enter value for form_process_id: 19721
old   5:      and s.process = '&form_process_id'
new 5:      and s.process = '19721'

                                               Shadow                 
ORACLE/OS User            sid (audsid)         Process ID                Waiting                                P1
------------------------- -------------------- ----------     ------------------------------ ----------
APPS applmgr ()           22,21485 (615657)    27872                    SQL*Net message from client    1952673792

--Concurrent jobs Details

--List of Running Requests

set lines 130
set pages 200
col os form A7 head AppProc
col spid form a6 head SPID
col program form A50 trunc
set pages 38
col time form 99999.99 head Elapsed
col "Req Id" form 99999999
col "Parent" form a8
col "Prg Id" form 9999999
col serial# form 999999 head Serial#
col qname head "Manager" format a10 trunc
col sid format 9999 head SID
col user_name form A12 head User trunc
set recsep off

select
       q.concurrent_queue_name qname
      ,f.user_name
      ,a.request_id "Req Id"
      ,decode(a.parent_request_id,-1,NULL,a.parent_request_id) "Parent"
      ,a.concurrent_program_id "Prg Id"
      ,a.phase_code,a.status_code
--      ,b.os_process_id "OS"
--      ,vs.sid
--      ,vs.serial# "Serial#"
--      ,vp.spid
      ,a.oracle_process_id "spid"
      ,(nvl(a.actual_completion_date,sysdate)-a.actual_start_date)*1440 "Time"
      ,c.concurrent_program_name||' - '||
       c2.user_concurrent_program_name||' '||a.description "Program"
from APPLSYS.fnd_Concurrent_requests a
    ,APPLSYS.fnd_concurrent_processes b
    ,applsys.fnd_concurrent_queues q
    ,APPLSYS.fnd_concurrent_programs_tl c2
    ,APPLSYS.fnd_concurrent_programs c
    ,APPLSYS.fnd_user f
--    ,v$session vs
--    ,v$process vp
where
      a.controlling_manager = b.concurrent_process_id
  and a.concurrent_program_id = c.concurrent_program_id
  and a.program_application_id = c.application_id
  and c2.concurrent_program_id = c.concurrent_program_id
  and c2.application_id = c.application_id
  and a.phase_code in ('I','P','R','T')
  and a.requested_by = f.user_id
  and b.queue_application_id = q.application_id
  and b.concurrent_queue_id = q.concurrent_queue_id
  and c2.language = 'US'
--  and vs.process (+) = b.os_process_id
--  and vs.paddr = vp.addr (+)
order by 9 desc;

--List of pending Jobs waiting for managers:

set lines 130
col USER_CONCURRENT_QUEUE_NAME format a39
SELECT b.USER_CONCURRENT_QUEUE_NAME , count(*)
FROM apps.FND_CONCURRENT_WORKER_REQUESTS a,apps.FND_CONCURRENT_QUEUES_VL b
WHERE (Phase_Code = 'P' ) and a.hold_flag != 'Y'
and a.Requested_Start_Date <= SYSDATE
AND ('' IS NULL OR ('' = 'B' AND PHASE_CODE = 'R' AND
STATUS_CODE IN ('I'))) and a.CONCURRENT_QUEUE_ID!=1003
and a.CONCURRENT_QUEUE_ID=b.CONCURRENT_QUEUE_ID  
group by b.USER_CONCURRENT_QUEUE_NAME ,status_code;


col program_description format a60
col user_concurrent_queue_name format a40

SELECT count(b.request_id) count, b.program_description, a.user_concurrent_queue_name
FROM apps.FND_CONCURRENT_QUEUES_VL a, apps.FND_CONCURRENT_WORKER_REQUESTS b
WHERE a.enabled_flag='Y'
AND  a.concurrent_queue_id = b.concurrent_queue_id
AND  (b.Phase_Code = 'P' OR b.Phase_Code = 'R') AND b.hold_flag != 'Y'
AND  b.Requested_Start_Date <= SYSDATE
AND  1=1
-- and a.user_concurrent_queue_name like 'Inventory%'
group by a.user_concurrent_queue_name, b.program_description order by 3,1 asc


--Long Running Requests (over a specified amount of time hard coded as 4 hours)

set lines 130
column start_time format a15
column USER_CONCURRENT_PROGRAM_NAME format a40
select b.REQUEST_ID, a.USER_CONCURRENT_PROGRAM_NAME, b.phase_code,
       (sysdate - b.actual_start_date) * 24 "running",
       to_char(b.request_date, 'mm/dd/yyyy hh24:mi') "request_date",
       to_char(b.actual_start_date, 'mm/dd/yyyy hh24:mi') "start_time"
   from APPS.FND_CONCURRENT_PROGRAMS_VL a,
       APPS.FND_CONCURRENT_REQUESTS b
 where a.CONCURRENT_PROGRAM_ID = b.CONCURRENT_PROGRAM_ID
   and a.APPLICATION_ID = b.PROGRAM_APPLICATION_ID
   and b.STATUS_CODE = 'R'
   and b.PHASE_CODE = 'R'
   and ((sysdate - b.actual_start_date) * 24) > 4
   and a.CONCURRENT_PROGRAM_ID NOT IN(36887,43393,38121,42789,31556);

--Total scheduled requests

set lines 130
select 'Scheduled requests:' schedt, count(*) schedcnt
from fnd_concurrent_requests
WHERE (requested_start_date > sysdate OR
status_code = 'P')
AND phase_code = 'P';

SELECT request_id id,
nvl(meaning, 'UNKNOWN') status,
user_concurrent_program_name pname,
to_char(request_date, 'DD-MON-RR HH24:MI:SS') submitd,
to_char(requested_start_date, 'DD-MON-RR HH24:MI:SS') requestd
FROM fnd_concurrent_requests fcr,
fnd_lookups fl,
fnd_concurrent_programs_vl fcpv
WHERE phase_code = 'P'
AND (fcr.requested_start_date >= sysdate OR
status_code = 'P')
AND LOOKUP_TYPE = 'CP_STATUS_CODE'
AND lookup_code = status_code
AND fcr.concurrent_program_id = fcpv.concurrent_program_id
AND fcr.program_application_id = fcpv.application_id
ORDER BY pname, request_date;

--Pending requests Kept (on hold)

set lines 130
select 'Requests on hold:' schedt, count(*) schedcnt
from fnd_concurrent_requests
WHERE hold_flag = 'Y'
AND phase_code = 'P';

SELECT request_id id,
nvl(meaning, 'UNKNOWN') status,
user_concurrent_program_name pname,
to_char(request_date, 'DD-MON-RR HH24:MI:SS') submitd
FROM fnd_concurrent_requests fcr,
fnd_lookups fl,
fnd_concurrent_programs_vl fcpv
WHERE phase_code = 'P'
AND hold_flag = 'Y'
AND fcr.requested_start_date <= sysdate
AND status_code != 'P'
AND LOOKUP_TYPE = 'CP_STATUS_CODE'
AND lookup_code = status_code
AND fcr.concurrent_program_id = fcpv.concurrent_program_id
AND fcr.program_application_id = fcpv.application_id
ORDER BY request_date, request_id;

--List of pending Requests (used for Pending jobs threshold Monitor)

set lines 130
set pages 200
column REQUEST heading 'Request' format 9999999999
column PHASE heading 'Phase' format A8
column STATUS heading 'Status' format A8
column PROGRAM heading 'Program Name' format A40
column SHORT heading 'Short Name' format A15
column REQUESTOR heading 'Requestor' format A15
column START_TIME heading 'Start Time' format A15
column RUN_TIME justify left heading 'Time(m)' format 999999.9
column description format a75
 select  b.REQUEST_ID "Request", a.description,
       to_char(b.request_date, 'mm/dd/yyyy hh:mi:ss') "request_date",
       to_char(b.requested_start_date, 'mm/dd/yyyy hh:mi:ss') "request_start"
  from APPS.FND_CONCURRENT_PROGRAMS_VL a,
       APPS.FND_CONCURRENT_REQUESTS b
 where a.CONCURRENT_PROGRAM_ID = b.CONCURRENT_PROGRAM_ID
   and a.APPLICATION_ID = b.PROGRAM_APPLICATION_ID
   and b.PHASE_CODE = 'P'
   and b.HOLD_FLAG !='Y'
   and b.requested_start_date <= sysdate



--Steps to terminate a Concurrent request from backend

set lines 130
set pages 200

A)
select oracle_process_id ,
decode(status_code,'R','Running','D','Canceled','E','Error','X','Terminated','G',       'Warning','T','Terminating')"Status_code",
 phase_code,to_char(actual_start_date,'DD-MON-YYYY=>hh24:mi:ss') "Login Time"
from apps.fnd_concurrent_requests where request_id='&Enter_conn_req_id'

B)
select s.sid,s.serial#,module,s.status from v$session s,v$process p
where s.paddr=p.addr
and p.spid=&oracle_process_id

C)
update fnd_concurrent_requests
set  phase_code='C',
status_code='X'
where request_id=&Enter_REQUESTID;
commit;

**Make sure that database session for the request id is killed using the output from step A and B.**

--Steps to Cancel pending jobs

update fnd_concurrent_requests
set phase_code='C',status_code='D'
WHERE phase_code = 'P' AND status_code in ('Q','I');
Commit;

--Steps to Terminate pending jobs

update fnd_concurrent_requests
set phase_code='C',status_code='X'
WHERE phase_code = 'P' AND status_code in ('Q','I');
Commit;

--Query to find out list of NLS installed in Applications

set lines 130
select LANGUAGE_CODE from fnd_languages where INSTALLED_FLAG='I';
select NLS_LANGUAGE,LANGUAGE_CODE ,INSTALLED_FLAG from fnd_languages
where INSTALLED_FLAG in ('I','B');


--To get the list of installed products and patch level

set lines 130
set pages 400
col application_id      format 99990    heading "ID"
col application_name    format a40      heading "Name"
col application_prefix  format a6       heading "Prefix"
col application_short_name format a10   heading "Short name"
col apps                format a8       heading "Product"
col install_group_num   format 90       heading "Inst Grp"
col installed_flag      format a9       heading "Type"
col language_code       format a4       heading "Code"
col module_short_name   format a8       heading "Module"
col module_version      format a8       heading "Version"
col product_group_id    format 990      heading "ID"
col product_group_name  format a28      heading "Product Group Name"
col product_group_type  format a10      heading "Type"
col product_version     format a8       heading "Version"
col argument1           format a20      heading "Arguments"
col release_name        format a12      heading "Release"
col updated             format a11      heading "Updated"
col patchset            format a20      heading "Patchset Level"
col status              format a14      heading "Appl Status"

prompt --> Product Installation Status, Version Info and Patch Level

select decode(nvl(a.APPLICATION_short_name,'Not Found'), 'SQLAP','AP','SQLGL','GL','OFA','FA',
'Not Found','id '||to_char(fpi.application_id), a.APPLICATION_short_name) apps,
decode(fpi.status,'I','Installed','S','Shared','N','Inactive',fpi.status) status,
fpi.product_version,nvl(fpi.patch_level,'-- Not Available --') Patchset,
to_char(fpi.last_update_date,'dd-Mon-RRRR') "Update Date"
from fnd_oracle_userid o, fnd_application a, fnd_product_installations fpi
where fpi.application_id = a.application_id(+)
  and fpi.oracle_id = o.oracle_id(+)
order by 1,2;

--Workflow Mailer configuration without login into OAM

set lines 130
set pages 200
col value format a30

select p.parameter_id,p.parameter_name,v.parameter_value value
from apps.fnd_svc_comp_param_vals_v v,
apps.fnd_svc_comp_params_b p,
apps.fnd_svc_components c
where c.component_type = 'WF_MAILER'
and v.component_id = c.component_id
and v.parameter_id = p.parameter_id
and p.parameter_name in
('OUTBOUND_SERVER', 'INBOUND_SERVER',
'ACCOUNT', 'FROM', 'NODENAME', 'REPLYTO','DISCARD' ,'PROCESS','INBOX')
order by p.parameter_name;           



--Script to backup database links

connect to database as sysdba
set lines 130
set pages 200
select  'DB link for '||b.username||chr(10)||'  create  database  link '||c.name||' connect to '||c.userid|| ' identified  by '||c.password||'   using '''||c.host||''';'
from dba_users b ,link$ c
where c.owner#=b.user_id
union
select  'DB link for Public' ||chr(10)|| '  create public  database  link '||c.name||' connect to
'||c.userid|| ' identified  by '||c.password||'   using '''||c.host||''';'
from link$ c
where owner#=1;

---------------------------------
**************************************************************
Removing Fatal NI connect error 12170 from Database Alert Log

In our one of the 11gR2 Production Database, there were so many entries w.r.t Fatal NI connect error 12170 + TNS-12535: TNS:operation timed out + TNS-00505: Operation timed out (with different ports), found in both RAC Instances Alert Log file.

Problem:

Fatal NI connect error 12170.

  VERSION INFORMATION:
        TNS for Linux: Version 11.2.0.3.0 - Production
        Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production
        TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production
  Time: 20-AUG-2014 14:17:36
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12535

TNS-12535: TNS:operation timed out
    ns secondary err code: 12560
    nt main err code: 505

TNS-00505: Operation timed out
    nt secondary err code: 110
    nt OS err code: 0
  Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=xxx.xxx.xxx.xxx)(PORT=44326))

Reason:

The same error message was repeating during whole day almost for every application server.

I’ve finally found what was causing the problem. Our database is behind a firewall. Firewall has an “idle session timeout” value. If a connection remains idle for more than the “idle session timeout” value it drops the connections. 

Application developers usually configure their connection pools to remain alive for a long time, which is more than the “idle session timeout” value of the firewall. Not all the connections in the pool are used. Some stays idle. After sometime the firewall drops them and I get those operation timed out problems.

Solution / Workaround: 

1) Add the following line to the sqlnet.ora file on the server.


SQLNET.EXPIRE_TIME=10

In this configuration database will probe the application servers every 10 minutes to see if they are up. In fact this is a mechanism to determine stale connections by database. Because it sends packets to application servers every ten minutes, the connections are detected as active by firewalls and they are not broken.

**In an installation that includes GRID, this parameter should be set in the RDBMS_HOME/network/admin/sqlnet.ora file. This would be the default location for sqlnet.ora file parameters referenced by the instance. 

2) One way to minimize the impact is by using the parameter SQLNET.INBOUND_CONNECT_TIMEOUT (default to 60 seconds on 10gR2 and 11g) but, sometimes, this value is not adequate.
Oracle also mention the occurrence of this error if you use DB Console or Enterprise Manager to monitor your databases and the em agent will try to connect to the target database repeatedly and, statistically, some will fail (frequency will depend on how busy your system is).
Most of the time (certainly for DB Console and Enterprise Manager Agent) the application will try to connect again and it will succeed.
To fix the problem you could increase the value of SQLNET.INBOUND_CONNECT_TIMEOUT (in Seconds) in the sqlnet.ora / CONNECT_TIMEOUT_<DB_Name> (in Minutes) in the listener.ora file located on the server side.
3) If you already have a value you have considered adequate, you might want to add the following line on your listener.ora file:
DIAG_ADR_ENABLED_<listener_name>=OFF  (in listener.ora)
& DIAG_ADR_ENABLED=OFF in sqlnet.ora

This line will hide the error on the alert.log file and the error will be posted on the $ORACLE_HOME/network/log/sqlnet.log file


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