查看: 460|回复: 9

[讨论] ORACLE空间收缩请教

[复制链接]
回帖奖励 15 pub币 回复本帖可获得 5 pub币奖励! 每人限 1 次
求职 : 数据库管理员
论坛徽章:
2
娜美
日期:2018-02-27 19:41:30火眼金睛
日期:2018-08-31 22:00:00
发表于 2019-12-3 10:27 | 显示全部楼层 |阅读模式
本帖最后由 gegeman 于 2019-12-3 10:31 编辑

环境:centos 7.4 + Oracle 11.2.0.4,Oracle使用文件系统存储数据

问题:我现在有一套数据库,有一个表空间是用来存储日志信息的,以前日志量非常大,所以空间使用了200GB左右,现在调整日志之后,该表空间数据基本维持在30GB,为了节省空间,想要把这个表空间不使用的部分释放出来,给其它表空间使用。我的方案是:
方案1:表空间存在2个日志大表,因为每天都在做删除数据,所以目前数据不多。对这2个大表先做shrink,shrink之后,表的空闲空间就释放到了表空间,然后在对数据文件进行resize(减小),从而达到释放表空间的目的,经过测试是没得问题的。
方案2:后续业务提出可以对日志表执行truncate操作,truncate之后直接对数据文件进行resize。为了操作简便,就使用该方案进行操作了。但是在执行resize时候无法减小数据文件。

我的方案1:
(一)删除数据,空间未释放场景再现
(1)创建测试表并插入数据
SQL> create table test01 as select * from sales;

Table created


SQL> create index idx_test01_transactionid on test01(transactionid);

Index created

(2)确认数据,插入了1000万条测试数据,大小为1536MB+192MB
SQL> select count(*) from test01;

  COUNT(*)
----------
  10000000

SQL> select owner,segment_name,segment_type,bytes/1024/1024 segment_mb from dba_segments where owner = 'LIJIAMAN' and segment_name in('TEST01','IDX_TEST01_TRANSACTIONID');

OWNER        SEGMENT_NAME                    SEGMENT_TYPE       SEGMENT_MB
------------ ------------------------------  ------------------ ----------
LIJIAMAN     IDX_TEST01_TRANSACTIONID        INDEX                     192
LIJIAMAN     TEST01                          TABLE                    1536

(3)查看表空间使用情况,这里test01表存储在TBS_MONIOTR表空间
[img][/img]

(4)查看服务器磁盘空间使用情况
因为磁盘除了存储Oracle的数据,还存储其它的数据,比如归档日志等,建议直接查看所有数据文件占用的磁盘空间。
[oracle@testserver archivelog]$ cd /u01/app/oracle/oradata/prodb/
[oracle@testserver prodb]$ du -sh
9.1G    .

##因为test01表是在TBS_MONITOR表空间里面的,这里可以单独看一下TBS_MONITOR表空间里面的数据文件
[oracle@testserver prodb]$ du -sh monitor01.dbf\  
5.1G    monitor01.dbf

(5)删除大约50%的数据
SQL> declare
  2     i number :=1;
  3   begin
  4     loop
  5       if i > 10000000
  6       then
  7         exit;
  8       end if;
  9       delete test01 where transactionid = i;
10       i:=i+2;
11     end loop;
12     commit;
13   end;
14  /
SQL>
SQL>

PL/SQL procedure successfully completed

Executed in 3026.232 seconds

(6)查看空间是否释放
SQL> select count(*) from test01;

  COUNT(*)
----------
   5070000

Executed in 0.454 seconds


SQL> select owner,segment_name,segment_type,bytes/1024/1024 segment_mb from dba_segments where owner = 'LIJIAMAN' and segment_name in('TEST01','IDX_TEST01_TRANSACTIONID');

OWNER        SEGMENT_NAME                    SEGMENT_TYPE       SEGMENT_MB
------------ ------------------------------- ------------------ ----------
LIJIAMAN     IDX_TEST01_TRANSACTIONID        INDEX                     192
LIJIAMAN     TEST01                          TABLE                    1536
经过确认,没有释放出任何空间。
表空间层面来看也是
[img][/img]

(二)执行shrink操作
SQL> ALTER TABLE test01 ENABLE ROW MOVEMENT;

Table altered

Executed in 0.421 seconds


SQL> ALTER TABLE test01 SHRINK SPACE CASCADE;

Table altered

Executed in 345.567 seconds


SQL> ALTER TABLE test01 DISABLE ROW MOVEMENT;

Table altered

Executed in 0.004 seconds


(三)再次查看空间信息
(1)查看表和索引的空间信息,发现已经下降到原来的50%
SQL> select owner,segment_name,segment_type,bytes/1024/1024 segment_mb from dba_segments where owner = 'LIJIAMAN' and segment_name in('TEST01','IDX_TEST01_TRANSACTIONID');

OWNER                          SEGMENT_NAME                SEGMENT_TYPE       SEGMENT_MB
------------------------------ --------------------------- ------------------ ----------
LIJIAMAN                       IDX_TEST01_TRANSACTIONID    INDEX                  94.875
LIJIAMAN                       TEST01                      TABLE                  762.75

Executed in 0.07 seconds

(2)查看表空间TBS_MONITOR,发现空闲空间从原来的1.8GB增加到了2.65GB,增加了870MB,恰好等于shrink后释放的空间。
[img][/img]
(3)查看服务器磁盘空间使用情况,发现磁盘上的空间并未减小
[root@testserver archivelog]# cd /u01/app/oracle/oradata/prodb/
[root@testserver prodb]# du -sh
9.1G    .
[root@testserver prodb]#
[root@testserver prodb]# du -sh monitor01.dbf\  
5.1G    monitor01.dbf


执行空间回收
--空闲空间大于100M则回收
select 'alter database datafile ''' || a.file_name || ''' resize ' ||
       round(a.filesize - (a.filesize - c.hwmsize) * 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) * (select value from v$parameter where name = 'db_block_size') / 1024 / 1024) HWMsize
          from dba_extents
         group by file_id) c
where a.file_id = c.file_id
   and a.filesize - c.hwmsize > 100

查询出的结果如下:
[img][/img]

执行上面的SQL语句:
SQL> alter database datafile '/u01/app/oracle/oradata/prodb/monitor01.dbf ' resize 3566M;

Database altered

Executed in 0.019 seconds

(三)确认空间回收
(1)查看表空间TBS_MONITOR
[img][/img]
(2)查看服务器磁盘空间使用情况
[oracle@testserver archivelog]$ cd /u01/app/oracle/oradata/prodb/
[oracle@testserver prodb]$ du -sh
7.6G    .

[oracle@testserver prodb]$ du -sh monitor01.dbf\  
3.5G    monitor01.dbf


确认表空间以及存储层面都有新的空间释放出来。












tbs1.png
求职 : 数据库管理员
论坛徽章:
2
娜美
日期:2018-02-27 19:41:30火眼金睛
日期:2018-08-31 22:00:00
 楼主| 发表于 2019-12-3 10:36 | 显示全部楼层
本帖最后由 gegeman 于 2019-12-3 10:41 编辑

不知道如何删除传错的图片,盖楼忽略。。。。

truncate之后

truncate之后

truncate之前

truncate之前

使用道具 举报

回复
求职 : 数据库管理员
论坛徽章:
2
娜美
日期:2018-02-27 19:41:30火眼金睛
日期:2018-08-31 22:00:00
 楼主| 发表于 2019-12-3 10:42 | 显示全部楼层
我的方案2:


SQL> select owner,segment_name,bytes/1024/1024 from dba_segments where segment_name = 'TEST01';

OWNER                          SEGMENT_NAME                                                                     BYTES/1024/1024
------------------------------ -------------------------------------------------------------------------------- ---------------
LIJIAMAN                       TEST01                                                                                       624

Executed in 0.029 seconds

此时,表空间使用如下:






执行truncate操作:
SQL> truncate table LIJIAMAN.TEST01;

Table truncated


Executed in 0.377 seconds




SQL> select owner,segment_name,bytes/1024/1024 from dba_segments where segment_name = 'TEST01';

OWNER                          SEGMENT_NAME                                                                     BYTES/1024/1024
------------------------------ -------------------------------------------------------------------------------- ---------------
LIJIAMAN                       TEST01                                                                                    0.0625

Executed in 0.061 seconds



再次查看表空间使用情况,发现可用空间增多了




这个时候我想要去执行truncate操作,执行的标准为:
--空闲空间大于100M则回收
select 'alter database datafile ''' || a.file_name || ''' resize ' ||
       round(a.filesize - (a.filesize - c.hwmsize) * 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) * (select value from v$parameter where name = 'db_block_size') / 1024 / 1024) HWMsize
          from dba_extents
         group by file_id) c
where a.file_id = c.file_id
   and a.filesize - c.hwmsize > 100



发现空间无法回收了。



执行truncate后表空间容量

执行truncate后表空间容量

执行truncate前表空间容量

执行truncate前表空间容量

使用道具 举报

回复
求职 : 数据库管理员
论坛徽章:
2
娜美
日期:2018-02-27 19:41:30火眼金睛
日期:2018-08-31 22:00:00
 楼主| 发表于 2019-12-3 10:48 | 显示全部楼层
ITPUB界面太不友好,上面的表空间图片传输有问题。这里文字描述一下,执行truncate之后,从toad的tablespace界面,看到已使用的空间小了,即truncate的空间释放到了表空间里面了。但是在后续执行alter database datafile N resize的时候,发现无法回收truncate释放的这部分空间。

请问:
1.为什么shrink之后释放的空间可以通过resize datafile的方式会受到磁盘?
2.为什么truncate之后释放的空间不能通过resize datafile的方式回收?
3.大家都用什么办法去做表空间的回收呢?

使用道具 举报

回复
论坛徽章:
183
生肖徽章:狗
日期:2006-11-23 04:26:03生肖徽章:羊
日期:2007-09-26 17:08:21生肖徽章:马
日期:2007-09-26 17:08:49授权会员
日期:2007-12-31 19:14:41生肖徽章2007版:牛
日期:2008-03-28 10:02:30奥运会纪念徽章:柔道
日期:2008-04-30 16:28:44奥运会纪念徽章:垒球
日期:2008-05-12 21:28:28奥运会纪念徽章:体操
日期:2008-06-26 10:00:41奥运会纪念徽章:沙滩排球
日期:2008-07-27 12:41:59奥运会纪念徽章:艺术体操
日期:2008-07-30 11:09:47
发表于 2019-12-3 13:07 | 显示全部楼层

回帖奖励 +5 pub币

对象在数据文件位置的高水位降下来即可resize

使用道具 举报

回复
求职 : 数据库管理员
论坛徽章:
2
娜美
日期:2018-02-27 19:41:30火眼金睛
日期:2018-08-31 22:00:00
 楼主| 发表于 2019-12-3 14:08 | 显示全部楼层
jieyancai 发表于 2019-12-3 13:07
对象在数据文件位置的高水位降下来即可resize

现在降不下来,现在的现象是:一个表空间存在一个数据文件,总大小为20GB,已使用量为15GB,里面有多个表,其中有一个10GB的大表,现在把10GB的大表删除了,理论上可以resize到5GB,但是实际上只能resize到14.9GB。

我看了一下官方文档(5.  case study的例子A),应该要用move操作才能将下来,但是风险太大,不敢操作。

How to Resize a Datafile.pdf

993.07 KB, 下载次数: 1

使用道具 举报

回复
论坛徽章:
183
生肖徽章:狗
日期:2006-11-23 04:26:03生肖徽章:羊
日期:2007-09-26 17:08:21生肖徽章:马
日期:2007-09-26 17:08:49授权会员
日期:2007-12-31 19:14:41生肖徽章2007版:牛
日期:2008-03-28 10:02:30奥运会纪念徽章:柔道
日期:2008-04-30 16:28:44奥运会纪念徽章:垒球
日期:2008-05-12 21:28:28奥运会纪念徽章:体操
日期:2008-06-26 10:00:41奥运会纪念徽章:沙滩排球
日期:2008-07-27 12:41:59奥运会纪念徽章:艺术体操
日期:2008-07-30 11:09:47
发表于 2019-12-3 14:11 | 显示全部楼层
gegeman 发表于 2019-12-3 14:08
现在降不下来,现在的现象是:一个表空间存在一个数据文件,总大小为20GB,已使用量为15GB,里面有多个表, ...

是的.脚本准备好,小心操作即可.
另外空间够的话,就不要去做收缩操作.本来表空间就建议预先分配足够才是合理的.
空间有空闲了,干嘛还要去收缩呢,多此一举

使用道具 举报

回复
论坛徽章:
120
现任管理团队成员
日期:2011-05-07 01:45:08乌索普
日期:2019-02-14 23:54:04
发表于 2019-12-4 11:35 | 显示全部楼层

回帖奖励 +5 pub币

不确定能缩减,不如直接move

使用道具 举报

回复
求职 : 数据库管理员
论坛徽章:
2
娜美
日期:2018-02-27 19:41:30火眼金睛
日期:2018-08-31 22:00:00
 楼主| 发表于 2019-12-4 15:53 | 显示全部楼层
jieyancai 发表于 2019-12-3 14:11
是的.脚本准备好,小心操作即可.另外空间够的话,就不要去做收缩操作.本来表空间就建议预先分配足够才是合理 ...

主要是以前日之多,这个表空间分配的多,现在日至少了,所以想把这部分空间拿出来使用

使用道具 举报

回复
求职 : 数据库管理员
论坛徽章:
2
娜美
日期:2018-02-27 19:41:30火眼金睛
日期:2018-08-31 22:00:00
 楼主| 发表于 2019-12-4 15:54 | 显示全部楼层
zergduan 发表于 2019-12-4 11:35
不确定能缩减,不如直接move

现在来看,只能这样了,但是move会锁表和索引会失效吧。

打算以后有维护机会直接expdp、impdp消除了

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表