Undo DDL statement on View in Oracle

Question

I Want to undo DDL changes on View, how to do that?

I have not set utl_file_dir parameter and can't restart the database to set it. So log miner can not be used yes? it needs this parameter... If there is any chance to use logminer without setting this parameter(without restarting database) ?

Waiting you suggestions, thank you

Answer 1

Depending on the version of Oracle and whether you have the appropriate privileges (FLASHBACK ANY TABLE) and if the change was relatively recent,

SELECT text
  FROM dba_views AS OF TIMESTAMP( systimestamp - interval '1' hour )
 WHERE owner = <<owner of view>>
   AND view_name = <<name of view>>

will give you the text of the view definition as of an hour ago. You can obviously adjust the flashback time to a timestamp just before the DDL you want to undo.

Answer 2

Here is my answer:

BEGIN
   DBMS_LOGMNR.ADD_LOGFILE( logfilename=> '/u2/oradata/ORCL/arch/1_2256_654825874.dbf',
                            options=> dbms_logmnr.NEW);
END;
-------------------------------------------------------------------
BEGIN
   DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG +   DBMS_LOGMNR.COMMITTED_DATA_ONLY + DBMS_LOGMNR.PRINT_PRETTY_SQL);
END;
-------------------------------------------------------------------
SELECT *
FROM v$logmnr_contents a
WHERE seg_name LIKE '%V_NAME%' 
-------------------------------------------------------------------
BEGIN  
   DBMS_LOGMNR.END_LOGMNR; 
END;

This doesn't use utl_file_dir parameter. (happy)

Answer 3

If you do not have source control on the DDL statement that created the view, or a development or production database which mirrors the database with the issue then you don't have many options. The logminer can be started using a dictionary file, redo logs or the online dictionary.

Have you started the logminer before the original view was created?

try select * from V$LOGMNR_CONTENTS;

to see if it was running