Oracle shrink table收缩表使用场景

﹏ヽ暗。殇╰゛Y 2022-08-05 01:20 247阅读 0赞

现场有一个外网的环境表空间严重不足，客户不想扩容，要清理一部分数据，都是大字段的blob. 表空间是1T，已经使用0.99T，删除2015年之前的blob后，通过附件的基础信息表查出还有200G的数据，不过表空间并没有释放（高水位线），这个时候有几种方式处理：

1. 新建一张表，把剩下的数据导入，不可行，没有这么大的空间了.

2. 用数据泵或用exp导出大字段，删除表后，然后导入.

3. 用shrink table.

最终选择用第三种方案，以下是试验，blob的实验也测试过，可以收缩，如何插入blob，参考http://blog.csdn.net/stevendbaguo/article/details/28636369 在此不再赘述.

select \* from v$version;  
BANNER  
\-------------------------------------------------------------------------------  
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production  
PL/SQL Release 11.2.0.1.0 - Production  
CORE    11.2.0.1.0      Production  
TNS for Linux: Version 11.2.0.1.0 - Production  
NLSRTL Version 11.2.0.1.0 - Production

create user TEST\_DB  
  identified by TEST\_DB  
  default tablespace testdb\_tbs  
  temporary tablespace TEMP;  
grant connect to TEST\_DB;  
grant dba to TEST\_DB;  
grant resource to TEST\_DB;  
grant select any dictionary to TEST\_DB;  
  
create tablespace testdb\_tbs datafile '/home/oracle/app/oradata/orcl/testdb\_tbs.dbf' size 100m autoextend off;  
create table test as select \* from dba\_objects;  
insert into test select  \* from dba\_objects;--执行多次，直到表空间使用率为90%以上  
  
SELECT Upper(F.TABLESPACE\_NAME)         "表空间名",  
       D.TOT\_GROOTTE\_MB                 "表空间大小(M)",  
       D.TOT\_GROOTTE\_MB - F.TOTAL\_BYTES "已使用空间(M)",  
       F.TOTAL\_BYTES                    "空闲空间(M)",         
       To\_char(Round(( D.TOT\_GROOTTE\_MB - F.TOTAL\_BYTES ) / D.TOT\_GROOTTE\_MB \* 100, 2), '990.99')  
       || '%'                           "使用比"  
FROM   (SELECT TABLESPACE\_NAME,  
               Round(Sum(BYTES) / ( 1024 \* 1024 ), 2) TOTAL\_BYTES,  
               Round(Max(BYTES) / ( 1024 \* 1024 ), 2) MAX\_BYTES  
        FROM   SYS.DBA\_FREE\_SPACE  
        GROUP  BY TABLESPACE\_NAME) F,  
       (SELECT DD.TABLESPACE\_NAME,  
               Round(Sum(DD.BYTES) / ( 1024 \* 1024 ), 2) TOT\_GROOTTE\_MB  
        FROM   SYS.DBA\_DATA\_FILES DD  
        having DD.TABLESPACE\_NAME='TESTDB\_TBS'  
        GROUP  BY DD.TABLESPACE\_NAME) D  
WHERE  D.TABLESPACE\_NAME = F.TABLESPACE\_NAME;  
表空间名      表空间大小(M) 已使用空间(M) 空闲空间(M) 使用比  
\------------- ------------- ------------- ----------- -----------  
TESTDB\_TBS              100            97           3   97.00%  
  
select count(1) from test;  
  COUNT(1)  
\----------  
    802835  
      
delete from test where rownum <=2835;  
commit;  
      
select count(1) from test;  
 COUNT(1)  
\---------  
   800000  
  
**--再次查询表空间，TESTDB\_TBS空间没有释放**  
  
alter table test shrink space cascade;  
alter table test shrink space cascade  
\*  
第 1 行出现错误:  
ORA-10636: ROW MOVEMENT is not enabled  
  
  
**alter table test enable row movement ;**  
表已更改。  
  
**alter table test shrink space cascade;**\--连同索引一起收缩  
表已更改。  
  
SELECT Upper(F.TABLESPACE\_NAME)         "表空间名",  
       D.TOT\_GROOTTE\_MB                 "表空间大小(M)",  
       D.TOT\_GROOTTE\_MB - F.TOTAL\_BYTES "已使用空间(M)",  
       F.TOTAL\_BYTES                    "空闲空间(M)",         
       To\_char(Round(( D.TOT\_GROOTTE\_MB - F.TOTAL\_BYTES ) / D.TOT\_GROOTTE\_MB \* 100, 2), '990.99')  
       || '%'                           "使用比"  
FROM   (SELECT TABLESPACE\_NAME,  
               Round(Sum(BYTES) / ( 1024 \* 1024 ), 2) TOTAL\_BYTES,  
               Round(Max(BYTES) / ( 1024 \* 1024 ), 2) MAX\_BYTES  
        FROM   SYS.DBA\_FREE\_SPACE  
        GROUP  BY TABLESPACE\_NAME) F,  
       (SELECT DD.TABLESPACE\_NAME,  
               Round(Sum(DD.BYTES) / ( 1024 \* 1024 ), 2) TOT\_GROOTTE\_MB  
        FROM   SYS.DBA\_DATA\_FILES DD  
        having DD.TABLESPACE\_NAME='TESTDB\_TBS'  
        GROUP  BY DD.TABLESPACE\_NAME) D  
WHERE  D.TABLESPACE\_NAME = F.TABLESPACE\_NAME;  
表空间名     表空间大小(M) 已使用空间(M) 空闲空间(M) 使用比  
\------------ ------------- ------------- ----------- ----------------  
TESTDB\_TBS             100         90.94        9.06   90.94%