How to query table space size in oracle

Oracle steps to query the table space size: 1. Log in to the Oracle database using a database administrator account; 2. Use the "SELECT" statement to view the space list; 3. There are 3 methods to query the table space size: 1. Use the dbms_utility package to query; 2. Use the dba_segments view to query; 3. Use the dba_data_files view to query; 4. Use the "DBMS_OUTPUT.PUT_LINE" function or other results display methods to display the query results.

The operating environment of this tutorial: Windows 10 system, Oracle version 19c, Dell g3 computer.

Oracle is a relational database management system (RDBMS) that allows users to query data through SQL statements. In Oracle, a tablespace is a logical structure of physical files used to store tables, indexes, and other database objects. Querying the size of table space is one of the important tasks in managing database space. It can help administrators understand the usage of the database and conduct reasonable space planning and management. This article will detail the steps to query the table space size in Oracle.

1: Log in to the Oracle database

First, we need to log in to the Oracle database using a database administrator (DBA) account. You can use SQL Plus tools or other Oracle management tools to log in.

2: Check the table space list

Before querying the table space size, we need to first understand what table spaces there are in the database. You can use the following SQL query to view the list of table spaces in the database:

SELECT tablespace_name FROM dba_tablespaces;

This SQL statement will return the names of all table spaces.

3: Query the table space size

There are many ways to query the table space size. You can choose the appropriate method to use according to your needs. The following are several commonly used methods:

1. Use the dbms_utility package

Oracle provides a dbms_utility package, in which the get_space_usage function can query the usage of table space.

SET SERVEROUTPUT ON;
DECLARE
total_blocks NUMBER;
total_bytes NUMBER;
used_blocks NUMBER;
used_bytes NUMBER;
free_blocks NUMBER;
free_bytes NUMBER;
BEGIN
dbms_utility.get_space_usage('TABLESPACE_NAME',
total_blocks, total_bytes, used_blocks, used_bytes, free_blocks, free_bytes);
DBMS_OUTPUT.PUT_LINE('Total Blocks: ' || total_blocks);
DBMS_OUTPUT.PUT_LINE('Total Bytes: ' || total_bytes);
DBMS_OUTPUT.PUT_LINE('Used Blocks: ' || used_blocks);
DBMS_OUTPUT.PUT_LINE('Used Bytes: ' || used_bytes);
DBMS_OUTPUT.PUT_LINE('Free Blocks: ' || free_blocks);
DBMS_OUTPUT.PUT_LINE('Free Bytes: ' || free_bytes);
END;
/

Replace "TABLESPACE_NAME" in the above SQL code with the name of the table space you want to query, and then execute. This code will return the total blocks, total bytes, used blocks, used bytes, free blocks, and free bytes for the tablespace.

2. Use the dba_segments view

You can use the dba_segments view to query the size information of the segment (segment) of the table space, and sum the sizes of these segments to obtain The total size of the tablespace.

SELECT tablespace_name, SUM(bytes) AS "Total Size"
FROM dba_segments
WHERE tablespace_name = 'TABLESPACE_NAME'
GROUP BY tablespace_name;

Replace "TABLESPACE_NAME" in the above SQL code with the name of the table space you want to query, and then execute. This code will return the total size of the tablespace.

3. Use the dba_data_files view

You can also use the dba_data_files view to query the size of the data files in the table space, and sum the sizes of these files to obtain the table space the total size.

SELECT tablespace_name, SUM(bytes) AS "Total Size"
FROM dba_data_files
WHERE tablespace_name = 'TABLESPACE_NAME'
GROUP BY tablespace_name;

Replace "TABLESPACE_NAME" in the above SQL code with the name of the table space you want to query, and then execute. This code will return the total size of the tablespace.

Four: Display query results

After executing the query statement, you can use the DBMS_OUTPUT.PUT_LINE function or other results display methods to display the query results.

So far, we have completed the detailed steps to query the table space size in Oracle. Through these query methods, we can obtain information about the database table space, so as to carry out reasonable space planning and management and ensure the normal operation of the database.

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