Re: fragmented sysauth$ table

This works for me; we have moved aud$ to a separate tabelspace.

exec dbms_pdb.exec_as_oracle_script('alter table aud$ move tablespace audit_data online')

Do look into the package dbms_audit_mgmt also. You can clean the audit log regularly to keep the size down and make those queries against aud$ run faster. Typically queries from monitoring systems executes them a lot.

ons. 28. apr. 2021 kl. 10:25 skrev Willy Klotz <willyk_at_kbi-gmbh.de>:

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

-- 
Øyvind Isene
+47 90864882

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