Re: fragmented sysauth$ table

Without seeing a trace of an attempt to "set role" it's a pretty safe bet that Tim's analysis is correct.

You might like to look at this blog note, though https://jonathanlewis.wordpress.com/2007/09/09/set-role/ It's dated 2007 but has updates for 18.3 saying the problems get worse, and an update in Feb 2020 that reports a known bug, fixed in 20.1 with backports to 12, 18, and 19.
There's also a comment connecting the problem with non-default roles.

Regards
Jonathan Lewis

On Wed, 28 Apr 2021 at 09:25, Willy Klotz <willyk_at_kbi-gmbh.de> wrote:

> Hi all,
>
>
>
> we are on Oracle 19.8. customer has several large databases, with several
> tens of thousands users and a number of roles.
>
>
>
> We are experiencing slow „set role“ (15 seconds and up), and we see some
> “select … from sysauth$” in top10 SQL of the AWR Reports . I can see that
> sys.sysauth$ is fragmented, 40% wasted space in there.
>
>
>
> Is there a way to compact this table? Alter table move or similar do not
> work on it.
>
>
>
> Thank you and best regards
>
> Willy
>
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l