2

I work on a database that's on Oracle 12.1. Whenever we drop a table for any reason, it goes to the recycle bin. Usually, whenever we build another table that requires more space, the space automatically opens up on the recycle bin. Recently, the recycle bin is not releasing space at all. I have to manually purge the recycle bin, or I cannot build tables that need that space.

What do I do to ensure that the recycle bin is setup to purge itself automatically, only when necessary? I don't want a custom script or automation; I want to use the recycle bin the way it's designed and intended.

jacobhobson
  • 1,075
  • 9
  • 14
  • 1
    Sounds like a bug to me. Perhaps you should ask support? Anything in the logs? – ewramner May 21 '18 at 19:20
  • @ewramner, that's what I'm afraid of. It might just be a bug I need to report. I'm not sure of what logs I would turn to in order to get info on why recyclebin's not doing its job. – jacobhobson May 21 '18 at 19:26
  • 1
    'They continue to count against user space quotas, until specifically purged from the recycle bin or the unlikely situation where they must be purged by the database because of tablespace space constraints.' - have you checked your ALERT LOG for messages around this? – thatjeffsmith May 21 '18 at 20:04

2 Answers2

2

You shouldn't rely on Oracle to automatically purge the recycle bin because its behavior is not properly documented. Manually purging the recycle bin may be the best option.

According to the Oracle 10g documentation, the recycle bin is purged first-in, first-out, based on space pressure. Space pressure happens if a user runs out of quota or the tablespace is full or needs a datafile to be extended.

That's a good start but there are some problems with that documentation - it doesn't cover all space scenarios like a full ASM diskgroup or OS drive, and it doesn't exist in version 11g or later. I couldn't find any relevant information on My Oracle Support either.

Oracle usually has great documentation and it's unusual to see a concept like this disappear from the manual. Maybe the behavior completely changed in modern versions, and it's now so weird and complicated that nobody wanted to document it. Or maybe they just forgot.

Use manual purging if possible. Or maybe look into shrinking some datafiles to more closely match the diskgroup or operating system limits; perhaps then Oracle will "see" the space pressure.

Jon Heller
  • 34,999
  • 6
  • 74
  • 132
  • +1 -- I do think this is a bug and I shouldn't rely on the recyclebin. However, I was able to restart Oracle to get the recyelbin going again, so I'll make an answer for that so users with production problems can note that as the answer. Thanks, @Jon! – jacobhobson May 24 '18 at 17:16
1

Resolved: Restart Oracle

Whenever I restarted Oracle, the bug went away. I still don't have a root cause explanation of this bug.

Secondary: "Purge" Safety Nets

Per @Jon Heller's response, it's not necessarily safe to rely on the recyclebin to do its job right now. With that being the case, where appropriate, my team created low-risk "purge" statements.

jacobhobson
  • 1,075
  • 9
  • 14