Query the table size and tablespace size in Oracle

The table size has two meanings. One is the number of physical spaces allocated to a table, regardless of whether the space is used or not. You can query the number of bytes as follows:

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

Space actually used by another table. Query as follows:

Analyze table EMP compute statistics;
Select num_rows * avg_row_len
From user_tables
Where table_name = 'emp ';

View the size of each tablespace
Select tablespace_name, sum (bytes)/1024/1024 from dba_segments group by tablespace_name

1. view the remaining tablespace size
 
Select tablespace_name tablespace, sum (blocks * 8192/1000000) residual space M from dba_free_space group by tablespace_name;
 
2. Check the total space of all tablespaces in the system.
Select B. name, sum (. bytes/1000000) total space from V $ datafile A, V $ tablespace B where. TS # = B. TS # group by B. name;
1. How to view table space information in Oracle databases:

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 method: Start Menu --Program-- Oracle-OraHome92 -- Enterprise Manager Console (click) -- Oracle Enterprise Manager Console Logon -- select 'standalone start' 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 --" OK "as the 'Connection authentic'. Then, you have successfully logged on to the tool and selected" Storage ">" tablespace ". The following interface is displayed, showing the tablespace name, tablespace type, partition management type, tablespace size in MB, used tablespace size, and tablespace utilization.

Figure 1 Table space size and usage

2. Command Method for viewing tablespace information in Oracle Database:

Query the data dictionary tables in the database system to obtain information about the tablespace. First, use the 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/1024 tablespace size m,

(B. b2-a.a2)/1024/1024 m used,

Substr (B. b2-a.a2)/B. B2 *, 1, 5) Utilization

From

(Select tablespace_name A1, sum (nvl (bytes, 0) A2 from dba_free_space group by tablespace_name),

(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 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:

Tablespace name type partition management tablespace size m 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 preceding descriptions describe how to view table space information in Oracle databases.

1. How to view the data file information in the Oracle database:

Log on to the Oracle Enterprise Manager Console using the method described above and select 'store'-data file. The following interface is displayed, showing the data file name and tablespace name, data File Size in MB, used data file size, and data file utilization rate.

Figure 2 data file size and usage

2. Command Method for viewing data file information in Oracle Database:

Query the data dictionary tables in the database system to obtain information about the data file. First, use the 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

B. file_name physical file name,

B. tablespace_name tablespace,

B. Bytes/1024/1024 size m,

(B. bytes-sum (nvl (A. bytes, 0)/1024/1024 m used,

Substr (B. bytes-sum (nvl (A. bytes, 0)/(B. bytes) *, 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

The statement execution result is as follows:

Physical file name tablespace size m used M Utilization
G: \ oracle \ oradata \ ora92 \ cwmlite01.dbf cwmlite 20 9.375 46.87
G: \ oracle \ oradata \ ora92 \ drsys01.dbf drsys 20 9.6875 48.43
G: \ oracle \ oradata \ ora92 \ example01.dbf example 149.375 149.25
G: \ oracle \ oradata \ ora92 \ indx01.dbf indx 25 0.0625 0.25
G: \ oracle \ oradata \ ora92 \ odm01.dbf ODM 20 9.375 46.87
G: \ oracle \ oradata \ ora92 \ system01.dbf system 400 397.375
G: \ oracle \ oradata \ ora92 \ tools01.dbf tools 10 6.0625 60.62
G: \ oracle \ oradata \ ora92 \ undotbs01.dbf undotbs1 200 5.9375 2.968
G: \ oracle \ oradata \ ora92 \ users01.dbf users 25 0.0625 0.25
G: \ oracle \ oradata \ ora92 \ xdb01.dbf XDB 38.125 37.9375
The preceding description introduces the tool and command methods for viewing data file information in the Oracle database.

In Oracle databases, temporary tablespaces are mainly used for temporary workspace that you need to sort and summarize using order by and group by statements. To query the name, size, and data file of the temporary tablespace in the database, you can query the data dictionary dba_tablespaces and dba_data_files. The command is as follows:

Select

A. talbespace_name tablespace name,

B. bytes size bytes,

B. file_name data file name

From dba_tablespaces A, dba_data_files B

Where a. talbespace_name = B. talbespace_name and A. Contents = 'temporary ';

The query result is as follows:

Tablespace name size bytes data file name

Temponlineg: \ oracle \ oradata \ ora92 \ temp01.dbf

From Oracle 9i, you can create a temporary tablespace, that is, a "temporary" tablespace, which uses temporary files. Temporary File information is stored in the data dictionary v $ tempfile. The command is as follows:

Select File #, status, name from V $ tempfile;

The query result of the data dictionary v $ tempfile is as follows:

File # status name
1 online G: \ oracle \ oradata \ ora92 \ temp01.dbf
In the method described above, it is recommended to master the command method, because your environment may not have a graphical tool, and sqlplus is generally usable, with the command script, it is easy to obtain information about tablespaces and data files. In addition, the database administrator should organize more command scripts and directly execute the scripts as needed to improve work efficiency.

In the daily work of the database administrator, you should frequently query the table space utilization and estimate the growth of the table space according to the specific situation of the database system. When the utilization of the table space exceeds 90%, you should take timely measures, such as clearing historical tables and historical data to release space, adding new data files to tablespaces, and extending the size of existing data files to reduce the utilization of tablespaces, so as to prevent the utilization of tablespaces from approaching 100%, errors with insufficient space will be generated.

1. query the usage of Oracle tablespace

Select B. file_id File ID,
B. tablespace_name tablespace,
B. file_name physical file name,
B. Total Bytes bytes,
(B. bytes-sum (nvl (A. bytes, 0) already in use,
Sum (nvl (A. bytes, 0) remaining,
Sum (nvl (A. bytes, 0)/(B. bytes) * 100 percentage remaining
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. file_id, B. bytes
Order by B. tablespace_name

 

2. query the default and temporary tablespaces of oracle users

Select default_tablespace, temporary_tablespace from dba_users

 

3. query the usage of a single table

Select segment_name, bytes from dba_segments where segment_name ='re _ stdevt_fact_day 'and owner = user

Re_stdevt_fact_day is the name of the table to be queried.

 

4. query the first thirty tables of all users.

Select * from (select segment_name, bytes from dba_segments where owner = user order by Bytes DESC) Where rownum <= 30

 

5. query the usage of the default tablespace of the current user.

Select tablespacename, sum (totalcontent), sum (usecontent), sum (sparecontent), AVG (sparepercent)
From
(
Select B. file_id as ID, B. tablespace_name as tablespacename, B. bytes as totalcontent, (B. bytes-sum (nvl (. bytes, 0) as usecontent, sum (nvl (. bytes, 0) as sparecontent, sum (nvl (. bytes, 0)/(B. bytes) * 100 as sparepercent
From dba_free_space A, dba_data_files B
Where a. file_id = B. file_id and B. tablespace_name = (select default_tablespace from dba_users where username = user)
Group by B. tablespace_name, B. file_name, B. file_id, B. bytes
)
Group by tablespacename

 

6. query the table in the user tablespace

Select * From user_tables

 

this article from the csdn blog, reprinted please indicate the source: http://blog.csdn.net/cosio/archive/2009/03/11/3978747.aspx