Oracle report all users, their roles and privileges

I took the sql for listing privileges recursively for Oracle users from Rene Nyffenegger's site, it is very good but only works for one user at a time as it stands, I wanted to run a report that outputted the same way for every user in my database.  So it needed to go into a PL/SQL loop.

set serveroutput on

declare

TYPE re_type IS TABLE OF varchar2(1024);
v_statement varchar2(1024);
v_result re_type;

BEGIN

for x in ( select username from dba_users)
LOOP

v_statement := 'select concat(lpad('' '', 2*level), granted_role) line1 from (select null grantee, username granted_role from dba_users where username = ''' || x.username || ''' union select grantee, granted_role from dba_role_privs union select grantee, privilege from dba_sys_privs ) start with grantee is null connect by grantee = prior granted_role';

execute immediate v_statement BULK COLLECT INTO v_result;

for indx in v_result.FIRST .. v_result.LAST
loop DBMS_OUTPUT.PUT_LINE(v_result(indx));

end loop; 

END LOOP;

END;

/

You get a formatted output like:

MYUSER1
    MYROLE_SESSION
      ALTER SESSION
      CREATE SESSION
    CONNECT
      CREATE SESSION
    MYROLE1
    MYROLE2
  MYUSER2
    CONNECT
      CREATE SESSION
    MYROLE1
    MYROLE3 

Particular thanks to Pete for seeing me past my quotes and array woes

Read more...

Finding Oracle tablespaces with mixed autoextensible datafiles

Two ways to identify tablespaces where some datafiles are autoextend true and some are false.

(Mixing these can cause issues with monitoring, so try to keep them consistent.)

SELECT DISTINCT a.tablespace_name
FROM dba_data_files a
WHERE a.autoextensible ='YES'
AND EXISTS

(SELECT DISTINCT b.tablespace_name

FROM dba_data_files b
WHERE b.autoextensible ='NO'
AND b.tablespace_name=a.tablespace_name )
ORDER BY tablespace_name;



SELECT  ddf2.tablespace_name
FROM

(SELECT DISTINCT ddf.tablespace_name,ddf.autoextensible
FROM  dba_data_files ddf
GROUP BY ddf.tablespace_name,ddf.autoextensible
) ddf2
GROUP BY ddf2.tablespace_name
HAVING  count(1) = 2
ORDER BY ddf2.tablespace_name;

Read more...