You Asked

Hi,
I am facing an issue, where one single process (eg process x) can consume most of the undo-quota, and then fails for rollback segment error.

When I'll increase my database undo retention, and increase my undo tablespace size, it'll grow for all of the users in the database, so other processes can fill it instead of process x.

My question: Is it possible to single out one user/schema for a dedicated quota retention/tablespace size ?

Thanks

and Connor said...

Yes you can, using Resource Manager. From the docs:

https://docs.oracle.com/cd/E11882_01/server.112/e25494/dbrm.htm#ADMIN11858

Undo Pool

You can specify an undo pool for each consumer group. An undo pool controls the total amount of undo for uncommitted transactions that can be generated by a consumer group. When the total undo generated by a consumer group exceeds its undo limit, the current DML statement generating the undo is terminated. No other members of the consumer group can perform further data manipulation until undo space is freed from the pool.

Here is an example

SQL> conn mcdonac/alicat1
Connected.

SQL> alter system set resource_manager_plan ='';

System altered.

SQL>
SQL> begin
  2    dbms_resource_manager.create_pending_area();
  3    --
  4
  5    dbms_resource_manager.create_consumer_group(
  6      CONSUMER_GROUP=>'CG_UNDO_LIMIT_PLAN',
  7      COMMENT=>'This is the consumer group that small undo limits'
  8      );
  9
 10    dbms_resource_manager.create_plan(
 11      PLAN=> 'UNDO_LIMIT_PLAN',
 12      COMMENT=>'Disallow exceeding undo'
 13    );
 14
 15    dbms_resource_manager.create_plan_directive(
 16      PLAN=> 'UNDO_LIMIT_PLAN',
 17      GROUP_OR_SUBPLAN=>'CG_UNDO_LIMIT_PLAN',
 18      COMMENT=>'Disallow exceeding undo',
 19      UNDO_POOL=>1000  -- one megabyte
 20    );
 21
 22
 23    dbms_resource_manager.create_plan_directive(
 24      PLAN=> 'UNDO_LIMIT_PLAN',
 25      GROUP_OR_SUBPLAN=>'OTHER_GROUPS',
 26      COMMENT=>'leave others alone'
 27    );
 28
 29    DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;
 30
 31    DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
 32
 33  end;
 34  /

PL/SQL procedure successfully completed.

SQL>
SQL> exec dbms_resource_manager_privs.grant_switch_consumer_group('DEMO','CG_UNDO_LIMIT_PLAN',false);

PL/SQL procedure successfully completed.

SQL> exec dbms_resource_manager.set_initial_consumer_group('DEMO','CG_UNDO_LIMIT_PLAN');

PL/SQL procedure successfully completed.

SQL>
SQL> alter system set resource_manager_plan ='UNDO_LIMIT_PLAN';

System altered.

SQL> conn demo/demo
Connected.

SQL> create table t as select * from all_Objects;

Table created.

SQL> insert /*+ APPEND */ into t select * from t;

57034 rows created.

SQL> commit;

Commit complete.

SQL> insert /*+ APPEND */ into t select * from t;

114068 rows created.

SQL> commit;

Commit complete.

SQL> delete from t;
delete from t
            *
ERROR at line 1:
ORA-30027: Undo quota violation - failed to get 288 (bytes)


SQL>

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks

A reader, March 07, 2018 - 3:40 am UTC

Thanks! Is there another way except using consumer groups? I have asked and in our organization we don't use those

March 08, 2018 - 2:48 am UTC

Well...thats a bit like saying

"Can we store data, but we'd rather not use tables"

:-)

We *have* a facility to limit undo ... its called Resource Manager

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Thanks

More to Explore

Performance

Shared Undo Quota - Can We Dedicate specific amount for a single user?

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Thanks

More to Explore

Performance