What if my database is full and I don't have any spare hard disk or memory space. What should my strategy to free up the space?
migrated from stackoverflow.com Sep 14 '10 at 13:17
This question came from our site for professional and enthusiast programmers.
"My database is full" can mean very different things in Oracle
1 Every byte of my hard drive is filled with "useful" data.
Compression / de-duplicating data / normalisation may help, but its cheaper to buy more disk.
2 I have space in the file for tablespace X but need it for tables in tablespace Y
If the space is at the end of the file, you can shrink the tablespace. If not best to move the tables from Y to X. Its pretty difficult to reclaim space from the middle of a tablespace file
3 I have deleted a lot of records from TABLE A but it hasn't reclaimed the space in the tablespace
ALTER TABLE ... MOVE; will tidy it up, but you'll need to rebuild indexes afterwards.
There are probably other scenarios, so I'll community wiki this answer/
Transaction logs will keep growing indefinitely until they consume all available space. It's likely this is one of the causes of you running out of space.
You can truncate the transaction logs to free up space, but be aware that this will remove your ability to restore-to-point-in-time or roll back changes to the DB.
To truncate the logs, run
then shrink the log file to free drive space. Note I've assumed MS SQL.
You will need an ongoing strategy to prevent this from happening again. Depending on your application and level of laissez-faire, you may want to: