上午同事说,他在用PL/SQL比对功能的时候,不小心更新了其中一个用户的表。
问怎么知道那个表最后被更新的时间,及如何回滚。
-- 先看看oracle的版本
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
NLSRTL Version 10.2.0.4.0 - Production
Oracle 10.2.0.4.0 中可以方便的用flashback_transaction_query解决这个问题
SQL> select to_char(f.start_timestamp, 'yyyy-mm-dd hh24:mi:ss') start_timestamp,
2 to_char(f.commit_timestamp, 'yyyy-mm-dd hh24:mi:ss') commit_timestamp,
3 f.logon_user,
4 f.table_name,
5 f.undo_sql
6 from flashback_transaction_query f
7 WHERE TABLE_NAME = 'PRPDUSER_T'
8 and f.table_name is not null;
START_TIMESTAMP COMMIT_TIMESTAMP LOGON_USER TABLE_NAME UNDO_SQL
----------------------- ------------------------- ----------------- ------------ ------------------------------------------------
2011-03-29 11:02:24 2011-03-29 11:02:24 DEV_TEST PRPDUSER_T update "DEV_TEST"."PRPDUSER_T" set
2011-03-29 11:02:24 2011-03-29 11:02:24 DEV_TEST PRPDUSER_T update "DEV_TEST"."PRPDUSER_T" set
可以利用UNDO_SQL方便的进行回滚操作
文档中记录了flashback_transaction_query各列的详细说明
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_4186.htm#REFRN23379
如下的SQL能得到表的创建时间,和最后一次DDL操作的时间
SQL> select owner,
2 object_name,
3 to_char(created, 'yyyy-mm-dd hh24:mi:ss') created,
4 to_char(Last_Ddl_Time, 'yyyy-mm-dd hh24:mi:ss') Last_Ddl_Time
5 from dba_objects
6 where object_type = 'TABLE'
7 and object_name = 'PRPDUSER_T';
OWNER OBJECT_NAME CREATED LAST_DDL_TIME
----------- ---------------- ---------------------------- -------------------------
DEV_TEST PRPDUSER_T 2010-01-20 11:30:04 2010-01-20 11:30:04