RelationalDBDesignRelationalDBDesign


Database Architecture   «Prev  Next»
Lesson 7Looking at Tablespaces
ObjectiveExplain how to get information about tablespaces.

Extract Oracle Tablespace Information

Viewing tablespace records

There are two DBA views that return useful information about the tablespaces in a database.
  1. DBA_TABLESPACES
  2. DBA_DATA_FILES
The first is DBA_TABLESPACES. This view returns one record for each tablespace. That record contains the tablespace name, default storage parameters for objects stored in that tablespace, and a field indicating the current status of the tablespace. Use the following query to find out what tablespaces exist in your database:

SELECT tablespace_name, status
FROM DBA_TABLESPACES;

The results that you will get when you execute the above query will resemble these:
TABLESPACE_NAME                STATUS
------------------------------ ---------

SYSTEM                         ONLINE
USER_DATA                      ONLINE
ROLLBACK_DATA                  ONLINE
TEMPORARY_DATA                 ONLINE

Viewing datafiles with Tablespaces

Another useful view is the DBA_DATA_FILES view. This view is similar to the V$DATAFILE view that lists the datafiles and their sizes, but DBA_DATA_FILES also returns the tablespace that each datafile is associated with. You can use the following query to get a list of datafiles used for each tablespace:


SELECT tablespace_name, file_name
FROM DBA_DATA_FILES
ORDER BY tablespace_name, file_name;

The results that you get when you execute the above query will resemble these:
TABLESPACE_NAME    FILE_NAME

---------------- ---------------------------------

ROLLBACK_DATA     C:\ORAWIN95\DATABASE\RBS1ORCL.ORA
SYSTEM            C:\ORAWIN95\DATABASE\SYS1ORCL.ORA
TEMPORARY_DATA    C:\ORAWIN95\DATABASE\TMP1ORCL.ORA
USER_DATA         C:\ORAWIN95\DATABASE\USR1ORCL.ORA

Tablespaces Datafiles - Quiz

Click on the Quiz link below to test your knowledge of datafiles and tablespaces.
Tablespaces Datafiles - Quiz