SyntaxFix

[oracle] Query to display all tablespaces in a database and datafiles

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'

Examples related to oracle

• concat yesterdays date with a specific time • ORA-28001: The password has expired • how to modify the size of a column • How to create a blank/empty column with SELECT query in oracle? • Find the number of employees in each department - SQL Oracle • Query to display all tablespaces in a database and datafiles • When or Why to use a "SET DEFINE OFF" in Oracle Database • How to insert date values into table • error: ORA-65096: invalid common user or role name in oracle • In Oracle SQL: How do you insert the current date + time into a table?

Examples related to oracle11g

• Convert timestamp to date in Oracle SQL • Query to display all tablespaces in a database and datafiles • Oracle Installer:[INS-13001] Environment does not meet minimum requirements • Forgot Oracle username and password, how to retrieve? • Extract number from string with Oracle function • How to solve : SQL Error: ORA-00604: error occurred at recursive SQL level 1 • Add days Oracle SQL • How to determine tables size in Oracle • ORA-28000: the account is locked error getting frequently • Oracle listener not running and won't start

Examples related to oracle10g

• Query to display all tablespaces in a database and datafiles • How to insert date values into table • Why do I get PLS-00302: component must be declared when it exists? • ORA-28000: the account is locked error getting frequently • Oracle Trigger ORA-04098: trigger is invalid and failed re-validation • Inserting Image Into BLOB Oracle 10g • Error System.Data.OracleClient requires Oracle client software version 8.1.7 or greater when installs setup • Different CURRENT_TIMESTAMP and SYSDATE in oracle • Search for a particular string in Oracle clob column • ORA-00984: column not allowed here