oracle 调整表空间大小 (resize)

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

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

  1. SQL> ALTER DATABASE
  2.   2    TEMPFILE '/opt/database/ebs/db/data/temp12.dbf'
  3.   3   RESIZE 4G;
  4. ALTER DATABASE
  5. *
  6. ERROR at line 1:
  7. 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

具体如下：

  1. REM Script is meant for Oracle version 9 and higher
  2. REM -----------------------------------------------
  4. set serveroutput on
  5. exec dbms_output.enable(1000000);
  7. declare
  9. cursor c_dbfile is
  10. select f.tablespace_name,f.file_name,f.file_id,f.blocks,t.block_size
  11. ,decode(t.allocation_type,'UNIFORM',t.initial_extent/t.block_size,0) uni_extent
  12. ,decode(t.allocation_type,'UNIFORM',(128+(t.initial_extent/t.block_size)),128) file_min_size
  13. from dba_data_files f,
  14. dba_tablespaces t
  15. where f.tablespace_name = t.tablespace_name
  16. and t.status = 'ONLINE'
  17. order by f.tablespace_name,f.file_id;
  19. cursor c_freespace(v_file_id in number) is
  20. select block_id, block_id+blocks max_block
  21. from dba_free_space
  22. where file_id = v_file_id
  23. order by block_id desc;
  25. /* variables to check settings/values */
  26. dummy number;
  27. checkval varchar2(10);
  28. block_correction1 number;
  29. block_correction2 number;
  31. /* running variable to show (possible) end-of-file */
  32. file_min_block number;
  34. /* variables to check if recycle_bin is on and if extent as checked is in ... */
  35. recycle_bin boolean:=false;
  36. extent_in_recycle_bin boolean;
  38. /* exception handler needed for non-existing tables note:344940.1 */
  39. sqlstr varchar2(100);
  40. table_does_not_exist exception;
  41. pragma exception_init(table_does_not_exist,-942);
  43. /* variable to spot space wastage in datafile of uniform tablespace */
  44. space_wastage number;
  46. begin
  48. /* recyclebin is present in Oracle 10.2 and higher and might contain extent as checked */
  49. begin
  50. select value into checkval from v$parameter where name = 'recyclebin';
  51. if checkval = 'on'
  52. then
  53. recycle_bin := true;
  54. end if;
  55. exception
  56. when no_data_found
  57. then
  58. recycle_bin := false;
  59. end;
  61. /* main loop */
  62. for c_file in c_dbfile
  63. loop
  64. /* initialization of loop variables */
  65. dummy :=0;
  66. extent_in_recycle_bin := false;
  67. file_min_block := c_file.blocks;
  69. begin
  71. space_wastage:=0; /* reset for every file check */
  73. <<check_free>>
  75. for c_free in c_freespace(c_file.file_id)
  76. loop
  77. /* if blocks is an uneven value there is a need to correct
  78. with -1 to compare with end-of-file which is even */
  79. block_correction1 := (0-mod(c_free.max_block,2));
  80. block_correction2 := (0-mod(c_file.blocks,2));
  81. if file_min_block+block_correction2 = c_free.max_block+block_correction1
  82. then
  84. /* free extent is at end so file can be resized */
  85. file_min_block := c_free.block_id;
  87. /* Uniform sized tablespace check if space at end of file
  88. is less then uniform extent size */
  89. elsif (c_file.uni_extent !=0) and ((c_file.blocks - c_free.max_block) < c_file.uni_extent)
  90. then
  92. /* uniform tablespace which has a wastage of space in datafile
  93. due to fact that space at end of file is smaller than uniform extent size */
  95. space_wastage:=c_file.blocks - c_free.max_block;
  96. file_min_block := c_free.block_id;
  98. else
  99. /* no more free extent at end of file, file cannot be further resized */
  100. exit check_free;
  101. end if;
  102. end loop;
  103. end;
  105. /* check if file can be resized, minimal size of file 128 {+ initial_extent} blocks */
  106. if (file_min_block = c_file.blocks) or (c_file.blocks <= c_file.file_min_size)
  107. then
  109. dbms_output.put_line('Tablespace: '||c_file.tablespace_name||' Datafile: '||c_file.file_name);
  110. dbms_output.put_line('cannot be resized no free extents found');
  111. 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');
  112. dbms_output.put_line('.');
  114. else
  116. /* file needs minimal no of blocks which does vary over versions,
  117. using safe value of 128 {+ initial_extent} */
  118. if file_min_block < c_file.file_min_size
  119. then
  120. file_min_block := c_file.file_min_size;
  121. end if;
  124. dbms_output.put_line('Tablespace: '||c_file.tablespace_name||' Datafile: '||c_file.file_name);
  125. 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)||' %)');
  128. /* below is only true if recyclebin is on */
  129. if recycle_bin
  130. then
  131. begin
  132. sqlstr:='select distinct 1 from recyclebin$ where file#='||c_file.file_id;
  133. execute immediate sqlstr into dummy;
  135. if dummy > 0
  136. then
  138. dbms_output.put_line('Extents found in recyclebin for above file/tablespace');
  139. dbms_output.put_line('Implying that purge of recyclebin might be needed in order to resize');
  140. dbms_output.put_line('SQL> purge tablespace '||c_file.tablespace_name||';');
  141. end if;
  142. exception
  143. when no_data_found
  144. then null;
  145. when table_does_not_exist
  146. then null;
  147. end;
  148. end if;
  149. dbms_output.put_line('SQL> alter database datafile '''||c_file.file_name||''' resize '||round((file_min_block*c_file.block_size)/1024)||'K;');
  151. if space_wastage!=0
  152. then
  153. dbms_output.put_line('Datafile belongs to uniform sized tablespace and is not optimally sized.');
  154. dbms_output.put_line('Size of datafile is not a multiple of NN*uniform_extent_size + overhead');
  155. dbms_output.put_line('Space that cannot be used (space wastage): '||round((space_wastage*c_file.block_size)/1024)||'K');
  156. 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');
  157. end if;
  159. dbms_output.put_line('.');
  161. end if;
  163. end loop;
  165. end;
  166. /

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

  1. [orahec@db-hx-189-208 awr]$ sqlplus / as sysdba
  3. SQL*Plus: Release 11.2.0.4.0 Production on Tue Dec 11 11:26:15 2018
  5. Copyright (c) 1982, 2013, Oracle.  All rights reserved.
  8. Connected to:
  9. Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
  10. With the Partitioning, OLAP, Data Mining and Real Application Testing options
  12. SQL> spool oracle_check_resize.txt
  13. SQL> @oracle_check_resize.sql
  15. PL/SQL procedure successfully completed.
  17. Tablespace: HEC_DATA Datafile: /opt/Oracle/hecdb/oradata/HEC/hecdata01.dbf
  18. cannot be resized no free extents found
  19. Note: for some cases, dba_free_spaces data is not accurate, and this script does
  20. not work for such cases. You may want to manually check if the datafile is
  21. feasible to be resized
  22. .
  23. Tablespace: HEC_DATA Datafile: /opt/Oracle/hecdb/oradata/HEC/hecdata02.dbf
  24. cannot be resized no free extents found
  25. Note: for some cases, dba_free_spaces data is not accurate, and this script does
  26. not work for such cases. You may want to manually check if the datafile is
  27. feasible to be resized
  28. .
  29. Tablespace: HEC_DATA Datafile: /opt/Oracle/hecdb/oradata/HEC/hecdata03.dbf
  30. current size: 16777216K can be resized to: 14888960K (reduction of: 11.25 %)
  31. SQL> alter database datafile '/opt/Oracle/hecdb/oradata/HEC/hecdata03.dbf'
  32. resize 14888960K;
  33. .
  34. Tablespace: HEC_DATA Datafile: /opt/Oracle/hecdb/oradata/HEC/hecdata04.dbf
  35. cannot be resized no free extents found
  36. Note: for some cases, dba_free_spaces data is not accurate, and this script does
  37. not work for such cases. You may want to manually check if the datafile is
  38. feasible to be resized
  39. .
  40. Tablespace: SYSAUX Datafile: /opt/Oracle/hecdb/oradata/HEC/sysaux01.dbf
  41. cannot be resized no free extents found
  42. Note: for some cases, dba_free_spaces data is not accurate, and this script does
  43. not work for such cases. You may want to manually check if the datafile is
  44. feasible to be resized
  45. .
  46. Tablespace: SYSAUX Datafile: /opt/Oracle/hecdb/oradata/HEC/sysaux02.dbf
  47. current size: 5242880K can be resized to: 190464K (reduction of: 96.37 %)
  48. SQL> alter database datafile '/opt/Oracle/hecdb/oradata/HEC/sysaux02.dbf' resize
  49. 190464K;
  50. .
  51. Tablespace: SYSTEM Datafile: /opt/Oracle/hecdb/oradata/HEC/system01.dbf
  52. cannot be resized no free extents found
  53. Note: for some cases, dba_free_spaces data is not accurate, and this script does
  54. not work for such cases. You may want to manually check if the datafile is
  55. feasible to be resized
  56. .
  57. Tablespace: SYSTEM Datafile: /opt/Oracle/hecdb/oradata/HEC/system02.dbf
  58. current size: 8007680K can be resized to: 8001536K (reduction of: .08 %)
  59. SQL> alter database datafile '/opt/Oracle/hecdb/oradata/HEC/system02.dbf' resize
  60. 8001536K;
  61. .
  62. Tablespace: SYSTEM Datafile: /opt/Oracle/hecdb/oradata/HEC/system03.dbf
  63. current size: 16777216K can be resized to: 1378304K (reduction of: 91.78 %)
  64. SQL> alter database datafile '/opt/Oracle/hecdb/oradata/HEC/system03.dbf' resize
  65. 1378304K;
  66. .
  67. Tablespace: UNDOTBS1 Datafile: /opt/Oracle/hecdb/oradata/HEC/undotbs01.dbf
  68. current size: 28385280K can be resized to: 24642624K (reduction of: 13.19 %)
  69. SQL> alter database datafile '/opt/Oracle/hecdb/oradata/HEC/undotbs01.dbf'
  70. resize 24642624K;
  71. .
  72. Tablespace: USERS Datafile: /opt/Oracle/hecdb/oradata/HEC/users01.dbf
  73. current size: 5120K can be resized to: 1344K (reduction of: 73.75 %)
  74. SQL> alter database datafile '/opt/Oracle/hecdb/oradata/HEC/users01.dbf' resize
  75. 1344K;
  76. .
  78. PL/SQL procedure successfully completed.
  80. SQL> exit

发表评论

表情：
评论列表 （有 0 条评论，1238人围观）

还没有评论，来说两句吧...

相关阅读

    相关 Linux下调整目录的空间大小

    一、目的 在使用CentOS6.5版本linux系统的时候，发现根目录(/)的空间不是很充足，而其他目录空间有很大的空闲，所以本文主要是针对现在已有的空间进行调整。首先，先来

    灰太狼灰太狼/ 0 赞/ 380 阅读