One may sometimes need to find the ddl which ran on the database at some certain period of time.If auditing is enabled for the you can get the details using various *_audit_* views. In case, its not and the database is in archivelog mode you can try using logminer.Here is an example for it —
From SYS session —
14:57:56 SQL> select supplemental_log_data_min from v$database; SUPPLEME -------- YES 14:58:11 SQL> 14:58:11 SQL> 14:58:11 SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination D:\oracle\archive\catdb Oldest online log sequence 24 Next log sequence to archive 26 Current log sequence 26 14:58:36 SQL> 14:58:36 SQL> 14:58:36 SQL> alter system switch logfile; System altered. 14:58:41 SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination D:\oracle\archive\catdb Oldest online log sequence 25 Next log sequence to archive 27 Current log sequence 27 14:58:43 SQL>
Logged in as ANAND user and did some DDLs
14:58:59 SQL> show user
USER is "ANAND"
14:59:01 SQL>
14:59:01 SQL>
14:59:01 SQL> create table test_log (a number,b number);
Table created.
14:59:15 SQL>
14:59:15 SQL> insert into test_log values (1,1);
1 row created.
14:59:23 SQL> insert into test_log values (1,2);
1 row created.
14:59:25 SQL> insert into test_log values (2,2);
1 row created.
14:59:27 SQL> insert into test_log values (2,1);
1 row created.
14:59:29 SQL>
14:59:30 SQL>
14:59:30 SQL> commit;
Commit complete.
14:59:31 SQL>
14:59:31 SQL> select * from test_log;
A B
---------- ----------
1 1
1 2
2 2
2 1
14:59:36 SQL>
14:59:37 SQL>
14:59:37 SQL>
15:01:35 SQL>
15:01:35 SQL> alter table test_log add (c number);
Table altered.
15:01:49 SQL>
15:01:49 SQL>
15:01:50 SQL> insert into test_log values (1,1,1);
1 row created.
15:01:55 SQL> insert into test_log values (1,1,2);
1 row created.
15:01:58 SQL> insert into test_log values (1,2,2);
1 row created.
15:02:00 SQL> insert into test_log values (2,2,2);
1 row created.
15:02:03 SQL> insert into test_log values (2,1,1);
1 row created.
15:02:08 SQL> commit;
Commit complete.
15:02:10 SQL>
15:02:10 SQL>
15:02:11 SQL>
15:02:11 SQL> select * from test_log;
A B C
---------- ---------- ----------
1 1
1 2
2 2
2 1
1 1 1
1 1 2
1 2 2
2 2 2
2 1 1
9 rows selected.
15:02:16 SQL>
15:12:02 SQL>
15:12:03 SQL>
16:08:39 SQL>
16:08:39 SQL>
16:08:40 SQL>
16:08:40 SQL> truncate table test_log;
Table truncated.
16:08:50 SQL>
16:08:51 SQL>
16:08:51 SQL>
16:08:51 SQL>
16:21:53 SQL>
16:21:53 SQL>
16:21:53 SQL> drop table test_log;
Table dropped.
As sys user
15:13:27 SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination D:\oracle\archive\catdb
Oldest online log sequence 25
Next log sequence to archive 27
Current log sequence 27
16:22:01 SQL>
16:22:01 SQL>
16:22:02 SQL> alter system switch logfile;
System altered.
16:22:06 SQL> alter system switch logfile;
System altered.
16:22:27 SQL>
So archive log 27 has the details
16:23:28 SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';16:31:35
SQL> EXECUTE dbms_logmnr.add_logfile(LogFileName=>'D:\oracle\archive\catdb\ARC0000000027_0785160155.0001',OPTIONS => DBMS_LOGMNR.NEW);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.29
16:31:48 SQL> EXECUTE dbms_logmnr.add_logfile(LogFileName=>'D:\oracle\archive\catdb\ARC0000000028_0785160155.0001',OPTIONS => DBMS_LOGMNR.ADDFILE);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
16:33:21 SQL> select filename,low_time,high_time,DICTIONARY_BEGIN,DICTIONARY_END from v$LOGMNR_LOGS;
FILENAME LOW_TIME HIGH_TIME DIC DIC
---------------------------------------------------------------------- -------------------- -------------------- --- ---
D:\oracle\archive\catdb\ARC0000000027_0785160155.0001 12-JUN-2012 14:58:41 12-JUN-2012 16:22:06 NO NO
D:\oracle\archive\catdb\ARC0000000028_0785160155.0001 12-JUN-2012 16:22:06 12-JUN-2012 16:22:27 NO NO
16:35:01 SQL> EXECUTE dbms_logmnr.start_logmnr(OPTIONS =>DBMS_LOGMNR.DDL_DICT_TRACKING +DBMS_LOGMNR.PRINT_PRETTY_SQL);
PL/SQL procedure successfully completed.
16:41:44 SQL> select username,OPERATION,TIMESTAMP,TABLE_NAME,SESSION#,SERIAL#,SQL_REDO from V$LOGMNR_CONTENTS where SEG_OWNER='ANAND';
USERNAME OPERATION TIMESTAMP TABLE_NAME SESSION# SERIAL# SQL_REDO
---------- -------------------------------- -------------------- -------------------------------- ---------- ---------- ------------------------------------------------------------
ANAND DDL 12-JUN-2012 14:59:14 TEST_LOG 191 95 create table test_log (a number,b number);
ANAND DDL 12-JUN-2012 15:01:49 TEST_LOG 191 95 alter table test_log add (c number);
ANAND DDL 12-JUN-2012 16:08:50 TEST_LOG 191 95 truncate table test_log;
ANAND DDL 12-JUN-2012 16:21:58 TEST_LOG 191 95 ALTER TABLE "ANAND"."TEST_LOG" RENAME TO "BIN$R2yT2OHzRhGspu
5YAsnXPw==$0" ;
ANAND DDL 12-JUN-2012 16:21:58 TEST_LOG 191 95 drop table test_log AS "BIN$R2yT2OHzRhGspu5YAsnXPw==$0" ;
If supplemental logging is not enabled, some columns of the view would have UNKNOWN value.It could be difficult in that case to identify the user and session information details.