[20150918]禁止用户truncate以及drop表.txt
--一个需求要求禁止用户truncate以及drop表,实际上很简单仅仅建立一个触发器就ok了.
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'
THEN
raise_application_error (-20000, 'YOU CAN NOT TRUNCATE or DROP ' || ora_dict_obj_name || ' TABLE!');
END IF;
END;
/
--仔细想想存在什么问题吗?我首先想到的物化视图的刷新,有时候要执行truncate.当然看刷新的方式:
1.建立测试环境:
SCOTT@test> @ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.3.0 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
--建立触发器,脚本见上.
2.建立测试信息:
create table t (id number, name varchar2(30));
alter table t add constraint pk_t primary key (id);
create materialized view log on t;
--drop materialized view log on t;
create materialized view mv_t refresh complete as select count(*) from t;
insert into t select rownum, tname from tab;
commit;
SCOTT@test> exec dbms_mview.refresh('mv_t')
PL/SQL procedure successfully completed.
--可以发现这样没有问题.
SCOTT@test> select * from mv_t;
COUNT(*)
----------
49
--但是如果执行如下呢?
--11G物化视图刷新有1个参数atomic_refresh.
--如果为false，采用的方式是truncate，再使用/*+ append */ 提示insert。这样redo最少，但是刷新期间无法访问。
--如果为true，采用的方式是delete，再insert。这样产生许多redo与undo。这样在刷新期间访问没问题，最多有点慢。
--12c在这个基础上引入1个参数Out of place,刷新时先建立表在外部,刷新后通过类似分区交换的技术与之交换,这个刷新很形象的命名
--为out-of-place refresh.
SCOTT@test> exec dbms_mview.refresh('mv_t', atomic_refresh => false)
BEGIN dbms_mview.refresh('mv_t', atomic_refresh => false); END;
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-20000: YOU CAN NOT TRUNCATE or DROP MV_T TABLE!
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2563
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2776
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2745
ORA-06512: at line 1
--因为是11g的缘故,无法测试out-of-place参数.不过应该不行.
3.还会有什么情况呢?
--重建索引.
SCOTT@test> ALTER INDEX SCOTT.PK_T REBUILD;
Index altered.
--再线重建索引呢?
SCOTT@test> ALTER INDEX SCOTT.PK_T REBUILD online;
ALTER INDEX SCOTT.PK_T REBUILD online
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 2
ORA-20000: YOU CAN NOT TRUNCATE or DROP SYS_JOURNAL_315614 TABLE!
ORA-06512: at line 4
ORA-00604: error occurred at recursive SQL level 2
ORA-20000: YOU CAN NOT TRUNCATE or DROP SYS_JOURNAL_315614 TABLE!
ORA-06512: at line 4
--再次执行会出现如下错误.
SCOTT@test> ALTER INDEX SCOTT.PK_T REBUILD online;
ALTER INDEX SCOTT.PK_T REBUILD online
*
ERROR at line 1:
ORA-08104: this index object 315614 is being online built or rebuilt
--可以发现使用online参数建立索引要建立一张SYS_JOURNAL_315614 IOT表(后面的数字对应PK_T索引的object_id),完成后再删除时
--由于触发器的限制报错!!也就是以上的脚本存在问题,必须排除这样情况.
--报ORA-08104错误处理可以参考许多网上的做法.删除SYS_JOURNAL_315614(关闭触发器先),执行如下:
DECLARE
ret BOOLEAN;
BEGIN
ret := DBMS_REPAIR.ONLINE_INDEX_CLEAN (315614);
END;
/
--以sys用执行ok.
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 like 'SYS_JOURNAL_%'
THEN
raise_application_error (-20000, 'YOU CAN NOT TRUNCATE or DROP ' || ora_dict_obj_name || ' TABLE!');
END IF;
END;
/
4.顺便做一个测试看看,如果存在SYS_JOURNAL_315614表会出现什么情况?
SCOTT@test> create table SYS_JOURNAL_315614 as select * from t where 1=0;
Table created.
SCOTT@test> ALTER INDEX SCOTT.PK_T REBUILD online;
ALTER INDEX SCOTT.PK_T REBUILD online
*
ERROR at line 1:
ORA-08106: cannot create journal table SCOTT.SYS_JOURNAL_315614
SCOTT@test> host oerr ora 8106
08106, 00000, "cannot create journal table %s.%s"
// *Cause: The online index builder could not create its journal table
// *Action: Rename the conflicting table or rerun the SQL statement. There
// * may be a concurrent online index rebuild on the same object.
// *Action: rename your table in conflict or rerun the SQL statement
// * there may be a concurrent online index rebuild on the same object.
--报ORA-08106错误,改名即可.
SCOTT@test> rename SYS_JOURNAL_315614 to txx;
Table renamed.
SCOTT@test> ALTER INDEX SCOTT.PK_T REBUILD online;
Index altered.
--实际上上面的写法还存在问题,主要_的解析为任何字符,如果存在SYSaJOURNALa315614这样的对象一样能删除.
SCOTT@test> rename txx to SYSaJOURNALa315614;
Table renamed.
SCOTT@test> drop table SYSaJOURNALa315614 purge ;
Table dropped.
--如果写正则表达式还复杂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;
/
5.其它还有什么情况呢?自己也想象不出来.只能等问题出现了.