dbms_redefinition
reader, February 20, 2004 - 10:50 pm UTC
If I used the above package to move the table online, do I still need to rebuild the index? Or it does keep the index maintained as well.
February 21, 2004 - 10:51 am UTC
if you "alter table t move", you must rebuild all indexes (and it is not truly an online operation - no modifications during the move)
clarification please ....
reader, February 21, 2004 - 11:42 am UTC
If I used dbms_redefinition package to reorg a table, No need to rebuild the associated indexes? thanks.
February 21, 2004 - 1:33 pm UTC
well, yes, no need to "rebuild" for the simple reason that you are (in 9i) responsible for BUILDING the indexes on the newly redefined table! (trick answer or trick answer, depends on how you want to look at it)
the steps to redefine in 9i are roughly:
a) create scratch (new) table to redefine into
b) start redefine process, copies data from old to new
c) index new
d) constrain new
e) add triggers , etc to new
f) grant on new
g) finish redefine
In 10g, much easier, you
a) create scratch (new) table to redefine into
b) start redefine process, copies data from old to new
c) ask dbms_redef to copy all of the stuff you did in c,d,e,f above
d) finish redefine
so yes, you do not need to rebuild anything
but
you need to create them in the first place!
dbms_redefinition
reader, February 21, 2004 - 3:21 pm UTC
Thanks for the clarification. In 9i, would you recommend creating index on the new "interim" table just before running finish procedure and not immediately after start redef proc? Does it matter? Thanks again for your time.
February 21, 2004 - 4:19 pm UTC
you'd want to do everything (index, constrain, grant, triggers, everything) after the start redef and before the finish redef -- else the index would "disappear" for your users and that would obviate the "online nature".
"immediately" and "just before" don't have a context here -- the steps are:
a) start
sometime in between index, grant, constrain, etc...
b) finish
it is both "immediately after" starting and "just before" finishing no matter how you look at it.
dbms_redefinition
David, July 20, 2004 - 5:15 pm UTC
Tom, using this package to rebuild seems to me a lot work. Does 9i OEM support online redefinition of table by doing all of the work for us? Thanks.
July 20, 2004 - 8:49 pm UTC
don't know -- you'd have to check the docs (that's all i would be doing for you -- haven't really used 9i OEM personally...)
10g supports the "push button" approach -- you can online redefine in pretty much one step (copies indexes, etc for you)
rebuild table -- oracle 9.2.0.6
Baqir Hussain, July 10, 2005 - 1:08 pm UTC
To rebuild a table I would like to do the following steps
1. rename table
2. drop Foreign keys constraints
3. drop all user named constraints
4. re-create table
5. insert into .. select from rename_table
6. drop indexes
7. create indexes
8. re-create all user named constraints
9. re-create FKeys that reference the new table
10. drop and re-create trigger
I am looking for a sql or pl/sql or package to extract all this information from a table in the above order.
Thanks
July 10, 2005 - 1:30 pm UTC
those are the wrong steps.
steps are
a) probably forget about doing it at all. Unless you have a really truly stupendous reason (like this is a really big table and you deleted every other row and you full scan this a lot).
b) revisit a, just to make sure
c) upon deciding "ok, we are doing to do this"
1) alter table T move;
2) alter index I rebuild; (repeat for each index)
3) go home.
no fkeys, no constraints, no drop/create/lose by accident indexes, no GRANTS (which you totally forgot!) no nothing
just move it
then rebuild the indexes
but only after you have really proven to yourself that a) is true.
rebuild table
Baqir Hussain, July 10, 2005 - 2:17 pm UTC
Thanks for this nice suggestion.
I am curious to know what steps are actually being taken when
alter table T move;
is executed.
Thanks
July 10, 2005 - 2:37 pm UTC
it rebuilds the table, full scan of old table, direct path load of new table, when done - make the old table become "temporary segments" and rename the new table to have the old tables name and make its extents permanent.
it is the "rebuild" of a table. you can specify new storage, a new tablespace even.
it automates the entire process you were going to painstakingly do.