Multiple JOIN of 6 tables via 5 database instances

I'm currently reengineering my corporates user management and created a table which lists all users of all the 5 database instances. Next step is, that I need to write a query which shows me all the roles of a user which he has of all instances.

I already used UNION ALL, but the output was unstructured and you couldn't tell which role on which instance. So I tried the following for only 3 tables:

SELECT W.GRANTED_ROLE "GRANTED_ROLE_DB1", V.GRANTED_ROLE "GRANTED_ROLE_DB2"
FROM SCHEMA.USR_ALL_USERS U
LEFT OUTER JOIN SYS.DBA_ROLE_PRIVS W
   ON (U.USERNAME = W.GRANTEE AND U.DB_INSTANCE = 'DB1')
LEFT OUTER JOIN SYS.DBA_ROLE_PRIVS@DB2_LINK V
   ON (U.USERNAME = V.GRANTEE AND U.DB_INSTANCE = 'DB2')
WHERE U.USERNAME = 'USER'
ORDER BY U.USERNAME ASC;

It actually worked, but the output wasn't satisfying:

GRANTED_ROLE_DB1            GRANTED_ROLE_DB2
--------------------------- --------------------------
ROLE_1
ROLE_2
ROLE_3
ROLE_4
                            ROLE_1
                            ROLE_2
                            ROLE_4
                            ROLE_5
                            ROLE_6

Is there any way to make an output like this:

GRANTED_ROLE_DB1            GRANTED_ROLE_DB2
--------------------------- --------------------------
ROLE_1                      ROLE_1
ROLE_2                      ROLE_2
ROLE_3
ROLE_4                      ROLE_4
                            ROLE_5
                            ROLE_6

I tried ON ((U.USERNAME = V.GRANTEE OR W.GRANTED_ROLE = V.GRANTED_ROLE) AND U.DB_INSTANCE = 'DB2') but the output was even worse.

You guys have any suggestions or helpful thoughts?

The issue is you're restricting on the instance field, which is resulting in the roles only appearing in one column or the other.

To get around this, remove the restrictions on db_instance and full outer join the roles from the second database to the roles in the first, like so:

SELECT W.GRANTED_ROLE "GRANTED_ROLE_DB1", V.GRANTED_ROLE "GRANTED_ROLE_DB2"
FROM SCHEMA.USR_ALL_USERS U
LEFT OUTER JOIN SYS.DBA_ROLE_PRIVS W
   ON (U.USERNAME = W.GRANTEE)
FULL OUTER JOIN SYS.DBA_ROLE_PRIVS@DB2_LINK V
   ON (U.USERNAME = V.GRANTEE AND V.GRANTED_ROLE = W.GRANTED_ROLE)
WHERE nvl(U.USERNAME, v.username) = 'USER'
ORDER BY nvl(W.GRANTED_ROLE, V.GRANTED_ROLE) ASC;

This SQLFiddle has a working example. Updated fiddle following comments from Andriy.

StackExchange Database Administrators Q#45344, answer score: 3