oracle 调整表空间大小 (resize)

﹏ヽ暗。殇╰゛Y 2022-04-05 06:52 1302阅读 0赞

有的时候为了解决磁盘空间不足等情况，需要把一些表空间大小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语句：

相关脚本连接：[https://support.oracle.com/epmos/faces/DocumentDisplay?\_afrLoop=254440300234769&id=130866.1&\_afrWindowMode=0&\_adf.ctrl-state=b3zfmsxmq\_4][https_support.oracle.com_epmos_faces_DocumentDisplay_afrLoop_254440300234769_id_130866.1_afrWindowMode_0_adf.ctrl-state_b3zfmsxmq_4]

具体如下：

REM Script is meant for Oracle version 9 and higher
    REM -----------------------------------------------
    
    set serveroutput on
    exec dbms_output.enable(1000000);
    
    declare
    
    cursor c_dbfile is
    select 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_size
    from dba_data_files f,
    dba_tablespaces t
    where f.tablespace_name = t.tablespace_name
    and t.status = 'ONLINE'
    order by f.tablespace_name,f.file_id;
    
    cursor c_freespace(v_file_id in number) is
    select block_id, block_id+blocks max_block
    from dba_free_space
    where file_id = v_file_id
    order 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 */
    begin
    select value into checkval from v$parameter where name = 'recyclebin';
    if checkval = 'on'
    then
    recycle_bin := true;
    end if;
    exception
    when no_data_found
    then
    recycle_bin := false;
    end;
    
    /* main loop */
    for c_file in c_dbfile
    loop
    /* initialization of loop variables */
    dummy :=0;
    extent_in_recycle_bin := false;
    file_min_block := c_file.blocks;
    
    begin
    
    space_wastage:=0; /* reset for every file check */
    
    <<check_free>>
    
    for c_free in c_freespace(c_file.file_id)
    loop
    /* if blocks is an uneven value there is a need to correct
    with -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_correction1
    then
    
    /* 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 file
    is 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 datafile
    due 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)
    then
    
    dbms_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_size
    then
    file_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_bin
    then
    begin
    sqlstr:='select distinct 1 from recyclebin$ where file#='||c_file.file_id;
    execute immediate sqlstr into dummy;
    
    if dummy > 0
    then
    
    dbms_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;
    exception
    when no_data_found
    then null;
    when table_does_not_exist
    then 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!=0
    then
    dbms_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 sysdba
    
    SQL*Plus: Release 11.2.0.4.0 Production on Tue Dec 11 11:26:15 2018
    
    Copyright (c) 1982, 2013, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    SQL> spool oracle_check_resize.txt
    SQL> @oracle_check_resize.sql
    
    PL/SQL procedure successfully completed.
    
    Tablespace: HEC_DATA Datafile: /opt/Oracle/hecdb/oradata/HEC/hecdata01.dbf
    cannot be resized no free extents found
    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
    .
    Tablespace: HEC_DATA Datafile: /opt/Oracle/hecdb/oradata/HEC/hecdata02.dbf
    cannot be resized no free extents found
    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
    .
    Tablespace: HEC_DATA Datafile: /opt/Oracle/hecdb/oradata/HEC/hecdata03.dbf
    current 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.dbf
    cannot be resized no free extents found
    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
    .
    Tablespace: SYSAUX Datafile: /opt/Oracle/hecdb/oradata/HEC/sysaux01.dbf
    cannot be resized no free extents found
    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
    .
    Tablespace: SYSAUX Datafile: /opt/Oracle/hecdb/oradata/HEC/sysaux02.dbf
    current size: 5242880K can be resized to: 190464K (reduction of: 96.37 %)
    SQL> alter database datafile '/opt/Oracle/hecdb/oradata/HEC/sysaux02.dbf' resize
    190464K;
    .
    Tablespace: SYSTEM Datafile: /opt/Oracle/hecdb/oradata/HEC/system01.dbf
    cannot be resized no free extents found
    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
    .
    Tablespace: SYSTEM Datafile: /opt/Oracle/hecdb/oradata/HEC/system02.dbf
    current size: 8007680K can be resized to: 8001536K (reduction of: .08 %)
    SQL> alter database datafile '/opt/Oracle/hecdb/oradata/HEC/system02.dbf' resize
    8001536K;
    .
    Tablespace: SYSTEM Datafile: /opt/Oracle/hecdb/oradata/HEC/system03.dbf
    current size: 16777216K can be resized to: 1378304K (reduction of: 91.78 %)
    SQL> alter database datafile '/opt/Oracle/hecdb/oradata/HEC/system03.dbf' resize
    1378304K;
    .
    Tablespace: UNDOTBS1 Datafile: /opt/Oracle/hecdb/oradata/HEC/undotbs01.dbf
    current 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.dbf
    current size: 5120K can be resized to: 1344K (reduction of: 73.75 %)
    SQL> alter database datafile '/opt/Oracle/hecdb/oradata/HEC/users01.dbf' resize
    1344K;
    .
    
    PL/SQL procedure successfully completed.
    
    SQL> exit

[https_support.oracle.com_epmos_faces_DocumentDisplay_afrLoop_254440300234769_id_130866.1_afrWindowMode_0_adf.ctrl-state_b3zfmsxmq_4]: https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=254440300234769&id=130866.1&_afrWindowMode=0&_adf.ctrl-state=b3zfmsxmq_4