When the data of Oracle database takes up a large space, and the data stored in it does not take up such a large space, the reason may be that the user has deleted some data, but the size of the data file will not automatically shrink. At this time, if you want to reduce the size of the data file, you can use the following methods.

1、 Use the following statement to query data files that can free up space:

select a.file#,

a.name,

a.bytes/1024/1024 CurrentMB,

ceil(HWM * a.block_ size)/1024/1024 ResizeTo,

(a.bytes – HWM * a.block_ size)/1024/1024 ReleaseMB,

‘alter database datafile ”’ || a.name || ”’ resize ‘ ||

ceil(HWM * a.block_ size)/1024/1024 || ‘M;’ ResizeCmd

from v$datafile a,

(SELECT file_ id, MAX(block_ id + blocks – 1) HWM

FROM DBA_ EXTENTS

GROUP BY file_ id) b

where a.file# = b.file_ id(+)

And (a.bytes – HWM * a.block_ size) >0

and rownum < 10

View the data file that belongs to the system table space, and reset it.

2、 Find out the data file that needs to be reset, and execute the reset statement

An error is reported because the reset data file size needs to be set to an integer.

Adjust the size of resize to 16GB, 16384mb;

3、 View free disk space

The size of the system file is reduced to 16GB, the remaining space of the root disk is greatly increased to 19.6gb, and the utilization rate is reduced to 78%.