SLTechnology News&Howtos › Database ›

Query whether the table space is automatically extended tablespace / auto

Shulou Source: shulou.com Published: 2022-06-01 16:11:22 09月30日 Update

Set pagesize 1000

Col tablespace_name for a14

Select d.tablespaceplayName space "sum_space (m)", blocks sum_blocks,space-nvl (free_space,0) "used_space (m)"

Round ((1-nvl (free_space,0) / space) * 100Pol 2) "used_rate (%)", free_space "free_space (m)"

From

(select tablespace_name,round (sum (bytes) / (1024 / 1024), 2) space,sum (blocks) blocks

From dba_data_files

Group by tablespace_name) d

(select tablespace_name,round (sum (bytes) / (1024 / 1024), 2) free_space

From dba_free_space

Group by tablespace_name) f

Where d.tablespace_name = f.tablespace_name (+) union all

Select d.tablespaceplayName 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

(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 (+)

Find out the usage of the table space through the SQL above, and find out whether the table space is automatically expanded by the following SQL.

SQL > select STATUS,AUTOEXTENSIBLE from dba_data_files where tablespace_name='ISS_UPDATE'

STATUS AUT

AVAILABLE NO