DBA Related Topics

DBA Commands Useful for Developer

Logfile Path
=============
$INST_TOP/apps/$CONTEXT_NAME/logs/appl/conc/log

Output File Path
=================
$APPLCSF/$APPLOUT  or $APPLCSF/out

XML File Path
=============
$APPLCSF/$APPLOUT/

Altering Session
================
ALTER SESSION SET CURRENT_SCHEMA=QA

alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';

Database Queries to find SID,OSUSER
===================================

select sys_context('USERENV','SESSION_SCHEMA') from dual;

select sys_context('userenv','instance_name') from dual;

SELECT sys_context('USERENV', 'SID') FROM DUAL;

SYS_CONTEXT('USERENV','TERMINAL') terminal
SYS_CONTEXT('USERENV','SESSIONID') sessionid
SYS_CONTEXT('USERENV','INSTANCE') instance
SYS_CONTEXT('USERENV','IP_ADDRESS') ip_address
SYS_CONTEXT('USERENV','SID') SID


select *from v$instance

select *from v$session

To get the SID and Serial Number
===============================
SELECT s.sid,
       s.serial#,
       s.osuser,
       s.program
FROM   v$session s;

SQL> select inst_id,sid,serial# from gv$session where username='SCOTT';

   INST_ID        SID    SERIAL#
---------- ---------- ----------
         1        130        620


SQL>  alter system kill session '130,620,1';



Killing Session
===============
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE

CONNECTING PUTTY
================
To Set Env Variables
. /home/appe0ds/scripts/setdse01.bash

https://sites.google.com/site/oracledb009/oracle-apps-concepts/profile-option

Query to Find the SID for REquest Id
=====================================
SELECT a.request_id,
       d.sid,
       d.serial#,
       d.osuser,
       d.process,
       c.SPID,
       d.inst_id
  FROM apps.fnd_concurrent_requests a,
       apps.fnd_concurrent_processes b,
       gv$process c,
       gv$session d
 WHERE     a.controlling_manager = b.concurrent_process_id
       AND c.pid = b.oracle_process_id
       AND b.session_id = d.audsid
       AND a.request_id = 19431286
       AND a.phase_code = 'R';

To Kill the Session
====================

ALTER SYSTEM KILL SESSION '225,40625,@1' IMMEDIATE

Query to Find the running Query for a SID
=========================================
SELECT a.sid, a.serial#, b.sql_text
  FROM v$session a, v$sqlarea b
 WHERE a.sql_address = b.address AND a.sid = 225

To Get the Status Codes and Phase Codes of COncurret Program
=============================================================

SELECT LOOKUP_CODE, MEANING
  FROM FND_LOOKUP_VALUES
 WHERE LOOKUP_TYPE = 'CP_PHASE_CODE' AND LANGUAGE = 'US'
       AND ENABLED_FLAG = 'Y';
     
SELECT LOOKUP_CODE, MEANING
  FROM FND_LOOKUP_VALUES
 WHERE LOOKUP_TYPE = 'CP_STATUS_CODE' AND LANGUAGE = 'US'
       AND ENABLED_FLAG = 'Y';