ORACLE查询表数据占用存储空间大小(清理表碎片) |
您所在的位置:网站首页 › 查询表所占空间大小 › ORACLE查询表数据占用存储空间大小(清理表碎片) |
查询”帐户/库”数据占用存储空间大小select sum(bytes)/1024/1024 as “size(M)” from dba_segments where owner=’帐户名/库名’ 查询单表数据占用存储空间大小select sum(bytes)/(1024*1024) as “size(M)” from user_segmentswhere segment_name=upper(‘表名’); 在表空间中,查询占用存储空间最大的表SELECT * FROM (SELECT SEGMENT_NAME, SUM(BYTES) / 1024 / 1024 MB FROM DBA_SEGMENTS WHERE TABLESPACE_NAME = upper(‘表空间名称’) GROUP BY SEGMENT_NAME ORDER BY 2 DESC) WHERE ROWNUM < 10; 在”帐户/库”中,查询占用存储空间最大的表SELECT * FROM (SELECT SEGMENT_NAME, SUM(BYTES) / 1024 / 1024 MB FROM DBA_SEGMENTS WHERE owner=upper(‘帐户名/库名’) GROUP BY SEGMENT_NAME ORDER BY 2 DESC) WHERE ROWNUM < 10; ------------------------------------------------------------------------------------------------------------------------------------ 为什么我的表delete数据之后再插入同样的数据,表空间占用会越来越大?我的操作首先是DELETE TABLE A WHERE A.UPDATE>=一个固定的日期然后commit之后INSERT INTO TABLE A 刚才删除的数据前后表数据数量是一样的,但是莫名其妙的表空间却越占越大没有lob字段。 --------------------------------- 楼主研究一下 pctfree 和 pctused 这两个参数,跟这个会有关系的; 这时,你可以 move 一下表,大小就应该和原来的一样了。 --------------------------------- 清理存储碎片 alter table 表名称 move
alter table move 操作来清理存储碎片,其实是读取t,然后在t表所在的表空间重建表t.等moce 操作完成之后删除原来的表。所以需要额外的存储空间开销,要求当前表空间的大小至少为 表的两倍
注意: 1.再用alter table table_name move时,表相关的索引会失效,所以之后还要执行 alter index index_name rebuild online; 最后重新编译数据库所有失效的对象 2. 在用alter table table_name shrink space cascade时,他相当于alter table table_name move和alter index index_name rebuild online. 所以只要编译数据库失效的对象就可以
--查询碎片情况 exec show_space('T'); --------------------------------- alter table move跟shrink space的区别 今天主要从两点说他们的区别: 1. 碎片的整理 2.空间的收缩
SQL> select * from v$version; BANNER----------------------------------------------------------------Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64biPL/SQL Release 10.2.0.1.0 - ProductionCORE 10.2.0.1.0 Production TNS for Linux: Version 10.2.0.1.0 - ProductionNLSRTL Version 10.2.0.1.0 - Production SQL>
创建测试表SQL> create table test3 as 2 select rownum id, 3 dbms_random.string('a', round(dbms_random.value(0,10))) col1, 4 trunc(sysdate) - dbms_random.value(1, 365*2) col2 5 from dual connect by rownum select count(1) from test3; COUNT(1)---------- 10000 查看表test3的blocks使用情况:
SQL> exec show_space_t('TEST3','auto','table','Y'); Total Blocks............................40Total Bytes.............................327680Unused Blocks...........................3Unused Bytes............................24576Last Used Ext FileId....................31Last Used Ext BlockId...................481921Last Used Block.........................5 *************************************************0% -- 25% free space blocks.............00% -- 25% free space bytes..............025% -- 50% free space blocks............025% -- 50% free space bytes.............050% -- 75% free space blocks............050% -- 75% free space bytes.............075% -- 100% free space blocks...........075% -- 100% free space bytes............0Unused Blocks...........................0Unused Bytes............................0Total Blocks............................32Total bytes.............................262144
PL/SQL procedure successfully completed 制造碎片 SQL> DELETE FROM TEST3 WHERE MOD(ID,3)=1; 3334 rows deleted
SQL> commit; Commit complete
发现有碎片了
SQL> exec show_space_t('TEST3','auto','table','Y'); Total Blocks............................40Total Bytes.............................327680Unused Blocks...........................3Unused Bytes............................24576Last Used Ext FileId....................31Last Used Ext BlockId...................481921Last Used Block.........................5 *************************************************0% -- 25% free space blocks.............00% -- 25% free space bytes..............025% -- 50% free space blocks............3125% -- 50% free space bytes.............25395250% -- 75% free space blocks............150% -- 75% free space bytes.............819275% -- 100% free space blocks...........075% -- 100% free space bytes............0Unused Blocks...........................0Unused Bytes............................0Total Blocks............................0Total bytes.............................0
PL/SQL procedure successfully completed SQL> 消除碎片 SQL> alter table test3 move; Table altered
查看碎片消除的效果 SQL> exec show_space_t('TEST3','auto','table','Y'); Total Blocks............................32Total Bytes.............................262144Unused Blocks...........................6Unused Bytes............................49152Last Used Ext FileId....................31Last Used Ext BlockId...................485065Last Used Block.........................2 *************************************************0% -- 25% free space blocks.............00% -- 25% free space bytes..............025% -- 50% free space blocks............025% -- 50% free space bytes.............050% -- 75% free space blocks............050% -- 75% free space bytes.............075% -- 100% free space blocks...........075% -- 100% free space bytes............0Unused Blocks...........................0Unused Bytes............................0Total Blocks............................22Total bytes.............................180224
PL/SQL procedure successfully completed SQL>
从以上看,碎片整理的效果很好!!! 下面在测试用shrink整理碎片 重建测试环境 SQL> drop table test3; Table dropped
SQL>SQL> create table test3 as 2 select rownum id, 3 dbms_random.string('a', round(dbms_random.value(0,10))) col1, 4 trunc(sysdate) - dbms_random.value(1, 365*2) col2 5 from dual connect by rownum exec show_space_t('TEST3','auto','table','Y'); Total Blocks............................40Total Bytes.............................327680Unused Blocks...........................3Unused Bytes............................24576Last Used Ext FileId....................31Last Used Ext BlockId...................481921Last Used Block.........................5 *************************************************0% -- 25% free space blocks.............00% -- 25% free space bytes..............025% -- 50% free space blocks............025% -- 50% free space bytes.............050% -- 75% free space blocks............050% -- 75% free space bytes.............075% -- 100% free space blocks...........075% -- 100% free space bytes............0Unused Blocks...........................0Unused Bytes............................0Total Blocks............................32Total bytes.............................262144
PL/SQL procedure successfully completed
制造碎片 SQL> delete from test3 where mod(id,3)=1; 3334 rows deleted
SQL> commit; Commit complete
查看碎片情况SQL> exec show_space_t('TEST3','auto','table','Y'); Total Blocks............................40Total Bytes.............................327680Unused Blocks...........................3Unused Bytes............................24576Last Used Ext FileId....................31Last Used Ext BlockId...................481921Last Used Block.........................5 *************************************************0% -- 25% free space blocks.............00% -- 25% free space bytes..............025% -- 50% free space blocks............3125% -- 50% free space bytes.............25395250% -- 75% free space blocks............050% -- 75% free space bytes.............075% -- 100% free space blocks...........075% -- 100% free space bytes............0Unused Blocks...........................0Unused Bytes............................0Total Blocks............................1Total bytes.............................8192
PL/SQL procedure successfully completed
用oracle10g新功能整理碎片 SQL> alter table test3 shrink space compact cascade; alter table test3 shrink space compact cascade ORA-10636: ROW MOVEMENT is not enabled
SQL> alter table test3 enable row movement; Table altered
SQL> alter table test3 shrink space compact cascade; Table altered
再次查看碎片的情况,发现还有一些碎片,整理碎片效果不好 SQL> exec show_space_t('TEST3','auto','table','Y'); Total Blocks............................40Total Bytes.............................327680Unused Blocks...........................3Unused Bytes............................24576Last Used Ext FileId....................31Last Used Ext BlockId...................481921Last Used Block.........................5 *************************************************0% -- 25% free space blocks.............10% -- 25% free space bytes..............819225% -- 50% free space blocks............225% -- 50% free space bytes.............1638450% -- 75% free space blocks............050% -- 75% free space bytes.............075% -- 100% free space blocks...........1275% -- 100% free space bytes............98304Unused Blocks...........................0Unused Bytes............................0Total Blocks............................17Total bytes.............................139264
PL/SQL procedure successfully completed
上面是没降低HWM,如果载降低HWM,看看效果 SQL> alter table test3 shrink space cascade; Table altered SQL> exec show_space_t('TEST3','auto','table','Y'); Total Blocks............................24Total Bytes.............................196608Unused Blocks...........................0Unused Bytes............................0Last Used Ext FileId....................31Last Used Ext BlockId...................481897Last Used Block.........................8 *************************************************0% -- 25% free space blocks.............10% -- 25% free space bytes..............819225% -- 50% free space blocks............225% -- 50% free space bytes.............1638450% -- 75% free space blocks............050% -- 75% free space bytes.............075% -- 100% free space blocks...........075% -- 100% free space bytes............0Unused Blocks...........................0Unused Bytes............................0Total Blocks............................17Total bytes.............................139264
PL/SQL procedure successfully completed
看来用shrink space整理碎片不彻底,再来看看move的方式
SQL> alter table test3 move; Table altered SQL> exec show_space_t('TEST3','auto','table','Y'); Total Blocks............................32Total Bytes.............................262144Unused Blocks...........................6Unused Bytes............................49152Last Used Ext FileId....................31Last Used Ext BlockId...................485081Last Used Block.........................2 *************************************************0% -- 25% free space blocks.............00% -- 25% free space bytes..............025% -- 50% free space blocks............025% -- 50% free space bytes.............050% -- 75% free space blocks............050% -- 75% free space bytes.............075% -- 100% free space blocks...........075% -- 100% free space bytes............0Unused Blocks...........................0Unused Bytes............................0Total Blocks............................22Total bytes.............................180224 PL/SQL procedure successfully completed 效果很明显,整理的很彻底
测试结论: 虽然alter table move和shrink space,都是通过物理调整rowid来整理碎片的,但shrink space整理的不彻底,他好像不是重组,而是尽可能的合并,随意会残留一些block无法整理
注意: 1.再用alter table table_name move时,表相关的索引会失效,所以之后还要执行 alter index index_name rebuild online; 最后重新编译数据库所有失效的对象 2. 在用alter table table_name shrink space cascade时,他相当于alter table table_name move和alter index index_name rebuild online. 所以只要编译数据库失效的对象就可以
alter table move和shrink space除了碎片整理的效果有时不一样外,还有什么其他的不同呢 1. Move会移动高水位,但不会释放申请的空间,是在高水位以下(below HWM)的操作。2. shrink space 同样会移动高水位,但也会释放申请的空间,是在高水位上下(below and above HWM)都有的操作。
下面通过实验来验证 SQL> drop table test3; Table dropped
SQL>SQL> create table test3 as 2 select rownum id, 3 dbms_random.string('a', round(dbms_random.value(0,10))) col1, 4 trunc(sysdate) - dbms_random.value(1, 365*2) col2 5 from dual connect by rownum analyze table test3 compute statistics; Table analyzed
SQL> col segment_name for a10;SQL> select us.segment_name,us.extents,us.blocks from user_segments us where us.segment_name=upper('test3'); SEGMENT_NA EXTENTS BLOCKS---------- ---------- ----------TEST3 5 40
SQL> col table_name for a10;SQL> select table_name,blocks,t.empty_blocks from user_tables t where t.table_name=upper('test3'); TABLE_NAME BLOCKS EMPTY_BLOCKS---------- ---------- ------------TEST3 37 3 SQL> 从以上查询可以看出共分了5个extents,使用了37个blocks,这37也就是test3的HWM
SQL> delete from test3 where rownum commit; Commit complete
SQL> analyze table test3 compute statistics; Table analyzed
SQL> col segment_name for a10;SQL> select us.segment_name,us.extents,us.blocks from user_segments us where us.segment_name=upper('test3'); SEGMENT_NA EXTENTS BLOCKS---------- ---------- ----------TEST3 5 40
SQL> col table_name for a10;SQL> select table_name,blocks,t.empty_blocks from user_tables t where t.table_name=upper('test3'); TABLE_NAME BLOCKS EMPTY_BLOCKS---------- ---------- ------------TEST3 37 3
SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) used_blocks from test3; USED_BLOCKS----------- 17 我们从查询中可以发现test3的HWM没有变换还是37blocks,tests总共空间为40blocks。经过删除后test3实际用的块是17个 下面我们用move降低下HWM
SQL> alter table test3 move; Table altered
SQL> col segment_name for a10;SQL> select us.segment_name,us.extents,us.blocks from user_segments us where us.segment_name=upper('test3'); SEGMENT_NA EXTENTS BLOCKS---------- ---------- ----------TEST3 3 24
SQL> col table_name for a10;SQL> select table_name,blocks,t.empty_blocks from user_tables t where t.table_name=upper('test3'); TABLE_NAME BLOCKS EMPTY_BLOCKS---------- ---------- ------------TEST3 37 3
user_tables里的数据没有变化,哦,原来是忘记analyze了,从这里也可以看出user_segments是oracle自动维护的。
SQL> analyze table test3 compute statistics; Table analyzed
SQL> select us.segment_name,us.extents,us.blocks from user_segments us where us.segment_name=upper('test3'); SEGMENT_NA EXTENTS BLOCKS---------- ---------- ----------TEST3 3 24
SQL> select table_name,blocks,t.empty_blocks from user_tables t where t.table_name=upper('test3'); TABLE_NAME BLOCKS EMPTY_BLOCKS---------- ---------- ------------TEST3 20 4 SQL> 现在再来看hwm变为20了,已经降下来了啊,空间也收缩了,从40blocks降到24blocks(注意收缩到initial指定值)。但shrink space就收缩到存储数据的最小值,下面测试说明 创建测试表: SQL> create table test5 (id number) storage (initial 1m next 1m); Table created
初始化数据 SQL>SQL> begin 2 for i in 1..100000 loop 3 insert into test5 values(i); 4 end loop; 5 end; 6 / PL/SQL procedure successfully completed
SQL> analyze table test5 compute statistics; Table analyzed
SQL> select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME='TEST5'; SEGMENT_NA EXTENTS BLOCKS INIT---------- ---------- ---------- ----------TEST5 17 256 1
SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST5'; TABLE_NAME BLOCKS EMPTY_BLOCKS---------- ---------- ------------TEST5 180 76
可以从查询数据看出,test5初始化1m即128个blocks,但数据比较多,所以又按next参数要求扩展了1m空间,扩展了17个extents。这里的test5总空间大小为256个blocks,使用空间为180blocks,HWM也是180blocks SQL> delete from test5 where rownum analyze table test5 compute statistics; Table analyzed
SQL> select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME='TEST5'; SEGMENT_NA EXTENTS BLOCKS INIT---------- ---------- ---------- ----------TEST5 17 256 1
SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST5'; TABLE_NAME BLOCKS EMPTY_BLOCKS---------- ---------- ------------TEST5 180 76
整理碎片,降低HWM SQL> alter table test5 move; Table altered SQL> analyze table test5 compute statistics; Table analyzed
SQL> select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME='TEST5'; SEGMENT_NA EXTENTS BLOCKS INIT---------- ---------- ---------- ----------TEST5 16 128 1
SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST5'; TABLE_NAME BLOCKS EMPTY_BLOCKS---------- ---------- ------------TEST5 85 43
从上面的查询数据可以看出HWM已经从180降低到85,test5总大小从256blocks收缩到128个blocks(initial指定大小)。 下面看看用shrink space收缩空间的情况 SQL> alter table test5 enable row movement; Table altered SQL> alter table test5 shrink space; Table altered
SQL> select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME='TEST5'; SEGMENT_NA EXTENTS BLOCKS INIT---------- ---------- ---------- ----------TEST5 11 88 1
SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST5'; TABLE_NAME BLOCKS EMPTY_BLOCKS---------- ---------- ------------TEST5 85 43 SQL> 从上面的数据可以看到test5进一步从128个blocks降低到88个blocks 结论: shrink space收缩到数据存储的最小值,alter table move(不带参数)收缩到initial指定值,也可以用alter table test5 move storage(initial 500k)指定收缩的大小,这样可以达到shrink space效果 经过以上测试,得出的两个结论,到底用哪一个命令来整理碎片,消除行迁移呢?这就要根据实际业务需要,如果你只是收缩空间,数据增长很慢,那用shrink可以但是如果数据增长很快的话,用move就比较合适,避免再重新分配空间啊 备注:在10g之后,整理碎片消除行迁移的新增功能shrink space alter table shrink space [ | compact | cascade ]; compact:这个参数当系统的负载比较大时可以用,不降低HWM。如果系统负载较低时,直接用alter table table_name shrink space就一步到位了 cascade:这个参数是在shrink table的时候自动级联索引,相当于rebulid index。 普通表: shrink必须开启行迁移功能。alter table table_name enable row movement ; 保持HWM,相当于把块中数据打结实了alter table table_name shrink space compact; 回缩表与降低HWMalter table table_name shrink space; 回缩表与相关索引,降低HWMalter table table_name shrink space cascade; 回缩索引与降低HWMalter index index_name shrink space 虽然在10g中可以用shrink ,但也有些限制: 1). 对cluster,cluster table,或具有Long,lob类型列的对象 不起作用。2). 不支持具有function-based indexes 或 bitmap join indexes的表3). 不支持mapping 表或index-organized表。4). 不支持compressed 表
|
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |