Oracle: Decreasing PCTFREE on large table

Question

Question about Oracle Database Enterprise Edition version 19.10.0.0.0 running as a standalone instance.

I have a table with billions of rows. The table has always had PCTFREE set to 10.

It turns out that we only ever perform inserts on this table, no updates, no deletions. I would like to save disk space by lowering PCTFREE to 0.

As I understand it, if I just do ALTER TABLE x PCTFREE 0 then in our case, only future allocated blocks are affected. But all the millions of existing blocks will never be filled above 90% because we never update or delete those rows.

Is there a way to get all the old blocks "re-evaluated" and put back on the freelist after I lower PCTFREE?

I would prefer not to move all of the data (e.g. use CTAS, or expdp+impdp, or delete+insert every row, or dbms_redefinition) because the table is so big that this would take forever and/or use enormous amounts of intermediate space. I just need the database to know that it's now okay to put more rows in the old blocks. Is this possible?

Answer 1

My question was answered on another message board. The answer is to use DBMS_REPAIR.SEGMENT_FIX_STATUS after the change in PCTFREE.

On my platform (sparc64) it's quite buggy, though, with lots of ORA-600 when repairing individual blocks. But repairing all blocks seems to work better for some reason.