How to write a query to display all tablespaces in database suppose db and it's corresponding datafiles belong to user SCOTT. I can connect to SCOTT account and see it but I like to connect to sys as sysdba to see all tablespaces and datafiles belong to SCOTT.
If you want to get a list of all tablespaces used in the current database instance, you can use the DBA_TABLESPACES view as shown in the following SQL script example:
SQL> connect SYSTEM/fyicenter
Connected.
SQL> SELECT TABLESPACE_NAME, STATUS, CONTENTS
2 FROM USER_TABLESPACES;
TABLESPACE_NAME STATUS CONTENTS
------------------------------ --------- ---------
SYSTEM ONLINE PERMANENT
UNDO ONLINE UNDO
SYSAUX ONLINE PERMANENT
TEMP ONLINE TEMPORARY
USERS ONLINE PERMANENT
http://dba.fyicenter.com/faq/oracle/Show-All-Tablespaces-in-Current-Database.html
SELECT a.file_name,
substr(A.tablespace_name,1,14) tablespace_name,
trunc(decode(A.autoextensible,'YES',A.MAXSIZE-A.bytes+b.free,'NO',b.free)/1024/1024) free_mb,
trunc(a.bytes/1024/1024) allocated_mb,
trunc(A.MAXSIZE/1024/1024) capacity,
a.autoextensible ae
FROM (
SELECT file_id, file_name,
tablespace_name,
autoextensible,
bytes,
decode(autoextensible,'YES',maxbytes,bytes) maxsize
FROM dba_data_files
GROUP BY file_id, file_name,
tablespace_name,
autoextensible,
bytes,
decode(autoextensible,'YES',maxbytes,bytes)
) a,
(SELECT file_id,
tablespace_name,
sum(bytes) free
FROM dba_free_space
GROUP BY file_id,
tablespace_name
) b
WHERE a.file_id=b.file_id(+)
AND A.tablespace_name=b.tablespace_name(+)
ORDER BY A.tablespace_name ASC;
Neither databases, nor tablespaces nor data files belong to any user. Are you coming to this from an MS SQL background?
select tablespace_name,
file_name
from dba_tablespaces
order by tablespace_name,
file_name;
In oracle, generally speaking, there are number of facts that I will mention in following section:
Therefore, your question, "to see all tablespaces and datafiles belong to SCOTT" is s bit wrong.
However, there are some DBA views encompass information about all database objects, regardless of the owner. Only users with DBA privileges can access these views: DBA_DATA_FILES, DBA_TABLESPACES, DBA_FREE_SPACE, DBA_SEGMENTS.
So, connect to your DB as sysdba and run query through these helpful views. For example this query can help you to find all tablespaces and their data files that objects of your user are located:
SELECT DISTINCT sgm.TABLESPACE_NAME , dtf.FILE_NAME
FROM DBA_SEGMENTS sgm
JOIN DBA_DATA_FILES dtf ON (sgm.TABLESPACE_NAME = dtf.TABLESPACE_NAME)
WHERE sgm.OWNER = 'SCOTT'
Source: Stackoverflow.com