Fighting with space is a neverending war 🙂 Of course, you have to keep an eye on old, unused, or duplicated objects. You have to closely monitor HWM on your tables and check whether users properly maintain their schemas. However, if this all is ok there are other handy methods you can use to save some space.
Compress data
If you have an archive table where you mostly store data and work with, let’s say, the last couple of months, there is a possibility of compressing the old data to reclaim some of your space back.
First of all, there are four options for compression:
- NOCOMPRESS – no compression applied; default behavior
- COMPRESS – compression is enabled on the table or partition during direct-path inserts only (mostly used in DWH system)
- COMPRESS FOR DIRECT_LOAD OPERATIONS – same as COMPRESS
- COMPRESS FOR OLTP – compression is enabled on the table or partition for all DML statements (mostly used in OLTP systems);
- COMPRESS FOR ALL OPERATIONS (for versions >= 11g)
-- tables ALTER TABLE schema.table MOVE COMPRESS; -- partitions ALTER TABLE schema.table MOVE PARTITION part_name COMPRESS; -- subpartitions ALTER TABLE schema.table MOVE SUBPARTITION sub_part_name COMPRESS;
This might save up to 70% of your storage. Just be careful not to compress data you are about to update; it might significantly slow down your performance. Only compress data that are meant to be read-only. To set a proper compression on a new table (or even existing one) you just need to omit MOVE (no matter whether it’s partitioned or not) – this will be default set compression for the whole table, all partitions, and sub-partitions; otherwise, it would only compress the existing partitions and newly created will not be compressed.
PCTFREE
This is another aspect of how you can claim some space back 🙂 PCTFREE is a parameter of a table defining how much of space (in percentage) should be allocated extra for each block of data for possible future updates. Oracle keeps adding new rows into a block until it reaches 90% of its capacity. The rest (10%) is left there for future updates and that exactly is the meaning of PCTFREE.
ALTER TABLE schema.table PCTFREE 0;
Well, but how about with tables you do not intend to update? Archive tables for example? Why do you need to keep extra space there? It is pure waste. For these tables, set PCTFREE = 0 and you can get some extra space back or save some in the future (in case of new table creation)