Hi,
I remember the 1st time I wrote query to find the minimum size of the
datafile that one can downsize it to it was quite difficult.
So in case this helps, here's a script I use (hopefully it's not too buggy),
It provides outputs like this, at the end of the the command it give some
extra info so it was more clear of what's going to happen when you run
command):
...
ALTER DATABASE DATAFILE '/u01/install/VISION/data/sys3.dbf' resize
2066743296 /* (freeing 29 M) The File is autoextensible by 30797M past the
new size */;
ALTER DATABASE DATAFILE '/u01/install/VISION/data/sys4.dbf' resize
1972371456 /* (freeing 119 M) The File is autoextensible by 30887M past the
new size */;
ALTER DATABASE DATAFILE '/u01/install/VISION/data/sys5.dbf' resize
1888485376 /* (freeing 199 M) The File is autoextensible by 30967M past the
new size */;
ALTER DATABASE DATAFILE '/u01/install/VISION/data/sys6.dbf' resize
1850736640 /* (freeing 235 M) The File is NOT AUTOEXTENSIBLE */;
ALTER DATABASE DATAFILE '/u01/install/VISION/data/sys7.dbf' resize
1919942656 /* (freeing 169 M) The File is NOT AUTOEXTENSIBLE */;
ALTER DATABASE DATAFILE '/u01/install/VISION/data/sys8.dbf' resize
1868562432 /* (freeing 218 M) The File is NOT AUTOEXTENSIBLE */;
...
And here's the script (replace dba_ with cdb_ for CDBs if needed):
set lines 500 tab off
WITH fspace AS
(SELECT fs.file_id, fs.block_id, fs.blocks, CONNECT_BY_ISLEAF AS leaf
FROM dba_free_space fs
START WITH (BLOCK_ID+BLOCKS) = (SELECT df.blocks FROM dba_data_files df
WHERE df.file_id=fs.file_id)
CONNECT BY BLOCK_ID + BLOCKS = PRIOR BLOCK_ID
AND file_id = PRIOR file_id
)
SELECT /*+ gather_plan_statistics*/ 'ALTER DATABASE DATAFILE '''
||d.file_name ||''' resize ' ||f.block_id*t.block_size
||' /* (freeing ' ||ROUND(d.blocks-f.block_id)*t.block_size/1024/1024 ||'
M) The File is '
|| CASE
WHEN d.autoextensible='YES'
AND d.maxblocks > f.block_id
THEN 'autoextensible by '
||ROUND((d.maxblocks-f.block_id)*t.block_size/1024/1024)
||'M past the new size'
ELSE 'NOT AUTOEXTENSIBLE'
END ||' */;'rsz_stmt
FROM fspace f,
dba_data_files d,
dba_tablespaces t
WHERE f.leaf =1
AND d.file_id =f.file_id
AND t.TABLESPACE_NAME=d.TABLESPACE_NAME;
---
Maris Elsins
@MarisElsins <https://twitter.com/MarisElsins>
www.facebook.com/maris.elsins
On Fri, Aug 5, 2016 at 8:47 PM, Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
wrote:
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
@jloracle
------------------------------
*From:* oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on
behalf of Cohen, Andrew M. [Andrew.Cohen@xxxxxxxxx]
*Sent:* 05 August 2016 18:19
*To:* Oracle-L@xxxxxxxxxxxxx
*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?
