How to query table space usage

Table space monitoring

The use of tablespace space is a problem that requires special attention. Because the data files cannot be expanded, the tablespace space is insufficient, and new data may not be written, and even causes the database to stop. The following statement can be used to monitor the space utilization of a tablespace.9iTo detect the temporary tablespace.

Select D. tablespace_name, space "sum_space (m)", blocks sum_blocks, SPACE-NVL (free_space, 0 ) "Used_space (m )",
Round (( 1 -Nvl (free_space, 0 )/Space )* 100 , 2 ) "Used_rate (%)", free_space "free_space (m )"
From
(SelectTablespace_name, round (Sum(Bytes )/( 1024 * 1024 ), 2 ) Space,Sum(Blocks) Blocks
FromDba_data_files
Group ByTablespace_name) d,
(SelectTablespace_name, round (Sum(Bytes )/( 1024 * 1024 ), 2 ) Free_space
FromDba_free_space
Group ByTablespace_name) f
WhereD. tablespace_name = f. tablespace_name (+)
-- If the temporary tablespace is fully locally managed, add the following parts:
Union All  -- If have tempfile
SelectD. tablespace_name, space "sum_space (m)", blocks sum_blocks,
Used_space "used_space (m)", round (nvl (used_space, 0 )/Space * 100 , 2 ) "Used_rate (% )",
Nvl (free_space, 0 ) "Free_space (m )"
From
(SelectTablespace_name, round (Sum(Bytes )/( 1024 * 1024 ), 2 ) Space,Sum(Blocks) Blocks
FromDba_temp_files
Group ByTablespace_name) d,
(SelectTablespace_name, round (Sum(Bytes_used )/( 1024 * 1024 ), 2 ) Used_space,
Round (Sum(Bytes_free )/( 1024 * 1024 ), 2 ) Free_space
FromV $ temp_space_header
Group ByTablespace_name) f
WhereD. tablespace_name = f. tablespace_name (+)

 

Segment space utilization monitoring

Segment space and interval utilization are particularly important in the tablespace managed by the dictionary. If an object has too many partitions, this not only greatly increases the management burden of dictionary tables and the pressure on system rollback segments, but also seriously affects the performance of the segments (such as tables or indexes. The query also shows the difference between the currently used range and the maximum number of intervals. If the difference is small, you need to pay attention to the allocation of new spaces, avoid writing errors to new data because new intervals cannot be allocated.

Select S. Owner, S. segment_name, S. segment_type, S. partition_name,
Round (Bytes /( 1024 * 1024 ), 2 ) "Used_space (m )",
ExtentsUsed_extents, S. max_extents, S. Blocks allocated_blocks,
S. Blocks used_bolcks, S. pct_increase, S. initial_extent,
S. next_extent/ 1024 "Next_extent (k)", S. tablespace_name
FromDba_segments s
WhereS. Owner =User
Order ByUsed_extentsDesc