resize数据文件的时候，常常会报ORA-03297错误，原来理解通过在相同表空间move一下对象，就会自动使用最小的未使用的空间，其实并不是这样的，在MOS文件中遇到ORA-03297错误也是直接给出的DROP对象，也不是在相同表空间中MOVE对象。下面通过测试来说明：
下面的测试没有实际意义，因为生产环境基本上不会做RESIZE操作。
1，数据库版本
|
orcl1123.htz.pw > select * from v$version where rownum=1;
BANNER ——————————————————————————– Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production |
2，查询表空间中对象的大小
|
这里可以看到对象总的大小为271974400 orcl1123.htz.pw > select tablespace_name,sum(bytes) from dba_segments where tablespace_name=’USERS’ group by tablespace_name;
TABLESPACE_NAME SUM(BYTES) —————————— ———- USERS 271974400 然后表表空间中总分配的空间远远大于271974400 orcl1123.htz.pw > select tablespace_name,sum(bytes) total,sum(user_bytes) used from dba_data_files where tablespace_name=’USERS’ group by tablespace_name; TABLESPACE_NAME TOTAL USED —————- ———— ————————- USERS 30703616000 30701518848 |
3 查询需要MOVE的对象
|
查看表空间中的数据文件系统 orcl1123.htz.pw > select file_id,file_name from dba_data_files where tablespace_name=’USERS’; FILE_ID FILE_NAME ———- ——————————————————————————– 4 /oracle/app/oracle/oradata/orcl1123/users01.dbf 5 /oracle/app/oracle/oradata/orcl1123/users02.dbf 原来以为表空间中使用数据文件是均匀的使用，但是后面的测试并不是这样的，下面这样的SQL是之前以后均匀的使用数据文件而写的 orcl1123.htz.pw > select 271974400/2+128 from dual; 271974400/2+128 ————— 135987328 由于查询dba_extents需要消耗大量的时间，特别是数据库越大的时候，所以这里我们通过一次性查询出来，并生成一个表来实现只查询一次dba_extents。 orcl1123.htz.pw > select * 2 from (select owner, 3 segment_name, 4 a.segment_type, 5 max(block_id + blocks) * 8192 max_size 6 from dba_extents a 7 where tablespace_name = ‘USERS’ 8 group by owner, segment_name, a.segment_type) 9 where max_size > 135987328;
OWNER SEGMENT_NAME SEGMENT_TYPE MAX_SIZE ——————– ———————————– ————— ———- HTZ SYS_LOB0000075782C00003$$ LOBSEGMENT 8798797824 HTZ HTZ4 TABLE 8798601216 HTZ SYS_IL0000075782C00003$$ LOBINDEX 8797552640 HTZ HTZ3 TABLE 8796504064 HTZ TEST TABLE 8801746944 HTZ SYS_IL0000075778C00002$$ LOBINDEX 8796700672 HTZ SYS_LOB0000075778C00002$$ LOBSEGMENT 8796635136 HTZ TEST_BFILE TABLE 8797421568
orcl1123.htz.pw > create table system.resize_datafile as select * from (select owner, segment_name, a.segment_type, max(block_id + blocks) * 8192 max_size from dba_extents a where tablespace_name = ‘USERS’ group by owner, segment_name, a.segment_type) where max_size > 135987328;
Table created. |
4 MOVE对象
|
orcl1123.htz.pw > select ‘alter table ‘ || owner || ‘.’ || segment_name || ‘ move;’ from system.resize_datafile a where a.segment_type = ‘TABLE’;
‘ALTERTABLE’||OWNER||’.’||SEGMENT_NAME||’MOVE;’ ———————————————— alter table HTZ.HTZ4 move; alter table HTZ.HTZ3 move; alter table HTZ.TEST move; alter table HTZ.HTZ_LONG move; alter table HTZ.TEST_BFILE move;
select ‘alter table ‘ || a.owner || ‘.’ || b.table_name || ‘ move lob(‘ || b.column_name || ‘) store as ‘ || decode(b.securefile, ‘NO’, ‘BASICFILE’, ‘YES’, ‘securefile’) || ‘ (tablespace ‘ || b.tablespace_name || ‘);’ ddl from system.resize_datafile a, dba_lobs b where a.segment_type = ‘LOBSEGMENT’ AND a.owner = b.owner and a.segment_name = b.segment_name;
alter table HTZ.TEST move lob(PHOTO) store as securefile (tablespace USERS); alter table HTZ.HTZ_LONG move lob(SQL_FULLTEXT) store as BASICFILE (tablespace USERS); alter table HTZ.HTZ4 move lob(SQL_FULLTEXT) store as securefile (tablespace USERS); 多次MOVE后，查看当前表空间使用情况 orcl1123.htz.pw > select max(block_id) from dba_extents where file_id=4; MAX(BLOCK_ID) ————- 1074144 orcl1123.htz.pw > select max(block_id) from dba_extents where file_id=5; MAX(BLOCK_ID) ————- 16896 这里看到两个数据文件中分配给对象的最大块的BLOCK_ID相差太多了，也说明数据文件中并不是平均使用数据文件的
orcl1123.htz.pw > select max(block_id+blocks)*8192+128*8192 from dba_extents where file_id=5; MAX(BLOCK_ID+BLOCKS)*8192+128*8192 ———————————- 147849216 orcl1123.htz.pw > select max(block_id+blocks)*8192+128*8192 from dba_extents where file_id=4; MAX(BLOCK_ID+BLOCKS)*8192+128*8192 ———————————- 8800501760 orcl1123.htz.pw > select tablespace_name,sum(bytes) from dba_segments where tablespace_name=’USERS’ group by tablespace_name;
TABLESPACE_NAME SUM(BYTES) —————————— ———- USERS 270073856 这里看到表空间分配的大小还是远远大于表空间中对象使用的大小 |
5 查询对表块的分布情况
|
orcl1123.htz.pw > select owner,segment_name,file_id,block_id+blocks from dba_extents where tablespace_name=’USERS’ order by file_id,4;
OWNER SEGMENT_NAME FILE_ID BLOCK_ID+BLOCKS ———- —————————— ———- ————— SCOTT DEPT 4 136 SCOTT PK_DEPT 4 144 SCOTT EMP 4 152 ……………….. HTZ HTZ_4 4 376 HTZ TEST 4 384 HTZ HTZ4 4 1016064 ……………………. HTZ SYS_IL0000075818C00002$$ 4 1016152 HTZ SYS_LOB0000075782C00003$$ 5 5120 HTZ HTZ3 5 18048 HTZ HTZ3 5 18176 HTZ HTZ3 5 18304 HTZ SYS_LOB0000075782C00003$$ 5 19328 ………………. |
通过上面信息得到，中间其它有很多块并没有使用，并且相隔还很大
6 dump数据文件位图块
|
File Space Bitmap Block: BitMap Control: RelFno: 4, BeginBlock: 128, Flag: 0, First: 23, Free: 63456 FFFFFFFF00000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 …………………. File Space Bitmap Block: BitMap Control: RelFno: 4, BeginBlock: 508032, Flag: 0, First: 0, Free: 63488 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 ……………………….. File Space Bitmap Block: BitMap Control: RelFno: 4, BeginBlock: 1015936, Flag: 0, First: 46, Free: 59945 FFFFFFFFFF3FFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF 通过上面的信息可以得到每一个位图块都对应数据文件中一段连续的空间，在分配空间的时候，并不是依次使用位图块，而是同时在多个位图块中使用位图块，估计是为了减少块的挣用，提高并发性。 |
7 MOVE到其它表空间
其实通过MOS文档可以发现，ORACLE官方并没有说通过MOVE方式，而是通过DROP方式来实现，下面我们通过MOVE到其它的表空间来使用。
|
alter table HTZ.HTZ5 move tablespace sysaux; alter table HTZ.HTZ3 move tablespace sysaux; alter table HTZ.TEST move tablespace sysaux; alter table HTZ.NL1 move tablespace sysaux; alter table HTZ.HTZ4 move tablespace sysaux; alter table HTZ.HTZ_LONG1 move tablespace sysaux; alter table HTZ.NL2 move tablespace sysaux; alter table HTZ.TEST move lob(PHOTO) store as securefile (tablespace SYSAUX); alter table HTZ.HTZ_LONG1 move lob(SQL_FULLTEXT) store as BASICFILE (tablespace SYSAUX); alter table HTZ.HTZ4 move lob(SQL_FULLTEXT) store as securefile (tablespace SYSAUX); |
8 RESIZE数据文件
|
查询数据分配给对象的最大块的位置 orcl1123.htz.pw > select max(block_id+blocks)*8192+128*8192 from dba_extents where file_id=5; MAX(BLOCK_ID+BLOCKS)*8192+128*8192 ———————————- 8126464 orcl1123.htz.pw > select max(block_id+blocks)*8192+128*8192 from dba_extents where file_id=4; MAX(BLOCK_ID+BLOCKS)*8192+128*8192 ———————————- 4128768 这里看到已经除下面了。 orcl1123.htz.pw > alter database datafile 5 resize 8126464; Database altered. orcl1123.htz.pw > alter database datafile 4 resize 4128768; Database altered. 已经resize成功 |
9 将对象MOVE回来
|
alter table HTZ.HTZ5 move tablespace USERS; alter table HTZ.HTZ3 move tablespace USERS; alter table HTZ.TEST move tablespace USERS; alter table HTZ.NL1 move tablespace USERS; alter table HTZ.HTZ4 move tablespace USERS; alter table HTZ.HTZ_LONG1 move tablespace USERS; alter table HTZ.NL2 move tablespace USERS; alter table HTZ.TEST move lob(PHOTO) store as securefile (tablespace USERS); alter table HTZ.HTZ_LONG1 move lob(SQL_FULLTEXT) store as BASICFILE (tablespace USERS); alter table HTZ.HTZ4 move lob(SQL_FULLTEXT) store as securefile (tablespace USERS); 查看当前数据文件分配的大小 orcl1123.htz.pw > select max(block_id+blocks)*8192+128*8192 from dba_extents where file_id=4; MAX(BLOCK_ID+BLOCKS)*8192+128*8192 ———————————- 139460608 orcl1123.htz.pw > select max(block_id+blocks)*8192+128*8192 from dba_extents where file_id=5; MAX(BLOCK_ID+BLOCKS)*8192+128*8192 ———————————- 147849216
orcl1123.htz.pw > !ls -l /oracle/app/oracle/oradata/orcl1123/users02.dbf -rw-r—– 1 oracle oinstall 153034752 Nov 29 23:02 /oracle/app/oracle/oradata/orcl1123/users02.dbf
orcl1123.htz.pw > !ls -l /oracle/app/oracle/oradata/orcl1123/users01.dbf -rw-r—– 1 oracle oinstall 141762560 Nov 29 23:02 /oracle/app/oracle/oradata/orcl1123/users01.dbf |
关于resize datafile理解的错误：等您坐沙发呢！