影响RESIZE数据文件的因素

时间：2015-01-29 02:18:00 阅读：252 评论：0 收藏：0 [点我收藏+]

Oracle从7.2版本开提供ALTER DATABASE DATAFILE ‘<full path and name of the file>‘ RESIZE [K|M|G]操作，既可以加大数据文件的大小，也可以减小数据文件的大小，增加数据文件大小很简单，只要不要超过Oracle支持的最大数据文件大小即可，但是resize缺会经常遭遇ORA-03297报错。下面对减小数据文件的操作进行讨论。
要想减小datafile的大小，必须在datafile的末尾有连续的free空间，这个可以通过DBA_FREE_SPACE视图查看，移除的空间不能被数据库对象使用。

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;

datafile能够resize到多大，需要统计数据文件中所有的extents的大小，resize后的大小应该大于所有extents的大小。

SELECT SUM(BYTES) FROM DBA_EXTENTS WHERE FILE_ID =&file_id;

另外，数据文件能够resize到多大，可以用下面的脚本进行检测，如果输出中提示要move某些对象，表示resize不能成功：

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;
/

一般如果上面的脚本提示可以resize时，resize就可以成功，但是有例外的情况，下面进行试验:

--建立测试环境
sqlplus / as sysdba
alter system set recyclebin=on;
SQL> create tablespace darren datafile ‘/oradata/gyl/darren.dbf‘ size 100m extent management local uniform size 1m;
Tablespace created.
SQL> select bytes from v$datafile where file# = 7;
BYTES
----------
104857600
SQL> !ls -trl /oradata/gyl/darren.dbf
-rw-r----- 1 oracle oinstall 104865792 Jan 27 20:23 /oradata/gyl/darren.dbf
SQL> create user test identified by test;
User created.
SQL> grant dba to test;
Grant succeeded.
SQL> alter user test default tablespace darren;
User altered.
SQL> conn test/test
Connected.
BEGIN
for i in 1..10 LOOP
execute immediate ‘create table ‘ || ‘TEST‘||i ||‘ as select * from dba_objects‘;
end loop;
end;
/

--此时的free空间为9437184

SQL> select sum(bytes) from dba_free_space where file_id=7;
SUM(BYTES)
----------
9437184

--查看此时的extent情况，发现此时的block都是连续的

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
/
Owner Segment Name Type Extent# File# Block# Blocks
-------- ------------------------------ -------- ------- ----- ------ --------
TEST TEST1 TABLE 0 7 128 128
TEST TEST1 TABLE 1 7 256 128
TEST TEST1 TABLE 2 7 384 128
TEST TEST1 TABLE 3 7 512 128
TEST TEST1 TABLE 4 7 640 128
TEST TEST1 TABLE 5 7 768 128
TEST TEST1 TABLE 6 7 896 128
TEST TEST1 TABLE 7 7 1024 128
TEST TEST1 TABLE 8 7 1152 128
TEST TEST2 TABLE 0 7 1280 128
TEST TEST2 TABLE 1 7 1408 128
...
TEST TEST10 TABLE 5 7 11136 128
TEST TEST10 TABLE 6 7 11264 128
TEST TEST10 TABLE 7 7 11392 128
TEST TEST10 TABLE 8 7 11520 128

--删除第2到第8个表，还有第10个表，让块不连续，在第9个表前后都有free块

BEGIN
for i in 2..8 LOOP
execute immediate ‘DROP table ‘ || ‘TEST‘||i;
end loop;
execute immediate ‘DROP TABLE TEST10‘;
end;
/

--观察extent的情况，现在块不连续了

Owner Segment Name Type Extent# File# Block# Blocks
-------- ------------------------------ -------- ------- ----- ------ --------
TEST TEST1 TABLE 0 7 128 128
TEST TEST1 TABLE 1 7 256 128
TEST TEST1 TABLE 2 7 384 128
TEST TEST1 TABLE 3 7 512 128
TEST TEST1 TABLE 4 7 640 128
TEST TEST1 TABLE 5 7 768 128
TEST TEST1 TABLE 6 7 896 128
TEST TEST1 TABLE 7 7 1024 128
TEST TEST1 TABLE 8 7 1152 128
TEST TEST9 TABLE 0 7 9344 128
TEST TEST9 TABLE 1 7 9472 128
TEST TEST9 TABLE 2 7 9600 128
TEST TEST9 TABLE 3 7 9728 128
TEST TEST9 TABLE 4 7 9856 128
TEST TEST9 TABLE 5 7 9984 128
TEST TEST9 TABLE 6 7 10112 128
TEST TEST9 TABLE 7 7 10240 128
TEST TEST9 TABLE 8 7 10368 128
--删除表后，表使用的空间减小到了18874368
SQL> SELECT SUM(BYTES) FROM DBA_EXTENTS WHERE FILE_ID =7;
SUM(BYTES)
----------
18874368
--使用上面的测试脚本观察resize是否能成功
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: 18874380 --这里稍微比extents的总大小大，保证能放下所有的对象
old 7: V_RESIZE_SIZE := &RESIZE_FILE_TO;
new 7: V_RESIZE_SIZE := 18874380;
.
.
.
OBJECTS IN FILE 7 THAT MUST MOVE IN ORDER TO RESIZE THE FILE TO 18874380 BYTES
===================================================================
NON-PARTITIONED OBJECTS
===================================================================
TEST.TEST9 - OBJECT TYPE = TABLE --这里提示需要move掉test9，这与之前的理论对应，resize只能移除datafile末尾连续的free空间
===================================================================
PARTITIONED OBJECTS
===================================================================
PL/SQL procedure successfully completed.

--不move掉test9是resize不成功的

SQL> ALTER DATABASE DATAFILE \‘/oradata/gyl/darren.dbf\‘ RESIZE 18874380;
ALTER DATABASE DATAFILE \‘/oradata/gyl/darren.dbf\‘ RESIZE 18874380
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value
--move走test9
SQL> alter table test9 move tablespace users;
Table altered.

--再次进行检测，这次检测通过

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: 18874380
old 7: V_RESIZE_SIZE := &RESIZE_FILE_TO;
new 7: V_RESIZE_SIZE := 18874380;
.
.
.
OBJECTS IN FILE 7 THAT MUST MOVE IN ORDER TO RESIZE THE FILE TO 18874380 BYTES
===================================================================
NON-PARTITIONED OBJECTS
===================================================================
===================================================================
PARTITIONED OBJECTS
===================================================================
PL/SQL procedure successfully completed.
--进行resize操作，还是失败
SQL> ALTER DATABASE DATAFILE \‘/oradata/gyl/darren.dbf\‘ RESIZE 18874380;
ALTER DATABASE DATAFILE \‘/oradata/gyl/darren.dbf\‘ RESIZE 18874380
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value
--查看DARREN表空间中被删除的对象
SQL> SELECT ORIGINAL_NAME FROM DBA_RECYCLEBIN WHERE TS_NAME = \‘DARREN\‘;
ORIGINAL_NAME
--------------------------------
TEST10
TEST8
TEST7
TEST6
TEST5
TEST4
TEST3
TEST2

--上面resize失败是由于部分显示free的空间，实际上被recylebin对象占用，清除recyclebin对象后，resize成功执行

SQL> purge tablespace darren;
Tablespace purged.
SQL> ALTER DATABASE DATAFILE \‘/oradata/gyl/darren.dbf\‘ RESIZE 18874380;
Database altered.
SQL> !ls -trl /oradata/gyl/darren.dbf
-rw-r----- 1 oracle oinstall 18890752 Jan 27 21:02 /oradata/gyl/darren.dbf

综上所述，resize是否能成功，有两个很重要的因素：1、free空间是否在datafile，且空间是连续的；
2、连续的剩余空间是否被回收站对象占用。

影响RESIZE数据文件的因素

原文：http://blog.itpub.net/29821678/viewspace-1417458/

踩

(0)

评论一句话评论（0）

分享档案

更多>

2021年09月23日 (328)
2021年09月24日 (313)
2021年09月17日 (191)
2021年09月15日 (369)
2021年09月16日 (411)
2021年09月13日 (439)
2021年09月11日 (398)
2021年09月12日 (393)
2021年09月10日 (160)
2021年09月08日 (222)