Re: trouble shrinking a datafle

As you've found out, RECYCLEBIN is just for objects you own, DBA_RECYCLEBIN is for all objects.
https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9018.htm

On 6 August 2016 at 02:09, Cohen, Andrew M. <Andrew.Cohen_at_tufts.edu> wrote:

> 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
>
>
>

--
http://www.freelists.org/webpage/oracle-l