Calculation of Oracle Table space capacity

--Data table space capacity usage

SELECT a.tablespace_name as "table space name",

To_char (b.total/1024/1024,999999.99) | | M ' as ' total capacity ',

To_char ((b.total-a.free)/1024/1024, ' 9999990d99 ') | | M ' as ' used capacity ',

To_char (a.free/1024/1024, ' 9999990d99 ') | | M ' as ' remaining capacity ',

To_char (Round (total-free)/total,4) *100, ' 9999990d99 ') | | % ' as '% used '

From (SELECT tablespace_name, sum (bytes) free from Dba_free_space GROUP by Tablespace_name) A,

(SELECT tablespace_name, sum (bytes) Total from Dba_data_files GROUP by Tablespace_name) b

WHERE A.tablespace_name=b.tablespace_name

Order by Round ((total-free)/total,4) DESC;

--View temporary table space capacity usage

Select D.tablespace_name "tablespace name", Space "total Capacity (M)",

Used_space "used Capacity (M)", Round (NVL (used_space,0)/space*100,2) "percent used (%)",

NVL (free_space,0) "Remaining Capacity (M)"

From

(Select Tablespace_name,round sum (bytes)/(1024*1024), 2) space,sum (blocks) blocks

From Dba_temp_files

Group by Tablespace_name) d,

(Select Tablespace_name,round (SUM (bytes_used)/(1024*1024), 2) Used_space,

Round (sum (bytes_free)/(1024*1024), 2) free_space

From V$temp_space_header

GROUP by Tablespace_name) F

where D.tablespace_name = F.tablespace_name (+)

--undo table Space

SELECT

Seg.tablespace_name "tablespace name",

ts.bytes/1024/1024 "TS Size (MB)",

Ue.status "UNDO status",

COUNT (*) "Used extents",

Round (sum (ue.bytes)/1024/1024, 2) "Used Size (MB)",

Round (sum (ue.bytes)/ts.bytes*100, 2) "Used Rate (%)"

From Dba_segments SEG, dba_undo_extents UE,

(SELECT tablespace_name, sum (bytes) bytes

From Dba_data_files GROUP by tablespace_name) TS

WHERE Ue.segment_name=seg.segment_name and Seg.tablespace_name=ts.tablespace_name

GROUP by Seg.tablespace_name, Ts.bytes, ue.status order by Seg.tablespace_name;

--Storage estimation of table space needs

Calculation formula: Estimate Storage total = on net user number * Average per user daily words singular * per single length (K) * Keep the number of days in the database/1024/1024/0.75

This column more highlights: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/