This article introduces readers to tablespaces in the Oracle database, how to view tablespaces and the physical data files that correspond to each tablespace.
The following data dictionary and dynamic performance views provide useful information about the tablespaces of a DATABASE.
|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.
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", Pct_increase 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
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|
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!|
Stay updated with our newsletter, packed with Tutorials, Interview Questions, How-to's, Tips & Tricks, Latest Trends & Updates, and more ➤ Straight to your inbox!
|Oracle DBA Training||Jun 03 to Jun 18|
|Oracle DBA Training||Jun 06 to Jun 21|
|Oracle DBA Training||Jun 10 to Jun 25|
|Oracle DBA Training||Jun 13 to Jun 28|
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.
Copyright © 2013 - 2023 MindMajix Technologies