收缩表空间-ORA-03297错误解决

1.查看数据文件的大小

 

select tablespace_name ,sum(bytes) / 1024 / 1024 as MB,file_name

from dba_data_files

where 　tablespace_name = 'APPS_UNDOTS1'

group by tablespace_name,file_name;

 

2.减少文件的大小

ALTER DATABASE DATAFILE 'D:\ora_tablespace\GCOMM2.dbf' RESIZE 5000M;

 

提示：ORA-03297: file contains used data beyond requested RESIZE value

 

 

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

原因2：表的初始大小分配的太大

一、原因1，可以采用移动表空间的方法来减小block_id

 

 

--a)查找file_id

select tablespace_name ,sum(bytes) / 1024 / 1024 as MB,file_name,file_id

from dba_data_files

where 　tablespace_name = 'APPS_UNDOTS1'

group by tablespace_name,file_name,file_id;

 

--b)找出目前APPS_UNDOTS1表空间,所要修改文件的 最大的block_id

 

select max(block_id) from dba_extents where tablespace_name = 'APPS_UNDOTS1' and  file_id=379;

 MAX(BLOCK_ID)

 -----------

 1310577

--c)计算目前最大块的段所占用的空间（该数据库的block大小是8092）

show parameter db_block_size;

结果为8192,就是8K。

然后计算所有数据块占用的物理空间

select 1310577*8/1024 from dual;

10238.8828125

结果为10238.8828125M，就是10G多

4. 然后我们知道目前用了10G多，我们就可以把数据文件大小Resize到10G多一点

ALTER DATABASE DATAFILE 'D:\ORADATA\ECSS20' RESIZE 10240M;

 

二、征对原因2可通过减小表或索引的初始化大小来收缩表空间
--征对块大的表 减小其初始分配大小
SELECT DISTINCT 'alter table ' || segment_name ||
                ' MOVE STORAGE(INITIAL 1M) ;'
  FROM dba_extents
 WHERE tablespace_name = 'GCOMM2'
   AND segment_type = 'TABLE'
   AND file_id = 11
   AND block_id > 30336;

--征对块大的索引减小其初始分配大小
SELECT DISTINCT 'alter index ' || segment_name ||
                ' REBUILD  tablespace GCOMM2 storage(initial 64K next 1M pctincrease 0);'
  FROM dba_extents
 WHERE tablespace_name = 'GCOMM2'
   AND segment_type = 'INDEX'
   AND file_id = 11
   AND block_id > 30336;

然后再收缩表空间
ALTER DATABASE DATAFILE 'D:\ora_tablespace\GCOMM2.dbf' RESIZE 1000M;
执行成功，原先为7G多。