Hi,
Below is the query i m using to get a report of a tablespace "FPR_GL_D02" to shrink to possible lowest values
SQL> SELECT FILE_NAME,
2 CEIL( (NVL(HWM,1)*&&BLKSIZE)/1024/1024 ) SMALLEST,
CEIL( BLOCKS*&&BLKSIZE/1024/1024) CURRSIZE,
CEIL( BLOCKS*&&BLKSIZE/1024/1024) - CEIL( (NVL(HWM,1)*&&BLKSIZE)/1024/1024 ) SAVINGS
3 4 5 FROM DBA_DATA_FILES DBADF, ( SELECT FILE_ID, MAX(BLOCK_ID+BLOCKS-1) HWM FROM DBA_EXTENTS GROUP BY FILE_ID ) DBAFS
6 WHERE DBADF.FILE_ID = DBAFS.FILE_ID(+)
7 and DBADF.FILE_NAME IN (SELECT FILE_NAME FROM DBA_DATA_FILES where TABLESPACE_NAME='FPR_GL_D02');
old 2: CEIL( (NVL(HWM,1)*&&BLKSIZE)/1024/1024 ) SMALLEST,
new 2: CEIL( (NVL(HWM,1)*8192)/1024/1024 ) SMALLEST,
old 3: CEIL( BLOCKS*&&BLKSIZE/1024/1024) CURRSIZE,
new 3: CEIL( BLOCKS*8192/1024/1024) CURRSIZE,
old 4: CEIL( BLOCKS*&&BLKSIZE/1024/1024) - CEIL( (NVL(HWM,1)*&&BLKSIZE)/1024/1024 ) SAVINGS
new 4: CEIL( BLOCKS*8192/1024/1024) - CEIL( (NVL(HWM,1)*8192)/1024/1024 ) SAVINGS
Smallest
Size Current Poss.
FILE_NAME Poss. Size Savings
------------------------------------------------------------ -------- -------- --------
/FRPDWT_2/data005/FPR_GL_D02_06.dbf 1 3,751 3,750
/FRPDWT_2/data006/FPR_GL_D02_09.dbf 1 512 511
/FRPDWT_2/data002/FPR_GL_D02_08.dbf 1 1,250 1,249
/FRPDWT_2/data005/FPR_GL_D02_07.dbf 1 2,500 2,499
/FRPDWT_2/data003/FPR_GL_D02_02.dbf 1 3,750 3,749
/FRPDWT_2/data007/FPR_GL_D02_04.dbf 1 3,750 3,749
/FRPDWT_2/data003/FPR_GL_D02_05.dbf 1 1,001 1,000
/FRPDWT_2/data001/FPR_GL_D02_10.dbf 1 1,280 1,279
/FRPDWT_2/data010/FPR_GL_D02_11.dbf 1 3,840 3,839
Smallest
Size Current Poss.
FILE_NAME Poss. Size Savings
------------------------------------------------------------ -------- -------- --------
/FRPDWT_2/data003/FPR_GL_D02_03.dbf 1 3,750 3,749
/FRPDWT_2/data003/FPR_GL_D02_01.dbf 1 3,750 3,749
/FRPDWT_2/data007/FPR_GL_D02_05.dbf 1 1,250 1,249
12 rows selected.
SQL> alter database datafile '/FRPDWT_2/data003/FPR_GL_D02_03.dbf' resize 1000M;
alter database datafile '/FRPDWT_2/data003/FPR_GL_D02_03.dbf' resize 1000M
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value
I can clearly see from shrink report that we can shrink upto 1M for all datafiles but still its giving me error ORA-03297
DB VERSION : 10.2.0.4