12 Selects of christmas (the second instalment)

The followup to the first set of SQLs

4. Connect by

Show all your roles and the route you have obtained them through.

select SYS_CONNECT_BY_PATH(granted_role, '/') path
from dba_role_privs
start with grantee = user
connect by prior granted_role = grantee

5. WITH clause

The data dictionary views where there is an ALL_ version and a USER_ version

column a_name format a32
column u_name format a32

with w_tab as
(select distinct object_name, substr(object_name,instr(object_name,'_')+1) suffix,
        substr(object_name,1,instr(object_name,'_')-1) prefix
from dba_objects
where substr(object_name,1,instr(object_name,'_')-1) in ('ALL','USER'))
select a.object_name a_name, u.object_name u_name
from w_tab a, w_tab u
where a.prefix = 'ALL'
and u.prefix = 'USER'
and a.suffix = u.suffix;

6. ANSI style FULL Outer Join

I'm now bulding on the WITH select, but with a full outer join to find where there's a USER_ view with no corresponding ALL_ version or vice versa :

column suffix format a32
column a_name format a32
column u_name format a32

with w_tab as
(select distinct object_name name,
        substr(object_name,instr(object_name,'_')+1) suffix,
        substr(object_name,1,instr(object_name,'_')-1) prefix
from dba_objects
where substr(object_name,1,instr(object_name,'_')-1) in ('ALL','USER'))
select nvl(a.suffix,u.suffix) suffix,
       a.name a_name, u.name u_name from
   (select * from w_tab where prefix = 'ALL') a
   full outer join
   (select * from w_tab where prefix = 'USER') u
   on (a.suffix = u.suffix)
where a.name is null or u.name is null
order by 1;