Solved

Rebuild table

Posted on 2009-07-01
13
704 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
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
 

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 47

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
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
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 47

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 47

Expert Comment

by:schwertner
ID: 24766621
Good luck!
0
 

Author Comment

by:sqlnewbie08
ID: 24766759
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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle Finace 3 66
SQL query of Oracle 10g database. 8 76
Maintaining Oracle Managed Accounts 2 29
Oracle 10g standard edition server with 4 processors 3 29
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  â€¦
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

919 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

17 Experts available now in Live!

Get 1:1 Help Now