Oracle数据库在删除表中记录后怎么才能释放表及表空间大小

paisen

1.查看一个表所占的空间大小：
SELECT SEGMENT_NAME,
   TABLESPACE_NAME,
   BYTES B,
   BYTES / 1024 KB,
   BYTES / 1024 / 1024 MB
  FROM USER_SEGMENTS
WHERE segment_name = 'T_RL_INTG_LOGALL'
AND TABLESPACE_NAME = 'FMIS9999';
2.查看一个表空间所占的实际大小：
SELECT SUM(BYTES) / 1024 / 1024 ||'MB' FROM USER_SEGMENTS U  WHERE TABLESPACE_NAME = 'FMIS9999';
3.查看一个表空间对应的数据文件：
SELECT * FROM DBA_DATA_FILES D WHERE D.TABLESPACE_NAME = 'FMIS9999';
4.查看表空间的使用情况：
SELECT A.TABLESPACE_NAME,
   FILENUM,
   TOTAL "TOTAL (MB)",
   F.FREE "FREE (MB)",
   TO_CHAR(ROUND(FREE * 100 / TOTAL, 2), '990.00') "FREE%",
   TO_CHAR(ROUND((TOTAL - FREE) * 100 / TOTAL, 2), '990.00') "USED%",
   ROUND(MAXSIZES, 2) "MAX (MB)"
  FROM (SELECT TABLESPACE_NAME,
            COUNT(FILE_ID) FILENUM,
            SUM(BYTES / (1024 * 1024)) TOTAL,
            SUM(MAXBYTES) / 1024 / 1024 MAXSIZES
      FROM DBA_DATA_FILES
      GROUP BY TABLESPACE_NAME) A,
   (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES / (1024 * 1024))) FREE
      FROM DBA_FREE_SPACE
      GROUP BY TABLESPACE_NAME) F
WHERE A.TABLESPACE_NAME = F.TABLESPACE_NAME
5.查看数据文件的实际使用情况：
SELECT CEIL(MAX_BLOCK * BLOCK_SIZE / 1024)
  FROM (SELECT MAX(BLOCK_ID) MAX_BLOCK
      FROM DBA_EXTENTS
      WHERE FILE_ID IN (SELECT FILE_ID
                           FROM DBA_DATA_FILES D
                        WHERE D.TABLESPACE_NAME = 'FMIS9999')) M,
   (SELECT VALUE / 1024 BLOCK_SIZE
      FROM V$PARAMETER
      WHERE NAME = 'db_block_size') B

一、创建一个有十万条记录的测试表t_rl_intg_logall ，查看其所占空间大小3873M
delete t_rl_intg_logall。再次查看大小不会变，此时执行select * from t_rl_intg_logall会发现速度超极慢，查询结果却是空，查看其COST，发现是10万多。很难理解吧，其实是其所占空间没有释放的缘故。
执行alter table t_rl_intg_logall move 或 alter table t_rl_intg_logall move storage(initial 64k)
或alter table t_rl_intg_logall deallocate unused或 alter table t_rl_intg_logall shrink space.
注意：因为alter table t_rl_intg_logall move 是通过消除行迁移，清除空间碎片，删除空闲空间，实现缩小所占的空间，但会导致此表上的索引无效（因为ROWID变了，无法找到），所以执行 move 就需要重建索引。
找到表对应的索引。
select index_name,table_name,tablespace_name,index_type,status,table_owner from dba_indexes WHERE table_owner='FMIS9999' ;
根据status 的值，重建无效的就行了。
sql='alter index '||index_name||' rebuild'; 使用存储过程执行，稍微安慰。
还要注意alter table move过程中会产生锁，应该避免在业务高峰期操作！
再次查看其所占空间大小，发现已经很小了，再一次执行查询，很快了吧。
另外说明：truncate table t_rl_intg_logall 会执行的更快，而且其所占的空间也会释放，我想应该是truncate 语句执行后是不会进入oracle回收站（recylebin）的缘故。如果drop 一个表加上purge 也不会进回收站(在此里面的数据可以通过flashback找回)。
不管是delete还是truncate 相应数据文件的大小并不会改变，如果想改变数据文件所占空间大小可执行如下语句：alter database datafile 'filename' resize 8g重定义数据文件的大小（不能小于该数据文件已用空间的大小）。
另补充一些PURGE知识
Purge操作：
1). Purge tablespace tablespace_name : 用于清空表空间的Recycle Bin
2). Purge tablespace tablespace_name user user_name: 清空指定表空间的Recycle Bin中指定用户的对象
3). Purge recyclebin: 删除当前用户的Recycle Bin中的对象
4). Purge dba_recyclebin: 删除所有用户的Recycle Bin中的对象，该命令要sysdba权限
5). Drop table table_name purge:  删除对象并且不放在Recycle Bin中，即永久的删除，不能用Flashback恢复。
6). Purge index recycle_bin_object_name：当想释放Recycle bin的空间，又想能恢复表时，可以通过释放该对象的index所占用的空间来缓解空间压力。因为索引是可以重建的。
二、如果某些表占用了数据文件的最后一些块，则需要先将该表导出或移动到其他的表空间中，然后删除表，再进行收缩。不过如果是移动到其他的表空间，需要重建其索引。

1）SQL> alter table t_obj move tablespace t_tbs1; ---移动表到其它表空间

也可以直接使用exp和imp来进行

2）SQL>alter owner.index_name rebuild;    --重建索引

3）删除原来的表空间
三：对表分析之后也可以优化（本人没有试过）
analyze table ysgl_compile_reqsub
compute statistics for all indexes;
也要看情况，不是什么情况都可以优化，等下次有机会再测试一下。
————————————————
版权声明：本文为CSDN博主「wangl2014」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/wangl2014/java/article/details/84337994

图文精华

Oracle数据库在删除表中记录后 怎么才能释放表及表空间大小

Oracle数据库在删除表中记录后怎么才能释放表及表空间大小