今天发现一个数据文件物理空间超大，但是里面数据对象很少，而且都占据很小的空间，使用alter table move tablespace命令将几个HWM较高的对象移走后，执行如下sql：
SELECT file_name,
2CEIL( (NVL(hwm,1)*8192)/1024/1024 ) smallest,
3CEIL( blocks*8192/1024/1024) currsize,
4CEIL( blocks*8192/1024/1024) -
5CEIL( (NVL(hwm,1)*8192)/1024/1024 ) savings
6FROM DBA_DATA_FILES a,
7( SELECT file_id, MAX(block_id+blocks-1) hwm
8FROM DBA_EXTENTS
9GROUP BY file_id ) b
10WHERE a.file_id = b.file_id(+)
11AND A.TABLESPACE_NAME='&tbs_name'
12;
Enter value for tbs_name: USERS
old11: AND A.TABLESPACE_NAME='&tbs_name'
new11: AND A.TABLESPACE_NAME='USERS'
FILE_NAME
SMALLEST CURRSIZESAVINGS
-------------------------------------------------- ---------- ---------- ----------
/home/ora10g/oradata/db1/users01.dbf
19781433612358
/mnt/db/oradata/db1/STANDBY/datafile/o1_mf_users_953 1000 47
50n6h5ml_.dbf
此时，/home/ora10g/oradata/epay/users01.dbf按照正常逻辑应该可以resize 到2G左右，但实际执行时却发现：
alter database datafile '/home/ora10g/oradata/epay/users01.dbf' resize 3G;
alter database datafile '/home/ora10g/oradata/epay/users01.dbf' resize 3G
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value
又用如下sql查询了一下users表空间上的数据对象：
select owner,segment_name,MAX(block_id+blocks-1),sum(bytes/1024/1024/1024) tatal_size
2from dba_extents
3where tablespace_name='USERS'
4group by owner,segment_name;
OWNER
SEGMENT_NAME
MAX(BLOCK_ID+BLOCKS-1) TATAL_SIZE
------------------------------ ------------------------------ ---------------------- ----------
USER1
TEST
120 .000061035
SYS
I_AUD1
212104.109375
SCOTT
PK_DEPT
24 .000061035
SYS
AUD$
253064 2.75
SYS
SYS_IOT_TOP_58889
72 .000061035
SCOTT
BONUS
48 .000061035
SCOTT
SALGRADE
56 .000061035
SCOTT
EMP
32 .000061035
SCOTT
PK_EMP
40 .000061035
SCOTT
DEPT
16 .000061035
显然，HWM为253064,乘以块大小8192后约为1.93G，为什么resize不了呢？
|
