ora-03297处理

1.先查看下表空间的使用情况，如果使用了1G，要resize到800M，当然是不行的。使用如下脚本

-------------------------------------------------------------------------------------------
-- SCRIPT:  DF.SQL
-- PURPOSE: Show Oracle tablespace free space in Unix df style
-- AUTHOR:  Tanel Poder [ http://www.tanelpoder.com ]
-- DATE:    2003-05-01
-------------------------------------------------------------------------------------------
set line 200
set pagesize 2000
--conn / as sysdba
col DUMMY noprint
compute sum of TotalMB on DUMMY
compute sum of UsedMB on DUMMY
compute sum of FreeMB on DUMMY
break on DUMMY
col "% Used" for a6
col "Used" for a22
select NULL DUMMY, t.tablespace_name, t.mb "TotalMB", t.mb - nvl(f.mb,0) "UsedMB", nvl(f.mb,0) "FreeMB",
       lpad(ceil((1-nvl(f.mb,0)/t.mb)*100)||'%', 6) "% Used", t.ext "Ext",
       '|'||rpad(lpad('#',ceil((1-nvl(f.mb,0)/t.mb)*20),'#'),20,' ')||'|' "Used"
from (
  select tablespace_name, trunc(sum(bytes)/1048576) MB
  from dba_free_space
  group by tablespace_name
 union all
  select tablespace_name, trunc(sum(bytes_free)/1048576) MB
  from v$temp_space_header
  group by tablespace_name
) f, (
  select tablespace_name, trunc(sum(bytes)/1048576) MB, max(autoextensible) ext
  from dba_data_files
  group by tablespace_name
 union all
  select tablespace_name, trunc(sum(bytes)/1048576) MB, max(autoextensible) ext
  from dba_temp_files
  group by tablespace_name
) t
where t.tablespace_name = f.tablespace_name (+)
 order by 3
--order by t.tablespace_name
/

2.如果表空间只用了800M，但是resize 的是1G呢。以下脚本对XXX tablespace下的表做shrink.跑下生成的sql就OK

select 'alter table  '||owner||'.'||table_name||' enable row movement;' from dba_tables where tablespace_name like'%xxx%' 
union all
select 'alter table  '||owner||'.'||table_name||' shrink space;' from dba_tables where tablespace_name like'%xxx%' 
union all
select 'alter table  '||owner||'.'||table_name||' enable row movement;' from dba_tables where tablespace_name like'%xxx%' ;

3.跑以下脚本看下BLOCK_ID有没有出现很大的跳跃。比如4000跳到10000

select segment_name, block_id from dba_extents where tablespace_name like'%XXX%' order by block_id desc;

有的话，将那些高位的SEGMENT_NAME（也就是索引啦）记录下来运行如下 脚本：

alter index owner.aaa rebuild tablespace xxx;

4.这时resize应当是OK的，如果还不行，可能是有被回收的对象。

col topseg_segment_name head SEGMENT_NAME for a30


select * from (
	select 
		tablespace_name, 
		owner, 
		segment_name topseg_segment_name, 
		partition_name,
		segment_type, 
		round(bytes/1048576) MB 
	from dba_segments
	where upper(tablespace_name) like upper('%xxx%')
	order by MB desc
)
where rownum <= 50;
发现有以BIN$开头的对象的话，清空下回收站。
purge dba_recyclebin;

--------- 注：最好先看第三步，针对那些在高位的表或是索引做调整，因为我当前的表空间比较少，而且没在使用，所以直接把所有的表都shrink了。