Skip to Main Content
  • Questions
  • Is there a way to interrupt rollback if I don't care about the table?

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Mikhail.

Asked: September 08, 2020 - 2:56 pm UTC

Answered by: Connor McDonald - Last updated: October 14, 2020 - 4:00 am UTC

Category: Database Administration - Version: 12.1.0.2

Viewed 100+ times

You Asked

Hello Connor, hello Chris.

Oracle version: 12.1.0.2.0.

I am wondering if there is a way to interrupt a long running rollback if we don't care what happens to the table? After the the interrupt we would truncate it anyway.

A junior developer attempted to populate an empty table with many indexes with tens of millions of rows. It didn't fit into the undo and the transaction went into a very long rollback. It lasted at least 10 times longer than the query ran before the rollback. During that time the database barely responded to other queries which slowed down or blocked his work and the work of other people.

This happened to me in the past as well and it is very frustrating to wait hours and hours until a useless(from our perspective, not oracle) task finishes because of a mistake.

I know that he should've disabled indexes and used /*+ append*/, I'm just asking is there a way to tell Oracle 'I don't care about that table'?

and we said...

Unfortunately not, because don't *know* until we are undoing the transaction what actually occurred. For example, a delete might have cascaded into dozens of other tables linked by a foreign key, or an insert might have fired off triggers which did DML on other tables etc etc.

Worst case scenario you could bounce the database. Note that it will *still* perform the undo, but it can take advantage of "fast_start_parallel_rollback" to minimize the time.

and you rated our response

  (3 ratings)

Reviews

What about the direct path inserts?

October 02, 2020 - 12:40 pm UTC

Reviewer: Mikhail Onishchenko from Moscow Russia

Thank you for your answer Connor.

What about the inserts with the append hint? I've seen in some other answer that with the direct path writes oracle can just discard the entire segment and rollback quickly instead of doing the usual rollback.

But what if there are triggers on inserts? Will Oracle ignore the append hint in this case? Is there a way to check whether Oracle is doing a direct path insert or a usual one?
Connor McDonald

Followup  

October 08, 2020 - 2:24 am UTC

Rather than hone in on the type of activity, I find it easier to look at the amount of work that will be needed for the rollback.

You can see this directly by looking at v$transaction and looking at the USED_UBLK, USED_UREC columns. That tells you how much undo will be required if you chose to rollback.

Thus for an insert-append (no triggers), then not much. Put the triggers on ...and lots more :-)

SQL> create table t ( x char(2000), y int );

Table created.

SQL>
SQL> insert /*+ APPEND */ into t
  2  select rownum , rownum from dual
  3  connect by level <= 10000;

10000 rows created.

SQL>
SQL> select used_ublk from v$transaction;

 USED_UBLK
----------
         1

SQL>
SQL> rollback;

Rollback complete.

SQL>
SQL> create or replace
  2  trigger trg before insert on t
  3  for each row
  4  begin
  5    :new.y := :new.y + 1;
  6  end;
  7  /

Trigger created.

SQL>
SQL> insert /*+ APPEND */ into t
  2  select rownum , rownum from dual
  3  connect by level <= 10000;

10000 rows created.

SQL>
SQL> select used_ublk from v$transaction;

 USED_UBLK
----------
        42

SQL>


Very difficult

October 13, 2020 - 2:58 pm UTC

Reviewer: David D. from PARIS from FRANCE

Hello Mikhail,

Like Connor said I don't think it is possible to cancel a roolback but...
here you can find a list of system event with triggers (Oracle 9.2, sorry, too hard to find the same for Oracle 12) : https://docs.oracle.com/cd/B10501_01/appdev.920/a96590/adg14evt.htm

Maybe you can use the SERVERERROR event and the folowing attributes of the event to isolate your problem which is "the INSERT was too big" I presume.
ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_server_error
ora_is_servererror
space_error_info

If you can isolate your error, maybe you can fire a TRUNCATE on your table and, maybe, it will be fired before the very long roolback.

I cannot test but I think there is a way of doing something...

CREATE OR REPLACE TRIGGER trigger_truncate
  AFTER SERVERERROR
  ON DATABASE
BEGIN
if ora_login_user = 'your_user' ...
if ora_server_error = 'INSERT failed'...
then
truncate table your_table;
END;


Complement

October 13, 2020 - 3:18 pm UTC

Reviewer: David D. from PARIS from FRANCE

I complete my last post : here, I show that we can cancel the implicit COMMIT of a DDL order; so maybe it is possible to cancel the ROLLBACK of a DML operation.

The objective of this article was to prove that the COMMIT takes place AFTER the DLL order and not in the code thereof, and therefore that it can be bypassed with a trigger. For this, I will test the CREATE TABLE order. My test is simple: if the table I created exists in DBA_OBJECTS and its status is VALID, then the DDL order has been executed in full. And then I cause an error to trigger this time an implicit ROLLBACK on the DDL order via the trigger attached to it. If the existing table no longer exists after this error in DBA_OBJECTS, then it means that the implicit COMMIT of the DDL command has been bypassed!

We create a trigger on the CREATE system event, with a scope on the whole database, so ON DATABASE. This trigger is AFTER, not BEFORE otherwise the table will not exist in DBA_OBJECTS at the start of my trigger. To cause an error, we insert an impossible value in DUAL (unless a little joker has modified the DUMMY field of DUAL but that is another story). Note that any CREATE will fail with this trigger, not just a CREATE TABLE or a CREATE SEQUENCE ... it is possible with an environment variable to test the type of object created but that's another story.

The trigger code with the hard name of the test table; I don't do error handling because I want the error on the INSERT not to be caught.
          CREATE OR REPLACE TRIGGER trig_after_create_database AFTER CREATE ON DATABASE
          DECLARED
                  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 of table ZZ01:' || V_V_STATUS);

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


Compile the OK trigger (the code is in the TRIG.sql file).
          SQL> @TRIG
          Trigger created.


We drop the test table.
          SQL> drop table zz01 purge;
          Table dropped.


Test creation of a table.
          SQL> set serveroutput on;

          SQL> create table ZZ01 (id number);
          Status of table ZZ01: VALID - The table has been successfully created because it exists in DBA_OBJECTS with the status VALID
          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



We now check that the table ZZ01 no longer exists after the INSERT in DUAL which failed.
          SQL> SELECT STATUS FROM SYS.DBA_OBJECTS WHERE OBJECT_NAME = 'ZZ01' AND OWNER = 'HR' AND OBJECT_TYPE = 'TABLE';
          no rows selected

Bingo, the table has disappeared, which proves that the DDL COMMIT was prevented from definitively validating the CREATE TABLE. The explanation is that the trigger on the CREATE TABLE order having failed, Oracle will generate an implicit ROLLBACK which will include the operations of the trigger but also the CREATE TABLE because the trigger is associated with this DDL order and therefore it is the DDL / trigger set that is canceled by Oracle.

One could object to me that the implicit COMMIT of the DDL order is at the end of it in its code and not after and that the call to the AFTER trigger is made at the very end of the DDL order, just before the COMMIT. Yes, maybe, but in this case I think the SELECT in DBA_OBJECTS wouldn't have returned anything. I think the program sequence is as follows: trigger BEFORE then DDL order then trigger AFTER then COMMIT with four independent programs rather than a DDL order which contains the call to the BEFORE trigger then its own code then call to the trigger AFTER then COMMIT to the end of the DDL order.


Connor McDonald

Followup  

October 14, 2020 - 4:00 am UTC

We don't "cancel" the final commit, we just never get there.

SQL> create or replace
  2  trigger sys.kill_all_ddl
  3  after create on database
  4  begin
  5    raise_application_error(-20000,'No create for you!');
  6  end;
  7  /

Trigger created.


then in another session

SQL> create table t ( x int );
create table t ( x int )
*
ERROR at line 1:
ORA-04088: error during execution of trigger 'SYS.KILL_ALL_DDL'
ORA-00604: error occurred at recursive SQL level 1
ORA-20000: No create for you!
ORA-06512: at line 2


and I traced that session

Before the 'create table' you'll see

XCTEND rlbk=0

which is "transaction end without rollback" ie, a commit

and then after the trigger fires you'll see

XCTEND rlbk=1

which is a rollback.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database