Oracle Apps DBA: IMP QUERIES ORACLE APPS DBA

2)How to find the release of Apps installed or version installed in our machine?
Ans:conn apps
Enter password:
Connected.

SQL> select release_name from fnd_product_groups;

RELEASE_NAME
--------------------------------------------------
12.1.1

3)What is Yellow Bar Warning in Apps?

Ans: Oracle Applications Release 11.5.1 (11i) requires that its code run in a trusted mode and uses J-Initiator to run Java applets on a desktop client. If an applet is “trusted,” however, Java will extend the privileges of the applet.The Yellow Warning Bar is a warning that your applet is not running in a trusted mode.To indicate that an applet is trusted, it must be digitally signed using a digital Certificate,so Oracle Applications requires that all Java archive files must be digitally signed.

4)How to check the custom top installled?

Ans:

SQL> Select BASEPATH,PRODUCT_CODE,APPLICATION_SHORT_NAME
From fnd_application
Where application_Short_name like '%CUST_TOP_name%';

5)How to check multi-org is enabled in Oracle applications?

Ans:

SQL> select multi_org_flag from fnd_product_groups;

M
-
Y
Note:For enabling multi-org check the MY ORACLE SUPPORT notes 396351.1 and 220601.1

6)How to compile invalid objects in Oracle Applications?

Ans: Check the below link for all possible ways to compile the invalid objects in Oracle Application.Usually 'adadmin' utility provides us the option to do this task.

http://onlineappsdba.blogspot.com/2008/05/how-to-compile-invalid-objects-in-apps.html

7)Can we install Apps Tier and Database Tier on different Operating system while installing Oracle EBS 11i/R12?
Ans: Yes it is possible.We can do this by following below MY ORACLE SUPPORT notes:

Oracle Apps 11i --> Using Oracle EBS with a Split Configuration Database Tier on 11gR2 [ID 946413.1]

Oracle Apps R12 --> Oracle EBS R12 with Database Tier Only Platform on Oracle Database 11.2.0 [ID 456347.1]

8)How to find the node details in Oracle Applications?
Ans: FND_NODES tables in 'apps' schema helps in finding node details after installation,clonning and migration of applications.
SQL> SELECT NODE_NAME||' '||STATUS ||' '||NODE_ID||' '||HOST
FROM FND_NODES;

9)How to see the products installed and their versions in Oracle Applications?
Ans:

SQL> SELECT APPLICATION_ID||''||ORACLE_ID||''||PRODUCT_VERSION||''||STATUS||''||PATCH_LEVEL
FROM FND_PRODUCT_INSTALLATIONS;

O/P looks like below:

172 172 12.0.0 I R12.CCT.B.1
191 191 12.0.0 I R12.BIS.B.1
602 602 12.0.0 I R12.XLA.B.1
805 805 12.0.0 I R12.BEN.B.1
8302 800 12.0.0 I R12.PQH.B.1
8303 800 12.0.0 I R12.PQP.B.1
809 809 12.0.0 I 11i.HXC.C
662 662 12.0.0 I R12.RLM.B.1
663 663 12.0.0 I R12.VEA.B.1
298 298 12.0.0 N R12.POM.B.1
185 185 12.0.0 I R12.XTR.B.1

10)How to see the concurrent Requests and jobs in Oracle Applications?
Ans: FND_CONCURRENT_REQUESTS can be used to see the concurrent requests and job details.These details are useful
in troubleshooting concurrent manager related issues.

SQL>SELECT REQUEST_ID||' '||REQUEST_DATE||' '||REQUESTED_BY||' '||PHASE_CODE||' '||STATUS_CODE
FROM FND_CONCURRENT_REQUESTS;

O/P will be as given below:
REQUEST_ID||''||REQUEST_DATE||''||REQUESTED_BY||''||PHASE_CODE||''||STATUS_CODE
--------------------------------------------------------------------------------------------------------
6088454 24-NOV-11 1318 P I
6088455 24-NOV-11 1318 P Q
6088403 24-NOV-11 0 C C
6088410 24-NOV-11 0 C C

Where:

PHASE_CODE column can have values:
C Completed
I Inactive
P Pending
R Running

STATUS_CODE Column can have values:
A Waiting
B Resuming
C Normal
D Cancelled
E Error
F Scheduled
G Warning
H On Hold
I Normal
M No Manager
Q Standby
R Normal
S Suspended
T Terminating
U Disabled
W Paused
X Terminated
Z Waiting

11)What is the significance of FND_INSTALL_PROCESSES and AD_DEFERRED_JOBS tables?
Ans: FND_INSTALL_PROCESSES and AD_DEFERRED_JOBS tables are created and Dropped during the 'adadmin' and 'adpatch' sessions.
Both AD utilities (adpatch/adadmin) access the same tables to store the workers details, so both FND_INSTALL_PROCESSES and
AD_DEFERRED_JOBS tables need to be dropped from the failed adpatch session ,so that adadmin/adpatch session can run successfully next time.

12) To find sessions list?

ANS:

clear col

set lines 180

set pages 90

REM 2007-10-31 S.E. Added machine column.

REM 2003-08-19 S.E. Added column statements for 9i sqlplus

column osuser format a14

column username format a15

column sid format 999999

column serial# format 999999

column pid format 999999

column spid format a6

column logon_time format a14

column status format a8

column machine format a30

column program format a54

spool sessionlist.txt

select s.osuser osuser,

s.username username,

s.sid sid,

s.serial# serial#,

p.pid pid,

p.spid spid,

substr(to_char(s.logon_time,'mm-dd hh24:mi:ss'),1,14) logon_time,

s.status status,

s.machine machine,

s.program program

from v$session s,

v$process p

where s.paddr = p.addr(+)

order by status, osuser, username, logon_time asc

spool off;

13) How to check SID is active or not?

Ans :

CLEAR COLUMNS

column sid format 9999

column serial format 99999

column osuser HEADING 'O/S|Username' FORMAT a9

column username HEADING 'Oracle|Username' FORMAT a9

column program FORMAT a20

undefine SID

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

from v$session

where sid like '&SID'

CLEAR COLUMNS

14) How to check session information with the help of SID?

clear col

set lines 180

set pages 90

REM 2007-10-31 S.E. Added machine column.

REM 2003-08-19 S.E. Added column statements for 9i sqlplus

column osuser format a14

column username format a15

column sid format 999999

column serial# format 999999

column pid format 999999

column spid format a6

column logon_time format a14

column status format a8

column machine format a30

column program format a54

spool sessionlist.txt

select s.osuser osuser,

s.username username,

s.sid sid,

s.serial# serial#,

p.pid pid,

p.spid spid,

substr(to_char(s.logon_time,'mm-dd hh24:mi:ss'),1,14) logon_time,

s.status status,

s.machine machine,

s.program program

from v$session s,

v$process p

where s.paddr = p.addr(+)

order by status, osuser, username, logon_time asc

spool off;

prdora@gs2f09dbebz1lv:/PRD02/prdora1/habeeb> clear

You have new mail in /var/mail/prdora

prdora@gs2f09dbebz1lv:/PRD02/prdora1/habeeb> cat sid.sql

CLEAR COLUMNS

column sid format 9999

column serial format 99999

column osuser HEADING 'O/S|Username' FORMAT a9

column username HEADING 'Oracle|Username' FORMAT a9

column program FORMAT a20

undefine SID

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

from v$session

where sid like '&SID'

CLEAR COLUMNS

prdora@gs2f09dbebz1lv:/PRD02/prdora1/habeeb> cat sessiondet.sql

Display all 150 possibilities? (y or n)

prdora@gs2f09dbebz1lv:/PRD02/prdora1/habeeb> cat sessiondet.sql

set lines 95

set pages 900

set head off

set feedb off

set echo off

set verify off

PROMPT

ACCEPT p_sid CHAR PROMPT 'Please enter a sid number: '

spool sessdettemp.sql

select 'spool /tmp/session_detail.&&p_sid..'||to_char(sysdate,'DD-MON-YYYY-HH24MI')||'.log' from dual;

spool off

@sessdettemp.sql

PROMPT

PROMPT V$SESSION INFORMATION

PROMPT

select

rpad('SADDR' ,22,'.')||' '||rpad(SADDR ,20,' ')||CHR(10)||

rpad('SID' ,22,'.')||' '||rpad(SID ,20,' ')||CHR(10)||

rpad('SERIAL#' ,22,'.')||' '||rpad(SERIAL# ,20,' ')||CHR(10)||

rpad('AUDSID' ,22,'.')||' '||rpad(AUDSID ,20,' ')||CHR(10)||

rpad('PADDR' ,22,'.')||' '||rpad(PADDR ,20,' ')||CHR(10)||

rpad('USER#' ,22,'.')||' '||rpad(USER# ,20,' ')||CHR(10)||

rpad('USERNAME' ,22,'.')||' '||rpad(USERNAME ,30,' ')||CHR(10)||

rpad('COMMAND' ,22,'.')||' '||rpad(COMMAND ,20,' ')||CHR(10)||

rpad('TADDR' ,22,'.')||' '||rpad(TADDR ,10,' ')||CHR(10)||

rpad('LOCKWAIT' ,22,'.')||' '||rpad(LOCKWAIT ,10,' ')||CHR(10)||

rpad('STATUS' ,22,'.')||' '||rpad(STATUS ,10,' ')||CHR(10)||

rpad('SERVER' ,22,'.')||' '||rpad(SERVER ,10,' ')||CHR(10)||

rpad('SCHEMA#' ,22,'.')||' '||rpad(SCHEMA# ,20,' ')||CHR(10)||

rpad('SCHEMANAME' ,22,'.')||' '||rpad(SCHEMANAME ,30,' ')||CHR(10)||

rpad('OSUSER' ,22,'.')||' '||rpad(OSUSER ,20,' ')||CHR(10)||

rpad('PROCESS' ,22,'.')||' '||rpad(PROCESS ,10,' ')||CHR(10)||

rpad('MACHINE' ,22,'.')||' '||rpad(MACHINE ,70,' ')||CHR(10)||

rpad('TERMINAL' ,22,'.')||' '||rpad(TERMINAL ,10,' ')||CHR(10)||

rpad('PROGRAM' ,22,'.')||' '||rpad(PROGRAM ,50,' ')||CHR(10)||

rpad('TYPE' ,22,'.')||' '||rpad(TYPE ,10,' ')||CHR(10)||

rpad('SQL_ADDRESS' ,22,'.')||' '||rpad(SQL_ADDRESS ,20,' ')||CHR(10)||

rpad('SQL_HASH_VALUE' ,22,'.')||' '||rpad(SQL_HASH_VALUE ,20,' ')||CHR(10)||

rpad('PREV_SQL_ADDR' ,22,'.')||' '||rpad(PREV_SQL_ADDR ,20,' ')||CHR(10)||

rpad('PREV_HASH_VALUE' ,22,'.')||' '||rpad(PREV_HASH_VALUE ,20,' ')||CHR(10)||

rpad('MODULE' ,22,'.')||' '||rpad(MODULE ,50,' ')||CHR(10)||

rpad('MODULE_HASH' ,22,'.')||' '||rpad(MODULE_HASH ,20,' ')||CHR(10)||

rpad('ACTION' ,22,'.')||' '||rpad(ACTION ,40,' ')||CHR(10)||

rpad('ACTION_HASH' ,22,'.')||' '||rpad(ACTION_HASH ,20,' ')||CHR(10)||

rpad('CLIENT_INFO' ,22,'.')||' '||rpad(CLIENT_INFO ,70,' ')||CHR(10)||

rpad('FIXED_TABLE_SEQUENCE' ,22,'.')||' '||rpad(FIXED_TABLE_SEQUENCE ,20,' ')||CHR(10)||

rpad('ROW_WAIT_OBJ#' ,22,'.')||' '||rpad(ROW_WAIT_OBJ# ,20,' ')||CHR(10)||

rpad('ROW_WAIT_FILE#' ,22,'.')||' '||rpad(ROW_WAIT_FILE# ,20,' ')||CHR(10)||

rpad('ROW_WAIT_BLOCK#' ,22,'.')||' '||rpad(ROW_WAIT_BLOCK# ,20,' ')||CHR(10)||

rpad('ROW_WAIT_ROW#' ,22,'.')||' '||rpad(ROW_WAIT_ROW# ,20,' ')||CHR(10)||

rpad('LOGON_TIME' ,22,'.')||' '||rpad(to_char(LOGON_TIME,'DD-MON-YYYY HH24:MI:SS'),20,' ')||CHR(10)||

rpad('LAST_CALL_ET' ,22,'.')||' '||rpad(LAST_CALL_ET ,20,' ')

from gv$session

where sid = &&p_sid;

PROMPT

PROMPT V$PROCESS INFORMATION

PROMPT

select

rpad('ADDR' ,22,'.')||' '||rpad(p.ADDR ,20,' ')||CHR(10)||

rpad('PID' ,22,'.')||' '||rpad(p.PID ,20,' ')||CHR(10)||

rpad('SPID' ,22,'.')||' '||rpad(p.SPID ,10,' ')||CHR(10)||

rpad('USERNAME' ,22,'.')||' '||rpad(p.USERNAME ,20,' ')||CHR(10)||

rpad('SERIAL#' ,22,'.')||' '||rpad(p.SERIAL# ,20,' ')||CHR(10)||

rpad('TERMINAL' ,22,'.')||' '||rpad(p.TERMINAL ,10,' ')||CHR(10)||

rpad('PROGRAM' ,22,'.')||' '||rpad(p.PROGRAM ,50,' ')||CHR(10)||

rpad('BACKGROUND' ,22,'.')||' '||rpad(p.BACKGROUND ,10,' ')||CHR(10)||

rpad('LATCHWAIT' ,22,'.')||' '||rpad(p.LATCHWAIT ,10,' ')||CHR(10)||

rpad('LATCHSPIN' ,22,'.')||' '||rpad(p.LATCHSPIN ,10,' ')

from gv$session s,

gv$process p

where s.sid = &&p_sid

and s.paddr = p.addr;

PROMPT

PROMPT V$SESS_IO INFORMATION

PROMPT

select

rpad('SID' ,22,'.')||' '||rpad(SID ,30,' ')||CHR(10)||

rpad('BLOCK_GETS' ,22,'.')||' '||rpad(BLOCK_GETS ,30,' ')||CHR(10)||

rpad('CONSISTENT_GETS' ,22,'.')||' '||rpad(CONSISTENT_GETS ,30,' ')||CHR(10)||

rpad('PHYSICAL_READS' ,22,'.')||' '||rpad(PHYSICAL_READS ,30,' ')||CHR(10)||

rpad('BLOCK_CHANGES' ,22,'.')||' '||rpad(BLOCK_CHANGES ,30,' ')||CHR(10)||

rpad('CONSISTENT_CHANGES' ,22,'.')||' '||rpad(CONSISTENT_CHANGES ,30,' ')

from gv$sess_io

where sid = &&p_sid;

PROMPT

PROMPT V$SESSION_EVENT INFORMATION

PROMPT

select

rpad('SID' ,22,'.')||' '||rpad(SID ,30,' ')||CHR(10)||

rpad('EVENT' ,22,'.')||' '||rpad(EVENT ,30,' ')||CHR(10)||

rpad('TOTAL_WAITS' ,22,'.')||' '||rpad(TOTAL_WAITS ,30,' ')||CHR(10)||

rpad('TOTAL_TIMEOUTS' ,22,'.')||' '||rpad(TOTAL_TIMEOUTS ,30,' ')||CHR(10)||

rpad('TIME_WAITED' ,22,'.')||' '||rpad(TIME_WAITED ,30,' ')||CHR(10)||

rpad('AVERAGE_WAIT' ,22,'.')||' '||rpad(round(AVERAGE_WAIT,4),30,' ')

from gv$session_event

where sid = &&p_sid;

PROMPT

PROMPT V$SESSION_WAIT INFORMATION

PROMPT

select

rpad('SID' ,22,'.')||' '||rpad(SID ,30,' ')||CHR(10)||

rpad('SEQ#' ,22,'.')||' '||rpad(SEQ# ,30,' ')||CHR(10)||

rpad('EVENT' ,22,'.')||' '||rpad(EVENT ,30,' ')||CHR(10)||

rpad('WAIT_TIME' ,22,'.')||' '||rpad(WAIT_TIME ,30,' ')||CHR(10)||

rpad('SECONDS_IN_WAIT' ,22,'.')||' '||rpad(SECONDS_IN_WAIT ,30,' ')||CHR(10)||

rpad('STATE' ,22,'.')||' '||rpad(STATE ,30,' ')

from gv$session_wait

where sid = &&p_sid

order by seq#;

PROMPT

PROMPT V$SQLAREA INFORMATION

PROMPT [Displays previous sql if no current sql.]

PROMPT

select

rpad('SQL_TEXT' ,22,'.')||' '||CHR(10)||CHR(10)||a.SQL_TEXT

from gv$session s,

gv$sqlarea a

where s.sid = &&p_sid

and decode(sql_address,'00', prev_sql_addr, sql_address) = a.address;

-- and decode(sql_address,'00', prev_hash_value, sql_hash_value) = a.hash_value;

select

rpad('SHARABLE_MEM' ,22,'.')||' '||rpad(a.SHARABLE_MEM ,30,' ')||CHR(10)||

rpad('PERSISTENT_MEM' ,22,'.')||' '||rpad(a.PERSISTENT_MEM ,30,' ')||CHR(10)||

rpad('RUNTIME_MEM' ,22,'.')||' '||rpad(a.RUNTIME_MEM ,30,' ')||CHR(10)||

rpad('SORTS' ,22,'.')||' '||rpad(a.SORTS ,30,' ')||CHR(10)||

rpad('VERSION_COUNT' ,22,'.')||' '||rpad(a.VERSION_COUNT ,30,' ')||CHR(10)||

rpad('LOADED_VERSIONS' ,22,'.')||' '||rpad(a.LOADED_VERSIONS ,30,' ')||CHR(10)||

rpad('OPEN_VERSIONS' ,22,'.')||' '||rpad(a.OPEN_VERSIONS ,30,' ')||CHR(10)||

rpad('USERS_OPENING' ,22,'.')||' '||rpad(a.USERS_OPENING ,30,' ')||CHR(10)||

rpad('EXECUTIONS' ,22,'.')||' '||rpad(a.EXECUTIONS ,30,' ')||CHR(10)||

rpad('USERS_EXECUTING' ,22,'.')||' '||rpad(a.USERS_EXECUTING ,30,' ')||CHR(10)||

rpad('LOADS' ,22,'.')||' '||rpad(a.LOADS ,30,' ')||CHR(10)||

rpad('FIRST_LOAD_TIME' ,22,'.')||' '||rpad(a.FIRST_LOAD_TIME ,30,' ')||CHR(10)||

rpad('INVALIDATIONS' ,22,'.')||' '||rpad(a.INVALIDATIONS ,30,' ')||CHR(10)||

rpad('PARSE_CALLS' ,22,'.')||' '||rpad(a.PARSE_CALLS ,30,' ')||CHR(10)||

rpad('DISK_READS' ,22,'.')||' '||rpad(a.DISK_READS ,30,' ')||CHR(10)||

rpad('BUFFER_GETS' ,22,'.')||' '||rpad(a.BUFFER_GETS ,30,' ')||CHR(10)||

rpad('ROWS_PROCESSED' ,22,'.')||' '||rpad(a.ROWS_PROCESSED ,30,' ')||CHR(10)||

rpad('COMMAND_TYPE' ,22,'.')||' '||rpad(a.COMMAND_TYPE ,30,' ')||CHR(10)||

rpad('OPTIMIZER_MODE' ,22,'.')||' '||rpad(a.OPTIMIZER_MODE ,30,' ')||CHR(10)||

rpad('PARSING_USER_ID' ,22,'.')||' '||rpad(a.PARSING_USER_ID ,30,' ')||CHR(10)||

rpad('PARSING_SCHEMA_ID' ,22,'.')||' '||rpad(a.PARSING_SCHEMA_ID ,30,' ')||CHR(10)||

rpad('KEPT_VERSIONS' ,22,'.')||' '||rpad(a.KEPT_VERSIONS ,30,' ')||CHR(10)||

rpad('ADDRESS' ,22,'.')||' '||rpad(a.ADDRESS ,30,' ')||CHR(10)||

rpad('HASH_VALUE' ,22,'.')||' '||rpad(a.HASH_VALUE ,30,' ')||CHR(10)||

rpad('MODULE' ,22,'.')||' '||rpad(a.MODULE ,60,' ')||CHR(10)||

rpad('MODULE_HASH' ,22,'.')||' '||rpad(a.MODULE_HASH ,30,' ')||CHR(10)||

rpad('ACTION' ,22,'.')||' '||rpad(a.ACTION ,30,' ')||CHR(10)||

rpad('ACTION_HASH' ,22,'.')||' '||rpad(a.ACTION_HASH ,30,' ')||CHR(10)||

rpad('SERIALIZABLE_ABORTS' ,22,'.')||' '||rpad(a.SERIALIZABLE_ABORTS,30,' ')

from gv$session s,

gv$sqlarea a

where s.sid = &&p_sid

and decode(sql_address,'00', prev_sql_addr, sql_address) = a.address;

-- and decode(sql_address,'00',prev_hash_value, sql_hash_value) = a.hash_value;

spool off

clear buffer

!rm sessdettemp.sql

15) How to check Locks?

ANS:

CLEAR COLUMNS

col ssid form A7

col lmode form 999 heading "loc"

col request form 999 heading "req"

col name form A30

break on id1 on sid

select lpad(' ',decode(a.request,0,0,3))||a.sid ssid,

a.id1,

a.lmode,

a.request,

c.name

from sys.obj$ c,

gv$lock b,

gv$lock a

where a.id1 in ( select id1 from gv$lock where lmode = 0 )

and a.sid = b.sid

and c.obj# = b.id1

and b.type = 'TM'

order by a.id1, a.request, b.sid, c.name

CLEAR COLUMNS

16) How to check Blocking Session?

ANS:

select blocking_session, sid from v$session where blocking_session !=0;

17) How to check Tablespace Size?

ANS:

SET PAGESIZE 60

SET FEEDBACK OFF

COLUMN tablespace_name HEADING 'Tablespace' FORMAT a20

COLUMN total HEADING 'Total|MB' FORMAT 99,99,999

COLUMN available HEADING 'Available|MB' FORMAT 99,99,999

COLUMN used HEADING 'Used|MB' FORMAT 99,99,999

COLUMN pctused HEADING 'Percent|Used' FORMAT 999.99

COLUMN large_extent HEADING 'Largest|Available|Extent' FORMAT 9,999

BREAK ON REPORT

COMPUTE SUM OF USED TOTAL ON REPORT

col today new_value v_date noprint

col db_name new_value db_sid noprint

select to_char(SYSDATE,'Month DD, YYYY') today from dual;

select name db_name from v$database ;

TTITLE LEFT db_sid ' - Daily Tablespace Usage Report' RIGHT v_date SKIP 2

select a.tablespace_name, b.total,

c.available, d.large_extent,

(b.total - c.available) used,

round((((b.total - c.available)*100)/b.total),2) pctused

from

(select tablespace_name, (sum(bytes)/1024)/1024 total

from dba_data_files group by tablespace_name) b,

(select tablespace_name, nvl(round(((sum(bytes)/1024)/1024),2),0) available

from dba_free_space group by tablespace_name) c,

(select tablespace_name, (max(bytes)/1024)/1024 large_extent

from dba_free_space group by tablespace_name) d,

dba_tablespaces a

where a.tablespace_name = b.tablespace_name (+)

and a.tablespace_name = c.tablespace_name (+)

and a.tablespace_name = d.tablespace_name (+)

order by pctused

undefine today

undefine db_name

clear col

TTITLE OFF

18) How to check current concurrent program running from backend?

ANS:

set head on

set feed on

set pages 56

set lines 200

set recsep off

col "rqst_id" heading "Reqst Id" format 999999999

col "pgm_appl_id" heading "Appl Id" format 999999

col "conc_pgm_id" heading "Prg Id" format 999999

col "stat_cd" heading "St" format a2

col "os_pid" heading "CMgr|OSPID" format a5

col "vspid" heading "SPID" format a5

col "vsid" heading "SID" format 99999

col "vserial#" heading "SER#" format 99999

col "pri" heading "Pri" format 99

col "que" heading "Mgr" format a3

col "time" heading "Run|Time" format 99999

col "avgtime" heading "Avg|Run|Time" format 99999

col "walltime" heading "Wall|Time" format 99999

col "usr" heading "User|Name" format a18

col "program" heading "Program" format a90

select a.request_id "rqst_id"

,a.program_application_id "pgm_appl_id"

,a.concurrent_program_id "conc_pgm_id"

,a.status_code||decode(a.resubmit_interval, null, null, '*') "stat_cd"

,b.os_process_id "os_pid"

,v.spid "vspid"

,v.sid "vsid"

,v.serial# "vserial#"

,a.priority "pri"

,substr(d.concurrent_queue_name,1,3) "que"

,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 "time"

,decode(sign(a.requested_start_date - a.request_date),1,

round(((sysdate-requested_start_date) * 1440),0),

round(((sysdate-request_date) * 1440),0)) "walltime"

,e.user_name "usr"

,c.concurrent_program_name||' - '||rtrim(ct.user_concurrent_program_name) "program"

from APPLSYS.fnd_concurrent_requests a

,APPLSYS.fnd_concurrent_processes b

,APPLSYS.fnd_concurrent_programs c

,APPLSYS.fnd_concurrent_programs_tl ct

,APPLSYS.fnd_concurrent_queues d

,APPLSYS.fnd_user e

,(select s.sid, s.serial#, p.spid from v$session s, v$process p where s.paddr = p.addr) v

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 a.concurrent_program_id = ct.concurrent_program_id

and a.program_application_id = ct.application_id

and b.concurrent_queue_id = d.concurrent_queue_id

and ct.language = 'US'

and a.requested_by = e.user_id

and a.phase_code in ('R','T')

and a.oracle_process_id=v.spid(+)

order by 11 asc

clear col

20) How to find SID with the help of OS PID????

ANS:

COLUMN OSUSER HEADING 'OS|Username' FORMAT a9

COLUMN USERNAME HEADING 'Oracle|Username' FORMAT a8

COLUMN SID FORMAT 9999

COLUMN SERIAL# FORMAT 999999

COLUMN PROGRAM FORMAT a32

SET FEEDBACK OFF

SET VERIFY OFF

select a.sid, a.serial#, a.osuser, a.username, a.status, a.logon_time, a.program

from v$session a, v$process b

where a.paddr = b.addr

and b.spid like '&os_process'

SET FEEDBACK ON

21) How to Re-Bulid Index??

ANS:

prompt

ACCEPT spoolfile CHAR prompt 'Output-file : ';

ACCEPT schema CHAR prompt 'Schema name (% allowed) : ';

prompt

prompt Rebuild the index when :

prompt - deleted entries represent 20% or more of the current entries

prompt - the index depth is more then 4 levels.

prompt Possible candidate for bitmap index :

prompt - when distinctiveness is more than 99%

prompt

spool &spoolfile

set serveroutput on

set verify off

declare

c_name INTEGER;

ignore INTEGER;

height index_stats.height%TYPE := 0;

lf_rows index_stats.lf_rows%TYPE := 0;

del_lf_rows index_stats.del_lf_rows%TYPE := 0;

distinct_keys index_stats.distinct_keys%TYPE := 0;

cursor c_indx is

select owner, table_name, index_name

from dba_indexes

where owner like upper('&schema')

and owner not in ('SYS','SYSTEM');

begin

dbms_output.enable (1000000);

dbms_output.put_line ('Owner Index Name Table Name % Deleted Entries Blevel Distinctiveness');

dbms_output.put_line ('--------------- --------------------------------------- ---------------------------------- ----------------------- ----------------------');

c_name := DBMS_SQL.OPEN_CURSOR;

for r_indx in c_indx loop

DBMS_SQL.PARSE(c_name,'analyze index ' || r_indx.owner || '.' ||

r_indx.index_name || ' validate structure',DBMS_SQL.NATIVE);

ignore := DBMS_SQL.EXECUTE(c_name);

select HEIGHT, decode (LF_ROWS,0,1,LF_ROWS), DEL_LF_ROWS,

decode (DISTINCT_KEYS,0,1,DISTINCT_KEYS)

into height, lf_rows, del_lf_rows, distinct_keys

from index_stats;

-- Index is considered as candidate for rebuild when :

-- - when deleted entries represent 20% or more of the current entries

-- - when the index depth is more then 4 levels.(height starts counting from 1 so > 5)

-- Index is (possible) candidate for a bitmap index when :

-- - distinctiveness is more than 99%

if ( height > 5 ) OR ( (del_lf_rows/lf_rows) > 0.2 ) then

dbms_output.put_line (rpad(r_indx.owner,16,' ') || rpad(r_indx.index_name,40,' ') ||

rpad(r_indx.table_name,35,' ') ||

lpad(round((del_lf_rows/lf_rows)*100,3),17,' ') ||

lpad(height-1,7,' ') || lpad(round((lf_rows-distinct_keys)*100/lf_rows,3),16,' '));

end if;

end loop;

DBMS_SQL.CLOSE_CURSOR(c_name);

end;

spool off

set verify on

22) To check Temp tablespace Free Blocks?

ANS :

SELECT A.tablespace_name tablespace, D.mb_total,

SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,

D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free

FROM v$sort_segment A,

(

SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total

FROM v$tablespace B, v$tempfile C

WHERE B.ts#= C.ts#

GROUP BY B.name, C.block_size

) D

WHERE A.tablespace_name = D.name

GROUP by A.tablespace_name, D.mb_total;

23) How to check OBJECT LEVEL LOCK?

ANS:

select a.INST_ID, a.OBJECT_ID, a.SESSION_ID,b.sid, b.serial#, b.status from gv$locked_object a, v$session b where object_id like '41411' and b.status='INVALID' and a.session_id=b.sid;

Oracle Apps DBA

Thursday 24 April 2014

IMP QUERIES ORACLE APPS DBA

No comments:

Post a Comment