Listing default tablespaces and data files per user
Query listing each user's default and temporary tablespace, with the data files of the default tablespace.
The following query lists each database user with its default and temporary tablespaces, along with the data files of the default tablespace. The set and column lines are SQL*Plus commands.
set linesize 300;
set pagesize 10000;
COLUMN username FORMAT A30
column default_tablespace format a30
column file_name format a50
column TEMPORARY_TABLESPACE format a30
SELECT u.username
,u.default_tablespace
,df.file_name
,TEMPORARY_TABLESPACE
FROM dba_users u
JOIN dba_data_files df ON u.default_tablespace=df.tablespace_name;
Notes
Because the query joins on the data files of the default tablespace, each user appears once per data file of that tablespace.