3

My Oracle instance up on Amazon's RDS filled a 20GB storage in the space of about 20 minutes. I checked all the applications that use this DB and nothing looks out of the ordinary - I can't figure out why this would have happened.

See the graph from CloudWatch:

enter image description here

As you can see, I increased the storage to 25GB hoping that I might "shake something loose" - but obviously, all it did was increase my free space by 5GB.

This instance runs a very low traffic web app and you can see the free space has been very stable with the usage. 20GB should be more than enough for this system. What's going on?

Any tips for how I can figure out what's taking up all the space?

Edit: Some stats I managed to get from the DB:

SELECT 
( SELECT SUM(BYTES)/1024/1024/1024 DATA_SIZE FROM DBA_DATA_FILES ) +
( SELECT NVL(SUM(BYTES),0)/1024/1024/1024 TEMP_SIZE FROM DBA_TEMP_FILES ) +
( SELECT SUM(BYTES)/1024/1024/1024 REDO_SIZE FROM SYS.V_$LOG ) +
( SELECT SUM(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 CONTROLFILE_SIZE FROM V$CONTROLFILE) "Size in GB"
FROM
DUAL;

Produces:

Size in GB
------------------
19.360321044921875

And using a process of elimination, I figured out that it's DBA_TEMP_FILES that's taking up 18 of the 19 GB.

What's in DBA_TEMP_FILES? How do I clear them out?

  • You need to a) provide more information to us and b) look at the oracle logs first - also please read our help pages about what we expect of question askers too. – Chopper3 Mar 10 '14 at 18:07
  • @Chopper3 I'll be the first to admit I'm no Oracle expert - and that's why I'm looking for info on how I can provide more information. Which logs are you referring to? – wwarren Mar 10 '14 at 18:14
4

OK, I resized my TEMP tablespace and that appears to have sorted out my issue.

Here's the command I ran:

ALTER TABLESPACE TEMP SHRINK SPACE KEEP 512M;

Seems to have fixed the issue (DB size went from 19GB to 2GB), but I'm still in the dark about why this would have occurred in such a small environment.

Added some CloudWatch alarms so I can jump on this ahead of time next time (if there is a next time).

  • @ToniToniChopper Every figure out why it happened? We've migrated away from Oracle since I wrote this Q&A – wwarren Oct 13 '16 at 17:18
  • In our case the undo tablespace was using 17 out of 30 GB. I had run a few DELETE on large tables but still... it should not expand so much. – Toni Toni Chopper Oct 14 '16 at 7:19
  • The default MAX_SIZE set by RDS is huge – Toni Toni Chopper Oct 14 '16 at 7:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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