[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;
/