锁表:
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;
记录…