Querying Oracle database table spaces, tables, and data file usage

Table size that has two meanings. One is the amount of physical space allocated to a table, regardless of whether space is used or not. You can get the number of bytes in this query:

Select Segment_name, bytes
From User_segments
where Segment_type = ' TABLE ';
Or
Select segment_name,sum (bytes)/1024/1024 from User_extents Group by segment_name

Another type of space that the table actually uses. This query:

Analyze table EMP Compute statistics;
Select Num_rows * Avg_row_len
From User_tables
WHERE table_name = ' EMP ';

View the size of each table space
Select tablespace_name,sum (bytes)/1024/1024 from Dba_segments Group by Tablespace_name

Table space is the largest logical unit and storage unit in Oracle database, and the database system allocates space for database objects through table space. Table space is physically represented as disk data files, each table space consists of one or more data files, a data file can only be associated with a table space, which is the unity of logic and physics. Understanding the properties and usage of tablespace and data files is an important responsibility for database administrators. The following is an example of oracle9i, which provides a detailed approach to querying Oracle database table space information and data file information.

A way to view table space information in Oracle database

1, view the Oracle database table space Information tool method:

Using the Oracle Enterprise Manager console tool, which is an Oracle client tool, automatically installs this tool when an Oracle server or client is installed, after Oracle installation is completed on the Windows operating system Log on to the tool by using the following method: Start Menu-Program--oracle-orahome92--enterprise Manager console (click)--oracle Enterprise Manager Console Login--select ' Standalone boot ' checkbox--' OK '--' Oracle Enterprise Manager console, standalone '--select ' instance name ' to log in--pop-up ' database connection information '--enter ' username/password ' (General use SYS user), ' Connect identity ' Choose sysdba--' OK ', at this time has successfully logged in to the tool, choose ' Storage '-table space, you will see the following interface, the interface shows the table space name, table space type, district management type, to "trillion" as the unit of the size of the table space, the size of the table space used and table space utilization.

Fig. 1 Table space size and utilization rate

2, view the Oracle database table Space Information command method:

By querying the data dictionary table in the database system (dictionary tables) to get information about the table space, first use the client tools to connect to the database, these tools can be sqlplus character tool, TOAD, pl/sql, etc. After you connect to the database, execute the following query statement:

Select
A.A1 table space Name,
C.C2 type,
C.C3 District Management,
b.b2/1024/1024 table space size M,
(B.B2-A.A2)/1024/1024 has used M,
SUBSTR ((B.B2-A.A2)/b.b2*100,1,5) utilization
From
(Select Tablespace_name a1,sum (NVL (bytes,0)) A2 from Dba_free_space Group by Tablespace_name) A,
(Select Tablespace_name b1,sum (bytes) B2 from Dba_data_files Group by Tablespace_name) B,
(select Tablespace_name c1,contents c2,extent_management c3 from Dba_tablespaces) c
where A.a1=b.b1 and C.C1=B.B1;

This statement through the query dba_free_space,dba_data_files,dba_tablespaces these three data dictionary table, obtained the table space name, the table space type, the district management type, in the "trillion" as the unit table space size, The size of the table space used and the utilization of the table space. Dba_free_space table describes the free size of the table space, Dba_data_files table describes the data files in the database, Dba_tablespaces table describes the table space in the database.

After the FROM clause in the preceding statement, there are three SELECT statements, each of which is equivalent to a view with the name of a, B, and C respectively, and through the association between them, we get information about the table space.

Statement execution results are as follows:

Table Space Name	Type	District Management	Table Space Size M	Already used M	Utilization
Cwmlite	Permanent	Local	20	9.375	46.87
Drsys	Permanent	Local	20	9.6875	48.43
EXAMPLE	Permanent	Local	149.375	149.25	99.91
INDX	Permanent	Local	25	0.0625	0.25
Odm	Permanent	Local	20	9.375	46.87
SYSTEM	Permanent	Local	400	397.375	99.34
TOOLS	Permanent	Local	10	6.0625	60.62
UNDOTBS1	UNDO	Local	200	5.9375	2.968
USERS	Permanent	Local	25	0.0625	0.25
XDB	Permanent	Local	38.125	37.9375	99.5

The tools and command methods for viewing tablespace information in Oracle databases are described in the above description.

Second, query Oracle database data file information method

1, view the Oracle database data file Information tool method:

Using the method described above, log on to the Oracle Enterprise Manager console tool, select ' Storage '--data file, see the following interface, which shows the data file name, table space name, in "megabytes" as the data file size, The size of the data file used and the utilization of the data file.

2. View data file information in Oracle Database command method:

By querying the data dictionary table in the database system (dictionary tables) to obtain relevant information about the data file, first use the client tools to connect to the database, these tools can be sqlplus character tool, TOAD, pl/sql, etc. After you connect to the database, execute the following query statement:

Select
B.file_name Physical file name,
B.tablespace_name table Space,
b.bytes/1024/1024 size M,
(B.bytes-sum (NVL (a.bytes,0))/1024/1024 has used M,
SUBSTR ((B.bytes-sum (NVL (a.bytes,0))/(b.bytes) *100,1,5) utilization
From Dba_free_space A,dba_data_files b
where a.file_id=b.file_id
GROUP BY B.tablespace_name,b.file_name,b.bytes
Order BY B.tablespace_name;

　

Physical file name	Table Space	Size M	Already used M	Utilization
G:/oracle/oradata/ora92/cwmlite01. Dbf	Cwmlite	20