[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Rebuild table

Posted on 2009-07-01
13
Medium Priority
?
710 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 1200 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 200 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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 400 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 1200 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 1200 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 400 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 200 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

Technology Partners: 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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

649 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