mysql 表结构变化_利用AUDIT查看数据库表结构变化
一朋友问我,想查看一下数据库中,表里边一个字段哪天修改字段长度了,我想到了用数据库审计来实现,做个实验,来看下。朋友那边数据库版本是ORACLE10g,10g版本默认没开启审计功能,需要开启审计。C:\Documents and Settings\Administrator>sqlplus / as sysdbaSQL*Plus: Release 10.2.0.3.0 - Productio
一朋友问我,想查看一下数据库中,表里边一个字段哪天修改字段长度了,我想到了用数据库审计来实现,做个实验,来看下。
朋友那边数据库版本是ORACLE10g,10g版本默认没开启审计功能,需要开启审计。
C:\Documents and Settings\Administrator>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on 星期一 12月 30 09:57:39 2013
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> show parameter audit
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
audit_file_dest string
D:\ORACLE\PRODUCT\10.2.0\ADMIN
\YING8\ADUMP
audit_sys_operations boolean
FALSE
audit_trail string
NONE
SQL> alter system set audit_trail=DB_EXTENDED ;
alter system set audit_trail=DB_EXTENDED
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
SQL> alter system set audit_trail=DB_EXTENDED scope=spfile;--静态参数,这里的DB_EXTENDED具有捕获SQL的功能。
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 583008256 bytes
Fixed Size 1291868 bytes
Variable Size 213911972 bytes
Database Buffers 360710144 bytes
Redo Buffers 7094272 bytes
Database mounted.
Database opened.
SQL> show parameter audit
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
audit_file_dest string
D:\ORACLE\PRODUCT\10.2.0\ADMIN
\YING8\ADUMP
audit_sys_operations boolean
FALSE
audit_trail string
DB_EXTENDED
SQL> conn wzz/wzz
Connected.
SQL> desc wh2
ERROR:
ORA-04043: object wh2 does not exist
SQL> create table wh2 (id int);
Table created.
SQL> audit alter on wh2 by access;
Audit succeeded.
SQL> select * from dba_audit_trail;
no rows selected
SQL> alter table wh2 add name varchar2(20);
Table altered.
SQL> select timestamp,owner,obj_name,action,action_name,sql_text from dba_audit_trail;
TIMESTAMP OWNER OBJ_N ACTION ACTION_NAME SQL_TEXT
---------------- ----- ----- ---------- -------------------------------------------------------- ------------------------------
30-12月-2013 WZZ WH2 15 ALTER TABLE alter table wh2 add name varch
ar2(20)
更多推荐
所有评论(0)