不知道大家在数据库运维中是否会有这样的困扰,一个数据文件里没有多少数据,但是数据文件的大小却调不下来,尝试使用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当然一个数据库的数据量非常大,存在上百个这样的数据文件有没有什么简洁的方法来统一处理呢。其实是有的。采用的思路就是今天分享的内容,不过后面补充了一些更多的验证和场景补充。能够达到的一个基本效果就是可以一键式部署,感兴趣可以私聊,我近期也会把脚本开放出来。