Query to view assigned tablespace and its associated datafiles to the database users

########RETURN NULL FOR THE TABLESPACE NOT ASSIGNED TO USERS#######
Here
ts# represents tablespace number
file# represents datafile number

select * from
(select c.username username,'default tablespace' type,a.ts# ts#,a.name tablespace,b.file# file#,b.name name from v$tablespace a, v$datafile b,dba_users c
where a.ts#=b.ts# and
a.name=c.default_tablespace(+)
union all
select c.username,'temporary tablespace' type, a.ts# ts#,a.name tablespace,b.file# file#,b.name name from v$tablespace a, v$tempfile b,dba_users c
where a.ts#=b.ts# and
a.name=c.temporary_tablespace)z
order by z.username,z.type;