欢迎关注“数据库运维之道”公众号，一起学习数据库技术! 本期将为大家分享“数据文件大小扩容或缩容必备技能” 。
关键词：Resize Datafile、ORA-03297、高水位线
表空间跟数据文件是一对多的关系，数据文件存放到磁盘或ASM磁盘组。当磁盘空间使用率较高时，DBA需要对数据文件进行扩容或缩容。Oracle 7.2版本开始引入“ALTER DATABASE DATAFILE .... RESIZE”命令进行数据文件物理大小调整（扩容或缩容）。数据文件扩容比较简单，只要磁盘空间充足，一条命令就可以解决。但是数据文件缩容会相对复杂些，因为空间可能会被数据库对象占用而无法释放。下面整理了几个常见的使用场景。
SYS@PROD4> !oerr ora 3297
03297, 00000, "file contains used data beyond requested RESIZE value"
// *Cause: Some portion of the file in the region to be trimmed is
// currently in use by a database object
// *Action: Drop or move segments containing extents in this region prior to
// resizing the file, or choose a resize value such that only free
// space is in the trimmed.
场景描述：如果数据文件大小未设置自动扩展，那么可以使用“ALTER DATABASE DATAFILE '' RESIZE [K|M|G]; ”命令进行数据文件大小扩容，设置的大小必须比当前数据文件大小更大。
实验步骤：
1、创建模拟环境
--创建表空间
create tablespace ywzd datafile '/u01/app/oracle/oradata/PROD4/ywzd.dbf' size 300m extent management local uniform size 1m;
--查看数据文件大小
column file_name format a45
select file_name, round(bytes/1024/1024) SizeMB from dba_data_files where file_name like '%ywzd%';
FILE_NAME SIZEMB
--------------------------------------------- ----------
/u01/app/oracle/oradata/PROD4/ywzd.dbf 300
SYS@PROD4> !ls -alt /u01/app/oracle/oradata/PROD4/ywzd.dbf
-rw-r----- 1 oracle oinstall 314580992 Apr 3 14:50 /u01/app/oracle/oradata/PROD4/ywzd.dbf
-- 查看表空间使用情况
set linesize 1000
col file_name for a70
SELECT UPPER(F.TABLESPACE_NAME) "TablespaceName",
D.TOT_GROOTTE_MB "TotalSize(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "UsedSize(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,
2),
'990.99') "UsedPercent",
F.TOTAL_BYTES "FreeSize(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
/
2、数据文件扩容
--扩容数据文件
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/PROD4/ywzd.dbf' RESIZE 301m;
--再次查看数据文件大小，最初创建大小是300M，当前大小是301M
col name for a45
set linesize 500
SELECT FILE#, STATUS, ENABLED, CHECKPOINT_CHANGE#, round(CREATE_BYTES/1024/1024)create_mb,
round(BYTES/1024/1024) current_MB, NAME FROM V$DATAFILE where name like '%ywzd%';
FILE# STATUS ENABLED CHECKPOINT_CHANGE# CREATE_MB CURRENT_MB NAME
---------- ------- ---------- ------------------ ---------- ---------- ---------------------------------------------
7 ONLINE READ WRITE 2415445 300 301 /u01/app/oracle/oradata/PROD4/ywzd.dbf
数据文件扩容场景操作比较简单!
场景描述：数据文件缩容就是移除数据文件的空闲空间，前提条件是只能移除数据文件高水位上连续的空闲空间。 可以调整高水位线，来整合连续的空闲空间。
实验步骤：
1、创建模拟环境
--创建用户
create user ywzd identified by ywzd;
grant dba to ywzd;
alter user ywzd default tablespace ywzd;
connect ywzd/ywzd;
--创建测试数据
BEGIN
for i in 1..19 LOOP
execute immediate 'create table ' || 'TEST'||i ||' as select * from dba_objects';
end loop;
end;
/2、查看数据文件的大小和剩余情况
--数据文件与段区块大小比较
select bytes-ebytes from (select sum(bytes) ebytes from dba_extents where file_id=7), dba_data_files where file_id=7;
BYTES-EBYTES
------------
56623104
--查看剩余的空间大小
select sum(bytes) from dba_free_space where file_id=7;
SUM(BYTES)
----------
5557452
set linesize 500
SELECT tablespace_name,file_id,block_id,round(bytes/1024/1024)free_mb,blocks FROM DBA_FREE_SPACE
WHERE TABLESPACE_NAME='YWZD'
ORDER BY BLOCK_ID;
TABLESPACE_NAME FILE_ID BLOCK_ID FREE_MB BLOCKS
------------------------------ ---------- ---------- ---------- ----------
YWZD 7 31744 53 6784
数据块BLOCK_ID=31744包含6784个blocks，数据文件还剩余53M空间。
3、创建FINDEXT.SQL文件，文本内容如下：
-- FINDEXT.SQL
SET ECHO OFF
SET PAGESIZ 25
column file_name format a50
select file_name, file_id from dba_data_files order by 2;
ttitle -
center 'Segment Extent Summary' skip 2
col ownr format a8 heading 'Owner' justify c
col type format a8 heading 'Type' justify c trunc
col name format a30 heading 'Segment Name' justify c
col exid format 990 heading 'Extent#' justify c
col fiid format 9990 heading 'File#' justify c
col blid format 99990 heading 'Block#' justify c
col blks format 999,990 heading 'Blocks' justify c
select owner ownr, segment_name name, segment_type type, extent_id exid, file_id fiid, block_id blid, blocks blks
from dba_extents
where file_id = &file_id
order by block_id
/4、执行@FINDEXT.SQL脚本，进一步确认这个数据文件里包含哪些对象。
@FINDEXT.SQL
Owner Segment Name Type Extent# File# Block# Blocks
-------- ------------------------------ -------- ------- ----- ------ --------
YWZD TEST19 TABLE 6 7 30848 128
YWZD TEST19 TABLE 7 7 30976 128
YWZD TEST19 TABLE 8 7 31104 128
YWZD TEST19 TABLE 9 7 31232 128
YWZD TEST19 TABLE 10 7 31360 128
YWZD TEST19 TABLE 11 7 31488 128
YWZD TEST19 TABLE 12 7 31616 128
247 rows selected.5、删除TEST2~TEST17测试表和TEST19测试表，模拟数据文件中间有连续的空闲空间和尾部有空闲的空间。
BEGIN
for i in 2..17 LOOP
execute immediate 'DROP table ' || 'TEST'||i;
end loop;
execute immediate 'DROP TABLE TEST19';
end;
/6、再次执行@FINDEXT.SQL脚本，进一步确认这个数据文件里包含哪些对象。
Owner Segment Name Type Extent# File# Block# Blocks
-------- ------------------------------ -------- ------- ----- ------ --------
YWZD TEST1 TABLE 0 7 128 128
YWZD TEST1 TABLE 1 7 256 128
YWZD TEST1 TABLE 2 7 384 128
YWZD TEST1 TABLE 3 7 512 128
YWZD TEST1 TABLE 4 7 640 128
YWZD TEST1 TABLE 5 7 768 128
YWZD TEST1 TABLE 6 7 896 128
YWZD TEST1 TABLE 7 7 1024 128
YWZD TEST1 TABLE 8 7 1152 128
YWZD TEST1 TABLE 9 7 1280 128
YWZD TEST1 TABLE 10 7 1408 128
YWZD TEST1 TABLE 11 7 1536 128
YWZD TEST1 TABLE 12 7 1664 128
YWZD TEST18 TABLE 0 7 28416 128
YWZD TEST18 TABLE 1 7 28544 128
YWZD TEST18 TABLE 2 7 28672 128
YWZD TEST18 TABLE 3 7 28800 128
YWZD TEST18 TABLE 4 7 28928 128
YWZD TEST18 TABLE 5 7 29056 128
YWZD TEST18 TABLE 6 7 29184 128
YWZD TEST18 TABLE 7 7 29312 128
YWZD TEST18 TABLE 8 7 29440 128
YWZD TEST18 TABLE 9 7 29568 128
YWZD TEST18 TABLE 10 7 29696 128
YWZD TEST18 TABLE 11 7 29824 128
YWZD TEST18 TABLE 12 7 29952 128
26 rows selected.
7、上述被删除的测试表被放入回收站，检查回收站YWZD表空间下有哪些对象。
SELECT ORIGINAL_NAME FROM DBA_RECYCLEBIN WHERE TS_NAME = 'YWZD';
ORIGINAL_NAME
--------------------------------------------------------------------------------------------------------------------------------
TEST2
TEST3
TEST4
TEST5
TEST6
TEST7
TEST8
TEST9
TEST10
TEST11
TEST12
TEST13
TEST14
TEST15
TEST16
TEST17
TEST19
17 rows selected.8、查看YWZD表空间下的数据段区占用空间，然后预留10K大小给数据文件头存放数据，可以计算出实际占用空间为27344896。
SELECT SUM(BYTES)+10*8192 FROM DBA_EXTENTS WHERE FILE_ID = 7;
SUM(BYTES)+10*8192
------------------
273448969、创建SHRINK_DATAFILE.SQL，文本内容如下。
-- SHRINK_DATAFILE.SQL
SET SERVEROUTPUT ON
DECLARE
V_FILE_ID NUMBER;
V_BLOCK_SIZE NUMBER;
V_RESIZE_SIZE NUMBER;
BEGIN
V_FILE_ID := &FILE_ID;
V_RESIZE_SIZE := &RESIZE_FILE_TO;
SELECT BLOCK_SIZE INTO V_BLOCK_SIZE FROM V$DATAFILE WHERE FILE# = V_FILE_ID;
DBMS_OUTPUT.PUT_LINE('.');
DBMS_OUTPUT.PUT_LINE('.');
DBMS_OUTPUT.PUT_LINE('.');
DBMS_OUTPUT.PUT_LINE('OBJECTS IN FILE '||V_FILE_ID||' THAT MUST MOVE IN ORDER TO RESIZE THE FILE TO '||V_RESIZE_SIZE||' BYTES');
DBMS_OUTPUT.PUT_LINE('===================================================================');
DBMS_OUTPUT.PUT_LINE('NON-PARTITIONED OBJECTS');
DBMS_OUTPUT.PUT_LINE('===================================================================');
for my_record in (
SELECT DISTINCT(OWNER||'.'||SEGMENT_NAME||' - OBJECT TYPE = '||SEGMENT_TYPE) ONAME
FROM DBA_EXTENTS
WHERE (block_id + blocks-1)*V_BLOCK_SIZE > V_RESIZE_SIZE
AND FILE_ID = V_FILE_ID
AND SEGMENT_TYPE NOT LIKE '%PARTITION%'
ORDER BY 1) LOOP
DBMS_OUTPUT.PUT_LINE(my_record.ONAME);
END LOOP;
DBMS_OUTPUT.PUT_LINE('===================================================================');
DBMS_OUTPUT.PUT_LINE('PARTITIONED OBJECTS');
DBMS_OUTPUT.PUT_LINE('===================================================================');
for my_record in (
SELECT DISTINCT(OWNER||'.'||SEGMENT_NAME||' - PARTITION = '||PARTITION_NAME||' - OBJECT TYPE = '||SEGMENT_TYPE) ONAME
FROM DBA_EXTENTS
WHERE (block_id + blocks-1)*V_BLOCK_SIZE > V_RESIZE_SIZE
AND FILE_ID = V_FILE_ID
AND SEGMENT_TYPE LIKE '%PARTITION%'
ORDER BY 1) LOOP
DBMS_OUTPUT.PUT_LINE(my_record.ONAME);
END LOOP;
END;
/10、如果数据文件有较多的空闲空间，同时一些数据库对象占用数据文件尾部的空间，那么我们执行缩容命令就会遇到“ORA-03297: file contains used data beyond requested RESIZE value”错误。
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/PROD4/ywzd.dbf' RESIZE 27344896
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value
11、虽然数据文件有大量的空闲空间，但是很多连续的空闲空间是在高水位线之下。
我们需要进一步找到哪些对象需要进行移动。执行@SHRINK_DATAFILE.SQL脚本后，要输入数据文件编号和步骤8计算出来的值 27344896。可以看到TEST18测试表需要移动位置。
YWZD@PROD4> @SHRINK_DATAFILE.SQL
Enter value for file_id: 7
old 6: V_FILE_ID := &FILE_ID;
new 6: V_FILE_ID := 7;
Enter value for resize_file_to: 27344896
old 7: V_RESIZE_SIZE := &RESIZE_FILE_TO;
new 7: V_RESIZE_SIZE := 27344896;
.
.
.
OBJECTS IN FILE 7 THAT MUST MOVE IN ORDER TO RESIZE THE FILE TO 27344896 BYTES
===================================================================
NON-PARTITIONED OBJECTS
===================================================================
YWZD.TEST18 - OBJECT TYPE = TABLE
===================================================================
PARTITIONED OBJECTS
===================================================================
PL/SQL procedure successfully completed.
12、将TEST18测试表移动到其他表空间，执行缩容依旧报错。
--移动数据文件
ALTER TABLE TEST18 MOVE TABLESPACE USERS;
--再次执行缩容
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/PROD4/ywzd.dbf' RESIZE 27344896
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value13、清理回收站，再次进行缩容。这次成功完成缩容。
purge tablespace YWZD;
YWZD@PROD4> ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/PROD4/ywzd.dbf' RESIZE 27344896;
Database altered.
YWZD@PROD4> !ls -alt /u01/app/oracle/oradata/PROD4/ywzd.dbf
-rw-r----- 1 oracle oinstall 27353088 Apr 3 15:27 /u01/app/oracle/oradata/PROD4/ywzd.dbf
14、收尾工作，清理模拟数据
-- 将TEST18测试表移回到YWZD表空间
ALTER TABLE TEST18 MOVE TABLESPACE TEST;
--清理掉测试环境
CONNECT / AS SYSDBA
drop user ywzd cascade;
drop tablespace ywzd including contents and datafiles cascade constraints;根据以上步骤成功实现数据文件缩容。不过实际生产环境会更复杂点，索引得失效重建，BLOB字段的段要单独移动，分区表要特殊处理。
推荐阅读一：临时表空间的数据文件收缩
1、确认哪个临时数据文件要收缩
--查看临时数据文件大小
set linesize 200
col file_name for a80
col tablespace_name for a20
select TABLESPACE_NAME,FILE_NAME,AUTOEXTENSIBLE,BYTES/1024/1024 Size_MB from dba_temp_files order by 1,2;
TABLESPACE_NAME FILE_NAME AUT SIZE_MB
-------------------- -------------------------------------------------------------------------------- --- ----------
TEMP /u01/app/oracle/oradata/PROD4/temp01.dbf YES 197
TEMP /u01/app/oracle/oradata/PROD4/temp02.dbf NO 500
2、查看临时表空间被哪些对象占用。
alter tablespace temp shrink space keep 1g;
alter tablespace temp shrink space keep 1g
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value
select sum(blocks*8/1024) size_mb from v$tempseg_usage where tablespace='TEMP';
SIZE_MB
----------
2173、确认哪些会话占用、执行哪些SQL语句
select inst_id,
sid,
serial#,
status,
"USERNAME",
machine,
program,
sql_id,
event,
'alter system kill session ''' || sid || ',' || serial# || ',@' ||
inst_id || ''' immediate;'
from gv$session
where saddr in (select session_addr from gv$tempseg_usage)
order by 1;4、上述的会话已经释放或清理后，则可以释放下临时表空间
select TABLESPACE_NAME,bytes/1024/1024 from dba_temp_files;
alter tablespace temp shrink space keep 10g;---从原32G调整至20G
alter tablespace temp shrink tempfile '/u01/app/oracle/oradata/PROD4/temp02.dbf' keep 100M; ---从原32G调整至20G推荐阅读二：高水位线以上的空闲空间进行快速缩容
1、对高水位线以上的空闲空间进行快速缩容，官方文档提供了show_segment_above_size.sql脚本。
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;
/2、执行@show_segment_above_size.sql脚本，可以看到要调整的信息。
Tablespace: EXAMPLE Datafile: /u01/app/oracle/oradata/PROD4/example01.dbf
current size: 1273600K can be resized to: 1248320K (reduction of: 1.98 %)
SQL> alter database datafile '/u01/app/oracle/oradata/PROD4/example01.dbf'
resize 1248320K;
.
Tablespace: SYSAUX Datafile: /u01/app/oracle/oradata/PROD4/sysaux01.dbf
current size: 747520K can be resized to: 702464K (reduction of: 6.03 %)
SQL> alter database datafile '/u01/app/oracle/oradata/PROD4/sysaux01.dbf' resize
702464K;
.
- Primary Note: Troubleshooting Oracle Tablespace Management (Doc ID 1522807.1)
- ORA-03297 When Resize A Datafile (Doc ID 578793.1)
- How to Resize a Datafile (Doc ID 1029252.6)
- How to Resolve ORA-03297 When Resizing a Datafile by Finding the Table Highwatermark (Doc ID 130866.1)
- OERR: ORA 3297 file contains blocks of data beyond requested RESIZE valu (Doc ID 38691.1)
以上就是本期关于“数据文件大小扩容或缩容必备技能”。希望能给大家带来帮助！
欢迎关注“数据库运维之道”公众号，一起学习数据库技术!
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
