Oracle resize数据文件大小
Oracle 大小 数据文件 Resize
2023-09-14 09:01:48 时间
有的时候为了解决磁盘空间不足等情况,需要把一些表空间大小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 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
相关文章
- 掌握Oracle编程艺术——免费下载版(oracle编程艺术下载)
- 有责任心东软慧聚:负责任的Oracle岗位(东软慧聚oracle岗位)
- 写Oracle中忽略大小写的实现方法(oracle忽略大小)
- Oracle数据库归档日志容量控制(oracle归档日志大小)
- Oracle 触发器的分类与功能介绍(oracle触发器类型)
- 利用Oracle触发器实现自动数据同步(oracle触发器类型)
- Oracle表权限授予的操作指南(oracle表赋权限)
- 轻松玩转Oracle数据文件压缩(oracle数据文件压缩)
- 遇到Oracle数据文件误删怎么办?简易解决方法(oracle数据文件误删)
- Oracle值判断函数及其用法(oracle值判断)
- Oracle自动增长字段的创建技巧(oracle创建自动增长)
- 查看Oracle数据库存储容量(oracle查询数据大小)
- 写敏感Oracle用户大小写敏感:字母不同,意义也不同(oracle用户大小)
- Oracle关闭还原完善自动数据库恢复能力(oracle关闭还原过程)
- C语言与Oracle结合,开启信息化新篇章(c oracle引入)
- AU3与Oracle联手促进企业数据库转型(au3 oracle)
- 优化Oracle性能认识使用索引权限(oracle使用索引权限)
- Oracle估计索引的最佳大小(oracle估计索引多大)
- Oracle数据库优化,提升处理运行速度(oracle优化处理速度)
- Oracle函数中求极差的应用(oracle中求差的函数)
- Oracle中文列的索引使用方法(oracle中文列作索引)
- 优化Oracle查询最小Cost调优实战(oracle 中cost)
- Oracle中两套序列的作用及影响(oracle 两套序列)