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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to oracle

Examples related to oracle11g

Examples related to oracle10g