Querying the actual physical use size of an Oracle table ____oracle

Source: Internet
Author: User

Table size with two meanings in Oracle
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;
The effect is as follows:


Or
Select segment_name,sum (bytes)/1024/1024 from User_extents Group by Segment_name;
The effect is as follows: "This query speed is slow"

As shown in the two figure, the query is assigned to the size of the table by 10896M
The following figure queries the actual amount of space currently occupied by the table

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;--emp for Table name (table name uppercase query)
The effect is as follows:

The table actually occupies about 9506M of space. &NBSP
 
 
Attach:
View the size of each table space
Select tablespace_name,sum (bytes)/1024/1024 from dba_segments Group by tablespace_name 
1. View remaining table space size
 
SELECT tablespace_name table space, sum (blocks*8192/1000000) Remaining space m from Dba_free_space GROUP by Tablespace_name;
 
2. Check the overall space in the system for all tablespace
Select B.name,sum (a.bytes/1000000) total space from V$datafile A,v$tablespace b where a.ts#= b.ts# GROUP BY B.name;
 
3. Query the total database for remaining and using the size of the table space used:
Select Df.tablespace_name "tablespace name", Totalspace "General space M", freespace "remaining space M", Round ((1-freespace/totalspace) *100,2) "Usage%"  
from 
(select Tablespace_name,round (sum (bytes)/ 1024/1024) totalspace 
from dba_data_files 
Group by Tablespace_name df, 
(select Tablespace_name,round (sum (bytes)/1024/1024) freespace 
from dba_free_space 
GROUP BY Tablespace_ Name) fs  the
where Df.tablespace_name=fs.tablespace_name
effect is as follows:
 

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.