Oracle tablespace reduction

If the database has been used for a long time, there will inevitably be no space to recycle. If the space is too large, you can use the following method to recycle it.

The space occupied by the query tables:

 
Selecttablespace_name, 100 * (sum_max-sum_alloc + nvl (sum_free, 0)/sum_max as capa_per, (sum_max-sum_alloc + nvl (sum_free, 0)/1024/1024 as capa_free, (sum_alloc-nvl (sum_free, 0)/1024/1024 as capa_used, sum_max/1024/1024 as capa_max, 100 * nvl (sum_free, 0)/sum_alloc as per, nvl (sum_free, 0) /1024/1024 as free, (sum_alloc-nvl (sum_free, 0)/1024/1024 as used, sum_alloc/1024/1024 as maxfrom (select tablespace_name, sum (bytes) as sum_alloc, sum (decode (maxbytes, 0, bytes, maxbytes) as sum_maxfrom dba_data_filesgroup by tablespace_name), (select tablespace_name as fs_ts_name, sum (bytes) as sum_freefrom created by tablespace_name) where tablespace_name = fs_ts_name (+) order by 2, 3;
 

Max is the disk space currently occupied (unit: MB), used is the actual space required for data, and free is the available space.

Assume that the system table max is 10240 M, used is 600 m, and data is stored in the D: \ oradata \ DBA directory. Use the following command to reduce the system tablespace to 610 m:

 
Alter database datafile 'd: \ oradata \ DBA \ system01.dbf' resize 610 m;
 

Other tablespace (such as undotbs1 and indx) methods are the same.