How to query tablespaces and data files in an Oracle database

Welcome to the Oracle community forum and interact with 2 million technical staff. The tablespace is the largest logical unit and storage space unit in the oracle database. The database system allocates space for database objects through the tablespace. A tablespace is physically a disk data file. Each tablespace is composed of one or more data files.

Welcome to the Oracle community forum and interact with 2 million technical staff> the tablespace is the largest logical unit and storage space unit in the oracle database. The database system allocates space for database objects through the tablespace. A tablespace is physically a disk data file. Each tablespace is composed of one or more data files.

Welcome to the Oracle community forum and interact with 2 million technical staff> enter

The tablespace is the largest logical unit and storage space unit in oracle databases. The database system allocates space for database objects through the tablespace. A tablespace is physically a disk data file. Each tablespace is composed of one or more data files. A data file can only be associated with one tablespace, which is a logical and physical unity. Understanding the attributes and usage of tablespaces and data files is an Important Role of the database administrator. The following uses oracle9i as an example to describe how to query the tablespace information and data file information of an Oracle database.

1. How to view table space information in Oracle Database 1. How to view table space information in Oracle Database: Use the oracle enterprise manager console tool, which is the oracle client tool, this tool is automatically installed when the oracle server or client is installed. After the oracle installation is completed on the windows operating system, log on to the tool using the following methods: start Menu-programs-Oracle-OraHome92-Enterprise Manager Console (click) -log on to the oracle enterprise manager console and choose 'start independently 'single region>' OK '> 'oracle enterprise manager console, standalone '-select the 'instance name' to log on-the 'database connection information' pop-up-enter the 'user name/password' (sys users are generally used ), select SYSDBA for 'Connection Authentication '. Log on to the tool and select "Storage"> "tablespace". The following interface is displayed, showing the tablespace name, tablespace type, and partition management type, the table space size in MB, used table space size, and table space utilization.

Table space size and usage

2. Command Method for viewing table space information in Oracle Database: query the data dictionary tables in the database system to obtain table space information. First, use a client tool to connect to the database, these tools can be SQLPLUS character tools, TOAD, PL/SQL, etc. After connecting to the database, execute the following query statement:

Select
A. a1 tablespace name,
C. c2 type,
C. c3 zone management,
B. b2/1024/1024Tablespace size M,
(B. b2-A. a2)/1024/1024M used,
Substr (B. b2-A. a2)/B. b2*100,1,5) Utilization
From
(SelectTablespace_name a1,Sum(Nvl (bytes,0) A2FromDba_free_spaceGroup ByTablespace_name),
(SelectTablespace_name b1,Sum(Bytes) b2FromDba_data_filesGroup ByTablespace_name) B,
(SelectTablespace_name c1, contents c2, extent_management c3FromDba_tablespaces) c
WhereA. a1=B. b1AndC. c1=B. b1;

This statement queries the three data dictionary tables dba_free_space, dba_data_files, and dba_tablespaces to obtain the tablespace name, tablespace type, partition management type, and tablespace size in MB, used table space size and table space utilization. The dba_free_space table describes the free space of the tablespace, The dba_data_files table describes the data files in the database, and the dba_tablespaces table describes the tablespace in the database.

In the preceding statement, the from clause has three select statements. Each select statement is equivalent to a view. The view names are a, B, and c respectively, we get information about the tablespace.

The statement execution result is as follows:

The preceding descriptions describe how to view table space information in Oracle databases.

[1] [2] [3]