In a 11.2.0.3 database, we tried to reclaim space from USERS tablespace.
We faced a problem.
We use the following query to the the end of a particular USERS' datafile [file_id=72, filename=/oradb/SBLCCPRD/oradata4/users03.dbf]
SELECT
A.FILE_ID,
A.BLOCK_ID,
A.BLOCK_ID + A.BLOCKS - 1 END_BLOCK,
A.BLOCKS,
--ROUND(A.BLOCKS*B.BLOCK_SIZE/1024/1024,2) MBYTES,
A.BLOCKS*B.BLOCK_SIZE "SIZE",
A.OWNER,
A.SEGMENT_NAME,
A.PARTITION_NAME,
A.SEGMENT_TYPE
FROM
DBA_EXTENTS A, DBA_TABLESPACES B
WHERE
A.FILE_ID=:A AND A.TABLESPACE_NAME=B.TABLESPACE_NAME
UNION ALL
SELECT
A.FILE_ID,
A.BLOCK_ID,
A.BLOCK_ID + A.BLOCKS - 1 END_BLOCK,
A.BLOCKS,
--ROUND(A.BLOCKS*B.BLOCK_SIZE/1024/1024,2) MBYTES,
A.BLOCKS*B.BLOCK_SIZE "SIZE",
'free' OWNER,
'free' SEGMENT_NAME,
NULL PARTITION_NAME,
NULL SEGMENT_TYPE
FROM
DBA_FREE_SPACE A, DBA_TABLESPACES B
WHERE
A.FILE_ID=:A AND A.TABLESPACE_NAME=B.TABLESPACE_NAME
ORDER BY 2 DESC;
We can see there were 5 contiguous free extents of 64 Kbytes each, total 320 Kbytes.
FILE_ID | BLOCK_ID | END_BLOCK | BLOCKS | SIZE | OWNER | SEGMENT_NAME | PARTITION_NAME | SEGMENT_TYPE |
72 | 1614952 | 1614959 | 8 | 65536 | free | free | | |
72 | 1614944 | 1614951 | 8 | 65536 | free | free | | |
72 | 1614936 | 1614943 | 8 | 65536 | free | free | | |
72 | 1614928 | 1614935 | 8 | 65536 | free | free | | |
72 | 1614920 | 1614927 | 8 | 65536 | free | free | | |
72 | 1614912 | 1614919 | 8 | 65536 | AMARGARI | CX_OTE_LOV_BACKUP_06052014 | | TABLE |
72 | 1614904 | 1614911 | 8 | 65536 | AMARGARI | CX_OTE_LOV_BACKUP_06052014 | | TABLE |
72 | 1614896 | 1614903 | 8 | 65536 | EFOTEINOP | CX_OTE_LOV_0106 | | TABLE |
... | ... | ... | ... | ... | ... | ... | ... | ... |
We tried to coalesce the tablespace, but it did not. The free extents did not get merged to one of 320 Kbytes.
SQL> alter tablespace users coalesce;
Tablespace altered.
This is the datafile's size in bytes:
SELECT FILE_NAME, BYTES
FROM DBA_DATA_FILES
WHERE FILE_ID=72;
FILE_NAME | BYTES |
/oradb/SBLCCPRD/oradata4/users03.dbf | 13229744128 |
When we tried to shrink the datafile, even by just 8 Kbytes, we got ORA-03297:
SQL> ALTER DATABASE DATAFILE 72 RESIZE 13229735936;
ALTER DATABASE DATAFILE 72 RESIZE 13229735936
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value
The solution was to purge the tablespace's recycle bin, even if there were no recycled objects near the end of the datafile. Furthermore, before I begin all this procedure, I had purged the database's recycle bin, using "PURGE DBA_RECYCLEBIN".
SQL> PURGE TABLESPACE USERS;
Tablespace purged.
SQL> ALTER DATABASE DATAFILE 72 RESIZE 13229735936;
Database altered.
FILE_ID | BLOCK_ID | END_BLOCK | BLOCKS | SIZE | OWNER | SEGMENT_NAME | PARTITION_NAME | SEGMENT_TYPE |
72 | 1614920 | 1614951 | 32 | 262144 | free | free | | |
72 | 1614912 | 1614919 | 8 | 65536 | AMARGARI | CX_OTE_LOV_BACKUP_06052014 | | TABLE |
72 | 1614904 | 1614911 | 8 | 65536 | AMARGARI | CX_OTE_LOV_BACKUP_06052014 | | TABLE |
72 | 1614896 | 1614903 | 8 | 65536 | EFOTEINOP | CX_OTE_LOV_0106 | | TABLE |
... | ... | ... | ... | ... | ... | ... | ... | ... |