How to view oracle table space

Oracle is a mainstream relational database management system, and its data is stored in table spaces. Table space is the logical storage unit of Oracle database. Each table space consists of one or more data files. Therefore, knowing how to view tablespaces is key to using Oracle databases. This article will introduce in detail how to view Oracle table spaces.

1. Log in to the Oracle database

First, open the command line window, enter the SQLPLUS command and press Enter, then enter the user name and password to log in to the Oracle database.

2. View table space

After successfully logging in to the database, we can view the table space in the following two ways:

(1) Through Oracle Data dictionary view viewing table space

Oracle database contains many data dictionary views to store database metadata. Among them, the dba_free_space view can provide table space information. The table space can be viewed through the following SQL statement:

SELECT tablespace_name, file_id, block_id, bytes/1024/1024 AS MB
FROM dba_free_space
WHERE tablespace_name = 'tablespace name';

Among them, tablespace_name is the name of the table space that needs to be viewed, file_id is the ID of the data file, block_id is the ID of the data block, and bytes is the number of free bytes of the block. The MB column is the result of converting the number of bytes to MB.

(2) View the table space through Oracle Enterprise Manager

Oracle Enterprise Manager is a user interface that facilitates users to manage Oracle databases. Table spaces can be viewed through Oracle Enterprise Manager. First, open Enterprise Manager in the browser, select "Table Space" from the left menu bar, and click to view the "Table Space Manager" menu. You can query a specific table space, or you can choose to view all table spaces.

Through these two methods, you can view the detailed information of the Oracle table space, including the name, size, file name, data file ID, etc. of the table space.

3. Check table space usage

We can also check table space usage through the following methods:

(1) Use Oracle data dictionary view View table space usage

Still use the dba_free_space view, but this time we are viewing the used table space. The following is the SQL statement:

SELECT a.tablespace_name "Table space name",
total/1024/1024 "Total size MB",
free/1024/1024 "Remaining space MB",
( total - free )/1024/1024 "Used space MB",
Round(( total - free ) / total,4)*100 "Used %",
Round(free/ total, 4)*100 "Remaining %",
MAXSIZE/1024/1024 "Maximum space MB",
autoextensible "Automatic growth",
status "Status"
FROM
( SELECT tablespace_name, sum(bytes) free

      FROM dba_free_space
     GROUP BY tablespace_name) a,

( SELECT tablespace_name,sum(bytes) total,maxbytes MAXSIZE,autoextensible,status

      FROM dba_data_files
     GROUP BY tablespace_name ) b

WHERE
a.tablespace_name = b.tablespace_name;

This will display the usage of the table space, including size, remaining space, used space, used percentage, maximum space, whether it can automatically grow and status.

(2) Use Oracle Enterprise Manager to view the table Space Usage

In Oracle Enterprise Manager, you can view table space usage by:

• Select "Table Spaces" and then select the table space you want to view.
• Hover your mouse over the name of a tablespace and you will see a summary of tablespace usage.
• Click on the tablespace name to open the Tablespaces page, which contains the tablespace Usage details.

Summary

Understanding how to view Oracle tablespaces is the key to using Oracle database. We can view tablespaces using Oracle data dictionary views or Oracle Enterprise Manager. At the same time, understanding the usage of table spaces helps manage and maintain the health of the Oracle database.

The above is the detailed content of How to view oracle table space. For more information, please follow other related articles on the PHP Chinese website!