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

Source: Internet
Author: User
Tags system log oracle database sqlplus

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
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.