oracle shrink space 与move

「爱情、让人受尽委屈。」 2022-06-16 06:18 129阅读 0赞

\--表分配空间大小  
select segment\_name, bytes / 1024 / 1024  
  from user\_segments a  
 where a.segment\_name = 'TB\_NAME';  
\--表实际占用空间大小  
select num\_rows \* avg\_row\_len / 1024 / 1024  
  from user\_tables  
 where table\_name = 'TB\_NAME';  
  
  
  
  
\--需要压缩的段  
select to\_number(a.reclaimable\_space / a.allocated\_space) rate,  
       replace(a.c3, '"', '') || ';' move\_script,  
       replace(replace(a.c2, '"', '') || ';', 'COMPACT', '') shrink\_script,  
       replace(a.c2, '"', '') || ';' busy\_shrink\_scripts,  
       a.\*  
  from table(dbms\_space.asa\_recommendations()) a  
 where to\_number(a.reclaimable\_space / a.allocated\_space) > 0.2  
 order by a.reclaimable\_space / a.allocated\_space desc;  
  
  
\--Shrink Segment能压缩数据段、整理数据碎片、降低高水位，以提高性能、节省空间。  
\--如遇到需要回收空间的，表空间可回收的最小值 参见reference   dba\_extents里block\_id为start\_block\_id  
select max(a.block\_id+a.blocks)\*8/1024 from dba\_extents a where file\_id = 8;  
alter database datafile '/XXXXXX/xxxx.dbf' resize --  max(a.block\_id+a.blocks)\*8/1024 ;  
  
  
\--存在行迁移问题的objects(move之后对应对的索引需要重建)  
select 'alter table ' || b.attr1 || '.' || b.attr2 || ' move pctfree XX;' move\_script,  
       a.task\_id,  
       a.type,  
       a.message,  
       a.more\_info  
  from dba\_advisor\_findings a, dba\_advisor\_objects b  
 where a.task\_id = b.task\_id  
   and a.object\_id = b.object\_id  
   and a.message =  
       'The object has chained rows that can be removed by re-org.'  
 order by a.task\_id desc;  
\--重建索引  
SELECT STATUS,  
       'alter index ' || a.owner || '.' || a.index\_name || ' rebuild;'  
  FROM DBA\_INDEXES A  
 WHERE A.owner = 'xxx'  
   AND A.table\_name = 'yyy';  
    
  
  
alter table scott.emp shrink space 与 ALTER TABLE SCOTT.EMP MOVE 区别  
1.都会去除hmw（高水位线），但 move 不会压缩分配的空间   注 :move可以通过制定storage参数做到真正压缩分配空间  
2.使用 move 时会改变一些记录的 rowid，所以move后索引会变为无效，需要rebuild , 使用 shrink space 时，索引会自动维护  
3.使用 shrink space 要先启用表的行迁移 ( alter table scott.emp enable row movement)  
4.shrink space 需要在表空间是自动段空间管理的，所以system表空间上的表无法shrinkspace;  
5.如果在业务繁忙时做压缩，可以先 shrink space compact，来压缩，shrink space cascade 会同时压缩索引  
  
  
  
  
\--block信息在统计信息收集完成后改变  
SELECT A.BLOCKS,A.LAST\_ANALYZED,A.\* FROM USER\_TABLES A WHERE A.TABLE\_NAME = 'TB\_NAME';  
\--block信息立即改变，同时  user\_segments.blocks 一般大于user\_table.blocks  差值为 segment\_head占用的存储。  
SELECT A.BLOCKS,A.\* FROM USER\_SEGMENTS A WHERE A.SEGMENT\_NAME = 'TB\_NAME';