Solved

Rebuild table

Posted on 2009-07-01
13
703 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
  • 5
  • 3
  • 3
  • +2
13 Comments
 
LVL 40

Assisted Solution

by:mrjoltcola
mrjoltcola earned 300 total points
Comment Utility
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
Comment Utility
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
Comment Utility
mrjoltcola:
Should other methods be used if the table is large or is the 'move' still a good option.  Thank you.
0
 

Author Comment

by:sqlnewbie08
Comment Utility
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 47

Assisted Solution

by:schwertner
schwertner earned 100 total points
Comment Utility
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
Comment Utility
>>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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 40

Accepted Solution

by:
mrjoltcola earned 300 total points
Comment Utility
>>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
Comment Utility
Thank you all... terrific explanations.
0
 
LVL 47

Assisted Solution

by:schwertner
schwertner earned 100 total points
Comment Utility
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
Comment Utility
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
Comment Utility
thank you all...
0
 
LVL 47

Expert Comment

by:schwertner
Comment Utility
Good luck!
0
 

Author Comment

by:sqlnewbie08
Comment Utility
thank you.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to take different types of Oracle backups using RMAN.

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now