Toad for Oracle datafile shrinking feature should perhaps take into account recyclebin contents

Toad for Oracle
1

Hi

So recently I was stuck with shrinking USERS tablespace datafile. Although there (at the end of the datafile) were at least few hundred MB free space, but I couldn’t resize this file even like 1MB.

When tried datafile shrinking feature inside Toad (Schema Browser -> Tablespaces -> (select single tablespace) -> Datafiles -> (select single datafile) -> Alter datafile -> Minimum Size?), then it, at start, actually allowed to shrink datafile (few hundred MB), but when I clicked “OK” from this dialog box, then I got ORA error:

“ORA-03297: file contains used data beyond requested RESIZE value”

toad_resize.jpeg

I couldn’t figure this out at start, but then I was wondering, what if something is in recyclebin. So the next step for me would be to

SQL> purge dba_recyclebin;

After that I managed to shrink datafile exactly as Toad suggested me.

So, what I am trying to say is, perhaps there could be this feature, that along with the minimum size suggests also that this resize value is possbile only if recyclebin is purged. Maybe there pops up another dialog box that asks me “Do you want to purge dba_recyclebin?”, Yes/No answers.

Or.

If you don’t like this idea, then this “Minimum Size?” feature should not suggest me resize value that isn’t possible at the present time. So in other words, it needs to consider recyclebin contents.

I personally would like first idea more…:slight_smile:

Toad for Oracle 12.12.0.39

Regards

Raul

2

I actually have some code in there that should be making it take recycle bin into account, but I found a problem with it, so I’ll fix. A prompt/indicator that recyclebin is not empty is a good idea. I can add that to.

3

Great news, thanks.

Raul