listagg()

Today I needed to create an audit statement with a lot of users who are excepted from auditing. It looks somewhat like :

audit policy <policy_name> except <comma separated list of users>

For my requirement all Oracle Maintained account should be excepted from auditing.

Following statement made it easy:

spool policy_on.sql
select 'audit policy <policy_name> except '||names||';'
       from (
              select listagg(name,',') names
              from (
                     select username name
                     from dba_users
                     where oracle_maintained='Y'
                   ) names
            );
spool off

@policy_on.sql

The statement generates a statement like:

audit policy nskp20a_audit_policy except SYS,SYSTEM,OUTLN,APPQOSSYS,GSMADMIN_INTERNAL,XDB,WMSYS,....;

And why do I write this down?  Because I did not know the listagg() function, and now I do.

(And yes…I do know that ther are a lot more possibilities…this is the one I needed the function for)