本文共 8784 字，大约阅读时间需要 29 分钟。

有的时候为了解决磁盘空间不足等情况，需要把一些表空间大小resize一下，其实语句很简单，如下：

SQL> ALTER DATABASE  2    TEMPFILE '/opt/database/ebs/db/data/temp12.dbf'  3   RESIZE 4G;ALTER DATABASE*ERROR at line 1:ORA-03297: file contains used data beyond requested RESIZE value

如上：ora-03297 报错估计大家在resize的时候会经常遇到，那么如何判定resize 大小准确的大小呢，下面给出一个脚本，利用该脚本来生成resize语句：

相关脚本连接：

具体如下：

REM Script is meant for Oracle version 9 and higherREM -----------------------------------------------set serveroutput onexec dbms_output.enable(1000000);declarecursor c_dbfile isselect f.tablespace_name,f.file_name,f.file_id,f.blocks,t.block_size,decode(t.allocation_type,'UNIFORM',t.initial_extent/t.block_size,0) uni_extent,decode(t.allocation_type,'UNIFORM',(128+(t.initial_extent/t.block_size)),128) file_min_sizefrom dba_data_files f,dba_tablespaces twhere f.tablespace_name = t.tablespace_nameand t.status = 'ONLINE'order by f.tablespace_name,f.file_id;cursor c_freespace(v_file_id in number) isselect block_id, block_id+blocks max_blockfrom dba_free_spacewhere file_id = v_file_idorder by block_id desc;/* variables to check settings/values */dummy number;checkval varchar2(10);block_correction1 number;block_correction2 number;/* running variable to show (possible) end-of-file */file_min_block number;/* variables to check if recycle_bin is on and if extent as checked is in ... */recycle_bin boolean:=false;extent_in_recycle_bin boolean;/* exception handler needed for non-existing tables note:344940.1 */sqlstr varchar2(100);table_does_not_exist exception;pragma exception_init(table_does_not_exist,-942);/* variable to spot space wastage in datafile of uniform tablespace */space_wastage number;begin/* recyclebin is present in Oracle 10.2 and higher and might contain extent as checked */beginselect value into checkval from v$parameter where name = 'recyclebin';if checkval = 'on'thenrecycle_bin := true;end if;exceptionwhen no_data_foundthenrecycle_bin := false;end;/* main loop */for c_file in c_dbfileloop/* initialization of loop variables */dummy :=0;extent_in_recycle_bin := false;file_min_block := c_file.blocks;beginspace_wastage:=0; /* reset for every file check */<
   
    >for c_free in c_freespace(c_file.file_id)loop/* if blocks is an uneven value there is a need to correctwith -1 to compare with end-of-file which is even */block_correction1 := (0-mod(c_free.max_block,2));block_correction2 := (0-mod(c_file.blocks,2));if file_min_block+block_correction2 = c_free.max_block+block_correction1then/* free extent is at end so file can be resized */file_min_block := c_free.block_id;/* Uniform sized tablespace check if space at end of fileis less then uniform extent size */elsif (c_file.uni_extent !=0) and ((c_file.blocks - c_free.max_block) < c_file.uni_extent)then/* uniform tablespace which has a wastage of space in datafiledue to fact that space at end of file is smaller than uniform extent size */space_wastage:=c_file.blocks - c_free.max_block;file_min_block := c_free.block_id;else/* no more free extent at end of file, file cannot be further resized */exit check_free;end if;end loop;end;/* check if file can be resized, minimal size of file 128 {+ initial_extent} blocks */if (file_min_block = c_file.blocks) or (c_file.blocks <= c_file.file_min_size)thendbms_output.put_line('Tablespace: '||c_file.tablespace_name||' Datafile: '||c_file.file_name);dbms_output.put_line('cannot be resized no free extents found');dbms_output.put_line('Note: for some cases, dba_free_spaces data is not accurate, and this script does not work for such cases. You may want to manually check if the datafile is feasible to be resized');dbms_output.put_line('.');else/* file needs minimal no of blocks which does vary over versions,using safe value of 128 {+ initial_extent} */if file_min_block < c_file.file_min_sizethenfile_min_block := c_file.file_min_size;end if;dbms_output.put_line('Tablespace: '||c_file.tablespace_name||' Datafile: '||c_file.file_name);dbms_output.put_line('current size: '||(c_file.blocks*c_file.block_size)/1024||'K'||' can be resized to: '||round((file_min_block*c_file.block_size)/1024)||'K (reduction of: '||round(((c_file.blocks-file_min_block)/c_file.blocks)*100,2)||' %)');/* below is only true if recyclebin is on */if recycle_binthenbeginsqlstr:='select distinct 1 from recyclebin$ where file#='||c_file.file_id;execute immediate sqlstr into dummy;if dummy > 0thendbms_output.put_line('Extents found in recyclebin for above file/tablespace');dbms_output.put_line('Implying that purge of recyclebin might be needed in order to resize');dbms_output.put_line('SQL> purge tablespace '||c_file.tablespace_name||';');end if;exceptionwhen no_data_foundthen null;when table_does_not_existthen null;end;end if;dbms_output.put_line('SQL> alter database datafile '''||c_file.file_name||''' resize '||round((file_min_block*c_file.block_size)/1024)||'K;');if space_wastage!=0thendbms_output.put_line('Datafile belongs to uniform sized tablespace and is not optimally sized.');dbms_output.put_line('Size of datafile is not a multiple of NN*uniform_extent_size + overhead');dbms_output.put_line('Space that cannot be used (space wastage): '||round((space_wastage*c_file.block_size)/1024)||'K');dbms_output.put_line('For optimal usage of space in file either resize OR increase to: '||round(((c_file.blocks+(c_file.uni_extent-space_wastage))*c_file.block_size)/1024)||'K');end if;dbms_output.put_line('.');end if;end loop;end;/
   

将上面的脚本保存到oracle_check_resize.sql,然后我们运行一下：

[orahec@db-hx-189-208 awr]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Tue Dec 11 11:26:15 2018Copyright (c) 1982, 2013, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> spool oracle_check_resize.txtSQL> @oracle_check_resize.sqlPL/SQL procedure successfully completed.Tablespace: HEC_DATA Datafile: /opt/Oracle/hecdb/oradata/HEC/hecdata01.dbfcannot be resized no free extents foundNote: for some cases, dba_free_spaces data is not accurate, and this script doesnot work for such cases. You may want to manually check if the datafile isfeasible to be resized.Tablespace: HEC_DATA Datafile: /opt/Oracle/hecdb/oradata/HEC/hecdata02.dbfcannot be resized no free extents foundNote: for some cases, dba_free_spaces data is not accurate, and this script doesnot work for such cases. You may want to manually check if the datafile isfeasible to be resized.Tablespace: HEC_DATA Datafile: /opt/Oracle/hecdb/oradata/HEC/hecdata03.dbfcurrent size: 16777216K can be resized to: 14888960K (reduction of: 11.25 %)SQL> alter database datafile '/opt/Oracle/hecdb/oradata/HEC/hecdata03.dbf'resize 14888960K;.Tablespace: HEC_DATA Datafile: /opt/Oracle/hecdb/oradata/HEC/hecdata04.dbfcannot be resized no free extents foundNote: for some cases, dba_free_spaces data is not accurate, and this script doesnot work for such cases. You may want to manually check if the datafile isfeasible to be resized.Tablespace: SYSAUX Datafile: /opt/Oracle/hecdb/oradata/HEC/sysaux01.dbfcannot be resized no free extents foundNote: for some cases, dba_free_spaces data is not accurate, and this script doesnot work for such cases. You may want to manually check if the datafile isfeasible to be resized.Tablespace: SYSAUX Datafile: /opt/Oracle/hecdb/oradata/HEC/sysaux02.dbfcurrent size: 5242880K can be resized to: 190464K (reduction of: 96.37 %)SQL> alter database datafile '/opt/Oracle/hecdb/oradata/HEC/sysaux02.dbf' resize190464K;.Tablespace: SYSTEM Datafile: /opt/Oracle/hecdb/oradata/HEC/system01.dbfcannot be resized no free extents foundNote: for some cases, dba_free_spaces data is not accurate, and this script doesnot work for such cases. You may want to manually check if the datafile isfeasible to be resized.Tablespace: SYSTEM Datafile: /opt/Oracle/hecdb/oradata/HEC/system02.dbfcurrent size: 8007680K can be resized to: 8001536K (reduction of: .08 %)SQL> alter database datafile '/opt/Oracle/hecdb/oradata/HEC/system02.dbf' resize8001536K;.Tablespace: SYSTEM Datafile: /opt/Oracle/hecdb/oradata/HEC/system03.dbfcurrent size: 16777216K can be resized to: 1378304K (reduction of: 91.78 %)SQL> alter database datafile '/opt/Oracle/hecdb/oradata/HEC/system03.dbf' resize1378304K;.Tablespace: UNDOTBS1 Datafile: /opt/Oracle/hecdb/oradata/HEC/undotbs01.dbfcurrent size: 28385280K can be resized to: 24642624K (reduction of: 13.19 %)SQL> alter database datafile '/opt/Oracle/hecdb/oradata/HEC/undotbs01.dbf'resize 24642624K;.Tablespace: USERS Datafile: /opt/Oracle/hecdb/oradata/HEC/users01.dbfcurrent size: 5120K can be resized to: 1344K (reduction of: 73.75 %)SQL> alter database datafile '/opt/Oracle/hecdb/oradata/HEC/users01.dbf' resize1344K;.PL/SQL procedure successfully completed.SQL> exit

转载地址：http://cjhji.baihongyu.com/