An idea of Recycling Database tablespace

Some projects are relatively small, and the hard disk space is only more than 40 gigabytes. Coupled with the fact that there is no maintenance, the problem of hard disk space alarm appears over time. After looking at it, it is found that some data files are set to 2G at the beginning, but only about 100m may actually be used. In order to solve the problem of hard disk space alarm, I came up with the method of resetting the size of the data file.

The first step is to look at the application rate of each tablespace and find out the data files that can be shrunk.

-- check tablespace usage and find idle tablespaces

SELECT Upper (F.TABLESPACE_NAME) "tablespace name"

D.TOT_GROOTTE_MB Tablespace size (M)

D.TOT_GROOTTE_MB-F.TOTAL_BYTES "Space used (M)"

To_char (Round ((D.TOT_GROOTTE_MB-F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2), '990.99')

| |'%'"usage ratio" |

F.TOTAL_BYTES "Free Space"

F.MAX_BYTES "maximum Block (M)"

FROM (SELECT TABLESPACE_NAME

Round (Sum (BYTES) / (1024 * 1024), 2) TOTAL_BYTES

Round (Max (BYTES) / (1024 * 1024), 2) MAX_BYTES

FROM SYS.DBA_FREE_SPACE

GROUP BY TABLESPACE_NAME) F

(SELECT DD.TABLESPACE_NAME

Round (Sum (DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB

FROM SYS.DBA_DATA_FILES DD

GROUP BY DD.TABLESPACE_NAME) D

WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME

ORDER BY 4 desc

The second step is to look at the high water mark of the selected tablespace data file, taking the USERS tablespace as an example.

-- look up the high waterline of the tablespace

SELECT *

FROM (SELECT / * + ordered use_hash (amembjinc) * /

A.file_id

A.file_name

A.filesize

B.freesize

(a.filesize-b.freesize) usedsize

C.hwmsize,-- high water mark

C.hwmsize-(a.filesize-b.freesize) unsedsize_belowhwm

A.filesize-c.hwmsize canshrinksize

FROM (SELECT file_id

File_name

Round (bytes / 1024 / 1024) filesize

FROM dba_data_files) a

(SELECT file_id, round (SUM (dfs.bytes) / 1024 / 1024) freesize

FROM dba_free_space dfs

GROUP BY file_id) b

(SELECT file_id, round (MAX (block_id) * 8 / 1024) HWMsize

FROM dba_extents

GROUP BY file_id) c

WHERE a.file_id = b.file_id

AND a.file_id = c.file_id

ORDER BY unsedsize_belowhwm DESC)

WHERE file_id IN (SELECT file_id

FROM dba_data_files

WHERE tablespace_name = 'USERS')

ORDER BY file_id

The third step is to log in using the administrator account and execute:

Alter database datafile file_id resize N M _ (map) G ~ (th) is an arbitrary integer.

Alter database datafile file_id autoextend on next 100M maxsize 1G

Through the above three steps, the problem of disk space alarm can be solved temporarily.