hana数据库的磁盘碎片化整理操作手册

一、操作背景  
因PO只是一个中间件，负责将外围系统和SAP连接的工具。但查看PO hana数据库数据文件占用多，达到1\.8T 比生产S4 数据库数据文件还大，故想要把数据文件给降下来  
![](/media/202508/20250828140302463743.png)  
  
  
二、操作准备  
  
先在PO uat环境进行测试，测试前先将uat 数据库停掉 进行打快照 打完快照后启动数据库

后在hana studio 中执行查看磁盘碎片率的sql 在租户数据库执行  
SELECT

/\*

\[NAME]

\- HANA\_Disks\_Overview

\[DESCRIPTION]

\- Disk space allocation and fragmentation information

\[SOURCE]

\- SAP Note 1969700

\[DETAILS AND RESTRICTIONS]

\[VALID FOR]

\- Revisions: all

\[SQL COMMAND VERSION]

\- 2014/09/22: 1\.0 (initial version)

\- 2016/02/14: 1\.1 (history included)

\- 2016/12/31: 1\.2 (TIME\_AGGREGATE\_BY \= 'TS\<seconds\>' included)

\- 2017/02/02: 1\.3 (M\_PERSISTENCE\_ENCRYPTION\_STATUS included)

\- 2018/12/04: 1\.4 (shortcuts for BEGIN\_TIME and END\_TIME like 'C', 'E\-S900' or 'MAX')

\[INVOLVED TABLES]

\- M\_VOLUME\_FILES

\- HOST\_VOLUME\_FILES

\- M\_PERSISTENCE\_ENCRYPTION\_STATUS

\[INPUT PARAMETERS]

\- BEGIN\_TIME

Begin time

'2018/12/05 14:05:00' \-\-\> Set begin time to 5th of December 2018, 14:05

'C' \-\-\> Set begin time to current time

'C\-S900' \-\-\> Set begin time to current time minus 900 seconds

'C\-M15' \-\-\> Set begin time to current time minus 15 minutes

'C\-H5' \-\-\> Set begin time to current time minus 5 hours

'C\-D1' \-\-\> Set begin time to current time minus 1 day

'C\-W4' \-\-\> Set begin time to current time minus 4 weeks

'E\-S900' \-\-\> Set begin time to end time minus 900 seconds

'E\-M15' \-\-\> Set begin time to end time minus 15 minutes

'E\-H5' \-\-\> Set begin time to end time minus 5 hours

'E\-D1' \-\-\> Set begin time to end time minus 1 day

'E\-W4' \-\-\> Set begin time to end time minus 4 weeks

'MIN' \-\-\> Set begin time to minimum (1000/01/01 00:00:00\)

\- END\_TIME

End time

'2018/12/08 14:05:00' \-\-\> Set end time to 8th of December 2018, 14:05

'C' \-\-\> Set end time to current time

'C\-S900' \-\-\> Set end time to current time minus 900 seconds

'C\-M15' \-\-\> Set end time to current time minus 15 minutes

'C\-H5' \-\-\> Set end time to current time minus 5 hours

'C\-D1' \-\-\> Set end time to current time minus 1 day

'C\-W4' \-\-\> Set end time to current time minus 4 weeks

'B\+S900' \-\-\> Set end time to begin time plus 900 seconds

'B\+M15' \-\-\> Set end time to begin time plus 15 minutes

'B\+H5' \-\-\> Set end time to begin time plus 5 hours

'B\+D1' \-\-\> Set end time to begin time plus 1 day

'B\+W4' \-\-\> Set end time to begin time plus 4 weeks

'MAX' \-\-\> Set end time to maximum (9999/12/31 23:59:59\)

\- HOST

Host name

'saphana01' \-\-\> Specific host saphana01

'saphana%' \-\-\> All hosts starting with saphana

'%' \-\-\> All hosts

\- PORT

Port number

'30007' \-\-\> Port 30007

'%03' \-\-\> All ports ending with '03'

'%' \-\-\> No restriction to ports

\- FILE\_NAME

File name

'alert.trc' \-\-\> File with name alert.trc

'%dat' \-\-\> File with name ending with 'dat'

'%' \-\-\> All files

\- MIN\_UNUSED\_SIZE\_GB

Minimum unused size to be displayed

10 \-\-\> Only display files with at least 10 GB of unused size

\-1 \-\-\> No restriction related to unused size

\- MIN\_FRAGMENTATION\_PCT

Minimum fragmentation in percent

30 \-\-\> Minimum fragmentation of 30 %

\-1 \-\-\> No restriction based on fragmentation

\- DATA\_SOURCE

Source of analysis data

'CURRENT' \-\-\> Data from memory information (M\_ tables)

'HISTORY' \-\-\> Data from persisted history information (HOST\_ tables)

\- AGGREGATE\_BY

Aggregation criteria (possible values can be found in comment)

'TYPE' \-\-\> Aggregation by file type

'HOST, PORT' \-\-\> Aggregation by host and port

'NONE' \-\-\> No aggregation

\- TIME\_AGGREGATE\_BY

Aggregation criteria (possible values can be found in comment)

'HOUR' \-\-\> Aggregation by hour

'YYYY/WW' \-\-\> Aggregation by calendar week

'TS\<seconds\>' \-\-\> Time slice aggregation based on \<seconds\> seconds

'NONE' \-\-\> No aggregation

\[OUTPUT PARAMETERS]

\- SNAPSHOT\_TIME: Snapshot timestamp

\- HOST: Host name

\- PORT: Port

\- FILE\_NAME: File name

\- FILE\_TYPE: File type

\- E: 'X' if file system is encrypted, otherwise ' '

\- TOTAL\_GB: Total space (GB)

\- USED\_GB: Used space (GB)

\- UNUSED\_GB: Unused space (GB)

\- FRAG\_PCT: Percentage of unused space compared to total space in file

\[EXAMPLE OUTPUT]

\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-

\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-

\|saphana1db\|30203\|/hana/data/PRD/mnt00001/hdb00002/datavolume\_0000\.dat\|DATA \| 33\.95\| 24\.16\| 9\.79\| 28\.84\|

\|saphana1db\|30205\|/hana/data/PRD/mnt00001/hdb00003/datavolume\_0000\.dat\|DATA \| 5\.43\| 2\.88\| 2\.55\| 47\.01\|

\|saphana1db\|30207\|/hana/data/PRD/mnt00001/hdb00004/datavolume\_0000\.dat\|DATA \| 0\.25\| 0\.06\| 0\.18\| 73\.93\|

\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-

\*/

SNAPSHOT\_TIME,

HOST,

LPAD(PORT, 5\) PORT,

FILE\_NAME,

FILE\_TYPE,

ENCRYPTION E,

LPAD(TO\_DECIMAL(TOTAL\_SIZE\_GB, 10, 2\), 8\) TOTAL\_GB,

LPAD(TO\_DECIMAL(USED\_SIZE\_GB, 10, 2\), 8\) USED\_GB,

LPAD(TO\_DECIMAL(TOTAL\_SIZE\_GB \- USED\_SIZE\_GB, 10, 2\), 9\) UNUSED\_GB,

LPAD(TO\_DECIMAL(MAP(TOTAL\_SIZE\_GB, 0, 0, ( 1 \- USED\_SIZE\_GB / TOTAL\_SIZE\_GB ) \* 100\), 10, 2\), 8\) FRAG\_PCT

FROM

( SELECT

CASE

WHEN AGGREGATE\_BY \= 'NONE' OR INSTR(AGGREGATE\_BY, 'TIME') !\= 0 THEN

CASE

WHEN TIME\_AGGREGATE\_BY LIKE 'TS%' THEN

TO\_VARCHAR(ADD\_SECONDS(TO\_TIMESTAMP('2014/01/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS'), FLOOR(SECONDS\_BETWEEN(TO\_TIMESTAMP('2014/01/01 00:00:00',

'YYYY/MM/DD HH24:MI:SS'), SNAPSHOT\_TIME) / SUBSTR(TIME\_AGGREGATE\_BY, 3\)) \* SUBSTR(TIME\_AGGREGATE\_BY, 3\)), 'YYYY/MM/DD HH24:MI:SS')

ELSE TO\_VARCHAR(SNAPSHOT\_TIME, TIME\_AGGREGATE\_BY)

END

ELSE 'any'

END SNAPSHOT\_TIME,

HOST,

PORT,

FILE\_NAME,

FILE\_TYPE,

ENCRYPTION,

AVG(USED\_SIZE\_GB) USED\_SIZE\_GB,

AVG(TOTAL\_SIZE\_GB) TOTAL\_SIZE\_GB,

MIN\_UNUSED\_SIZE\_GB,

MIN\_FRAGMENTATION\_PCT

FROM

( SELECT

F.SNAPSHOT\_TIME,

CASE WHEN BI.AGGREGATE\_BY \= 'NONE' OR INSTR(BI.AGGREGATE\_BY, 'HOST') !\= 0 THEN F.HOST ELSE MAP(BI.HOST, '%', 'any', BI.HOST) END HOST,

CASE WHEN BI.AGGREGATE\_BY \= 'NONE' OR INSTR(BI.AGGREGATE\_BY, 'PORT') !\= 0 THEN TO\_VARCHAR(F.PORT) ELSE MAP(BI.PORT, '%', 'any', BI.PORT) END PORT,

CASE WHEN BI.AGGREGATE\_BY \= 'NONE' OR INSTR(BI.AGGREGATE\_BY, 'FILE') !\= 0 THEN F.FILE\_NAME ELSE MAP(BI.FILE\_NAME, '%', 'any', BI.FILE\_NAME) END FILE\_NAME,

CASE WHEN BI.AGGREGATE\_BY \= 'NONE' OR INSTR(BI.AGGREGATE\_BY, 'TYPE') !\= 0 THEN F.FILE\_TYPE ELSE MAP(BI.FILE\_TYPE, '%', 'any', BI.FILE\_TYPE) END FILE\_TYPE,

CASE WHEN BI.AGGREGATE\_BY \= 'NONE' OR INSTR(BI.AGGREGATE\_BY, 'ENCRYPTION') !\= 0 THEN MAP(E.ENCRYPTION\_ACTIVE, 'TRUE', 'X', '') ELSE 'any' END ENCRYPTION,

SUM(F.USED\_SIZE) / 1024 / 1024 / 1024 USED\_SIZE\_GB,

SUM(F.TOTAL\_SIZE) / 1024 / 1024 / 1024 TOTAL\_SIZE\_GB,

BI.MIN\_UNUSED\_SIZE\_GB,

BI.MIN\_FRAGMENTATION\_PCT,

BI.AGGREGATE\_BY,

BI.TIME\_AGGREGATE\_BY

FROM

( SELECT

CASE

WHEN BEGIN\_TIME \= 'C' THEN CURRENT\_TIMESTAMP

WHEN BEGIN\_TIME LIKE 'C\-S%' THEN ADD\_SECONDS(CURRENT\_TIMESTAMP, \-SUBSTR\_AFTER(BEGIN\_TIME, 'C\-S'))

WHEN BEGIN\_TIME LIKE 'C\-M%' THEN ADD\_SECONDS(CURRENT\_TIMESTAMP, \-SUBSTR\_AFTER(BEGIN\_TIME, 'C\-M') \* 60\)

WHEN BEGIN\_TIME LIKE 'C\-H%' THEN ADD\_SECONDS(CURRENT\_TIMESTAMP, \-SUBSTR\_AFTER(BEGIN\_TIME, 'C\-H') \* 3600\)

WHEN BEGIN\_TIME LIKE 'C\-D%' THEN ADD\_SECONDS(CURRENT\_TIMESTAMP, \-SUBSTR\_AFTER(BEGIN\_TIME, 'C\-D') \* 86400\)

WHEN BEGIN\_TIME LIKE 'C\-W%' THEN ADD\_SECONDS(CURRENT\_TIMESTAMP, \-SUBSTR\_AFTER(BEGIN\_TIME, 'C\-W') \* 86400 \* 7\)

WHEN BEGIN\_TIME LIKE 'E\-S%' THEN ADD\_SECONDS(TO\_TIMESTAMP(END\_TIME, 'YYYY/MM/DD HH24:MI:SS'), \-SUBSTR\_AFTER(BEGIN\_TIME, 'E\-S'))

WHEN BEGIN\_TIME LIKE 'E\-M%' THEN ADD\_SECONDS(TO\_TIMESTAMP(END\_TIME, 'YYYY/MM/DD HH24:MI:SS'), \-SUBSTR\_AFTER(BEGIN\_TIME, 'E\-M') \* 60\)

WHEN BEGIN\_TIME LIKE 'E\-H%' THEN ADD\_SECONDS(TO\_TIMESTAMP(END\_TIME, 'YYYY/MM/DD HH24:MI:SS'), \-SUBSTR\_AFTER(BEGIN\_TIME, 'E\-H') \* 3600\)

WHEN BEGIN\_TIME LIKE 'E\-D%' THEN ADD\_SECONDS(TO\_TIMESTAMP(END\_TIME, 'YYYY/MM/DD HH24:MI:SS'), \-SUBSTR\_AFTER(BEGIN\_TIME, 'E\-D') \* 86400\)

WHEN BEGIN\_TIME LIKE 'E\-W%' THEN ADD\_SECONDS(TO\_TIMESTAMP(END\_TIME, 'YYYY/MM/DD HH24:MI:SS'), \-SUBSTR\_AFTER(BEGIN\_TIME, 'E\-W') \* 86400 \* 7\)

WHEN BEGIN\_TIME \= 'MIN' THEN TO\_TIMESTAMP('1000/01/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS')

WHEN SUBSTR(BEGIN\_TIME, 1, 1\) NOT IN ('C', 'E', 'M') THEN TO\_TIMESTAMP(BEGIN\_TIME, 'YYYY/MM/DD HH24:MI:SS')

END BEGIN\_TIME,

CASE

WHEN END\_TIME \= 'C' THEN CURRENT\_TIMESTAMP

WHEN END\_TIME LIKE 'C\-S%' THEN ADD\_SECONDS(CURRENT\_TIMESTAMP, \-SUBSTR\_AFTER(END\_TIME, 'C\-S'))

WHEN END\_TIME LIKE 'C\-M%' THEN ADD\_SECONDS(CURRENT\_TIMESTAMP, \-SUBSTR\_AFTER(END\_TIME, 'C\-M') \* 60\)

WHEN END\_TIME LIKE 'C\-H%' THEN ADD\_SECONDS(CURRENT\_TIMESTAMP, \-SUBSTR\_AFTER(END\_TIME, 'C\-H') \* 3600\)

WHEN END\_TIME LIKE 'C\-D%' THEN ADD\_SECONDS(CURRENT\_TIMESTAMP, \-SUBSTR\_AFTER(END\_TIME, 'C\-D') \* 86400\)

WHEN END\_TIME LIKE 'C\-W%' THEN ADD\_SECONDS(CURRENT\_TIMESTAMP, \-SUBSTR\_AFTER(END\_TIME, 'C\-W') \* 86400 \* 7\)

WHEN END\_TIME LIKE 'B\+S%' THEN ADD\_SECONDS(TO\_TIMESTAMP(BEGIN\_TIME, 'YYYY/MM/DD HH24:MI:SS'), SUBSTR\_AFTER(END\_TIME, 'B\+S'))

WHEN END\_TIME LIKE 'B\+M%' THEN ADD\_SECONDS(TO\_TIMESTAMP(BEGIN\_TIME, 'YYYY/MM/DD HH24:MI:SS'), SUBSTR\_AFTER(END\_TIME, 'B\+M') \* 60\)

WHEN END\_TIME LIKE 'B\+H%' THEN ADD\_SECONDS(TO\_TIMESTAMP(BEGIN\_TIME, 'YYYY/MM/DD HH24:MI:SS'), SUBSTR\_AFTER(END\_TIME, 'B\+H') \* 3600\)

WHEN END\_TIME LIKE 'B\+D%' THEN ADD\_SECONDS(TO\_TIMESTAMP(BEGIN\_TIME, 'YYYY/MM/DD HH24:MI:SS'), SUBSTR\_AFTER(END\_TIME, 'B\+D') \* 86400\)

WHEN END\_TIME LIKE 'B\+W%' THEN ADD\_SECONDS(TO\_TIMESTAMP(BEGIN\_TIME, 'YYYY/MM/DD HH24:MI:SS'), SUBSTR\_AFTER(END\_TIME, 'B\+W') \* 86400 \* 7\)

WHEN END\_TIME \= 'MAX' THEN TO\_TIMESTAMP('9999/12/31 00:00:00', 'YYYY/MM/DD HH24:MI:SS')

WHEN SUBSTR(END\_TIME, 1, 1\) NOT IN ('C', 'B', 'M') THEN TO\_TIMESTAMP(END\_TIME, 'YYYY/MM/DD HH24:MI:SS')

END END\_TIME,

HOST,

PORT,

SERVICE\_NAME,

FILE\_NAME,

FILE\_TYPE,

MIN\_UNUSED\_SIZE\_GB,

MIN\_FRAGMENTATION\_PCT,

DATA\_SOURCE,

AGGREGATE\_BY,

MAP(TIME\_AGGREGATE\_BY,

'NONE', 'YYYY/MM/DD HH24:MI:SS',

'HOUR', 'YYYY/MM/DD HH24',

'DAY', 'YYYY/MM/DD (DY)',

'HOUR\_OF\_DAY', 'HH24',

TIME\_AGGREGATE\_BY ) TIME\_AGGREGATE\_BY

FROM

( SELECT /\* Modification section \*/

'1000/10/18 07:58:00' BEGIN\_TIME, /\* YYYY/MM/DD HH24:MI:SS timestamp, C, C\-S\<seconds\>, C\-M\<minutes\>, C\-H\<hours\>, C\-D\<days\>, C\-W\<weeks\>, E\-S\<seconds\>, E\-M\<minutes\>, E\-H\<hours\>, E\-D\<days\>, E\-W\<weeks\>, MIN \*/

'9999/10/18 08:05:00' END\_TIME, /\* YYYY/MM/DD HH24:MI:SS timestamp, C, C\-S\<seconds\>, C\-M\<minutes\>, C\-H\<hours\>, C\-D\<days\>, C\-W\<weeks\>, B\+S\<seconds\>, B\+M\<minutes\>, B\+H\<hours\>, B\+D\<days\>, B\+W\<weeks\>, MAX \*/

'%' HOST,

'%' PORT,

'%' SERVICE\_NAME,

'%' FILE\_NAME,

'DATA' FILE\_TYPE, /\* DATA, LOG \*/

\-1 MIN\_UNUSED\_SIZE\_GB,

\-1 MIN\_FRAGMENTATION\_PCT,

'CURRENT' DATA\_SOURCE,

'NONE' AGGREGATE\_BY, /\* TIME, HOST, PORT, FILE, TYPE, ENCRYPTION and comma\-separated combinations, NONE for no aggregation \*/

'NONE' TIME\_AGGREGATE\_BY /\* HOUR, DAY, HOUR\_OF\_DAY or database time pattern, TS\<seconds\> for time slice, NONE for no aggregation \*/

FROM

DUMMY

)

) BI,

( SELECT

'CURRENT' DATA\_SOURCE,

CURRENT\_TIMESTAMP SNAPSHOT\_TIME,

HOST,

PORT,

FILE\_NAME,

FILE\_TYPE,

USED\_SIZE,

TOTAL\_SIZE

FROM

M\_VOLUME\_FILES

UNION ALL

SELECT

'HISTORY' DATA\_SOURCE,

SERVER\_TIMESTAMP SNAPSHOT\_TIME,

HOST,

PORT,

FILE\_NAME,

FILE\_TYPE,

USED\_SIZE,

TOTAL\_SIZE

FROM

\_SYS\_STATISTICS.HOST\_VOLUME\_FILES

) F,

M\_PERSISTENCE\_ENCRYPTION\_STATUS E

WHERE

F.SNAPSHOT\_TIME BETWEEN BI.BEGIN\_TIME AND BI.END\_TIME AND

F.HOST LIKE BI.HOST AND

TO\_VARCHAR(F.PORT) LIKE BI.PORT AND

F.FILE\_NAME LIKE BI.FILE\_NAME AND

F.FILE\_TYPE LIKE BI.FILE\_TYPE AND

F.DATA\_SOURCE \= BI.DATA\_SOURCE AND

E.HOST \= F.HOST AND

E.PORT \= F.PORT

GROUP BY

F.SNAPSHOT\_TIME,

CASE WHEN BI.AGGREGATE\_BY \= 'NONE' OR INSTR(BI.AGGREGATE\_BY, 'HOST') !\= 0 THEN F.HOST ELSE MAP(BI.HOST, '%', 'any', BI.HOST) END,

CASE WHEN BI.AGGREGATE\_BY \= 'NONE' OR INSTR(BI.AGGREGATE\_BY, 'PORT') !\= 0 THEN TO\_VARCHAR(F.PORT) ELSE MAP(BI.PORT, '%', 'any', BI.PORT) END,

CASE WHEN BI.AGGREGATE\_BY \= 'NONE' OR INSTR(BI.AGGREGATE\_BY, 'FILE') !\= 0 THEN F.FILE\_NAME ELSE MAP(BI.FILE\_NAME, '%', 'any', BI.FILE\_NAME) END,

CASE WHEN BI.AGGREGATE\_BY \= 'NONE' OR INSTR(BI.AGGREGATE\_BY, 'TYPE') !\= 0 THEN F.FILE\_TYPE ELSE MAP(BI.FILE\_TYPE, '%', 'any', BI.FILE\_TYPE) END,

CASE WHEN BI.AGGREGATE\_BY \= 'NONE' OR INSTR(BI.AGGREGATE\_BY, 'ENCRYPTION') !\= 0 THEN MAP(E.ENCRYPTION\_ACTIVE, 'TRUE', 'X', '') ELSE 'any' END,

BI.MIN\_UNUSED\_SIZE\_GB,

BI.MIN\_FRAGMENTATION\_PCT,

BI.AGGREGATE\_BY,

BI.TIME\_AGGREGATE\_BY

)

GROUP BY

CASE

WHEN AGGREGATE\_BY \= 'NONE' OR INSTR(AGGREGATE\_BY, 'TIME') !\= 0 THEN

CASE

WHEN TIME\_AGGREGATE\_BY LIKE 'TS%' THEN

TO\_VARCHAR(ADD\_SECONDS(TO\_TIMESTAMP('2014/01/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS'), FLOOR(SECONDS\_BETWEEN(TO\_TIMESTAMP('2014/01/01 00:00:00',

'YYYY/MM/DD HH24:MI:SS'), SNAPSHOT\_TIME) / SUBSTR(TIME\_AGGREGATE\_BY, 3\)) \* SUBSTR(TIME\_AGGREGATE\_BY, 3\)), 'YYYY/MM/DD HH24:MI:SS')

ELSE TO\_VARCHAR(SNAPSHOT\_TIME, TIME\_AGGREGATE\_BY)

END

ELSE 'any'

END,

HOST,

PORT,

FILE\_NAME,

FILE\_TYPE,

ENCRYPTION,

MIN\_UNUSED\_SIZE\_GB,

MIN\_FRAGMENTATION\_PCT

)

WHERE

( MIN\_UNUSED\_SIZE\_GB \= \-1 OR TOTAL\_SIZE\_GB \- USED\_SIZE\_GB \>\= MIN\_UNUSED\_SIZE\_GB) AND

( MIN\_FRAGMENTATION\_PCT \= \-1 OR MAP(TOTAL\_SIZE\_GB, 0, 0, ( 1 \- USED\_SIZE\_GB / TOTAL\_SIZE\_GB ) \* 100\) \>\= MIN\_FRAGMENTATION\_PCT )

ORDER BY

SNAPSHOT\_TIME DESC,

HOST,

PORT,

FILE\_NAME,

FILE\_TYPE  
  
执行结果如下 磁盘碎片率有51\.72  
![](/media/202508/20250828140302495061.png)  
  
在执行命令前查看当前的磁盘大小  
![](/media/202508/20250828140302504432.png)  
和当前实际使用的大小  
![](/media/202508/20250828140302512363.png)  
  
  
在uat 环境下执行  
![](/media/202508/20250828140302519780.png)  
完成后查看当前磁盘空间  
![](/media/202508/20250828140302610247.png)  
相比之磁盘空间减少了72G

sap相关note  
<https://me.sap.com/notes/2499913/E>https://me.sap.com/notes/2400005\#L16