当前位置：首页 > Oracle > 正文内容

Oracle中使用系统触发器记录表结构的修改操作

管理员2年前 (2018-09-26)Oracle650

[TOC]

在运行的某个业务系统中，发现某张表多出来了一些字段，所有权限的用户都没有对其进行操作，怀疑是有一些后台服务做了这个操作，遂查询一下根源，来确定这些修改是实现哪些功能的。
在实际工作中，我们记录对某个表数据的操作，一般会使用触发器和日志表相配合来完成，那么DML的操作可以使用触发器，DDL是否也可以呢，答案是肯定的。
## 1、问题引入
------------
我们先看一个针对 DML操作的简单操作片断，BEFORE INSERT是表明在INSERT操作时这个触发器的文本才会执行，ON TEST 表明是应用于TEST表。

```
create or replace trigger tri_test_insert
  before insert on test
  for each row
declare
```
## 2、实现过程
------------
ORACLE除了针对于DML的触发器以外，还有专门针对DDL操作的触发器，比如对于CREATE、DROP、ALTER 的操作，下面的示例将是一个记录对所有对象（表、视图、过程、函数等等）的增加、删除、修改的过程。
首先创建一个日志表，和填充日志表的一个序列，表结构的说明如下：username用于记录是当前的用户名，event用于记录操作的类型，是CREATE还是DROP，object\_name记录一下被操作的对象名称，ddl\_sql用于记录当时执行的实际SQL，created 用于记录操作日期和时间。

```
SQL> create sequence seq_ddl_logger;
Sequence created
SQL> create table ddl_logger(
  2      id int,
  3      username varchar(30),
  4      event varchar(30),
  5      object_name varchar(30),
  6      ddl_sql varchar(4000),
  7      crdate date default sysdate
  8  );
Table created
```
下面将是触发器的文本，before ddl，表明记录所有的DDL操作，on schema表明只在当前用户下的操作才能生效，ora_login_user是登录的用户名，ora_sysevent是具体的操作，ora_dict_obj_name是对象名称，ora_name_list_t中存储的是具体的SQL，这4个属性，都是系统提供的，我们可以直接使用。

```
create or replace trigger tri_schema_ddl
  before ddl on schema
declare
  l_text   varchar(300);
  sql_text ora_name_list_t;
  i        number;
begin
  i      := sql_txt(sql_text);
  l_text := sql_text(1);

insert into ddl_logger
    (id, username, event, object_name, ddl_sql)
  values
    (seq_ddl_logger.nextval,
     ora_login_user,
     ora_sysevent,
     ora_dict_obj_name,
     l_text);
end;
```

到此为止，所有的准备工作都已完成，下在可以做一个简单的测试，新建表、增加字段、增加索引、增加主键约束、删除表等几个操作。

```
SQL>
SQL> create table test(id int, name varchar(10));
Table created
SQL> alter table test add remark varchar(10);
Table altered
SQL> create index ix_test_name on test(name);
Index created
SQL> alter table test add primary key (id);
Table altered
SQL> drop table test purge;
Table dropped
SQL>
```
## 3、查看结果
------------
此时，我们可以查看一下日志的记录情况，基本上可以从这个结果中，提取出最开始需求的内容。

```
SQL> select id, event, object_name, ddl_sql from ddl_logger;
I EVENT      OBJECT_NAME    DDL_SQL
- ---------- -------------- ---------------------------------------------------------
1 CREATE     TEST           create table test(id int, name varchar(10))
2 ALTER      TEST           alter table test add remark varchar(10)
3 CREATE     IX_TEST_NAME   create index ix_test_name on test(name)
4 ALTER      TEST           alter table test add primary key (id)
5 CREATE     SYS_C0015235   CREATE UNIQUE INDEX "RDM"."SYS_C0015235" on "RDM"."TEST"("ID")
6 DROP       TEST           drop table test purge
6 rows selected

SQL>
```

## 4、注意事项

不能记录其他的用户登录以后（比如 sys或system），对本账户表结构的调整，如想要这些内容，可以把 on schema修改为on database或on instance，当然这需要更大的权限。
在本例中，记录的事件范围是 ON DDL，如果不想记录太多的记录，可以调整为ON ALTER，则不会记录 CREATE和DROP这些操作。
当该触发器牌无效状态时，所有的DDL操作都会报错，所在结束记录这些操作之前，应该先删除该触发器，再删除logger表，以防止对其他的会话产生影响。
如果在本例中的记录的SQL不完整，可以把sql_text这个数据组里的其他数据也取出来，拼接在一起。

打赏

支付宝打赏

微信打赏

标签: 触发器

返回列表

上一篇：CentOS 7 安装Oracle11g记录

没有最新的文章了...

Oracle 降低高水位的方法

在很多系统运行多年以后，会把历史业务数据归档的需求，常见于把大量历史数据拆分到另一张新表中，并在原表中删除，实际上，业务数据被迁移到新表之后，原来的表所占的空间并不会主动释放，这时就需要人为的干预，这里提供3种方法，各有利弊。移动表用move 操作表实际上执行的 ctas 操作。 alte...

在Oracle中查询视图参数化的实现方法

有这样的一个需求，在查询视时，能否和执行存储过程一样，传入一些参数，根据不同的参数，返回出不同的结果集，我们知道，视图并没有这样的功能，也不存在这样的入口，这个问题的解决思路有两个，一是使用管道函数直接返回结果集，二是在查询视图之前，把参数存储在其他位置（内存或存储），相当于会话级变量，视图的查询语...

评论列表

访客

2年前 (2018-11-14)

对用户的访问限制，可以设置吗？

回复该评论

卖水果的net

Oracle中使用系统触发器记录表结构的修改操作

相关文章

Oracle 降低高水位的方法

在Oracle中查询视图参数化的实现方法

评论列表

发表评论

@2020 www.msgde.net 冀ICP备20003249号

冀公网安备 13060602001226号

Powered By Z-BlogPHP. Theme by TOYEAN.

卖水果的net

Oracle中使用系统触发器记录表结构的修改操作

相关文章

Oracle 降低高水位的方法

在Oracle中查询视图参数化的实现方法

评论列表

发表评论取消回复

@2020 www.msgde.net 冀ICP备20003249号冀公网安备 13060602001226号

Powered By Z-BlogPHP. Theme by TOYEAN.

发表评论

@2020 www.msgde.net 冀ICP备20003249号

冀公网安备 13060602001226号