1

This may be a lack of understanding as I am a MSSQL DBA more than Oracle.

We are running Oracle on RDS in Amazon. Engine Version 19.0.0.0.ru-2020-10.rur-2020-10.r1

I dropped an old _backup table but didn't specify the purge option, the table IS NOT in the recycle bin but the space has not been reclaimed from the DB.

I have run the following and no rows returned:

SELECT * FROM RECYCLEBIN;
SELECT * FROM DBA_RECYCLEBIN;
SELECT * FROM USER_RECYCLEBIN;

Even tried (On a test backup) the command PURGE 'TABLENAME' and this fails as it isn't in the recycle bin. I am logged in as the ROOT user, unsure if this will make a difference.

Any advice or guidance would be welcomed.

Improve this question
5
  • What do you mean 'space hasn't been reclaimed'? Free space in the datafile or tablespace? Space on the file system?
    – REW
    Commented Jun 5 at 2:09
  • RDS, so not the file system. In the data file, like I said not an Oracle DBA so as I missed the purge command I am after advice on next steps.
    – Stockburn
    Commented Jun 6 at 3:25
  • 1
    The datafile has a high water mark. If you have dropped a 'segment' it doesn't automatically shrink the datafile. If you have a segment created or extended ABOVE the object you dropped, the space in the middle is marked for reuse, but it doesn't do like the windows 'defrag' where it shifts stuff around to reduce your datafile size. There are techniques to do that though. Is that what you're looking for?
    – REW
    Commented Jun 6 at 14:40
  • Hi REW, yeh I think I am. I just wasn't sure after being stupid and not running the purge if there was another way around this. If there was another recycle bin I wasn't seeing. I have learnt a lesson though, and thank you.
    – Stockburn
    Commented Jun 13 at 1:46
  • 1
    @REW if you want to add this is an answer. I can then log it as answered by you.
    – Stockburn
    Commented Jun 17 at 5:53

1 Answer 1

Reset to default
1

The datafile has a high water mark. If you have dropped a 'segment' it doesn't automatically shrink the datafile. If you have a segment created or extended ABOVE the object you dropped, the space in the middle is marked for reuse, but it doesn't do like the windows 'defrag' where it shifts stuff around to reduce your datafile size.

Improve this answer
1
  • Thanks @Rew appreciate the help
    – Stockburn
    Commented Jun 20 at 1:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.