oracle 如何查询表的修改历史记录，没有创建触发器，也没有日志操作表，这个表已经修改了

发布网友发布时间：2022-05-02 11:28

共3个回答

懂视网时间：2022-05-02 15:49

 select uat.table_name as 表名,(select last_ddl_time from user_objects
　　　　　　　　　　　　　　　　　　 where object_name = uat.table_name and uat.tablespace_name=‘JMY‘
　　　　　　　　　　　　　　　　　　) as last_ddl_time 
 from user_all_tables uat ORDER By last_ddl_time desc

通过该语句，可以得到所有表的最后修改时间。（大家可以根据实际情况在该SQL后面加上相应的条件表达式）

通过对查询结果中最后修改时间的降序排列，就可以知道那些表的结构修改过了。

Oracle中查看最近被修改过的表的方法

标签：rac 条件 blog 情况 sele space desc highlight 表名

热心网友时间：2022-05-02 12:57

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

-- create table
CREATE table ddl_event (
timestamp date,
user_name varchar2(30),
os_user varchar2(30),
machine varchar2(20),
ip_addr varchar2(20),
program VARCHAR2(30),
event varchar2(20),
Object_name varchar2(30),
object_type varchar2(30),
object_owner varchar2(30),
statement varchar2(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_osuser varchar2(30);
v_machine varchar2(20);
v_ip_addr varchar2(20);
v_program VARCHAR2(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 al;
-- 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 procere, 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

热心网友时间：2022-05-02 14:15

查归档日志追问怎么查归档日志？？？？？详细的sql？