Question about Oracle Database Enterprise Edition version 126.96.36.199.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?