oracle 数据库表空间文件收缩

最新推荐文章于 2025-06-15 14:01:30 发布
最新推荐文章于 2025-06-15 14:01:30 发布
阅读量1.1k 收藏
点赞数
分类专栏： oracle 文章标签： 表空间文件 收缩 oracle
本文介绍了一种查询Oracle数据库中数据文件使用情况的方法，并提供了具体的SQL语句来展示如何获取文件的详细信息，包括文件大小、已用空间、可用空间等，并给出了通过调整数据文件大小进行空间回收的具体建议。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

1. 查看数据文件使用情况

select /*+ ordered use_hash(a,b,c) */
a.file_id,a.file_name,a.filesize, b.freesize,
(a.filesize-b.freesize) usedsize,
 c.hwmsize,
 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

结果说明：

File_id ： 文件编号

File_name: 文件名称

File_size: 数据文件占用磁盘空间大小

Freesize：文件中被标记为free的空间大小

Usedsize: 使用的空间大小。

Hwmsize： 已经分配出去的空间大小，如果希望通过alter database datafile … resize integerM回收空间，将需要这个值作为参考，不能回收到这个值之下，否则会报错。

Freee_belowhwm_size： 在HWM（高水位标记线之下的空闲空间数），这个是理论上的可以回收的空间大小。

Curr_can_shrink： 这个是实际大小与HWM标记之间的差，就是还没有分配出去的空间大小。

file_id

file_name

filesize

freesize

usedsize

hwmsize

free_belowhwm_size

curr_can_shrink

11

/oradata/ODSD01.dbf

2048

1908

140

2048

1908

0

12

/oradata/ODSD02.dbf

2048

1897

151

2048

1897

0

20

/oradata/ODSD10.dbf

2048

1897

151

2048

1897

0

16

/oradata/ODSD06.dbf

2048

1889

159

2048

1889

0

15

/oradata/ODSD05.dbf

2048

1888

160

2048

1888

0

19

/oradata/ODSD09.dbf

2048

1885

163

2048

1885

0

13

/oradata/ODSD03.dbf

2048

1884

164

2048

1884

0

17

/oradata/ODSD07.dbf

2048

1884

164

2048

1884

0

14

/oradata/ODSD04.dbf

2048

1813

235

2041

1806

7

34

/oradata/DWD01.dbf

4000

3701

299

2088

1789

1912

51

/oradata/ODSD11.dbf

2048

1963

85

1584

1499

464

21

/oradata/ODSI01.dbf

2048

1913

135

1617

1482

431

25

/oradata/ODSI05.dbf

2048

1910

138

1607

1469

441

22

/oradata/ODSI02.dbf

2048

1903

145

1606

1461

442

24

/oradata/ODSI04.dbf

2048

1909

139

1592

1453

456

23

/oradata/ODSI03.dbf

2048

1892

156

1603

1447

445

48

/oradata/ODSI06.dbf

2048

1925

123

1559

1436

489

30

/oradata/TODSD05.dbf

2048

1804

244

1315

1071

733

18

/oradata/ODSD08.dbf

2048

1881

167

1225

1058

823

27

/oradata/TODSD02.dbf

2048

1818

230

1244

1014

804

31

/oradata/TODSI01.dbf

2048

1977

71

936

865

1112

35

/oradata/DWI01.dbf

2048

1973

75

936

861

1112

32

/oradata/TODSI02.dbf

2048

1969

79

867

788

1181

43

/oradata/DWI03.dbf

2048

1975

73

802

729

1246

42

/oradata/DWI02.dbf

2048

1983

65

755

690

1293

39

/oradata/TODSI04.dbf

2048

1971

77

680

603

1368

26

/oradata/TODSD01.dbf

2048

1819

229

830

601

1218

40

/oradata/TODSI05.dbf

2048

1976

72

609

537

1439

28

/oradata/TODSD03.dbf

2048

1793

255

702

447

1346

37

/oradata/TODSI03.dbf

2048

1946

102

450

348

1598

29

/oradata/TODSD04.dbf

2048

1793

255

485

230

1563

33

/oradata/CTL01.dbf

500

494

6

21

15

479

10

/oradata/xdb01.dbf

47

3

44

46

2

1

1

/oradata/system01.dbf

1040

6

1034

1034

0

6

3

/oradata/cwmlite01.dbf

20

2

18

18

0

2

4

/oradata/drsys01.dbf

20

10

10

10

0

10

36

/oradata/OD01.dbf

500

407

93

93

0

407

5

/oradata/example01.dbf

139

0

139

139

0

0

54

/oradata/TCLKING.dbf

5

0

5

5

0

0

56

/oradata/undotbs03.dbf

1000

996

4

4

0

996

55

/oradata/HWM01.dbf

5000

4963

37

37

0

4963

49

/oradata/DP23.dbf

10

7

3

3

0

7

7

/oradata/odm01.dbf

20

11

9

9

0

11

9

/oradata/users01.dbf

83

0

83

82

-1

1

46

/oradata/RPTI01.dbf

1024

802

222

221

-1

803

45

/oradata/RPTD01.dbf

1024

923

101

100

-1

924

38

/oradata/FBI.dbf

200

79

121

120

-1

80

 


select /*+ ordered use_hash(a,c) */

  'alter database datafile '''||a.file_name||''' resize '
   ||round(a.filesize - (a.filesize - c.hwmsize-100) *0.8)||'M;',
  a.filesize,
  c.hwmsize
from 
(
select file_id,file_name,round(bytes/1024/1024) filesize from dba_data_files
) a,
(
select file_id,round(max(block_id)*8/1024) HWMsize from dba_extents
group by file_id) c
where a.file_id = c.file_id

  and a.filesize - c.hwmsize > 100

 

收缩语句

文件大小

收缩目标

alter database datafile '/oradata/HWM02.dbf' resize 2671M;

5000

1989

alter database datafile '/oradata/ODSD01.dbf' resize 598M;

2048

136

alter database datafile '/oradata/ODSD02.dbf' resize 592M;

2048

128

alter database datafile '/oradata/ODSD03.dbf' resize 591M;

2048

127

alter database datafile '/oradata/ODSD04.dbf' resize 742M;

2048

316

alter database datafile '/oradata/ODSD05.dbf' resize 594M;

2048

130

alter database datafile '/oradata/ODSD06.dbf' resize 597M;

2048

134

alter database datafile '/oradata/ODSD07.dbf' resize 598M;

2048

135

alter database datafile '/oradata/ODSD08.dbf' resize 472M;

1470

122

alter database datafile '/oradata/ODSD09.dbf' resize 587M;

2048

122

alter database datafile '/oradata/ODSD10.dbf' resize 595M;

2048

132

alter database datafile '/oradata/ODSI01.dbf' resize 507M;

1783

88

alter database datafile '/oradata/ODSI02.dbf' resize 505M;

1774

88

alter database datafile '/oradata/ODSI03.dbf' resize 529M;

1772

118

alter database datafile '/oradata/ODSI04.dbf' resize 517M;

1763

105

alter database datafile '/oradata/ODSI05.dbf' resize 525M;

1775

113

alter database datafile '/oradata/TODSD01.dbf' resize 497M;

1154

233

alter database datafile '/oradata/TODSD02.dbf' resize 561M;

1485

230

alter database datafile '/oradata/TODSD03.dbf' resize 465M;

1051

218

alter database datafile '/oradata/TODSD04.dbf' resize 431M;

878

219

alter database datafile '/oradata/TODSD05.dbf' resize 598M;

1542

262

alter database datafile '/oradata/TODSI01.dbf' resize 385M;

1238

72

alter database datafile '/oradata/TODSI02.dbf' resize 365M;

1183

60

alter database datafile '/oradata/CTL01.dbf' resize 146M;

197

33

alter database datafile '/oradata/DWD01.dbf' resize 770M;

2550

225

alter database datafile '/oradata/DWI01.dbf' resize 386M;

1238

73

alter database datafile '/oradata/OD01.dbf' resize 152M;

254

27

alter database datafile '/oradata/TODSI03.dbf' resize 288M;

850

48

alter database datafile '/oradata/TODSI04.dbf' resize 324M;

1034

46

alter database datafile '/oradata/TODSI05.dbf' resize 343M;

977

84

alter database datafile '/oradata/DWI02.dbf' resize 356M;

1094

72

alter database datafile '/oradata/DWI03.dbf' resize 366M;

1131

75

alter database datafile '/oradata/RPTD01.dbf' resize 231M;

365

98

alter database datafile '/oradata/RPTI01.dbf' resize 300M;

462

159

alter database datafile '/oradata/ODSI06.dbf' resize 505M;

1737

97

alter database datafile '/oradata/ODSD11.dbf' resize 535M;

1757

129

alter database datafile '/oradata/undotbs03.dbf' resize 176M;

283

49




oracle数据库表空间文件收缩实例
47522341的专栏
02-06 2万+
Oracle数据文件收缩实例数据文件的作用     HWM的基本概念      查看数据文件的使用情况包括内容：数据文件大小，已经used空间，free空间，hwm信息select /*+ ordered use_hash(a,b,c) */a.file_id,a.file_name,a.filesize, b.freesize,(a.filesize-b.
ORACLE数据文件收缩步骤
shangqiu100100的博客
03-26 1521
本人使用的是19c版本，使用使用过程中发现预分配空间冗余率太高，通过下述步骤成功进行文件收缩。---5 统计指定数据文件的数据位置大小，block_size=8k。---6 结合总空间和剩余空间，计算收缩后的数据文件大小。---查询数据文件对应的file_id。---621952 已使用4859M。-----1 查询各表空间文件使用情况。---3 了解各数据文件的剩余空间详情。---4 统计表空间合计空间大小。----数据的使用占用容量大小。---2 了解各数据文件详情。
Oracle数据文件收缩
04-22
如题，用于进行ORACEL数据库的收缩。减少磁盘这空间。
收缩Oracle数据文件
乐沙弥的世界
10-30 7327
最近有网友提到收缩Oracle数据文件的问题，这是DBA经常碰到的一个常见问题。通常我们需要收缩相应的数据文件以减少来自磁盘空间的压力以及提高数据库的整体性能。但这并非对于所有情形都是适用的，尤其是生产环境。因为生产环境数据清洗相当较少，因此空间浪费也比较小，而且一旦收缩之后又要重新自动扩展数据文件，浪费系统资源。对于UAT，DEV环境，多DB，磁盘空间压力大的情形，收缩一下非常有必要。勒紧裤带过
37-Oracle 23 ai Shrink Tablespace（一键收缩表空间）
最新发布
远方的专栏
06-15 815
小伙伴们有没有经历过，超大表和超大数据的导入后，数据被删除了，然而空间迟迟不释放，存储添置又跟不上，业务空间告警的时候。收缩就很必须了，然而收缩需谨慎，数据大过天。DBMS_SPACE.SHRINK_TABLESPACE是Oracle 23ai新增功能，使得23 ai有了在线一键shrink tablespace的功能，19c及以下还需要手动操作。在Oracle 23ai中，​当表被截断(TRUNCATE)或数据删除后，数据文件中会产生不连续的空白空间间隙(GAP)​。
oracle数据文件收缩,oracle数据文件的收缩
weixin_32921457的博客
04-07 241
You created T1 and T2, your datafile in that tablespace might look like this:XYffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffyou could shrink that file now and get rid of all of the f'...
Oracle表空间数据库文件收缩案例解析
09-09
在Oracle数据库管理中，表空间（Tablespace）是存储数据库对象（如表、索引等）的逻辑结构，而数据库文件（Datafile）则是表空间的物理实现。当数据库文件初始化时，通常会预先分配一定的磁盘空间，但有时这可能会...
Oracle数据库表空间收缩方法探讨.pdf
10-10
4. **使用`ALTER TABLE SHRINK SPACE`命令**：这个命令可以收缩表和索引段的空间，但要注意，它可能会导致表的数据重新排序，从而产生额外的I/O操作。 5. **段压缩**：Oracle提供了压缩选项，可以在表或索引创建时...
Oracle 表空间 收缩
10-27
- **表空间**（Tablespace）：Oracle数据库中的逻辑存储单元，由一个或多个数据文件组成。 - **数据文件**（Data File）：表空间的具体物理实现，每个表空间至少包含一个数据文件。 - **段**（Segment）：表空间中...
收缩oracle数据库表空间大放送
06-05 592
当要做oracle数据库迁移, 备份时,需要对数据库的整体使用情况有一个了解,有时要把占用的无用空间释放出来,可以用下面的slq 查询,这是我从网上整理出来的: --查询系统各个表空间的大小及使用百分比情况 SELECT UPPER(F.TABLESPACE_NAME) "表空间名",D.TOT_GROOTTE_MB "表空间大小(M)",     D.TOT_GROOTTE_MB - F.
Oracle数据文件收缩实例
02-11
近期来，FreeList的重要作用逐渐为Oracle DBA所认识，网上也出现一些相关的讨论。本文以FreeList为线索对Oracle的存储管理的原理进行较深入的探讨，涉及Oracle段区块管理的原理，FreeList算法等。而与FreeList密切相关的一个重用特性HWM，与sql性能密切相关，本文也作了原理分析介绍。在原理探讨的基础上，介绍了常用的存储参数分析方法，并对所涉及的存储优化、HWM的优化和Freelist竞争优化作了说明。 缩略语： ASSM：auto segement space management HWM：high water mark DBA：data block address OLTP：online transaction process OPS：oracle parallel server
oracle数据文件收缩,oracle数据库表空间文件收缩实例
weixin_32304579的博客
04-07 597
Oracle数据文件收缩实例数据文件的作用HWM的基本概念查看数据文件的使用情况包括内容：数据文件大小，已经used空间，free空间，hwm信息select /*+ ordered use_hash(a,b,c) */a.file_id,a.file_name,a.filesize, b.freesize,(a.filesize-b.freesize) usedsize,c.hwmsize,c....
oracle数据文件的收缩
cuhhjuh46130的博客
11-10 131
一般来讲，oracle的数据文件只扩展，不会因为tablespace上的表数据删除而自动收缩数据文件，释放磁盘空间。昨天在asktom看了一篇文章，很有意思，贴过来看看。The only thing that'll make a...
oracle收缩数据文件
u014199512的博客
05-02 976
--oracle收缩数据文件 --由于 ALTER DATABASE DATAFILE 'E:\ORADATA\OLD_TABS01.DBF' RESIZE 3072M; 语句不能将文件大小改到hwmsize以下,所以要先将hwmsize缩小;--通过将表空间的数据移动到另一个表空间来实现缩小hwmsize --查询表空间使用情况 select /*+ ordered use_hash(a,b,c...
oracle收缩表空间
08-27 657
1、发现一个表空间aaa使用率已达到93%，急需扩容，但asm磁盘组空间同样不足，只剩下不到100G，磁盘组扩容需要存储工程师配合，存储工程师未在现场，不具备扩容条件。5、使用alter database datafile 35 resize 1G，无法成功，发现曾经使用过最大的扩展分区；11、如果resize 不能成功，可将resize空间改大一些，比如5G，10G，就可成功。2、查看发现有一个表空间bbb为1T，但使用很低，只有20%。3、查看该bbb表空间最大的对象是sys.aud$;
Oracle数据库表空间管理详解
"Oracle数据库中表空间的基本管理操作小结" 在Oracle数据库管理系统中，表空间是存储数据的主要逻辑单元，它将物理磁盘空间与数据库对象关联起来。表空间管理是数据库管理员（DBA）日常维护工作的重要部分。本文...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值