数据库收缩数据文件的尝试(三)(r11笔记第22天)

  不知道大家在数据库运维中是否会有这样的困扰,一个数据文件里没有多少数据,但是数据文件的大小却调不下来,尝试使用resize来调整屡屡失败。如果一个数据文件里有很多的小表,存在大量这样的碎片表,虽然我们从前端看不到,但是如果查看存储结构就会发现还是挺混乱的。

    本质上来说,Oracle也不希望我们去刻意处理这些物理存储方面的设置,比如设定某个表一定存放在某个数据文件里,一个表空间里存在10个数据文件,一条insert语句运行下去,到底数据进了哪个数据文件,应该不需要DBA刻意去关心,而且Oracle也没提供这样的数据字典来告诉你,所以我们查看的最细粒度的存储数据字典是dba_extents,而没有db_blocks当然Oracle给了你一把钥匙,那就是ROWID。通过ROWID我们可以得到很多未曾发现的问题和可能性。

    我们换一个问法,在一个事务中是否会改变ROWID?

如果是普通的增删改操作,基于主键,基于数据变化,肯定是无法改变ROWID,因为ROWID本身就是一个伪列,这个伪列的效果本质上其实比主键还要给力,查询效率还要高。

    如果我要做这样一个操作,表test的数据量不大在5万条,分布在6,7,8三个数据文件上,如果我们新建一个数据文件9,希望把这些数据都迁移到9号数据文件,而且希望保证高可用的情况下,是否可以实现?

   在这个场景中,我们就可以充分利用ROWID来玩一玩了。

我们创建一个临时中转的表,比如表名为test,则中转的临时表为tmp_test

把表test在8号数据文件里的数据筛查出来插入临时的中转表tmp_test

insert into  test.tmp_test  select * from test.test where dbms_rowid.rowid_relative_fno(rowid)=8
100 rows created.然后删除已有的表test在8号数据文件的数据delete from test.test where dbms_rowid.rowid_relative_fno(rowid)=8;
100 rows deleted.注意此处,这里是一个事务,对于事务外的应用数据的查询还是可以满足一致性的需求。
但是因为表里的数据量很小,所以这个过程造成的阻塞时间会很短。

然后把数据插入

insert  /*+append*/ into  mbi.test select *from test.tmp_test;
100 rows created.

完成之后就是提交commit

当然如果我们要求数据要放在指定的数据文件里,而不是根据数据的增长情况增量的放置,可以使用allocate的方式来处理,比如指定数据放入9号数据文件中。

alter table  test allocate extent (size 1M datafile  '/U01/app/oracle/oradata/test/test_data09.dbf');

操作之后还是需要验证一下,原来的数据文件里确实是不存在那些数据了。

select count(*)  from test.test where dbms_rowid.rowid_relative_fno(rowid)=8;
  COUNT(*)
----------
         0

这些数据还是在临时的表里可以查到,确认无误之后就可以直接drop了。

select count(*)  from test.tmp_test;
  COUNT(*)
----------
        100当然一个数据库的数据量非常大,存在上百个这样的数据文件有没有什么简洁的方法来统一处理呢。其实是有的。采用的思路就是今天分享的内容,不过后面补充了一些更多的验证和场景补充。能够达到的一个基本效果就是可以一键式部署,感兴趣可以私聊,我近期也会把脚本开放出来。

上一篇:看云栖说云栖——大型企业互联网转型升级


下一篇:华夏基金CSO葛朝晖:登山爱好者的数字化转型洞察 | 阿里CIO学院名人堂