Oracle Killing Sessions Version 10.2

kill oracle sessions scrit (with sqlplus)

select s.sid, s.serial#, s.username, spid, 
osuser, s. program, schemaname 
from v$session s, v$process p 
where machine like '%ta%' and s.program like '%%' and s.paddr = p.addr
/

select 'alter system kill session ''' 
|| s.sid || ',' || s.serial# || '''; -- ' 
|| s.machine || ',' || s.program  
from v$session s, v$process p 
where machine like '%%' and s.program like '%%' and s.paddr = p.addr
/

---

Oracle Killing Sessions
Version 10.2

UNIX
Killing sessions in the UNIX environment ps -ef | grep ora to find Oracle processes. Be sure to get the process id of the session you are trying to kill.

kill -1
or
kill -9
kill -9 5745
All in one kill ps -ef | grep pmon_$ORACLE_SID | awk '{print $2}' | xargs kill -9

Windows
Killing sessions in the Windows environment with ORAKILL. orakill
SELECT instance_name
FROM gv$instance;

col program format a30

SELECT spid, osuser, s.program, schemaname
FROM gv$process p, gv$session s
WHERE p.addr = s.paddr;

c:\oracle\product\ora102\bin> orakill orabase spid

All
Killing sessions from inside the database To kill sessions within the database requires the ALTER SYSTEM privilege and the sid and serial# of the session to be killed

GRANT alter system TO ;

SELECT sid, serial#, username, schemaname, osuser
FROM gv$session
WHERE username = ;

ALTER SYSTEM KILL SESSION '< sid>,';
conn / as sysdba

GRANT alter system TO aqadmin;

conn aqadmin/aqadmin

SELECT sid, serial# , username, schemaname, osuser
FROM gv$session
WHERE username = 'AQUSER';

SQL> ALTER SYSTEM KILL SESSION '9,177' IMMEDIATE;

Kill All Sessions
Kill All Instance Sessions conn / as sysdba

set heading off
set termout off
set verify off
set echo off
set feedback off

ALTER SYSTEM enable restricted session;

ALTER SYSTEM checkpoint global;

spool kill_all.sql

SELECT 'execute kill_session('|| chr(39) || sid || chr(39) || ',' || chr(39) || serial# || chr(39) || ');'
FROM gv_$session
WHERE (username IS NOT NULL OR username <> 'SYS');

spool off

@kill_all

Session Kill Demo Procedure
An infinite loop for testing CREATE OR REPLACE PROCEDURE infinite_loop IS

BEGIN
LOOP
NULL;
END LOOP;
END infinite_loop;
/

SQL> exec infinite_loop

Enterprise DB - another choice of database

change the column name via exchange partition

        create table base_table (
               col1    number(5) constraint pk_bt primary key,
               col2    number(5),
               clo3    number(5)
        );
        
        create table pt_table (
               col1    number(5) constraint pk_pt primary key using index local,
               col2    number(5),
               col3    number(5)
        )
        partition by range (col1) (
        partition p1 values less than (maxvalue)
        );
        
        alter table pt_table 
        exchange partition p1 with table base_table
        including indexes without validation;
        
        drop table base_table;
        create table base_table (
               col1    number(5) constraint pk_bt primary key,
               col2    number(5),
               col3    number(5)
        );
        
        alter table pt_table 
        exchange partition p1 with table base_table
        including indexes without validation;

Fw foriegn key and indices

Oracle中更新主外键时的一些注意事项
发表人:winkexp | 发表时间: 2005年一月18日, 23:58
这是很早前看tom的经典文章expert 101时的练习笔记，也许当时的理解并不深刻....现在不做开发了好多东西已经模糊了很多。


更新主外键时的一些注意事项
一、更新主外键

用scott用户打开两个窗口


1、外键无索引时，子表更新外键未提交，主表更新非子表引用的主键时被阻塞


会话1：


create table t1 (x int primary key);

insert into t1 values(1);

insert into t1 values(2);

insert into t1 values(3);

commit;

create table t2（y int references t1）；

insert into t2 values（1）；

commit;

update t2 set y=2 where y=1;


会话2：


update t1 set x=4 where x=3; //会话被阻塞


2、外键有索引时，子表更新外键未提交，主表更新非子表引用的主键时不会被阻塞


会话1：


create index t2_index on t2(y) ; //创建外键索引

update t2 set y=2 where y=1;


会话2：


update t1 set x=4 where x=3;

已更新 1 行；//可以正常更新


3、外键有无索引，对于子表更新外键未提交，主表更新相对应的主键无影响，更新主键的session都会被阻塞


会话1：


update t2 set y=2 where y=1;


会话2：


update t1 set x=4 where x=1; //更新子表已引用的


会话被阻塞。


会话1：


update t2 set y=2 where y=1;


会话2：


update t1 set x=4 where x=2 ; //更新子表将要引用的

会话被阻塞。――很好理解，主表要判断是否违反约束


二、更新子表非外键列未提交


1、外键无索引，更新主表已被外键引用的主键时，更新主键的session被阻塞


会话1：


create table t1 (x int primary key,x1 int);

insert into t1 values(1,1);

insert into t1 values(2,2);

insert into t1 values(3,3);

commit ;


create table t2(y int references t1,y1 int);

insert into t2 values(1,1);

commit ;

update t2 set y1=2 where y1=1;


会话2：



update t1 set x=4 where x=1; //更新外键引用的主键

会话被阻塞。


2、外键有索引，更新主表已被外键引用的主键时，更新主键的session不会被阻塞而报约束错误


会话1：


create index t2_index on t2(y);

update t2 set y1=2 where y1=1;


会话2：


update t1 set x=4 where x=1

*

ERROR 位于第 1 行:


ORA-02292: 违反完整约束条件 (SCOTT.SYS_C001607) - 已找到子记录日志


3、外键无索引，更新主表未被外键引用的主键时，更新主键的session被阻塞


会话1：


drop index t2_index；

update t2 set y1=2 where y1=1;


会话2:


update t1 set x=4 where x=2;

会话被阻塞。


4、外键有索引，更新主表未被外键引用的主键时，更新主键的session不会被阻塞


会话1：


create index t2_index on t2(y);

update t2 set y1=2 where y1=1;


会话2：


update t1 set x=4 where x=2;
已更新 1 行。



另外在一个主表有on delete cascade，子表没有外键索引时，对主表操作会级联到子表，子表将进行全表扫描。

总结：在需要更新主键的情况下，最好是创建子表的外键索引。

list foriegn key constraints without indeces

COLUMN COLUMNS format a30 word_wrapped
COLUMN table_name format a30 word_wrapped
COLUMN constraint_name format a30 word_wrapped

SELECT TABLE_NAME,
       CONSTRAINT_NAME,
       CNAME1 || NVL2(CNAME2, ',' || CNAME2, NULL) ||
       NVL2(CNAME3, ',' || CNAME3, NULL) ||
       NVL2(CNAME4, ',' || CNAME4, NULL) ||
       NVL2(CNAME5, ',' || CNAME5, NULL) ||
       NVL2(CNAME6, ',' || CNAME6, NULL) ||
       NVL2(CNAME7, ',' || CNAME7, NULL) ||
       NVL2(CNAME8, ',' || CNAME8, NULL) COLUMNS
  FROM 
       /* convert relation constraints columns to single row format */
       (SELECT B.TABLE_NAME,
               B.CONSTRAINT_NAME,
               MAX(DECODE(POSITION, 1, COLUMN_NAME, NULL)) CNAME1,
               MAX(DECODE(POSITION, 2, COLUMN_NAME, NULL)) CNAME2,
               MAX(DECODE(POSITION, 3, COLUMN_NAME, NULL)) CNAME3,
               MAX(DECODE(POSITION, 4, COLUMN_NAME, NULL)) CNAME4,
               MAX(DECODE(POSITION, 5, COLUMN_NAME, NULL)) CNAME5,
               MAX(DECODE(POSITION, 6, COLUMN_NAME, NULL)) CNAME6,
               MAX(DECODE(POSITION, 7, COLUMN_NAME, NULL)) CNAME7,
               MAX(DECODE(POSITION, 8, COLUMN_NAME, NULL)) CNAME8,
               COUNT(*) COL_CNT
          FROM 
               /* format name into 30 chars */
               (SELECT SUBSTR(TABLE_NAME, 1, 30) TABLE_NAME,
                       SUBSTR(CONSTRAINT_NAME, 1, 30) CONSTRAINT_NAME,
                       SUBSTR(COLUMN_NAME, 1, 30) COLUMN_NAME,
                       POSITION
                  FROM USER_CONS_COLUMNS) A,
               USER_CONSTRAINTS B
         WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
           AND B.CONSTRAINT_TYPE = 'R'
         GROUP BY B.TABLE_NAME, B.CONSTRAINT_NAME) CONS
 WHERE 
 /* make sure the constraints can not utilize the indices */
 COL_CNT > ALL
 (SELECT COUNT(*)
          FROM USER_IND_COLUMNS I
         WHERE I.TABLE_NAME = CONS.TABLE_NAME
           AND I.COLUMN_NAME IN (CNAME1, CNAME2, CNAME3, CNAME4, CNAME5,
                CNAME6, CNAME7, CNAME8)
           AND I.COLUMN_POSITION <= CONS.COL_CNT
          GROUP BY I.INDEX_NAME)  
/