One option would be to use a "Flashback database" to rewind the database to its previous state (including all ddl's and dml's).

But ensure that you have proper privileges to do that.

Let's say that i am production DBA doing out a deployment in the database system, either at the mid of deployment or at the end , business call me to revoke the entire DB changes ( aka - rollback the entire process) - this included undo ddls, dmls and procedure compilation etc,etc. All these can be accomplished with a simple FLASHBACK DATABASE command.

Here is a quick demo of it.

rajesh@ORA12C> conn demo/demo@ora12c
Connected.
demo@ORA12C>
demo@ORA12C> create or replace procedure process_data
  2  as
  3  begin
  4     dbms_output.put_line('Version 1.0 !!!');
  5  end;
  6  /

Procedure created.

demo@ORA12C>
demo@ORA12C> exec process_data
Version 1.0 !!!

PL/SQL procedure successfully completed.

demo@ORA12C> set linesize 71
demo@ORA12C> desc emp
 Name                                Null?    Type
 ----------------------------------- -------- -------------------------
 EMPNO                                        NUMBER(4)
 ENAME                                        VARCHAR2(10)
 JOB                                          VARCHAR2(9)
 MGR                                          NUMBER(4)
 HIREDATE                                     DATE
 SAL                                          NUMBER(7,2)
 COMM                                         NUMBER(7,2)
 DEPTNO                                       NUMBER(2)

demo@ORA12C> desc T
ERROR:
ORA-04043: object T does not exist


demo@ORA12C> conn sys/Password-1@ora12c as sysdba
Connected.
sys@ORA12C>
sys@ORA12C> select flashback_on from v$database ;

FLASHBACK_ON
------------------
NO

1 row selected.

sys@ORA12C> alter database flashback on ;

Database altered.

sys@ORA12C> select flashback_on from v$database ;

FLASHBACK_ON
------------------
YES

1 row selected.

sys@ORA12C>
sys@ORA12C> create restore point before_changes;

Restore point created.

sys@ORA12C> conn demo/demo@ora12c
Connected.
demo@ORA12C> create table t as select * from all_objects;

Table created.

demo@ORA12C> create or replace procedure process_data
  2  as
  3  begin
  4     dbms_output.put_line('Version 2.0 !!!');
  5  end;
  6  /

Procedure created.

demo@ORA12C> alter table emp add DOB date;

Table altered.

demo@ORA12C> exec process_data
Version 2.0 !!!

PL/SQL procedure successfully completed.

demo@ORA12C> set linesize 71
demo@ORA12C> desc t
 Name                                Null?    Type
 ----------------------------------- -------- -------------------------
 OWNER                               NOT NULL VARCHAR2(128)
 OBJECT_NAME                         NOT NULL VARCHAR2(128)
 SUBOBJECT_NAME                               VARCHAR2(128)
 OBJECT_ID                           NOT NULL NUMBER
 DATA_OBJECT_ID                               NUMBER
 OBJECT_TYPE                                  VARCHAR2(23)
 CREATED                             NOT NULL DATE
 LAST_DDL_TIME                       NOT NULL DATE
 TIMESTAMP                                    VARCHAR2(19)
 STATUS                                       VARCHAR2(7)
 TEMPORARY                                    VARCHAR2(1)
 GENERATED                                    VARCHAR2(1)
 SECONDARY                                    VARCHAR2(1)
 NAMESPACE                           NOT NULL NUMBER
 EDITION_NAME                                 VARCHAR2(128)
 SHARING                                      VARCHAR2(13)
 EDITIONABLE                                  VARCHAR2(1)
 ORACLE_MAINTAINED                            VARCHAR2(1)

demo@ORA12C> desc emp
 Name                                Null?    Type
 ----------------------------------- -------- -------------------------
 EMPNO                                        NUMBER(4)
 ENAME                                        VARCHAR2(10)
 JOB                                          VARCHAR2(9)
 MGR                                          NUMBER(4)
 HIREDATE                                     DATE
 SAL                                          NUMBER(7,2)
 COMM                                         NUMBER(7,2)
 DEPTNO                                       NUMBER(2)
 DOB                                          DATE

demo@ORA12C>
demo@ORA12C> conn sys/Password-1@ora12c as sysdba
Connected.
sys@ORA12C>
sys@ORA12C>
sys@ORA12C> flashback database to restore point before_changes;
flashback database to restore point before_changes
*
ERROR at line 1:
ORA-38757: Database must be mounted and not open to FLASHBACK.


sys@ORA12C> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@ORA12C>
sys@ORA12C> startup mount
ORACLE instance started.

Total System Global Area  734003200 bytes
Fixed Size                  3049976 bytes
Variable Size             553649672 bytes
Database Buffers          171966464 bytes
Redo Buffers                5337088 bytes
Database mounted.
sys@ORA12C>
sys@ORA12C> flashback database to restore point before_changes;

Flashback complete.

sys@ORA12C> alter database open resetlogs;

Database altered.

sys@ORA12C> conn demo/demo@ora12c
Connected.
demo@ORA12C> exec process_data
Version 1.0 !!!

PL/SQL procedure successfully completed.

demo@ORA12C> set linesize 71
demo@ORA12C> desc emp
 Name                                Null?    Type
 ----------------------------------- -------- -------------------------
 EMPNO                                        NUMBER(4)
 ENAME                                        VARCHAR2(10)
 JOB                                          VARCHAR2(9)
 MGR                                          NUMBER(4)
 HIREDATE                                     DATE
 SAL                                          NUMBER(7,2)
 COMM                                         NUMBER(7,2)
 DEPTNO                                       NUMBER(2)

demo@ORA12C> desc T
ERROR:
ORA-04043: object T does not exist


demo@ORA12C>
demo@ORA12C> conn sys/Password-1@ora12c as sysdba
Connected.
sys@ORA12C> drop restore point before_changes;

Restore point dropped.

sys@ORA12C> conn demo/demo@ora12c
Connected.
demo@ORA12C>

Can i use flashback transaction to rollback DDL Transactions,If Yes then explain with a simple example?

SQL> create table t as select * from dba_objects;

Table created.

SQL>
SQL> select count(*) from t;

  COUNT(*)
----------
     99403

1 row selected.

SQL> commit;

Commit complete.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
   96653332

1 row selected.

SQL> delete from t where rownum < 1000;

999 rows deleted.

SQL> commit;

Commit complete.

SQL> select count(*) from t as of scn  96653332;

  COUNT(*)
----------
     98404

1 row selected.

SQL> alter table t add x int ;

Table altered.

SQL> select count(*) from t as of scn  96653332;

  COUNT(*)
----------
     98404

1 row selected.

SQL> alter table t drop column SUBOBJECT_NAME;

Table altered.

SQL> select count(*) from t as of scn  96653332;
select count(*) from t as of scn  96653332
                     *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed


SQL>

Have you gone through the above link? the very first statement from that link has this.

The transactions being rolled back are subject to these restrictions:
a) They cannot have performed DDL operations that changed the logical structure of database tables
b) They cannot use Large Object (LOB) Data Types:
 BFILE
 BLOB
 CLOB
 NCLOB

Hello,

I wish to add something but maybe I am wrong.

I you have a trigger "AFTER CREATE ON DATABASE" for example, it is possible to by-passed this implicit COMMIT of a succeded DDL if you do an error in this trigger.


Let's see that. My trigger.

CREATE OR REPLACE TRIGGER trig_after_create_database AFTER CREATE ON DATABASE

DECLARE
V_V_STATUS SYS.DBA_OBJECTS.STATUS%TYPE;

BEGIN
SELECT STATUS INTO V_V_STATUS FROM SYS.DBA_OBJECTS WHERE OBJECT_NAME = 'ZZ01' AND OWNER = 'HR' AND OBJECT_TYPE = 'TABLE';

DBMS_OUTPUT.PUT_LINE('Status table ZZ01 : ' || V_V_STATUS);

INSERT INTO SYS.DUAL VALUES ('AA'); /* An error */
END;
/

I drop the table.
SQL> drop table zz01 purge;
Table dropped.

I recreate the table.
SQL> set serveroutput on;
SQL> create table ZZ01 (id number);
Status table ZZ01 : VALID -- The table was created. Can I say that the DDL is finished BUT the COMMIT is not reached again?
create table ZZ01 (id number)
*
ERROR at line 1:
ORA-04088: error during execution of trigger 'SYS.TRIG_AFTER_CREATE_DATABASE'
ORA-00604: error occurred at recursive SQL level 1
ORA-12899: value too large for column "SYS"."DUAL"."DUMMY" (actual: 2, maximum:
1)
ORA-06512: at line 10

Now we verify if table ZZ01 still exists after the trigger problem.
SQL> SELECT STATUS FROM SYS.DBA_OBJECTS WHERE OBJECT_NAME = 'ZZ01' AND OWNER = 'HR' AND OBJECT_TYPE = 'TABLE';
no rows selected

The table was created (we saw it in DBA_OBJECTS) but, because of the trigger error, the table was dropped or not really created.

Can I say that with this trigger, the implicit COMMIT of a succeded DDL was by-passsed? If Yes, so I can make some tests in this trigger and cancel the DDL if tests are wrong to cancel the whole transaction?

I am aware that there are two COMMIT with DDL
COMMIT
DDL
COMMIT
but maybe with a BEFORE trigger or this AFTER trigger it is possible to cancel the first COMMIT and also the whole transaction.