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: