2

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?

2
  • 1
    My question was answered on another message board. The answer is to use DBMS_REPAIR.SEGMENT_FIX_STATUS after the change in PCTFREE :)
    – Kebs
    Feb 27 at 20:11
  • Welcome! Glad you found a solution. Feel free to answer your own question (after verifying it works!), using your comment text, and 2 days after you post your answer, you can accept it with the check-mark icon, so others faced with the same issue can see there's a solution. Mar 2 at 15:31
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.

1
  • Thanks for creating an answer. I found some interesting behavior when using that package too; I got some ORA-600 errors, not during the dbms_repair, but when trying to access some partitions later! Corrupted blocks... Mar 3 at 20:04

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.