ANBOB™

如果有人truncate了你的表，你能揪出是谁么？会记录redo日志么？下现做一个实验，用事实说话

SQL> select * from v$version;

BANNER
——————————————————————————————————————————–
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Prod
PL/SQL Release 10.2.0.1.0 – Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 – Production
NLSRTL Version 10.2.0.1.0 – Production

SQL> show user;
USER is “ANBOB”
SQL> create table test_trun(id int);

Table created.

SQL> begin
2  for i in 1..100 loop
3  insert into test_trun values(i);
4  end loop;
5  end;
6  /

PL/SQL procedure successfully completed.

SQL> select count(*) from test_trun;

COUNT(*)
———-
100

SQL> commit;

Commit complete.

SQL> truncate table test_trun;

Table truncated.

SQL> select count(*) from test_trun;

COUNT(*)
———-
0

SQL> select * from v$log;
select * from v$log
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> conn system/oracle
Connected.
SQL> select * from v$log;

GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIV STATUS                           FIRST_CHANGE# FIRST_TIME
———- ———- ———- ———- ———- —— ——————————– ————- ————–
1          1          5   52428800          2 YES    INACTIVE                              45730371 03-5月 -11
2          1          6   52428800          2 NO     CURRENT                               45754111 04-5月 -11
3          1          3   52428800          2 YES    INACTIVE                              45703339 03-5月 -11
5          1          4   52428800          2 YES    INACTIVE                              45706825 03-5月 -11

SQL> col member for a80
SQL> run
1* select group#,member from v$logfile

GROUP# MEMBER
———- ——————————————————————————–
5 /u01/app/oracle/oradata/ORCL/onlinelog/redo5.log
5 /u01/app/oracle/flash_recovery_area/ORCL/onlinelog/redo5.log
3 /u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_3_5y4dgorl_.log
3 /u01/app/oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_3_5y4dgq04_.log
2 /u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_5y4dgmch_.log
2 /u01/app/oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_2_5y4dgnkh_.log
1 /u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_1_5y4dgjvk_.log
1 /u01/app/oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_1_5y4dgl2s_.log

8 rows selected.

SQL> execute dbms_logmnr.add_logfile(logfilename=>’/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_5y4dgmch_.log’,options=>dbms_logmnr.new);
BEGIN dbms_logmnr.add_logfile(logfilename=>’/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_5y4dgmch_.log’,options=>dbms_logmnr.new); END;

*
ERROR at line 1:
ORA-06550: line 1, column 116:
PLS-00201: identifier ‘DBMS_LOGMNR.NEW’ must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

SQL> conn / as sysdba
Connected.
SQL> execute dbms_logmnr.add_logfile(logfilename=>’/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_5y4dgmch_.log’,options=>dbms_logmnr.new);

PL/SQL procedure successfully completed.

SQL> execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);

PL/SQL procedure successfully completed.

SQL> select count(*) from v$logmnr_contents;

COUNT(*)
———-
73570

SQL> col seg_name for a10
SQL> col username for a10
SQL> col sql_redo for a80
SQL> run
1* select seg_name,username,sql_redo from v$logmnr_contents where seg_owner=’ANBOB’

SEG_NAME   USERNAME   SQL_REDO
———- ———- ——————————————————————————–
BIN$oluqk1 SYS        drop table “ANBOB”.”BIN$oluqk1zthjzgQAB/AQBaPw==$0″ purge;
zthjzgQAB/
AQBaPw==$0

BIN$ol0mwU SYS        drop table “ANBOB”.”BIN$ol0mwU7R+j/gQAB/AQBasw==$0″ purge;
7R+j/gQAB/
AQBasw==$0

T                     create table t (id int) tablespace users;
T                     ALTER TABLE “ANBOB”.”T” RENAME TO “BIN$om+fx5wJnKngQAB/AQBwSQ==$0” ;
T                     drop table t AS “BIN$om+fx5wJnKngQAB/AQBwSQ==$0” ;

SEG_NAME   USERNAME   SQL_REDO
———- ———- ——————————————————————————–
BIN$om+fx5 ANBOB      drop table “ANBOB”.”BIN$om+fx5wJnKngQAB/AQBwSQ==$0″ purge;
wJnKngQAB/
AQBwSQ==$0

TEST_TRUN             create table test_trun(id int);
TEST_TRUN             insert into “ANBOB”.”TEST_TRUN”(“ID”) values (’87’);
TEST_TRUN             insert into “ANBOB”.”TEST_TRUN”(“ID”) values (’88’);
TEST_TRUN             insert into “ANBOB”.”TEST_TRUN”(“ID”) values (’89’);
TEST_TRUN             insert into “ANBOB”.”TEST_TRUN”(“ID”) values (’90’);
TEST_TRUN             insert into “ANBOB”.”TEST_TRUN”(“ID”) values (’91’);
TEST_TRUN             insert into “ANBOB”.”TEST_TRUN”(“ID”) values (’92’);

SEG_NAME   USERNAME   SQL_REDO
———- ———- ——————————————————————————–
TEST_TRUN             insert into “ANBOB”.”TEST_TRUN”(“ID”) values (’93’);
TEST_TRUN             insert into “ANBOB”.”TEST_TRUN”(“ID”) values (’94’);
TEST_TRUN             insert into “ANBOB”.”TEST_TRUN”(“ID”) values (’95’);
TEST_TRUN             insert into “ANBOB”.”TEST_TRUN”(“ID”) values (’96’);
TEST_TRUN             insert into “ANBOB”.”TEST_TRUN”(“ID”) values (’97’);
TEST_TRUN             insert into “ANBOB”.”TEST_TRUN”(“ID”) values (’98’);
TEST_TRUN             insert into “ANBOB”.”TEST_TRUN”(“ID”) values (’99’);
TEST_TRUN             insert into “ANBOB”.”TEST_TRUN”(“ID”) values (‘100’);
TEST_TRUN  ANBOB      truncate table test_trun;

22 rows selected.

打赏

微信扫一扫，打赏作者吧～