RE: trouble shrinking a datafle

From: Cohen, Andrew M. <Andrew.Cohen_at_tufts.edu>
Date: Fri, 5 Aug 2016 18:09:04 +0000
Message-ID: <D70A21454DE84C4AAC03B46092E1718543A94854_at_SSVMEXDAG01MB05.tufts.ad.tufts.edu>


Thanks Mark. I purged the recyclebin (purge recyclebin), but that alone did not do the trick. I had to also issue: purge dba_recyclebin in order to free up the space beyond the HWM and shrink the data file.

-----Original Message-----
From: Powell, Mark [mailto:mark.powell2_at_hpe.com] Sent: Friday, August 05, 2016 1:46 PM
To: Oracle-L_at_freelists.org; Cohen, Andrew M. Subject: Re: trouble shrinking a datafle

The error message is pretty clear you are trying to remove file space that contains an object allocation. The release works from the logical end of the file toward the logical front of the file. If an object allocation exists between these two points the shrink is aborted since corruption would otherwise result.

Purge the recycle bin first if it is in use

Then map the tablespace (just union DBA_FREE_SPACE and DBA_EXTENTS) for the tablepsace files as this will show you where objects are located.

  • -

Next either remove some of the space after the last allocated object extent and call it a day or choose what objects to relocate via ALTER TABLE MOVE, ALTER INDEX REBUILD, ALTER TABLE SHRINK, EXPORT/IMPORT, and or DBMS_REDEFINITION to make more space at the logical end of the file.

  • -

Remember there is no real value in releasing space if objects will almost immediately request new allocation that will then require the file to extend. That is, when you are done the tablespace should have some free space to accommodate growth expected in the near term.

  • -

HTH



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: Friday, August 5, 2016 1:19:10 PM
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 ) savings
from 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-l
Received on Fri Aug 05 2016 - 20:09:04 CEST

Original text of this message