Query to display all tablespaces in a database and datafiles

Question

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.

Answer 1

In oracle, generally speaking, there are number of facts that I will mention in following section:

• Each database can have many Schema/User (Logical division).
• Each database can have many tablespaces (Logical division).
• A schema is the set of objects (tables, indexes, views, etc) that belong to a user.
• In Oracle, a user can be considered the same as a schema.
• A database is divided into logical storage units called tablespaces, which group related logical structures together. For example, tablespaces commonly group all of an application’s objects to simplify some administrative operations. You may have a tablespace for application data and an additional one for application indexes.

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'