ora-03297处理

来源：互联网发布：config.json是什么编辑：程序博客网时间：2024/06/14 21:53

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 200set pagesize 2000--conn / as sysdbacol DUMMY noprintcompute sum of TotalMB on DUMMYcompute sum of UsedMB on DUMMYcompute sum of FreeMB on DUMMYbreak on DUMMYcol "% Used" for a6col "Used" for a22select 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) twhere 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 allselect 'alter table  '||owner||'.'||table_name||' shrink space;' from dba_tables where tablespace_name like'%xxx%' union allselect '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 a30select * from (select tablespace_name, owner, segment_name topseg_segment_name, partition_name,segment_type, round(bytes/1048576) MB from dba_segmentswhere upper(tablespace_name) like upper('%xxx%')order by MB desc)where rownum <= 50;发现有以BIN$开头的对象的话，清空下回收站。purge dba_recyclebin;

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

0 0