DBA_LOCKS

select l.session_id, l.oracle_username, l.os_user_name, o.object_name , o.owner
from gv$locked_object l, dba_objects o
where l.object_id = o.object_id;

select l.session_id, l.oracle_username, l.os_user_name, o.object_name , o.owner,
'alter system kill session ''' || s.sid || ',' || s.serial# ||''';' kill_session
from v$locked_object l, dba_objects o, v$session s
where l.object_id = o.object_id
and l.session_id = s.sid;
select sid, serial#
from gv$session
where sid = 0000;

alter sysetm kill session '';

select s.sid, s.serial#, p.spid unix_process, s.osuser, s.username, s.machine, s.program,
s.sql_hash_value current_hash_value, q1.sql_text current_sql_text, s.prev_hash_value prev_hash_value,
q2.sql_text prev_sql_text, to_char(s.logon_time,'DD/MM/YYYY HH24:MI') session_logon_time,
o.owner ||'.'|| o.object_name locked_object, l.type lock_type, l.lmode lock_mode, l.ctime lock_time_in_sec
from v$lock l, v$session s, v$process p, v$sqlarea q1, v$sqlarea q2, dba_objects o
where l.lmode in (3,5,6) and l.type in ('TX','TM','UL')
and l.sid=s.sid and s.paddr=p.addr(+)
and s.sql_hash_value=q1.hash_value(+)
and s.prev_hash_value=q2.hash_value(+)
and l.id1=o.object_id(+)
and l.ctime> &secs
order by l.ctime;

Metalink Note:29787.1 for information about lock types/modes.

Note:15476.1
3.   Which lock modes are required for which table action?
==========================================================
The following table describes what lock modes on DML enqueues are actually
gotten for which table operations in a standard Oracle installation.
 Operation                Lock Mode LMODE Lock Description
------------------------- --------- ----- ----------------
 Select                     NULL     1    null
 Select for update          SS       2    sub share
 Insert                     SX       3    sub exclusive
 Update                     SX       3    sub exclusive
 Delete                     SX       3    sub exclusive
 Lock For Update            SS       2    sub share
 Lock Share                 S        4    share
 Lock Exclusive             X        6    exclusive
 Lock Row Share             SS       2    sub share
 Lock Row Exclusive         SX       3    sub exclusive
 Lock Share Row Exclusive   SSX      5    share/sub exclusive
 Alter table                X        6    exclusive
 Drop table                 X        6    exclusive
 Create Index               S        4    share
 Drop Index                 X        6    exclusive
 Truncate table             X        6    exclusive
-----------------------------------------------------------
4.   How compatibility of locks work
====================================
The compatibility of lock modes are normally represented by following matrix:
       NULL    SS      SX      S       SSX     X
-----------------------------------------------------
 NULL  YES     YES     YES     YES     YES     YES
   SS  YES     YES     YES     YES     YES     no
   SX  YES     YES     YES     no      no      no
    S  YES     YES     no      YES     no      no
  SSX  YES     YES     no      no      no      no
    X  YES     no      no      no      no      no
5.   Which views can be used to detect locking problems?
========================================================
A number of Oracle views permits to detect locking problems.
V$SESSION_WAIT When a session is waiting on a resource, it can be
found waiting on the enqueue wait event, 
e.g.  SELECT * FROM V$SESSION_WAIT WHERE EVENT = 'enqueue';
- SID identifier of session holding the lock
- P1, P2, P3 determine the resource when event = 'enqueue'
- SECONDS_IN_WAIT gives how long the wait did occurs
V$SESSION session information and row locking information
- SID, SERIAL# identifier of the session
- LOCKWAIT address of the lock waiting, otherwise null
- ROW_WAIT_OBJ# object identified of the object we are waiting on
  (object_id of dba_objects)
- ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# file_id , block_id and
  row location within block of the locked row
V$LOCK list of all the locks in the system
- SID identifier of session holding the lock
- TYPE, ID1 and ID2 determine the resource
- LMODE and REQUEST indicate which queue the session is waiting on, as follows:
   LMODE > 0, REQUEST = 0 owner
   LMODE = 0, REQUEST > 0 acquirer
   LMODE > 0, REQUEST > 0 converter
- CTIME time since current mode was converted (see Note 223146.1)
- BLOCK are we blocking another lock
   BLOCK = 0 non blocking
   BLOCK = 1 blocking others
DBA_LOCK or DBA_LOCKS   formatted view on V$LOCK (created via
$ORACLE_HOME/rdbms/admin/catblock.sql)
- SESSION_ID == SID in V$LOCK
- LOCK_TYPE, LOCK_ID1, LOCK_ID2 formatted value of TYPE, ID1, ID2 from V$LOCK
- MODE_HELD and MODE_REQUESTED formatted value of LMODE and REQUEST from V$LOCK
- LAST_CONVERT == CTIME of V$LOCK
- BLOCKING_OTHERS formatted value of BLOCK from V$LOCK
V$TRANSACTION_ENQUEUE subset of V$LOCK for the blocking TX resources only
(same description as for the V$LOCK view)
V$ENQUEUE_LOCK subset of V$LOCK for the system resources only and
blocked TX resources only. (same description as for the V$LOCK view)
DBA_DML_LOCKS subset of the V$LOCK for the DML (TM) locks only
(created via $ORACLE_HOME/rdbms/admin/catblock.sql
 - same description as the DBA_LOCK view)
V$LOCKED_OBJECT same info as DBA_DML_LOCKS, but linked with the
rollback and session information
- XIDUSN, XIDSLOT and XIDSQN rollback information to be linked with V$TRANSACTION
- OBJECT_ID object being locked
- SESSION_ID session id
- ORACLE_USERNAME oracle user name
- OS_USER_NAME OS user name
- PROCESS OS process id
- LOCKED_MODE lock mode
V$RESOURCE list of all the currently locked resources in the system.
Each row can be associated with one or more rows in V$LOCK
- TYPE, ID1 and ID2 determine the resource
DBA_DDL_LOCKS has a row for each DDL lock that is being held, and
one row for each outstanding request for a DDL lock. It is subset of DBA_LOCKS
same description as the DBA_LOCK view
DBA_WAITERS view that retrieve information for each session waiting on a
lock (created via $ORACLE_HOME/rdbms/admin/catblock.sql)
- WAITING_SESSION waiting session
- HOLDING_SESSION holding session
- LOCK_TYPE, LOCK_ID1, LOCK_ID2 resource locked
- MODE_HELD lock type held
- MODE_REQUESTED lock type requested
DBA_BLOCKERS view that gives the blocking sessions (created via
$ORACLE_HOME/rdbms/admin/catblock.sql)
-HOLDING_SESSION holding session


select B.SID, C.USERNAME, C.OSUSER, C.TERMINAL,
       DECODE(B.ID2, 0, A.OBJECT_NAME,
            'Trans-'||to_char(B.ID1)) OBJECT_NAME,
     B.TYPE,
       DECODE(B.LMODE,0,'--Waiting--',
                      1,'Null',
                      2,'Row Share',
                      3,'Row Excl',
                   4,'Share',
                      5,'Sha Row Exc',
           6,'Exclusive',
                        'Other') "Lock Mode",
       DECODE(B.REQUEST,0,' ',
                      1,'Null',
                      2,'Row Share',
                      3,'Row Excl',
                      4,'Share',
                      5,'Sha Row Exc',
                      6,'Exclusive',
                     'Other') "Req Mode"
  from DBA_OBJECTS A, V$LOCK B, V$SESSION C
where A.OBJECT_ID(+) = B.ID1
  and B.SID = C.SID
  and C.USERNAME is not null
order by B.SID, B.ID2;
select nvl(S.USERNAME,'Internal') username,
 nvl(S.TERMINAL,'None') terminal,
 L.SID||','||S.SERIAL# Kill,
 U1.NAME||'.'||substr(T1.NAME,1,20) tab,
 decode(L.LMODE,1,'No Lock',
  2,'Row Share',
  3,'Row Exclusive',
  4,'Share',
  5,'Share Row Exclusive',
  6,'Exclusive',null) lmode,
 decode(L.REQUEST,1,'No Lock',
  2,'Row Share',
  3,'Row Exclusive',
  4,'Share',
  5,'Share Row Exclusive',
  6,'Exclusive',null) request
from V$LOCK L, 
 V$SESSION S,
 SYS.USER$ U1,
 SYS.OBJ$ T1
where L.SID = S.SID 
and T1.OBJ# = decode(L.ID2,0,L.ID1,L.ID2) 
and U1.USER# = T1.OWNER#
and S.TYPE != 'BACKGROUND'
order by 1,2,5
/

set linesize 80 
set pagesize 66 
column lmode heading 'Lock|Held' format a4 
column request heading 'Lock|Req.' format a4 
column username  format a10  heading "Username" 
column tab format a30 heading "Table Name" 
column LAddr heading "ID1 - ID2" format a16 
column Lockt heading "Lock|Type" format a4 
select nvl(S.USERNAME,'Internal') username, 
      decode(command, 
0,'None',decode(l.id2,0,U1.NAME||'.'||substr(T1.NAME,1,20), 
'Rollback Segment')) tab, 
      decode(L.LMODE,1,'NoLk', 2,' RS ', 3,' RX ', 
                4,'  S ', 5,' SRX', 6,'  X ','NONE') lmode, 
      decode(L.REQUEST,1,'NoLk', 2,' RSh ', 3,' RX ', 
        4,'  S ', 5,' SRX', 6,'  X ','NONE') request, 
l.id1||'-'||l.id2 Laddr, l.type Lockt 
from    V$LOCK L, V$SESSION S, SYS.USER$ U1, SYS.OBJ$ T1 
where   L.SID = S.SID and       T1.OBJ#  = decode(L.ID2,0,L.ID1,1)  
and     U1.USER# = T1.OWNER# and        S.TYPE != 'BACKGROUND' 
order by 1,2,5 
/
set lines 200
set pagesize 66
break on Kill on sid on  username on terminal
column Kill heading 'Kill String' format a13
column res heading 'Resource Type' format 999
column id1 format 9999990
column id2 format 9999990
column locking heading 'Lock Held/Lock Requested' format a40
column lmode heading 'Lock Held' format a20
column request heading 'Lock Requested' format a20
column serial# format 99999
column username  format a10  heading "Username"
column terminal heading Term format a6
column tab format a30 heading "Table Name"
column owner format a9
column LAddr heading "ID1 - ID2" format a18
column Lockt heading "Lock Type" format a40
column command format a25
column sid format 990
select
nvl(S.USERNAME,'Internal') username,
        L.SID,
        nvl(S.TERMINAL,'None') terminal,
        decode(command,
0,'None',decode(l.id2,0,U1.NAME||'.'||substr(T1.NAME,1,20),'None')) tab,
decode(command,
0,'BACKGROUND',
1,'Create Table',
2,'INSERT',
3,'SELECT',
4,'CREATE CLUSTER',
5,'ALTER CLUSTER',
6,'UPDATE',
7,'DELETE',
8,'DROP',
9,'CREATE INDEX',
10,'DROP INDEX',
11,'ALTER INDEX',
12,'DROP TABLE',
13,'CREATE SEQUENCE',
14,'ALTER SEQUENCE',
15,'ALTER TABLE',
16,'DROP SEQUENCE',
17,'GRANT',
18,'REVOKE',
19,'CREATE SYNONYM',
20,'DROP SYNONYM',
21,'CREATE VIEW',
22,'DROP VIEW',
23,'VALIDATE INDEX',
24,'CREATE PROCEDURE',
25,'ALTER PROCEDURE',
26,'LOCK TABLE',
27,'NO OPERATION',
28,'RENAME',
29,'COMMENT',
30,'AUDIT',
31,'NOAUDIT',
32,'CREATE EXTERNAL DATABASE',
33,'DROP EXTERNAL DATABASE',
34,'CREATE DATABASE',
35,'ALTER DATABASE',
36,'CREATE ROLLBACK SEGMENT',
37,'ALTER ROLLBACK SEGMENT',
38,'DROP ROLLBACK SEGMENT',
39,'CREATE TABLESPACE',
40,'ALTER TABLESPACE',
41,'DROP TABLESPACE',
42,'ALTER SESSION',
43,'ALTER USER',
44,'COMMIT',
45,'ROLLBACK',
46,'SAVEPOINT',
47,'PL/SQL EXECUTE',
48,'SET TRANSACTION',
49,'ALTER SYSTEM SWITCH LOG',
50,'EXPLAIN',
51,'CREATE USER',
52,'CREATE ROLE',
53,'DROP USER',
54,'DROP ROLE',
55,'SET ROLE',
56,'CREATE SCHEMA',
57,'CREATE CONTROL FILE',
58,'ALTER TRACING',
59,'CREATE TRIGGER',
60,'ALTER TRIGGER',
61,'DROP TRIGGER',
62,'ANALYZE TABLE',
63,'ANALYZE INDEX',
64,'ANALYZE CLUSTER',
65,'CREATE PROFILE',
66,'DROP PROFILE',
67,'ALTER PROFILE',
68,'DROP PROCEDURE',
69,'DROP PROCEDURE',
70,'ALTER RESOURCE COST',
71,'CREATE SNAPSHOT LOG',
72,'ALTER SNAPSHOT LOG',
73,'DROP SNAPSHOT LOG',
74,'CREATE SNAPSHOT',
75,'ALTER SNAPSHOT',
76,'DROP SNAPSHOT',
79,'ALTER ROLE',
85,'TRUNCATE TABLE',
86,'TRUNCATE CLUSTER',
87,'-',
88,'ALTER VIEW',
89,'-',
90,'-',
91,'CREATE FUNCTION',
92,'ALTER FUNCTION',
93,'DROP FUNCTION',
94,'CREATE PACKAGE',
95,'ALTER PACKAGE',
96,'DROP PACKAGE',
97,'CREATE PACKAGE BODY',
98,'ALTER PACKAGE BODY',
99,'DROP PACKAGE BODY',
command||' - ???') COMMAND,
        decode(L.LMODE,1,'No Lock',
                2,'Row Share',
                3,'Row Exclusive',
                4,'Share',
                5,'Share Row Exclusive',
                6,'Exclusive','NONE') lmode,
        decode(L.REQUEST,1,'No Lock',
                2,'Row Share',
                3,'Row Exclusive',
                4,'Share',
                5,'Share Row Exclusive',
                6,'Exclusive','NONE') request,
l.id1||'-'||l.id2 Laddr,
l.type||' - '||
decode(l.type,
'BL','Buffer hash table instance lock',
'CF',' Control file schema global enqueue lock',
'CI','Cross-instance function invocation instance lock',
'CS','Control file schema global enqueue lock',
'CU','Cursor bind lock',
'DF','Data file instance lock',
'DL','Direct loader parallel index create',
'DM','Mount/startup db primary/secondary instance lock',
'DR','Distributed recovery process lock',
'DX','Distributed transaction entry lock',
'FI','SGA open-file information lock',
'FS','File set lock',
'HW','Space management operations on a specific segment lock',
'IN','Instance number lock',
'IR','Instance recovery serialization global enqueue lock',
'IS','Instance state lock',
'IV','Library cache invalidation instance lock',
'JQ','Job queue lock',
'KK','Thread kick lock',
'MB','Master buffer hash table instance lock',
'MM','Mount definition gloabal enqueue lock',
'MR','Media recovery lock',
'PF','Password file lock',
'PI','Parallel operation lock',
'PR','Process startup lock',
'PS','Parallel operation lock',
'RE','USE_ROW_ENQUEUE enforcement lock',
'RT','Redo thread global enqueue lock',
'RW','Row wait enqueue lock',
'SC','System commit number instance lock',
'SH','System commit number high water mark enqueue lock',
'SM','SMON lock',
'SN','Sequence number instance lock',
'SQ','Sequence number enqueue lock',
'SS','Sort segment lock',
'ST','Space transaction enqueue lock',
'SV','Sequence number value lock',
'TA','Generic enqueue lock',
'TD','DDL enqueue lock',
'TE','Extend-segment enqueue lock',
'TM','DML enqueue lock',
'TO','Temporary Table Object Enqueue',
'TT','Temporary table enqueue lock',
'TX','Transaction enqueue lock',
'UL','User supplied lock',
'UN','User name lock',
'US','Undo segment DDL lock',
'WL','Being-written redo log instance lock',
'WS','Write-atomic-log-switch global enqueue lock',
'TS',decode(l.id2,0,'Temporary segment enqueue lock (ID2=0)',
                    'New block allocation enqueue lock (ID2=1)'),
'LA','Library cache lock instance lock (A=namespace)',
'LB','Library cache lock instance lock (B=namespace)',
'LC','Library cache lock instance lock (C=namespace)',
'LD','Library cache lock instance lock (D=namespace)',
'LE','Library cache lock instance lock (E=namespace)',
'LF','Library cache lock instance lock (F=namespace)',
'LG','Library cache lock instance lock (G=namespace)',
'LH','Library cache lock instance lock (H=namespace)',
'LI','Library cache lock instance lock (I=namespace)',
'LJ','Library cache lock instance lock (J=namespace)',
'LK','Library cache lock instance lock (K=namespace)',
'LL','Library cache lock instance lock (L=namespace)',
'LM','Library cache lock instance lock (M=namespace)',
'LN','Library cache lock instance lock (N=namespace)',
'LO','Library cache lock instance lock (O=namespace)',
'LP','Library cache lock instance lock (P=namespace)',
'LS','Log start/log switch enqueue lock',
'PA','Library cache pin instance lock (A=namespace)',
'PB','Library cache pin instance lock (B=namespace)',
'PC','Library cache pin instance lock (C=namespace)',
'PD','Library cache pin instance lock (D=namespace)',
'PE','Library cache pin instance lock (E=namespace)',
'PF','Library cache pin instance lock (F=namespace)',
'PG','Library cache pin instance lock (G=namespace)',
'PH','Library cache pin instance lock (H=namespace)',
'PI','Library cache pin instance lock (I=namespace)',
'PJ','Library cache pin instance lock (J=namespace)',
'PL','Library cache pin instance lock (K=namespace)',
'PK','Library cache pin instance lock (L=namespace)',
'PM','Library cache pin instance lock (M=namespace)',
'PN','Library cache pin instance lock (N=namespace)',
'PO','Library cache pin instance lock (O=namespace)',
'PP','Library cache pin instance lock (P=namespace)',
'PQ','Library cache pin instance lock (Q=namespace)',
'PR','Library cache pin instance lock (R=namespace)',
'PS','Library cache pin instance lock (S=namespace)',
'PT','Library cache pin instance lock (T=namespace)',
'PU','Library cache pin instance lock (U=namespace)',
'PV','Library cache pin instance lock (V=namespace)',
'PW','Library cache pin instance lock (W=namespace)',
'PX','Library cache pin instance lock (X=namespace)',
'PY','Library cache pin instance lock (Y=namespace)',
'PZ','Library cache pin instance lock (Z=namespace)',
'QA','Row cache instance lock (A=cache)',
'QB','Row cache instance lock (B=cache)',
'QC','Row cache instance lock (C=cache)',
'QD','Row cache instance lock (D=cache)',
'QE','Row cache instance lock (E=cache)',
'QF','Row cache instance lock (F=cache)',
'QG','Row cache instance lock (G=cache)',
'QH','Row cache instance lock (H=cache)',
'QI','Row cache instance lock (I=cache)',
'QJ','Row cache instance lock (J=cache)',
'QL','Row cache instance lock (K=cache)',
'QK','Row cache instance lock (L=cache)',
'QM','Row cache instance lock (M=cache)',
'QN','Row cache instance lock (N=cache)',
'QO','Row cache instance lock (O=cache)',
'QP','Row cache instance lock (P=cache)',
'QQ','Row cache instance lock (Q=cache)',
'QR','Row cache instance lock (R=cache)',
'QS','Row cache instance lock (S=cache)',
'QT','Row cache instance lock (T=cache)',
'QU','Row cache instance lock (U=cache)',
'QV','Row cache instance lock (V=cache)',
'QW','Row cache instance lock (W=cache)',
'QX','Row cache instance lock (X=cache)',
'QY','Row cache instance lock (Y=cache)',
'QZ','Row cache instance lock (Z=cache)','????') Lockt
from    V$LOCK L, 
        V$SESSION S,
        SYS.USER$ U1,
        SYS.OBJ$ T1
where   L.SID = S.SID 
and     T1.OBJ#  = decode(L.ID2,0,L.ID1,1) 
and     U1.USER# = T1.OWNER#
and     S.TYPE != 'BACKGROUND'
order by 1,2,5
/

set pagesize 60
set linesize 132
select s.username username, 
       a.sid sid, 
       a.owner||'.'||a.object object, 
       s.lockwait, 
       t.sql_text SQL
from   v$sqltext t, 
       v$session s, 
       v$access a
where  t.address=s.sql_address 
and    t.hash_value=s.sql_hash_value 
and    s.sid = a.sid 
and    a.owner != 'SYS'
and    upper(substr(a.object,1,2)) != 'V$'
/


clear columns
clear computes
clear breaks

set linesize 240
set pagesize 40

column sid format 99999 heading "SID"
column locktype format A9 heading "Lock Type"
column lockreq format A9 heading "Lock     |Requested"
column lockheld format A9 heading "Lock Held"
column timeheld format 9999999 heading "Time Held|(minutes)"
column block format a5 heading "Block"


select /* RULE */ l.sid sid,
       decode(l.type,'TM','DML','TX','Trans','UL','User',l.type) locktype,
       decode(l.request,0,'None',1,'Null',2,'Row-S',3,'Row-X',4,'Share',5,'S/Row-X',6,'Exclusive') lockreq,
       decode(l.lmode,0,'None',1,'Null',2,'Row-S',3,'Row-X',4,'Share',5,'S/Row-X',6,'Exclusive') lockheld,
       l.ctime/60 timeheld,
       decode(l.block,0,'No',1,'Yes') block 
from gv$lock l
where l.request != 0 or l.block != 0

order by l.id1, l.lmode desc, l.ctime desc

ODA administration

you can do with oakcli
Deploy Oracle Database Appliance
Configure network for Oracle Database Appliance deployment
Patching Oracle Database Appliance
Unpacking packages into oakcli repository
Troubleshoot Oracle Database Appliance
Monitor Oracle Database Appliance
Validate Oracle Database Appliance
Applying the Core Configuration key
Copying the deployment configuration file
Locate a disk on ODA
Manage ODA Repository
Manage ODA diagnostics collection

oakcli -h command to list the different options available with oakcli:

oakcli show -h

check if whether ODA deployment is Bare Metal or Virtualized

[root@raj ~]# oakcli show env_hw

o get the ODA software version

[root@raj ~]# oakcli show version

oakcli show -h



o get the ODA software and component version

[root@raj ~]# /opt/oracle/oak/bin/oakcli show version -detail

oakcli show disk

To list ASM disk groups on ODA

[root@raj ~]# oakcli show diskgroup

To list the Local file system and ACFS file system on ODA

[root@raj ~]# oakcli show fs

To list Database homes on ODA

[root@raj ~]# oakcli show dbhomes


To list database storage on ODA

[root@raj ~]# oakcli show dbstorage

[root@raj ~]# oakcli show db_config_params

To check OAK location

[root@raj ~]# env |grep ORA

o create Database on ODA

[root@raj ~]# oakcli create database -db odatest -oh OraDb12102_home1

How To Gather & Backup ASM/ACFS Metadata In A Formatted Manner version 10.1, 10.2, 11.1, 11.2 and 12.1? (Doc ID 470211.1)

>> From Node0

# oakcli show disk
# oakcli show diskgroup DATA
# oakcli show diskgroup REDO
# oakcli show diskgroup RECO
# oakcli show env_hw
# oakcli show validation storage failures
# oakcli show storage -errors


>> From both the Nodes
oakcli validate -d

>> ASM Metadata from ASM instance -

SPOOL ASM.HTML
SET MARKUP HTML ON
set echo on
set pagesize 200

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

select 'THIS ASM REPORT WAS GENERATED AT: ==> ' , sysdate " " from dual;

select 'HOSTNAME ASSOCIATED WITH THIS ASM INSTANCE: ==> ' , MACHINE " " from v$session where program like '%SMON%';
select * from v$asm_diskgroup;
select group_number,disk_number,path,name,header_status,mode_status,failgroup from v$asm_disk
order by group_number,failgroup;
SELECT * FROM V$ASM_DISK ORDER BY GROUP_NUMBER,DISK_NUMBER;
SELECT * FROM V$ASM_CLIENT;
select * from V$ASM_ATTRIBUTE;
select * from v$asm_operation;
select * from gv$asm_operation;
select * from v$asm_alias;
select * from v$version;

show parameter asm
show parameter cluster
show parameter instance_type
show parameter instance_name
show parameter spfile
show sga
spool off
exit 

<<<<<<<<<<<<<<<<<

select name, state from v$asm_diskgroup;
View the information for all ACFS volumes created by ODA Grid defaultly
asmcmd volinfo -G DATA -a
asmcmd volinfo -G FLASH -a
asmcmd volinfo -G REDO -a
asmcmd volinfo -G RECO -a
Get detailed file system information

/sbin/acfsutil info fs


select volume_name, volume_device, mountpath from v$asm_volume;

Verify that both the ASM-Proxy instances obtain the metadata related to ACFS from ASM instance running locally:

Ref:http://allthingsoracle.com/asm-proxy-new-instance-type-in-oracle-12c/


SELECT DISTINCT 
    i.instance_name asm_instance_name, 
    i.host_name asm_host_name, 
    c.instance_name client_instance_name, 
    c.status
  FROM gv$instance i, gv$asm_client c
 WHERE i.inst_id = c.inst_id;



verify that All the ASM and ACFS-related resources are running on both nodes


crsctl stat res ora.asm ora.DATA.dg ora.DATA.VOL1.advm ora.data.vol1.acfs  -t

Blocking session on RAC database

Blocking session on RAC database

Blocking session of the databases

Generate the report for Blocking session

The SQL will provide Blocking sessions with SID, node information

SELECT DISTINCT S1.USERNAME || '@' || S1.MACHINE
|| ' ( INST=' || S1.INST_ID || ' SID=' || S1.SID || ' ) IS BLOCKING '
|| S2.USERNAME || '@' || S2.MACHINE || ' ( INST=' || S1.INST_ID || ' SID=' || S2.SID || ' ) ' AS BLOCKING_STATUS
FROM GV$LOCK L1, GV$SESSION S1, GV$LOCK L2, GV$SESSION S2
WHERE S1.SID=L1.SID AND S2.SID=L2.SID
AND S1.INST_ID=L1.INST_ID AND S2.INST_ID=L2.INST_ID
AND L1.BLOCK > 0 AND L2.REQUEST > 0
AND L1.ID1 = L2.ID1 AND L1.ID2 = L2.ID2;

Blockers information

select s1.username || '@' || s1.machine  || ' ( SID=' || s1.sid || ' ) is blocking '  || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from gv$lock l1, gv$session s1, gv$lock l2, gv$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 ;
---------------------------------------------

select b.session_id ,a.SERIAL#, a.username "Blocker Details"
from gv$session a,dba_lock b
where b.session_id = a.sid
and b.blocking_others = 'Blocking';
--------------------------------

 select distinct 'alter system kill session '''||s1.sid||','||s1.serial#||',@'||s1.inst_id||''' immediate;'
  from gv$lock l1, gv$session s1, gv$lock l2, gv$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
  order by 1;


You can determine what type of Oracle process this is by querying the data dictionary:
SELECT
'USERNAME : ' || s.username || CHR(10) ||
'SCHEMA : ' || s.schemaname || CHR(10) ||
'OSUSER : ' || s.osuser || CHR(10) ||
'PROGRAM : ' || s.program || CHR(10) ||
'SPID : ' || p.spid || CHR(10) ||
'SID : ' || s.sid || CHR(10) ||
'SERIAL# : ' || s.serial# || CHR(10) ||
'KILL STRING: ' || '''' || s.sid || ',' || s.serial# || '''' || CHR(10) ||
'MACHINE : ' || s.machine || CHR(10) ||
'TYPE : ' || s.type || CHR(10) ||
'TERMINAL : ' || s.terminal || CHR(10) ||
'SQL ID : ' || q.sql_id || CHR(10) ||
'SQL TEXT : ' || q.sql_text
FROM v$session s
,v$process p
,v$sql q
WHERE s.paddr = p.addr
AND p.spid = '&&PID_FROM_OS'
AND s.sql_id = q.sql_id(+);

session details – any connection from Developer tool

set linesize 200
col MACHINE format a30
col OSUSER format a10
col SCHEMANAME format a10
col MODULE format a20

Select to_char(logon_time,'dd/mm/yyyy hh24:mi:ss'),osuser,status,schemaname,machine,SID,module,INST_ID from gv$session where type !='BACKGROUND' and module like '%Developer' order by logon_time asc;

Select to_char(logon_time,'dd/mm/yyyy hh24:mi:ss'),osuser,status,schemaname,machine,SID,module, INST_ID from gv$session where type !='BACKGROUND' and USERNAME='&a' order by logon_time asc;

Select to_char(logon_time,'dd/mm/yyyy hh24:mi:ss'),osuser,status,schemaname,machine,SID,module, INST_ID from gv$session where type !='BACKGROUND' and SID='&a' order by logon_time asc;



Select to_char(logon_time,'dd/mm/yyyy hh24:mi:ss'),osuser,status,schemaname,machine,SID,module, INST_ID from gv$session where type !='BACKGROUND'  order by logon_time asc;

Detail report  - the blocking session for the particular session

set lines 200
set pages 1000
select inst_id,sid,serial#,sql_id,prev_sql_id,sql_hash_value,status,osuser,username,program,machine,last_call_et,to_char(logon_time,'DD/MON/YYYY hh24:mi') logon_time
from gv$session
where sid in (&sid)
order by inst_id;

SQL information

select s.sid, s.sql_id, q.sql_text from gv$sqltext q, gv$session s
where q.address = s.sql_address
and s.sid = &sid
order by piece;

Kill the sessions

Command to kill the session: 
---------------------------
ALTER SYSTEM KILL SESSION 'sid, serial#';

ALTER SYSTEM KILL SESSION 'sid, serial#' immediate;

ALTER SYSTEM KILL SESSION 'sid, serial#,@<instance_id>';  (RAC)



-- SID
select p.SPID, substr(s.sid,1,6) SID, substr(s.serial#,1,8) SERIAL , s.status STATUS,
substr(s.username,1,10) USERNAME , substr(s.osuser,1,10) OSUSER,
to_char(s.logon_time,'dd-mm-yyyy hh24:mi') LOGON_TIME, s.program PROGRAM
from gv$session s , gv$process p
where s.paddr = p.addr
and s.sid = &sid;

select p.SPID, s.process CPID, substr(s.sid,1,6) SID, substr(s.serial#,1,8) SERIAL , s.status STATUS,
substr(s.username,1,10) USERNAME , substr(s.osuser,1,10) OSUSER,
to_char(s.logon_time,'dd-mm-yyyy hh24:mi') LOGON_TIME, s.program PROGRAM
from gv$session s , gv$process p
where s.paddr = p.addr
and s.program like '%sqlplus%'

select p.SPID, s.process CPID, substr(s.sid,1,6) SID, substr(s.serial#,1,8) SERIAL , s.status STATUS,
substr(s.username,1,10) USERNAME , substr(s.osuser,1,10) OSUSER,
to_char(s.logon_time,'dd-mm-yyyy hh24:mi') LOGON_TIME,
round(s.last_call_et/60) IDLE_MIN, s.program PROGRAM
from gv$session s , gv$process p
where s.paddr = p.addr
and s.program like '%sqlplus%' ;

select p.SPID, s.process CPID, substr(s.sid,1,6)||','||substr(s.serial#,1,8) SIDSERIAL , s.status STATUS,
to_char(s.logon_time,'dd-mm-yyyy hh24:mi') LOGON_TIME, round(s.last_call_et/60) IDLE_MIN,
substr(s.username,1,10) USERNAME , substr(s.osuser,1,10) OSUSER,
s.program PROGRAM
from gv$session s , gv$process p
where s.paddr = p.addr
and s.username IS not null
and s.username not in ('SYS')
order by IDLE_MIN desc;

Long running

column username format 'a10'
column osuser format 'a10'
column module format 'a16'
column program_name format 'a20'
column program format 'a20'
column machine format 'a20'
column action format 'a20'
column sid format '9999'
column serial# format '99999'
column spid format '99999'
set linesize 200
set pagesize 30
select
a.sid,a.serial#,a.username,a.osuser,c.start_time,
b.spid,a.status,a.machine,
a.action,a.module,a.program
from
gv$session a, gv$process b, gv$transaction c,
gv$sqlarea s
Where
a.paddr = b.addr
and a.saddr = c.ses_addr
and a.sql_address = s.address (+)
and to_date(c.start_time,'mm/dd/yy hh24:mi:ss') <= sysdate - (60/1440) -- running for 60 minutes
order by c.start_time
/

Notification Text: The following list of SQL statements have been running for over 60 minutes.

SQL Statement:

select 'SID:'||s.sid||', Serial#:'||s.serial#||', Username:'||s.username||', Machine:'||s.machine||
       ', Program:'||s.program||', HashValue:'||s.sql_hash_value||', SQL Text:'||nvl(substr(sql.sql_text,1,40),'Unknown SQL'), last_call_et
from v$session s
left outer join v$sql sql on sql.hash_value=s.sql_hash_value and sql.address=s.sql_address
where s.status='ACTIVE'
and s.type <> 'BACKGROUND'

and last_call_et >= 3600

SID detail 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
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



directly query data dictionary views such as V$SQL to determine which SQL statements
are using excessive I/O—for example:
SELECT *
FROM
(SELECT
parsing_schema_name
,direct_writes
,SUBSTR(sql_text,1,75)
,disk_reads
FROM v$sql
ORDER BY disk_reads DESC)
WHERE rownum < 20;
To determine which sessions are currently waiting for I/O resources, query V$SESSION:
SELECT
username
,program
,machine
,sql_id
FROM v$session
WHERE event LIKE 'db file%read';

You can determine what type of Oracle process this is by querying the data dictionary:
SELECT
'USERNAME : ' || s.username || CHR(10) ||
'SCHEMA : ' || s.schemaname || CHR(10) ||
'OSUSER : ' || s.osuser || CHR(10) ||
'PROGRAM : ' || s.program || CHR(10) ||
'SPID : ' || p.spid || CHR(10) ||
'SID : ' || s.sid || CHR(10) ||
'SERIAL# : ' || s.serial# || CHR(10) ||
'KILL STRING: ' || '''' || s.sid || ',' || s.serial# || '''' || CHR(10) ||
'MACHINE : ' || s.machine || CHR(10) ||
'TYPE : ' || s.type || CHR(10) ||
'TERMINAL : ' || s.terminal || CHR(10) ||
'SQL ID : ' || q.sql_id || CHR(10) ||
'SQL TEXT : ' || q.sql_text
FROM v$session s
,v$process p
,v$sql q
WHERE s.paddr = p.addr
AND p.spid = '&&PID_FROM_OS'
AND s.sql_id = q.sql_id(+);


the process is running a SQL statement, further details about the query can be
extracted by generating an execution plan:
SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(('&&sql_id')));


SELECT sid, sql_text FROM v$sql_monitor
WHERE status = 'EXECUTING';

see all executions for a given query (based on the SQL_ID column), we can get that
information by querying on the three necessary columns to drill to a given execution of a SQL query:

SELECT * FROM (
SELECT sql_id, to_char(sql_exec_start,'yyyy-mm-dd:hh24:mi:ss') sql_exec_start,
sql_exec_id, sum(buffer_gets) buffer_gets,
sum(disk_reads) disk_reads, round(sum(cpu_time/1000000),1) cpu_secs
FROM v$sql_monitor
WHERE sql_id = 'fcg00hyh7qbpz'
GROUP BY sql_id, sql_exec_start, sql_exec_id
ORDER BY 6 desc)
WHERE rownum <= 5;

top five recent SQL statements that performed the highest disk I/O, you could
issue the following query:
SELECT sql_text, disk_reads FROM
(SELECT sql_text, buffer_gets, disk_reads, sorts,
cpu_time/1000000 cpu, rows_processed, elapsed_time
FROM v$sqlstats
ORDER BY disk_reads DESC)
WHERE rownum <= 5;

SELECT sid, buffer_gets, disk_reads, round(cpu_time/1000000,1) cpu_seconds
FROM v$sql_monitor
WHERE SID=100
AND status = 'EXECUTING';

the top five most CPU-consuming queries in your database, you could
issue the following query:
SELECT * FROM (
SELECT sid, buffer_gets, disk_reads, round(cpu_time/1000000,1) cpu_seconds
FROM v$sql_monitor
ORDER BY cpu_time desc)
WHERE rownum <= 5;

If you join V$SQLSTATS to V$SQL, you can see information for particular users. See the
following example:
SELECT schema, sql_text, disk_reads, round(cpu,2) FROM
(SELECT s.parsing_schema_name schema, t.sql_id, t.sql_text, t.disk_reads,
t.sorts, t.cpu_time/1000000 cpu, t.rows_processed, t.elapsed_time
FROM v$sqlstats t join v$sql s on(t.sql_id = s.sql_id)
WHERE parsing_schema_name = 'SCOTT'
ORDER BY disk_reads DESC)
WHERE rownum <= 5;


SQL> set autotrace on
Then, you can run a query using AUTOTRACE, which will show the execution plan and query execution
statistics for your query:
SELECT last_name, first_name
FROM employees NATURAL JOIN departments
WHERE employee_id = 101;
LAST_NAME FIRST_NAME
------------------------- --------------------
Kochhar Neena
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 33 | 2 (0)| 00:00:01
| 1 | NESTED LOOPS | | 1 | 33 | 2 (0)| 00:00:01
| 2 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 26 | 1 (0)| 00:00:01
|* 3 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01
|* 4 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 11 | 77 | 1 (0)| 00:00:01
|* 5 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 0 (0)| 00:00:01
--------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
490 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)

1 rows processed