Oracle减小数据文件大小

收集表空间的统计信息，查询表空间的使用率

-- 查询非临时表空间的使用率 SELECT d.Tablespace_Name,        Space "SUM_SPACE(M)",        Blocks "SUM_BLOCKS",        Space - Nvl(Free_Space, 0) "USED_SPACE(M)",        Round((1 - Nvl(Free_Space, 0) / Space) * 100, 2) "USED_RATE(%)",        Free_Space "FREE_SPACE(M)"   FROM (SELECT Tablespace_Name,                Round(SUM(Bytes) / (1024 * 1024), 2) Space,                SUM(Blocks) Blocks           FROM Dba_Data_Files          GROUP BY Tablespace_Name) d,        (SELECT Tablespace_Name,                Round(SUM(Bytes) / (1024 * 1024), 2) Free_Space           FROM Dba_Free_Space          GROUP BY Tablespace_Name) f  WHERE d.Tablespace_Name = f.Tablespace_Name(+)

如果表空间的使用率较低，可以通过resize datafile减小数据文件的大小

需要清楚数据文件的真实使用空间，可以通过查询dba_extents

SELECT Ddf.File_Name, De.File_Id, SUM(De.Bytes) / 1024 / 1024 AS Mb   FROM Dba_Extents De   JOIN Dba_Data_Files Ddf     ON De.File_Id = Ddf.File_Id  GROUP BY Ddf.File_Name, De.File_Id;

可以看出DEV1_oim02.dbf 实际使用空间为304.6M，但实际存储中可能存在很多存储碎片，例如收缩到1024M，就会出现如下的错误。

SQL> alter database datafile 115 resize 1024M; alter database datafile 115 resize 1024M * ERROR at line 1: ORA-03297: file contains used data beyond requested RESIZE value

这种情况可能有以下原因

原因1：压缩表空间大小，最小必须是该表空间目前最大块段的大小

            ---可以通过移动表空间来收缩表空间大小。
原因2：表的初始大小分配的太大

            ---可以通过减小表或索引的初始大小来收缩表空间

但在实际过程中对于表空间中表比较多，且关系比较复杂的时候，操作是有难度的，可以直接找出最小可收缩大小。

找出目前DEV1_OIM最大的block_id

SELECT MAX(block_id)   FROM dba_extents  WHERE tablespace_name = 'DEV1_OIM';   MAX(BLOCK_ID) ------------------------- 1008888

Block大小

SQL>  show parameter db_block_size ;   NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ db_block_size                        integer     8192   SQL> SELECT 1008888 * 8192 / 1024 / 1024 FROM Dual; 7881.9375

因此该表空间收缩到7882M是没有问题的。