| Lesson 7 | Looking at Tablespaces |
| Objective | Explain 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.
- DBA_TABLESPACES
- 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