Home  >  Blog  >   Oracle DBA  > 

How to View Tablespace in Oracle

This article introduces readers to tablespaces in the Oracle database, how to view tablespaces and the physical data files that correspond to each tablespace.

Rating: 5

View Tablespace in Oracle

The following data dictionary and dynamic performance views provide useful information about the tablespaces of a DATABASE.

View     Description
V$tablespace Name and number of all tablespaces from the control file.
Dba_tablespaces, user_tablespaces Descriptions of all (or user accessible) tablespaces.
Dba_tablespace_groups Displays the tablespace groups and the tablespaces that belong to them.
Dba_segments, user_segments Information about segments within all (or user accessible) tablespaces.
Dba_extents, user_extents Information about data extents within all (or user accessible) tablespaces.
Dba_free_space, user_free_space Information about free extents within all (or user accessible) tablespaces.
V$datafile Information about all datafiles, including tablespace number of owning tablespace.
V$tempfile Information about all tempfiles, including tablespace number of owning tablespace.
Dba_data_files Shows files (datafiles) belonging to tablespaces.
Dba_temp_files Shows files (tempfiles) belonging to temporary tablespaces.
V$temp_extent_map Information for all extents in all locally managed temporary tablespaces.
V$temp_extent_pool For locally managed temporary tablespaces: the state of temporary space cached and used for by each instance.
V$temp_space_header Shows space used/free for each tempfile.
Dba_users Default and temporary tablespaces for all users.
Dba_ts_quotas Lists tablespace quotas for all users.
V$sort_segment Information about every sort segment in a given instance. The view is only updated when the tablespace is of the temporary type.
V$tempseg_usage Describes temporary (sort) segment usage by user for temporary or permanent tablespaces.


Want To Get  Training From Experts? Enroll Now For Free Demo On Oracle DBA Online Certification.

The following are just a few examples of using some of these views.

Example 1:

listing TABLESPACES and default storage parameters:

To list the names and default storage parameters of all tablespaces in a database, use the following query on the dba_tablespaces view:

Sql>select tablespace_name "tablespace",
Initial_extent "initial_ext",
Next_extent "next_ext",
Min_extents "min_ext",
Max_extents "max_ext",
From dba_tablespaces;
Tablespace   initial_ext    next_ext     min_ext      max_ext       pct_increase 
——–             ———          ——–            ——-     ——–             ———-
Rbs                1048576     1048576         2               40               0
System           106496      106496           1               99               1
Temp              106496      106496           1               99              0
Testtbs            57344        16384            2              10                1
Users               57344        57344            1               99               1

 MindMajix YouTube Channel

Example 2:

listing the datafiles and associated tablespaces of a database:

To list the names, sizes, and associated tablespaces of a database, enter the following query on the dba_data_files view:

Sql>select  file_name, blocks, tablespace_name  from dba_data_files;
File_name                                              blocks        tablespace_name
—————-                                          ——–          —————–
/u02/oracle/iddb3/dbf/rbs01.dbf               1536                    rbs
/u02/oracle/iddb3/dbf/system01.dbf        6586                   system
/u02/oracle/iddb3/dbf/temp01.dbf            6400                  temp
/u02/oracle/iddb3/dbf/testtbs01.dbf         6400                   testtbs
/u02/oracle/iddb3/dbf/users01.dbf             384                    user

Related Article: Oracle DBA Interview Questions

Example 3:

Displaying statistics for free space (extents) of each tablespace:

To produce statistics about free extents and coalescing activity for each tablespace in the database, enter the following query:

Sql>select tablespace_name "tablespace", file_id, count(*) "pieces", max(blocks) "maximum",   

 min(blocks) "minimum", avg(blocks) "average", sum(blocks) "total"

 From dba_free_space  group by tablespace_name, file_id;

Tablespace    file_id      pieces     maximum     minimum   average    total
———–     ——            ——-       ———-        ———-        ——–   —-
Rbs                    2             1            955             955             955            955
System              1              1             119               119            119          119
Temp                 4              1          6399            6399        6399            6399
Testtbs              5              5           6364               3            1278           6390
Users                3               1             363               363            363          363

Pieces show the number of free space extents in the tablespace file, maximum and minimum show the largest and smallest contiguous area of space in database blocks, average shows the average size in blocks of a free space extent, and total shows the amount of free space in each tablespace file in blocks. This query is useful when you are going to create a new object or you know that a segment is about to extend, and you want to make sure that there is enough space in the containing tablespace.
Explore Oracle DBA Sample Resumes! Download & Edit, Get Noticed by Top Employers!
Join our newsletter

Stay updated with our newsletter, packed with Tutorials, Interview Questions, How-to's, Tips & Tricks, Latest Trends & Updates, and more ➤ Straight to your inbox!

Course Schedule
Oracle DBA TrainingJun 03 to Jun 18
Oracle DBA TrainingJun 06 to Jun 21
Oracle DBA TrainingJun 10 to Jun 25
Oracle DBA TrainingJun 13 to Jun 28
Last updated: 31 May 2023
About Author
Remy Sharp
Ravindra Savaram

Ravindra Savaram is a Content Lead at Mindmajix.com. His passion lies in writing articles on the most popular IT platforms including Machine learning, DevOps, Data Science, Artificial Intelligence, RPA, Deep Learning, and so on. You can stay up to date on all these technologies by following him on LinkedIn and Twitter.

Recommended Courses

1 /15