Oracle Disk space Usage statistics

Source: Internet
Author: User

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

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.