[20160520]alter table xxx disable table lock.txt
--如果你想别人不能drop,truncate表,可以简单取消表锁.例子如下:
1.环境:
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------- -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SCOTT@book> create table emp2 as select * from emp ;
Table created.
2.测试:
SCOTT@book> select owner,table_name,table_lock from dba_tables where owner=user and table_name='EMP2';
OWNER TABLE_NAME TABLE_LO
------ ---------- --------
SCOTT EMP2 ENABLED
SCOTT@book> alter table emp2 disable table lock;
Table altered.
SCOTT@book> select owner,table_name,table_lock from dba_tables where owner=user and table_name='EMP2';
OWNER TABLE_NAME TABLE_LO
------ ---------- --------
SCOTT EMP2 DISABLED
3.drop ,truncate测试:
SCOTT@book> drop table emp2 ;
drop table emp2
*
ERROR at line 1:
ORA-00069: cannot acquire lock -- table locks disabled for EMP2
SCOTT@book> truncate table emp2 ;
truncate table emp2
*
ERROR at line 1:
ORA-00069: cannot acquire lock -- table locks disabled for EMP2
SCOTT@book> insert into emp2 select * from emp where rownum=1;
1 row created.
SCOTT@book> alter table emp2 drop column comm;
alter table emp2 drop column comm
*
ERROR at line 1:
ORA-00069: cannot acquire lock -- table locks disabled for EMP2
--drop ,truncate以及修改表结构不行.
SCOTT@book> @ &r/spid
SID SERIAL# SPID PID P_SERIAL# C50
---------- ---------- ------ ------- ---------- --------------------------------------------------
46 49 7900 28 19 alter system kill session '46,49' immediate;
SYS@book> @ &r/viewlock
SID SERIAL# USERNAME OSUSER MACHINE MODULE LOCK_TYPE MODE_HELD MODE_REQUE LOCK_ID1 LOCK_ID2 OWNER OBJECT_TYP OBJECT_NAME BLOCK LOCKWAIT
------ ---------- ---------- ---------- ---------- ------------ --------------- ---------- ---------- ---------- ---------- ------ ---------- ----------- ----- ---------
46 49 SCOTT oracle gxqyydg4 SQL*Plus TX Transaction Exclusive None 655392 38715 No
--你可以发现没有TM类型的锁.
4.修改回来:
SCOTT@book> alter table emp2 enable table lock;
Table altered.
SCOTT@book> insert into emp2 select * from emp where rownum=1;
1 row created.
SCOTT@book> @ &r/viewlock
SID SERIAL# USERNAME OSUSER MACHINE MODULE LOCK_TYPE MODE_HELD MODE_REQUE LOCK_ID1 LOCK_ID2 OWNER OBJECT_TYP OBJECT_NAME BLOCK LOCKWAIT
------ ---------- ---------- ---------- ---------- ------------ --------------- ---------- ---------- ---------- ---------- ------ ---------- ----------- ----- ----------
46 49 SCOTT oracle gxqyydg4 SQL*Plus TX Transaction Exclusive None 655362 38708 No
46 49 SCOTT oracle gxqyydg4 SQL*Plus TM DML(TM) Row-X (SX) None 93674 0 SCOTT TABLE EMP2 No
--对比可以发现这样多了一个TM类型的对象锁.另外注意一点给表设置只读,并不意味着不能drop,
SCOTT@book> alter table emp2 read only ;
Table altered.
SCOTT@book> truncate table emp2 ;
truncate table emp2
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "SCOTT"."EMP2"
--truncate不允许.
SCOTT@book> drop table emp2 ;
Table dropped.
--依旧可以可以drop表,当然这些方法控制意外操作不是很好,小量的可以接受.
--要禁止最好参考链接:http://blog.itpub.net/267265/viewspace-1802941/
--如果写正则表达式还复杂1点,修改如下:
CREATE OR REPLACE TRIGGER SYS.TRI_PREVENT_DROP_TRUNCATE
BEFORE TRUNCATE OR DROP ON DATABASE
BEGIN
IF ora_dict_obj_type = 'TABLE' AND ora_dict_obj_owner = 'SCOTT' and ORA_DICT_OBJ_NAME not like 'SYS\_JOURNAL\_%' escape '\'
THEN
raise_application_error (-20000, 'YOU CAN NOT TRUNCATE or DROP ' || ora_dict_obj_name || ' TABLE!');
END IF;
END;
/
