Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
add comment

3 Answers

up vote 3 down vote accepted

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.

share|improve this answer
    
Thank you for your great answer: But it gives me the following error ORA-01466: unable to read data - table definition has changed –  kupa Sep 8 '11 at 17:21
    
I think it is because of my retention_target parameter value is 3hours and this happened 4hours later :( –  kupa Sep 8 '11 at 17:53
    
Do you know if I can use logminer without setting utl_file_dir parameter?(and of course to understand V$LOGMNR_CONTENTS content) –  kupa Sep 8 '11 at 17:54
    
I think you need to be logged in as sysdba or have additional privileges granted in order to flashback dba_views. –  Leigh Riffel Sep 8 '11 at 20:35
    
@Leigh - I tested on 11.2 and granting and revoking FLASHBACK ANY TABLE to SCOTT allowed the flashback to work (or fail with an ORA-01031: insufficient privileges error) –  Justin Cave Sep 8 '11 at 20:37
show 2 more comments

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)

share|improve this answer
    
This operation doesn't show me SQL_UNDO(just SQL_REDO is shown) I don't know why yet... but by this content I can identify n-1 DDL and return my view to this state.. But this is not what I wanted... I want Oracle to show me exact SQL_UNDO –  kupa Sep 8 '11 at 21:54
add comment

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

share|improve this answer
    
Thank you :)... Can you give me any example which will not use that parameter utl_file_dir? –  kupa Sep 8 '11 at 16:48
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.