Mindmajix

Viewing tablespace information in Oracle Database

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.

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",
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

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                    users

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 shows 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.


0 Responses on Viewing tablespace information in Oracle Database"

Leave a Message

Your email address will not be published. Required fields are marked *

Copy Rights Reserved © Mindmajix.com All rights reserved. Disclaimer.
Course Adviser

Fill your details, course adviser will reach you.