[oracle@psd backup]$ cd /oracle/fast_recovery_area/PSD/archivelog/
[oracle@psd archivelog]$ cd 2015_03_17/
[oracle@psd 2015_03_17]$ ll
total 10248
-rw-r-----. 1 oracle oinstall 7795200 Mar 17 11:41 o1_mf_1_16_bjh8mbov_.arc
-rw-r-----. 1 oracle oinstall 2048 Mar 17 11:43 o1_mf_1_17_bjh8qp80_.arc
-rw-r-----. 1 oracle oinstall 2674688 Mar 17 12:03 o1_mf_1_18_bjh9wyqj_.arc
-rw-r-----. 1 oracle oinstall 1024 Mar 17 12:03 o1_mf_1_19_bjh9x0hy_.arc
-rw-r-----. 1 oracle oinstall 9728 Mar 17 12:03 o1_mf_1_20_bjh9x3xb_.arc
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/oracle/fast_recovery_area/PSD/archivelog/2015_03_17/o1_mf_1_16_bjh8mbov_.arc',OPTIONS => DBMS_LOGMNR.NEW);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/oracle/fast_recovery_area/PSD/archivelog/2015_03_17/o1_mf_1_17_bjh8qp80_.arc',OPTIONS => DBMS_LOGMNR.ADDFILE);
PL/SQL procedure successfully completed.
SQL>
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/oracle/fast_recovery_area/PSD/archivelog/2015_03_17/o1_mf_1_18_bjh9wyqj_.arc',OPTIONS => DBMS_LOGMNR.ADDFILE);
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
PL/SQL procedure successfully completed.
SQL> col sql_redo format a50
SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
SQL> select sql_redo,scn,start_scn,TIMESTAMP,START_TIMESTAMP from V$LOGMNR_CONTENTS where lower(sql_redo) like \'%drop%\' and lower(sql_redo) like \'%scott%\';
SQL_REDO SCN START_SCN TIMESTAMP START_TIMESTAMP
------------------------------------------------------------ ---------- ---------- ------------------- -------------------
drop table scott.emp1 AS "BIN$EWlpmtpQdoXgU5F3qMAiMw==$0" ;1272611 2015-03-16 22:10:29
drop table "SCOTT"."BIN$EWNzFGZja8TgU5F3qMDpoQ==$0" purge;1278724 2015-03-17 11:46:13
drop table "SCOTT"."BIN$EWOQLk80bF3gU5F3qMBi4g==$0" purge;1278746 2015-03-17 11:46:14
drop table "SCOTT"."BIN$EWNb3bwua9TgU5F3qMA7BA==$0" purge;1278770 2015-03-17 11:46:14
drop table "SCOTT"."BIN$EWNb3bwva9TgU5F3qMA7BA==$0" purge;1278791 2015-03-17 11:46:15
drop table "SCOTT"."BIN$EWNzFGZka8TgU5F3qMDpoQ==$0" purge;1278813 2015-03-17 11:46:15
drop table "SCOTT"."BIN$EWOU7d6fbGPgU5F3qMA5vw==$0" purge;1278834 2015-03-17 11:46:15
drop table "SCOTT"."BIN$EWOQLk81bF3gU5F3qMBi4g==$0" purge;1278856 2015-03-17 11:46:15
drop table "SCOTT"."BIN$EWOniXMLbHvgU5F3qMAS1g==$0" purge;1278878 2015-03-17 11:46:15
drop table "SCOTT"."BIN$EWOniXMMbHvgU5F3qMAS1g==$0" purge;1278900 2015-03-17 11:46:15
drop table "SCOTT"."BIN$EWOniXMNbHvgU5F3qMAS1g==$0" purge;1278921 2015-03-17 11:46:15
SQL_REDO SCN START_SCN TIMESTAMP START_TIMESTAMP
------------------------------------------------------------ ---------- ---------- ------------------- -------------------
drop table "SCOTT"."BIN$EWOniXMObHvgU5F3qMAS1g==$0" purge;1278943 2015-03-17 11:46:15
drop table "SCOTT"."BIN$EWPg+H8lbNLgU5F3qMCj4g==$0" purge;1278965 2015-03-17 11:46:15
drop table "SCOTT"."BIN$EWPg+H8mbNLgU5F3qMCj4g==$0" purge;1278986 2015-03-17 11:46:15
drop table "SCOTT"."BIN$EWPg+H8nbNLgU5F3qMCj4g==$0" purge;1279008 2015-03-17 11:46:16
drop table "SCOTT"."BIN$EWTPDfF1bmPgU5F3qMBohA==$0" purge;1279030 2015-03-17 11:46:16
drop table "SCOTT"."BIN$EWTPDfF2bmPgU5F3qMBohA==$0" purge;1279052 2015-03-17 11:46:16
drop table "SCOTT"."BIN$EWTPDfF3bmPgU5F3qMBohA==$0" purge;1279074 2015-03-17 11:46:16
drop table "SCOTT"."BIN$EWTSV1AibnLgU5F3qMCKBg==$0" purge;1279096 2015-03-17 11:46:16
drop table "SCOTT"."BIN$EWTaumLtbn7gU5F3qMAgNQ==$0" purge;1279118 2015-03-17 11:46:16
drop table "SCOTT"."BIN$EWTdffDnboXgU5F3qMCDZg==$0" purge;1279140 2015-03-17 11:46:16
drop table "SCOTT"."BIN$EWTdffDoboXgU5F3qMCDZg==$0" purge;1279162 2015-03-17 11:46:16
SQL_REDO SCN START_SCN TIMESTAMP START_TIMESTAMP
------------------------------------------------------------ ---------- ---------- ------------------- -------------------
drop table "SCOTT"."BIN$EWTdffDpboXgU5F3qMCDZg==$0" purge;1279184 2015-03-17 11:46:16
drop table "SCOTT"."BIN$EWVlDuUgb2jgU5F3qMAyEQ==$0" purge;1279206 2015-03-17 11:46:16
drop table "SCOTT"."BIN$EWi11zOecYPgU5F3qMAYuA==$0" purge;1279228 2015-03-17 11:46:17
drop table "SCOTT"."BIN$EWjjuPdrc7fgU5F3qMD+ag==$0" purge;1279250 2015-03-17 11:46:17
drop table "SCOTT"."BIN$EWjqnThDc77gU5F3qMC90A==$0" purge;1279272 2015-03-17 11:46:17
drop table "SCOTT"."BIN$EWjqnThEc77gU5F3qMC90A==$0" purge;1279294 2015-03-17 11:46:17
drop table "SCOTT"."BIN$EWlYdSbqdmHgU5F3qMBLJg==$0" purge;1279314 2015-03-17 11:46:17
drop table "SCOTT"."BIN$EWlpmtpQdoXgU5F3qMAiMw==$0" purge;1279336 2015-03-17 11:46:17
drop table "SCOTT"."T2" cascade constraints purge force;1279359 2015-03-17 11:46:22
drop table "SCOTT"."T1" cascade constraints purge force;1279379 2015-03-17 11:46:22
drop table "SCOTT"."DEPT1" cascade constraints purge force;1279401 2015-03-17 11:46:22
SQL_REDO SCN START_SCN TIMESTAMP START_TIMESTAMP
------------------------------------------------------------ ---------- ---------- ------------------- -------------------
drop table "SCOTT"."SALGRADE" cascade constraints purge forc1279424 2015-03-17 11:46:22
e;
drop table "SCOTT"."BONUS" cascade constraints purge force;1279447 2015-03-17 11:46:22
drop table "SCOTT"."EMP" cascade constraints purge force;1279459 2015-03-17 11:46:23
drop table "SCOTT"."DEPT" cascade constraints purge force;1279497 2015-03-17 11:46:23
drop procedure "SCOTT"."P_EMP1";1279533 2015-03-17 11:46:23
drop procedure "SCOTT"."P_INSERT";1279555 2015-03-17 11:46:24
drop procedure "SCOTT"."P_INSERT_T";1279573 2015-03-17 11:46:24
drop user scott cascade;1279609 2015-03-17 11:46:40
41 rows selected.
SQL>