Every now and again, I come across a file where Toad says it can be shrunk to (approximately) a given size, but then it fails with "ORA-03297: file contains used data beyond requested RESIZE value". I don't think the file is growing while I'm in the dialog (doing it again calculates the same value), and it's not a small rounding discrepancy (in the below example, rounding up to e.g. 20750 failed as well). This functionality generally works fine for me, but it's not entirely uncommon to encounter this
Hi Paul,
Thanks for reporting this. I have seen it too. I always thought it was a small rounding problem. I see that your recycle bin is not empty. If you are at liberty to empty it next time this happens, that may solve it.
I'll investigate and get back to you.
Next time it happens, would you mind running these queries and sharing the result? I don't think there is anything proprietary here. You'll need to substitute in your tablespace name and file ID. Really I just want to know if they all return the same value for highblock.
SELECT block_id highblock,
LAG (block_id + BLOCKS, 1, 0) OVER (ORDER BY block_id) prior_highblock
from sys.dba_lmt_free_space fs,
sys.ts$ ts
WHERE fs.tablespace_id = ts.ts#
AND ts.name = 'USERS' -- tablespace name
AND fs.file_id = 94 -- file id
ORDER BY block_id desc
FETCH FIRST 1 ROWS ONLY;
SELECT block_id highblock,
LAG (block_id + BLOCKS, 1, 0) OVER (ORDER BY block_id) prior_highblock
from sys.dba_free_space fs
WHERE fs.tablespace_name = 'USERS' -- tablespace name
AND fs.file_id = 94 -- file id
ORDER BY block_id desc
FETCH FIRST 1 ROWS ONLY;
SELECT MAX (block_id + blocks) highblock
FROM DBA_EXTENTS
WHERE tablespace_name = 'USERS' -- tablespace name
AND file_id = 94 -- file id
-John
I can still reproduce it on that same file:
Query 1:
| HIGHBLOCK | PRIOR_HIGHBLOCK |
|---|---|
| 3047552 | 3047552 |
Query 2:
| HIGHBLOCK | PRIOR_HIGHBLOCK |
|---|---|
| 3047552 | 3047552 |
Query 3:
| HIGHBLOCK |
|---|
| 3087360 |
I haven't emptied the recyclebin yet, I should be able to do it later, but happy to leave in this state for the time being if there's anything else you want
Interesting, thank you.
I was trying to avoid DBA_EXTENTS because it is slow, but maybe only looking at the free space isn't always correct.
Is the DBA_EXTENTS query slow for you?
Is this query faster and does it give the same result?
SELECT MAX(ue.block + ue.length) highblock
FROM sys.dba_lmt_used_extents ue,
sys.ts$ ts
WHERE ue.tablespace_id = ts.ts#
AND ts.name = '...' -- tablespace name
AND ue.fileid = ...; -- file id
thanks.
SELECT MAX (block_id + blocks) highblock
FROM DBA_EXTENTS
WHERE tablespace_name = '...' -- tablespace name
AND file_id = ...; -- file id
is a lot faster for me than
SELECT MAX(block + length) highblock
FROM sys.dba_lmt_used_extents ue,
sys.ts$ ts
WHERE ue.tablespace_id = ts.ts#
AND ts.name = '...' -- tablespace name
AND ue.fileid = ...; -- file id
They both give the same result:
| HIGHBLOCK |
|---|
| 3087360 |
Thanks for the info. I'm surprised DBA_EXTENTS is faster. Both are pretty fast for me but the DBA_EXTENTS one has a much higher cost if I do an explain plan on it.
I see what's happening now. This has nothing to do with recycle bin after all. The DBA_LMT_FREE_SPACE query returns a correct result if the tail end of the datafile is free space. If there is an extent there, then it's "wrong" (actually not wrong, but not a value that's useful to me).
You can verify this in the Schema Browser -> Tablespaces -> RHS -> Objects.
Set the dropdown to "Extents"
Filter the file ID if there is more than one file in your tablespace.
Check the "Show free space" box.
Scroll down to the end so you are looking at the highest BLOCK_ID.
If the row with the highest BLOCK_ID is free space, the file can be shrunk.
If the row with the highest BLOCK_ID is a database object, the file cannot be shrunk.
I'll fix this for the next version.
Splendid, thank you!