Oracle 共享撤消配额-我们可以为单个用户指定特定金额吗？

嗨，
我面临一个问题，其中一个进程 (例如进程x) 可以消耗大部分撤消配额，然后失败的回滚段错误。

当我将增加我的数据库撤消保留，并增加我的撤消表空间大小时，它将为数据库中的所有用户增长，所以其他进程可以填充它而不是进程x。

我的问题: 是否可以为专用的配额保留/表空间大小挑出一个用户/模式？

谢谢

是的，你可以，使用资源管理器。从文档中:

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.

这是一个例子

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>
复制