如何查看数据库数据表的修改记录 oracle

如何查看数据库数据表的修改记录 oracle

1. 用 sys 用户登录 Oracle

2. 创建记录用户登录信息的表

CREATE TABLE LOG$INFORMATION

(

ID NUMBER(10),

USERNAME VARCHAR2(30),

LOGINTIME DATE,

TERMINAL VARCHAR2(50),

IPADRESS VARCHAR2(20),

OSUSER VARCHAR2(30),

MACHINE VARCHAR2(64),

PROGRAM VARCHAR2(64),

SID NUMBER,

SERIAL# NUMBER,

1、打开PL/SQL Developer,输入正常的用户名密码后，选择数据库，然后正常连接。如下图。

2、正常登录后，在PL/SQL Developer中打开一个sql执行窗口， *** 作如下图1中标红的位置，选择"SQL Window"。

3、在新出现的空白窗口中输入正确的sql语句，如下图。

2、在出现数据结果集后，点击上图2中标红的按钮，出现新的对话框。这是要将数据结果集导出成各个不同的格式。

3、选择第一个"CSV file"，这样导出的就是excel格式的数据集。

4、打开指定的文件夹，就可以看到刚刚导出的文件，双击该文件就可以正常打开，结果和数据库中查询完出来的结果是一致的。

给你一个，你可以稍微修改一下

-- create table

CREATE table ddl_event (

timestamp date,

user_name varchar2(30),

os_uservarchar2(30),

machinevarchar2(20),

ip_addrvarchar2(20),

programVARCHAR2(30),

event varchar2(20),

Object_name varchar2(30),

object_type varchar2(30),

object_owner varchar2(30),

statementvarchar2(256) )

/

-- who changed what and when and how

create or replace trigger ddl_watcher

after ddl on database

when (user not in ('SYS', 'SYSTEM'))

declare

v_osuservarchar2(30)

v_machinevarchar2(20)

v_ip_addrvarchar2(20)

v_programVARCHAR2(30)

event varchar2(30)

obj_name varchar2(30)

obj_type varchar2(30)

obj_owner varchar2(30)

sql_text ora_name_list_t

stmt VARCHAR2(256)

n number

begin

select osuser,

machine,

nvl(program, 'sqlplus'),

sys_context('userenv','ip_address')

into

v_osuser,

v_machine,

v_program,

v_ip_addr

from v$session

where audsid = userenv('sessionid')

-- select sys_context('userenv','ip_address') into v_ip_addr from dual

-- v_ip_addr := ora_client_ip_address

event := ora_sysevent

obj_name := ora_dict_obj_name

obj_type := ora_dict_obj_type

obj_owner := ora_dict_obj_owner

n := ora_sql_txt(sql_text)

if n >256 then

n:= 256

end if

FOR i IN 1..n LOOP

stmt := stmt || sql_text(i)

END LOOP

insert into ddl_event (timestamp, user_name, os_user, machine, ip_addr,

program, event, object_name, object_type, object_owner, statement)

values (sysdate, user, v_osuser, v_machine, v_ip_addr, v_program,

event, obj_name, obj_type, obj_owner, stmt)

end

/

DDL trigger

Quite often, DBAs need to know what DDL operations the users have done in a test environment. Here is the way I can know what they did.

This table and the trigger (you may name them the way you like) should be in SYS, SYSTEM or an account with DBA role. It will store who did what, when and from where (machine and IP), by what method (sqlplus, toad, sql worksheet etc).

There is a limitation: when a DDL operation has more than 2000 characters, it won't go through. For example, when a user is trying to create a new or modify an existing stored procedure, s/he may be in trouble if the code has more than 2000 characters.

I have caught follwoing DDLs:

alter,

analyze,

comment,

create,

drop,

grant,

revoke,

truncate

---