Lesson 7	Looking at Tablespaces
Objective	Explain how to get information about tablespaces.

Extract Oracle Tablespace Information

As an Oracle Database Administrator (DBA), acquiring detailed information about tablespaces is critical for monitoring health, optimizing performance, and planning for scalability. Oracle provides multiple avenues to obtain this data, primarily through its data dictionary views. Below are the methodologies and specific SQL queries that can be employed to gather essential information on tablespaces.

Using Data Dictionary Views

Querying `DBA_TABLESPACES`

This view provides comprehensive information about each tablespace in the database. Execute the following SQL statement to retrieve information such as tablespace name, block size, logging attribute, and other relevant details:

SELECT * FROM DBA_TABLESPACES;

Querying `DBA_DATA_FILES` and `DBA_TEMP_FILES`

These views provide information about the data files and temporary files associated with tablespaces. To acquire details like file location, size, and status, execute:
For Permanent Tablespaces

SELECT * FROM DBA_DATA_FILES 
WHERE TABLESPACE_NAME = 'YOUR_TABLESPACE_NAME';

For Temporary Tablespaces

SELECT * FROM DBA_TEMP_FILES 
WHERE TABLESPACE_NAME = 'YOUR_TEMP_TABLESPACE_NAME';

Querying `DBA_FREE_SPACE`

This view enables you to get the amount of free space available in a tablespace. Execute the following to gather this information:

SELECT TABLESPACE_NAME, SUM(BYTES) AS FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME;

Querying `DBA_TS_QUOTAS`

For information related to users' quotas on tablespaces, execute:

SELECT * FROM DBA_TS_QUOTAS WHERE TABLESPACE_NAME = 'YOUR_TABLESPACE_NAME';

Querying `V$TABLESPACE`

This dynamic performance view provides real-time information about tablespaces. It includes status, number of blocks, and other runtime specifics:

SELECT * FROM V$TABLESPACE;

Command-Line Utilities

SQL*Plus: Using the SQL*Plus utility, you can execute any of the aforementioned SQL queries to obtain tablespace information.
Oracle Enterprise Manager (OEM): This graphical tool allows you to navigate through tablespaces and their associated attributes, providing a more user-friendly method to obtain the same information.

Custom Scripts

For routine checks and automated reporting, custom SQL scripts can be developed to fetch tablespace details at specified intervals. Such scripts can be scheduled using cron jobs in Unix/Linux environments or Task Scheduler in Windows.

Best Practices

Scheduled Monitoring: Consistent monitoring of tablespaces is essential. Set up automated alerts to notify you when a tablespace crosses a certain threshold of space utilization.
Audit and Logging: Maintain logs of tablespace attributes over time to identify patterns, which will be instrumental in capacity planning and performance tuning.
Access Control: Ensure only authorized personnel have the privilege to query sensitive system views related to tablespaces.

By utilizing the aforementioned methods and adhering to best practices, you can obtain a comprehensive understanding of tablespaces within your Oracle database environment. This is crucial for effective database management, which encompasses performance tuning, space management, and data integrity.

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

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