RE: trouble shrinking a datafle
Date: Fri, 5 Aug 2016 17:47:58 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D90150337728_at_exmbx05.thus.corp>
The simplest possible explanation is that you have an object in that tablespace that has been dropped but is still in the recyclebin. Such objects show up as empty space in the dba_free_space which is probably why the report suggests you can shrink the file but get an error message when you try.
If you select segment_name from dba_segments for that tablespace you will probably see some bizarre names reported - these will be the dropped objects. You need to "purge recyclebin" or even "purge dba_recyclebin"
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle
From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Cohen, Andrew M. [Andrew.Cohen_at_tufts.edu] Sent: 05 August 2016 18:19
To: Oracle-L_at_freelists.org
Subject: trouble shrinking a datafle
Hi list,
I�m having trouble shrinking a data file that at one time extended to 32 GB and now is using about 4 GB. I have done this in the past where I move tables and indexes from the �back end� out of the tablespace, so that there is nothing but free space after about the 4 GB mark of the file. I can see this with a tablespace map on the data file through TOAD. I am on Oracle 12.1.0.2 EE. I�ve even bounced the instance after all of the moves just in case that had anything to do with it.
I found an Ask Tom thread to do the following:
select file_name,
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 ) savingsfrom dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
/
It showed:
Smallest
Size Current Poss.
FILE_NAME Poss. Size Savings
-------------------------------------------------- -------- -------- --------
/SDWSTG/DATA/dwdata.dbf 3,327 31,507 28,180
I then ran his next script to create the command to shrink. This was the result.
alter database datafile '/SDWSTG/DATA/dwdata.dbf' resize 3327m;
When I attempt to shrink I get:
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value
I get this error if I try to shrink the file to anything below the current size of 31,507.
Anyone know what I may be missing?
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Aug 05 2016 - 19:47:58 CEST