Oracle Disk space Usage statistics

For large databases, Oracle occupies a very large amount of disk space, mastering those users in the database, the table takes up more disk space, and the growth situation, you can easily maintain and expand the disk system later.

Oracle disk space usage can be divided into a table space, a user, or a table for statistics.

(a), table space

Calculate the remaining size of a table space

Select a.tablespace_name,a.bytes/(1024*1024*1024) "Space (G)", c.bytes/(1024*1024) "Free SPACE (M)", (c.bytes*100)/A. BYTES '% free ' from SYS. Sm$ts_avail A,sys. Sm$ts_free C WHERE A.tablespace_name=c.tablespace_name;

Or

Select Tablespace_name, sum (bytes)/(1024*1024*1024) "SPACE (G)" from Dba_free_space Group by Tablespace_name;

(ii), user

Calculate the disk space consumed by each user

Select Owner,sum (bytes)/1024/1024/1024 "Space (G)" from Dba_segments Group by owner order by 2;

Calculate the disk space occupied by a user

Select Owner,sum (bytes)/1024/1024/1024 "Space (G)" from dba_segments where owner= ' Liaojl ' GROUP by owner;

(iii), table

Oracle is stored as a field, Segment_name contains tables, indexes, rollback segments, and so on, so you can find the space-consuming information in dba_extents,dba_segments.

Select sum (bytes)/1024/1024 "Space (M)" from dba_extents where owner= ' liaojl ' and segment_name= ' STUDENTS ';

Dba_segments can also calculate the size of the table:

Select segment_name,bytes/1024/1024 "Space (MB)" from dba_segments where segment_type= ' TABLE ' and Segment_name=upper (' The name of the table you are looking for ');

At that time the above writing is not exactly correct, when the table is a partitioned table, Dba_segments has a number of information, can be changed to:

Select Segment_name,sum (bytes)/1024/1024 "Space (MB)" from Dba_segments where Segment_name=upper (' The name of the table you are looking for ');

The above method for a large database, SQL statements can be slow to execute, and consume database resources. Oracle supports analysis of tables, which can query table size, number of rows, etc. in system tables such as Dba_tables, but this information is not updated in real time and can be updated by scheduled tasks when the database is idle.

Analytic SQL Method:

Analyze table Tab_name compute statistics;

If the table is too large, you can do it:

Analyze table tab_name estimate statistics; /*select tablespace_name, sum (blocks) as FREE_BLK, trunc (sum (bytes)/(1024*1024)) as Free_m, max (bytes)/ (1024x768) as Big_chunk_k, COUNT (*) as num_chunks from Dba_free_space GROUP by tablespace_name;*/
/*
* SQLNAME:OBJ_SEG
* 2006-11-17 by Huiyi
* Check the size of the image
*/
Select Owner,sum (bytes)/1024/1024/1024 "Space (G)" from Dba_segments Group by owner ORDER by 2 desc COL segment_name format A30
Col segment_type format A15
Col tablespace_name format A10
Col bytes heading "Size (M)"
With T as (
Select Segment_name,owner,owner| | '. ' | | Segment_name,
Segment_type,
Tablespace_name,
bytes/1024/1024 bytes
From Dba_segments
where owner = Upper (' &user ')
Order BY bytes Desc
)
SELECT * FROM t where rownum<10;

Oracle Disk space Usage statistics