Oracle 锁表, 死循环, 表关联, 约束删除

  锁表:

select sess.sid,sess.serial#, 
  lo.oracle_username,lo.os_user_name, 
  ao.object_name,lo.locked_mode  
from v$locked_object lo,dba_objects ao,v$session sess 
where ao.object_id=lo.object_id 
  and lo.session_id=sess.sid;

  杀掉session:

alter system kill session '68,51';--分别为sid和serail#号

oracle表锁的几种模式v$locked_object-locked_mode

0：none
1：null 空
2：Row-S 行共享(RS)：共享表锁，sub share
3：Row-X 行独占(RX)：用于行的修改，sub exclusive
4：Share 共享锁(S)：阻止其他DML操作，share
5：S/Row-X 共享行独占(SRX)：阻止其他事务操作，share/sub exclusive
6：exclusive 独占(X)：独立访问使用，exclusive

数字越大锁级别越高, 影响的操作越多。

• 1级锁有：Select，有时会在v$locked_object出现。

• 2级锁有：Select for update, Lock For Update, Lock Row Share
  select for update
  当对话使用for update子串打开一个游标时，所有返回集中的数据行都将处于行级(Row-X)独占式锁定，其他对象只能查询这些数据行，不能进行update、delete或select for update操作。

• 3级锁有：Insert,Update, Delete, Lock Row Exclusive 没有commit之前插入同样的一条记录会没有反应, 因为后一个3的锁会一直等待上一个3的锁, 我们必须释放掉上一个才能继续工作。

• 4级锁有：Create Index, Lock Share
  locked_mode为2,3,4不影响DML(insert,delete,update,select)操作,
  但DDL(alter,drop等)操作会提示ora-00054错误。

  00054, 00000, “resource busy and acquire with NOWAIT specified” // *Cause: Resource interested is busy. // *Action: Retry if necessary.

• 5级锁有：Lock Share Row Exclusive
  具体来讲有主外键约束时update / delete … ; 可能会产生4,5的锁。

• 6级锁有：Alter table, Drop
  table, Drop Index, Truncate table, Lock Exclusive

因此, 杀session的时候优先从高级别锁开始.

  查死循环:

-- 知道 object类型 可用 type来筛选 如 type='PACKAGE'
select * from v$db_object_cache where locks>0 and pins>0; --后面最好跟上 and owner=

  生成杀掉死循环的语句:

select --a.type,
  b.sid,b.SERIAL#,
  a.OBJECT, 
  'alter system kill session '||''''||b.sid||','||b.SERIAL#||''';' kill_sql
from SYS.V_$ACCESS a, SYS.V_$session b
WHERE A.TYPE = 'PACKAGE'--PROCEDURE,FUNCTION等, 或者直接用 A.OBJECT='...'来筛选
   AND A.SID = B.SID
   and b.status = 'ACTIVE';

  查询表关联:

-- 头表查子表(哪些表关联的这个表, 即这个表是外键)
select u1.constraint_Name, u1.table_Name as table_, u2.table_Name as reference_
from user_constraints u1 
  join user_constraints u2 on u1.r_constraint_Name = u2.constraint_Name
where u1.constraint_type='R'
  and u2.table_name=upper('target_table_name')
;

-- 子表查头表(这个表关联的哪些表, 其他表是外键)
SELECT target.constraint_name
     , head.TABLE_NAME AS head_table
     , target.TABLE_NAME AS detail_table
FROM user_CONSTRAINTS head 
  JOIN user_CONSTRAINTS target ON target.r_constraint_name=head.CONSTRAINT_NAME
WHERE target.TABLE_NAME=upper('target_table_name')
  and head.CONSTRAINT_TYPE='P'
;

  查询约束; 删除约束:

-- 根据表名查询约束
select table_name, CONSTRAINT_NAME, CONSTRAINT_TYPE, SEARCH_CONDITION, R_CONSTRAINT_NAME
from USER_CONSTRAINTS
where TABLE_NAME=upper('table_name')
--   and instr(SEARCH_CONDITION_VC, upper('field_name'))>0
;
-- 删除非空约束
alter table table_name modify field_name null
;
-- 删除唯一约束, 外键等
alter table table_name drop constraint constraint_name
;

  数据闪回(delete 且 commit之后的数据恢复):

-- 先查误删的数据, 查到了就可以恢复. 下面两种查询都是20min以内的数据
select * from test_table as of timestamp (systimestamp - interval '20' minute);
select * from test_table as of timestamp sysdate - 20/1440;
-- 删除的数据在库中是不存在了的, 我们要使用 insert 命令插入要恢复的数据. 
insert into test_table 
  select * from test_table as of timestamp  sysdate - 20/1440 where id = 1521;
-- 注意带上准确的条件. 插入后, 使用 commit 命令提交数据.
-- 另外, 如果表使用了 truncate 命令清空数据, 则无法通过此方法恢复误删的数据.

  查询表中是否有某个字段, 有就将之改为0

declare
  type_count int;
begin
  select count(*) into TYPE_COUNT from user_tab_columns
  where Table_Name = 'YOUR_TABLE_NAME'
    and COLUMN_NAME = 'YOUR_FIELD_NAME'
  ;
  if type_count>0 then
    update YOUR_TABLE_NAME set YOUR_FIELD_NAME=0 where your_field_name is null;
  end if;
end

  查询字段有没有 非空约束 , 有就去掉约束

declare
  type_count int;
begin
  select COUNT(*) INTO TYPE_COUNT from USER_constraints
  where TABLE_NAME = 'YOUR_TABLE_NAME'
  /** 
   * C:非空约束|检查约束(check constraint on a table),
   * P:主键约束(primary key), 
   * U:唯一约束(unique key), 
   * R:外键(referential integrity)
   */
    and CONSTRAINT_TYPE = 'C'
    and search_condition_VC = '"YOUR_FIELD_NAME" IS NOT NULL'
  ;
  IF TYPE_COUNT>0 THEN
    execute immediate 'alter table YOUR_TABLE_NAME modify YOUR_FIELD_NAME null';
  END IF;
end

  调序列

-- 调序列
declare
    -- set serveroutput on
    sequenceName VARCHAR2(255) := upper('seqname'); -- 序列名
    temporaryText varchar2(1000);
    NUM varchar2(1000);
    i_step int := 1000; -- 步长
begin
    -- 调大步长
    temporaryText := 'alter sequence '||sequenceName||' increment by '|| i_step;
    DBMS_OUTPUT.PUT_LINE('1.execute: ' || temporaryText);
    execute immediate temporaryText;
    -- 调用序列
    temporaryText := 'select '||sequenceName||'.nextval from dual';
    execute immediate temporaryText INTO NUM;
    DBMS_OUTPUT.PUT_LINE('2.execute: ' || temporaryText || ' => ' || NUM);
    -- 恢复步长
    temporaryText := 'alter sequence '||sequenceName||' increment by 1';
    execute immediate temporaryText;
    DBMS_OUTPUT.PUT_LINE('3.execute: ' || temporaryText);
end;

记录…