AskTom

Hi,

today I had problem with one query.
Even I killed session from sqlplus by command ALTER SESSION KILL, the process still running in database.
The status where killed but still consumed CPU, so I had to kill it from OS.

1. Find the problemtic query

SQL> SET LINESIZE 100
COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A45

SELECT s.inst_id,
s.sid,
s.serial#,
p.spid,
s.username,
s.program
FROM   gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE  s.type != ‘BACKGROUND’ and sid=287;

INST_ID      SID     SERIAL# SPID        USERNAME   PROGRAM
———- ———- ———- ———- ———- ———————————————
1      287       25938 18146        VPTS       SQL Developer

SQL> select username, status from v$session where status=’KILLED’;

USERNAME   STATUS
———- ——–
VPTS       KILLED

2. Now you can kill sessiion from OS

oracle@vserver1:~> ps -ef | grep 18146
oracle   18146     1 95 Mar15 ?        21:31:08 oracleprod (LOCAL=NO)
oracle   18418 14337  0 07:54 pts/0    00:00:00 grep 18146

oracle@vserver1:~>kill -9 18146

3. After than the process is gone

SQL> select username, status from v$session where status=’KILLED’;

no rows selected

SQL> SQL> SET LINESIZE 100
COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A45

SELECT s.inst_id,
s.sid,
s.serial#,
p.spid,
s.username,
s.program
FROM   gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE  s.type != ‘BACKGROUND’ and sid=287;

no rows selected

Regards,

Tom

SQL> col nm for a8
select nm, max(description) descript
from
(select regexp_replace( name, ‘[0-9a-z]‘, ‘#’ ) nm, description
from v$bgprocess)
group by nm
order by nm
/SQL>

NM       DESCRIPT
——– —————————————————————-
ACMS     Atomic Controlfile to Memory Server
ARB#     ASM Rebalance 9
ARBA     ASM Rebalance 10
ARC#     Archival Process 9
ASMB     ASM Background
CJQ#     Job Queue Coordinator
CKPT     checkpoint
CTWR     Change Tracking Writer
DBRM     Resource Manager process
DBW#     db writer process 9
DIA#     diagnosibility process 9
DIAG     diagnosibility process
DMON     DG Broker Monitor Process
DSKM     slave DiSKMon process
EMNC     EMON Coordinator
FBDA     Flashback Data Archiver Process
FMON     File Mapping Monitor Process
FSFP     Data Guard Broker FSFO Pinger
GMON     diskgroup monitor
GTX#     Global Txn process 9
INSV     Data Guard Broker INstance SlaVe Process
LCK#     Lock Process 0
LGWR     Redo etc.
LMD#     global enqueue service daemon 0
LMON     global enqueue service monitor
LMS#     global cache service process 9
LNS#     Network Server 9
LSP#     Set Guard Standby Information for Logical Standby
MARK     mark AU for resync koordinator
MMAN     Memory Manager
MMNL     Manageability Monitor Process 2
MMON     Manageability Monitor Process
MRP#     Managed Standby Recovery
NSV#     Data Guard Broker NetSlave Process 9
OFSC     OFS CSS
PING     interconnect latency measurement
PMON     process cleanup
PSP#     process spawner 0
QMNC     AQ Coordinator
RBAL     ASM Rebalance master
RCBG     Result Cache: Background
RECO     distributed recovery
RMS#     rac management server
RSM#     Data Guard Broker Resource Guard Process 1
RSMN     Remote Slave Monitor
RVWR     Recovery Writer
SMCO     Space Manager Process
SMON     System Monitor Process
VBG#     Volume BG 9
VDBG     Volume Driver BG
VKTM     Virtual Keeper of TiMe process

51 rows selected.

SQL>

Regards,

Tom

Hi,

there are two ways how to get information about disk group usage.

Sqlplus
——–

SQL> set lines 255
SQL> col path for a35
SQL> col Diskgroup for a15
SQL> col DiskName for a20
SQL> col disk# for 999
SQL> col total_mb for 999,999,999
SQL> col free_mb for 999,999,999
SQL> compute sum of total_mb on DiskGroup
SQL> compute sum of free_mb on DiskGroup
SQL> break on DiskGroup skip 1 on report -
>
SQL> set pages 255
SQL>
SQL> select a.name DiskGroup, b.disk_number Disk#, b.name DiskName, b.total_mb, b.free_mb, b.path, b.header_status
2  from v$asm_disk b, v$asm_diskgroup a
3  where a.group_number (+) =b.group_number
4  order by b.group_number, b.disk_number, b.name
5  /

set lines 122
set pages 66
DISKGROUP       DISK# DISKNAME                 TOTAL_MB      FREE_MB PATH       HEADER_STATU
————— —– ——————– ———— ———— ———————————– ————
DATA                0 DATA                      238,747       37,328 ORCL:DATA  MEMBER
***************                            ———— ————
sum                                             238,747       37,328

ASMCMD tool
———–

oracle@antarctic:~> asmcmd
ASMCMD> lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB Req_mir_free_MB  Usable_file_MB  Offline_disks  Name
MOUNTED  EXTERN  N         512   4096  1048576    238747    37293 0           37293              0  DATA/

Regards,

Tom