ITPub博客

首页 > 数据库 > Oracle > [20150918]禁止用户truncate以及drop表.txt

[20150918]禁止用户truncate以及drop表.txt

原创 Oracle 作者:lfree 时间:2015-09-18 11:34:58 1 删除 编辑

  [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.其它还有什么情况呢?自己也想象不出来.只能等问题出现了.





来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-1802941/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2471
  • 访问量
    6279425