Rebuild table

We need to change the pct free for some Oracle tables.  What is the best way to of this?
sqlnewbie08Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mrjoltcolaCommented:
I think this should work:

alter table t pctfree 5;
alter table t move;

It should rebuild the table in the same tablespace.
0
shru_0409Commented:
A smaller PCTFREE has the following effects:

1. Reserves less room for updates to expand existing table rows
2. Allows inserts to fill the block more completely
3. May save space, because the total data for a table or index is stored in fewer blocks (more rows or entries for each block)
4. A small PCTFREE might be suitable, for example, for a segment that is rarely changed.

A larger PCTFREE has the following effects:

1. Reserves more room for future updates to existing table rows
2. May require more blocks for the same amount of inserted data (inserting fewer rows for each block)
3. May improve update performance, because Oracle does not need to chain row pieces as frequently, if ever
4. A large PCTFREE is suitable, for example, for segments that are frequently updated.

Check the views ALL_OBJECT_TABLES, DBA_OBJECT_TABLES, USER_OBJECT_TABLES for lot of valuabe information
0
sqlnewbie08Author Commented:
mrjoltcola:
Should other methods be used if the table is large or is the 'move' still a good option.  Thank you.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

sqlnewbie08Author Commented:
I just read some text where you only need to issue the alter table to change the pctfree...

So, does the table need to be rebuilt?  If so, why?
0
schwertnerCommented:
The way can be:
1. ALTER TABLE my_table PCTFREE 45;
2. Export and Import the tables dropping the old one.

Only physical recreation of the tables can change the PCTFREE of existing blocks
immideatelly. The other way is conservative - to wait the changes in the block
to make the job.
0
mrjoltcolaCommented:
>>So, does the table need to be rebuilt?  If so, why?

Altering the pctfree affects new blocks only.

Moving the table rebuilds the whole thing online and all blocks will be migrated to the new PCTFREE.

Physical recreation of the table is not required, nor is import/export. ALTER TABLE MOVE works fine and is preferred for large segments.


>>Only physical recreation of the tables can change the PCTFREE of existing blocks immideatelly.

Rebuilding the table changes the PCTFREE immediately.
0
mrjoltcolaCommented:
>>I just read some text where you only need to issue the alter table to change the pctfree

Let me try to explain in more detail.

When you create a table in Oracle (or an index or any other type of segment) there is a default PCTFREE that is part of the "metadata" of that table. It says which percentage to reserve for each new block. PCTFREE is the guideline, but it is not used until blocks actually get created, and is applied to each new block.

If you have a table of 100m with PCTFREE of 50, and you simply do:

alter table t pctfree 5;

as schwertner said above, NOTHING changes with the pre-existing blocks. They will still have the 50% overhead. As soon as a new insert happens, the new blocks will be packed according to the NEW PCTFREE of 5. So you will end up with a mixed density in the table. The usual goal of changing PCTFREE is either to pack a segment into less blocks, or to make space in a segment for frequent updates without chaining. In _either_ case, the usual approach is to rebuild the whole segment to apply the PCTFREE. The only time it is not is when the table is HUGE, such as in a data warehouse with MANY gigabytes or terabytes in a single table.

The way to "defrag" the table or apply the new PCTFREE to the whole table is by using any method that rebuilds or recreates the table.

1) alter table move
2) create table t_new as select * from t ...; (then renaming)
3) export / (rename | drop) / import

I recommend the MOVE option because it is preferred in data warehouses where we store huge segments. It is much faster and less risky than export + drop + import, but it accomplishes the same end goal.

As always, before doing any major reorgs of data, take a backup.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sqlnewbie08Author Commented:
Thank you all... terrific explanations.
0
schwertnerCommented:
Without moving the tables only the new blocks and also the affected by changes old blocks will use the changed parameter.
Moving the table using some of the methods will affect all blocks.
But doing table by table renaming can hit the foreign keys.
Thatswhy I recommended Export/Import of a whole schema but also other moving methods are good. Depends on the size of course.
0
latorreconsultoresCommented:
hello sqlnewbie08:

Just to tell u that u has only 3 thigs to do:

1. alter table my_table pctfree 10;

2. alter table my_table move;

3. recreate ur indexes and synonyms wich is this table asociated.
0
sqlnewbie08Author Commented:
thank you all...
0
schwertnerCommented:
Good luck!
0
sqlnewbie08Author Commented:
thank you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.