Re: ORA-30027: Undo quota violation

From: Norman Dunbar <oracle_at_dunbar-it.co.uk>
Date: Thu, 9 Apr 2020 12:13:22 +0100
Message-ID: <8ec5ea25-a92b-c132-314f-2641d746dbe9_at_dunbar-it.co.uk>


Hi Arpit,

On 09/04/2020 10:07, Arpit Aggarwal wrote:

> Ok so i could find out a way using DBA_RSRC_PLAN_DIRECTIVES and I see
> CLOUD_GROUP2_HIGH resource consumer group has undo_pool as 5GB , does
> this means any statements by a user having CLOUD_GROUP2_HIGH as resource
> manager group will terminate if they would need undo quota greater than 5G ?

I'm not great with resource pools yet, we don't use the UNDO_POOL. So treat the following with a degree of scepticism!

I think the pool is what can be used ab all members of the same resource plan. If you have 100 users in the plan, they share the 5G. I need to check though.

> Also is it normal for DDL statements to hit this error ?
DDL statements do a fair bit of DML behind the scenes, so yes, DDL can impinge on your resource plan limits, it busy enough.

You could check to see which other users are in the same pool, and, if necessary, how many are running sessions in the database. It could be just a lot of connections using UNDO all at once.

As mentioned, I need to check up on this stuff though, so don;t take my answers as "gospel".

Cheers,
Norm.

-- 
Norman Dunbar
Dunbar IT Consultants Ltd

Registered address:
27a Lidget Hill
Pudsey
West Yorkshire
United Kingdom
LS28 7LG

Company Number: 05132767
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Apr 09 2020 - 13:13:22 CEST

Original text of this message