RE: Re: Rollback !

Oracle definitely *does* generate redo and undo for DDL. The reason you can't roll back a DDL statement is that each DDL "statement" is actually a macro for a sequence of DML statements on the data dictionary; that sequence of DML statements ends in a COMMIT. You cannot roll back a committed transaction.

SQL Trace a DDL statement like DROP TABLE. You'll see 40+ recursive SQL statements on the dictionary followed by a commit.

You probably could recover a DROP TABLE with log miner if you really had to, but there are better tools to help you do it. You might consider the "tablespace point-in-time recovery" feature, for example.

Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com

Upcoming events:
- Hotsos Clinic, Oct 1-3 San Francisco, Oct 15-17 Dallas, Dec 9-11 Honolulu
- 2003 Hotsos Symposium on Oracle® System Performance, Feb 9-12 Dallas - Next event: Miracle Database Forum, Sep 20-22 Middelfart Denmark

-----Original Message-----
WILLIAMS
Sent: Wednesday, August 28, 2002 9:49 AM To: Multiple recipients of list ORACLE-L

Alexandre - Thanks for posting the link. I read the document. It describes
how to track the DDL statements. Unless I overlooked something, it doesn't
say you can UNDO DDL. I think the reason was explained earlier on this post,
that Oracle doesn't write any redo records. For example, in the case of a
DROP TABLE statement, in order to undo that statement, Oracle would have to
write the equivalent of a DELETE statement for each row. This could be enormous if the table was large, and take a long time. Then we DBAs would be
clamoring for a parameter that would bypass all that. I could be wrong, which is why I respond to issues like this.

Dennis Williams
DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----Original Message-----
Sent: Wednesday, August 28, 2002 6:13 AM To: Multiple recipients of list ORACLE-L

For 9i:
http://otn.oracle.com/docs/products/oracle9i/doc_library/901_doc/server. 901/
a90117/logminer.htm#18681
For 8i not sure. Has anyone tried DROP undo in 8i? (8i docs are very poor on
LogMiner)

Alexandre
----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Wednesday, August 28, 2002 11:48 AM

Alexandre Gorbatchev,

     As far as i know, you cannot recover from drop using logmnr.It is internal dml to data dictionary, maybe in 9i it will work, i am not sure.But
in 8i, it won't work.

Regards
zhu chao
Eachnet DBA
86-21-32174588-667
chao_ping_at_vip.163.com

• 2002-08-28 00:13:00 ,you wrote£º=======

>> 1.) Can we rollback a drop statement ?
>No. However, you can use Log Miner.
>
>Alexandre
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Alexandre Gorbatchev
> INET: alexandre.gorbatchev_at_avermann.de
>
>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>San Diego, California -- Public Internet access / Mailing Lists
>--------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).

• = = = = = = = = = = = = = = = = = = =

¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡zhu chao
¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡chaospku_at_163.net
¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡2002-08-28

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: zhu chao
  INET: chaospku_at_163.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Alexandre Gorbatchev
  INET: alexandre.gorbatchev_at_avermann.de

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: DENNIS WILLIAMS
  INET: DWILLIAMS_at_LIFETOUCH.COM

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Cary Millsap
  INET: cary.millsap_at_hotsos.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).