In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-03-17 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)06/01 Report--
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.
Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.
Views: 0
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.