Solved

Rebuild table

Posted on 2009-07-01
13
707 Views
Last Modified: 2013-12-19
We need to change the pct free for some Oracle tables.  What is the best way to of this?
0
Comment
Question by:sqlnewbie08
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
  • +2
13 Comments
 
LVL 40

Assisted Solution

by:mrjoltcola
mrjoltcola earned 300 total points
ID: 24754806
I think this should work:

alter table t pctfree 5;
alter table t move;

It should rebuild the table in the same tablespace.
0
 
LVL 14

Assisted Solution

by:shru_0409
shru_0409 earned 50 total points
ID: 24754835
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
 

Author Comment

by:sqlnewbie08
ID: 24754966
mrjoltcola:
Should other methods be used if the table is large or is the 'move' still a good option.  Thank you.
0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

Author Comment

by:sqlnewbie08
ID: 24755078
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
 
LVL 48

Assisted Solution

by:schwertner
schwertner earned 100 total points
ID: 24755250
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
 
LVL 40

Assisted Solution

by:mrjoltcola
mrjoltcola earned 300 total points
ID: 24755341
>>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
 
LVL 40

Accepted Solution

by:
mrjoltcola earned 300 total points
ID: 24755524
>>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
 

Author Comment

by:sqlnewbie08
ID: 24756543
Thank you all... terrific explanations.
0
 
LVL 48

Assisted Solution

by:schwertner
schwertner earned 100 total points
ID: 24756887
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
 
LVL 2

Assisted Solution

by:latorreconsultores
latorreconsultores earned 50 total points
ID: 24765253
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
 

Author Closing Comment

by:sqlnewbie08
ID: 31598781
thank you all...
0
 
LVL 48

Expert Comment

by:schwertner
ID: 24766621
Good luck!
0
 

Author Comment

by:sqlnewbie08
ID: 24766759
thank you.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question