View tablespace actual usage and remaining space

Source: Internet
Author: User

Add and value to all segments of a table space, grouped by table space
Dba_data_files.bytes-dba_free_space.bytes=dba_segments.bytes
Dba_segments can query the allocated segment space size (dba_extents is the detailed table in Dba_segments, segment is composed of multiple extent), the total table space

Size can be queried dba_data_files to obtain, the remaining size can be queried dba_free_space obtained, the two subtraction is the amount of space allocated can be analyzed by the table after the query Dba_tables (the following +1 is the segment head block) Dba_ tables.blocks+ Dba_tables.empty_blocks+1=user_extents.blocks=user_segments.blocks

Other than that:
Average President * Total number of rows = Table Data volume size
Here is the amount of data that Oracle allocates to this table is naturally larger than this, and is subject to pctfree restrictions and whether the table has been deleted, such as delete a lot of records, space is not freed, allocated space is wasted more
An extreme is the table parameter setting pctfree=0 initial and Next are also small, such as 16K, and then insert the data
For example, 10M of data, and then analysis of the table, then this situation is very high space utilization, calculated according to the above formula, the results
will be close to the value of dba_segments allocated space, almost equal. Approximate volume algorithm: Query tablespace usage: Select Tablespace_name,sum (bytes)/1024/1024 from dba_extents where Tablespace_name= ' Mis_ Tablespace ' GROUP by Tablespace_name

Query tablespace remaining space: Select Tablespace_name,round (sum (bytes)/1024/1024,2) "FREE_MB" from Dba_free_space Group by Tablespace_name

Query the size of the hard disk that the table occupies: SELECT table_name, Blocks * (select value from V$parameter where name= ' db_block_size ')/1024/1024 as Size_m b from User_tables where table_name=table_name

View tablespace actual usage and remaining space

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.