RE: Move table online and update the indexes at the same time.
- From: "Igor Neyman" <ineyman@xxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 27 Jul 2004 10:04:02 -0400
Interesting.....
Will "ddl_wait_for_locks" (when set to true) help with "CREATE OR
REPLACE PACKAGE ...", if package is in use at the time?
Will the session wait (as with "ALTER TABLE...)?
Igor Neyman, OCP DBA
ineyman@xxxxxxxxxxxxxx
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Tanel P=F5der
Sent: Monday, July 26, 2004 5:37 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Move table online and update the indexes at the same time.
> Tanel,
> Thanks for the reply.
> It's 9i. I thought about table redefinition but I am not sure how it
> affect the performance. Second, I wonder if there a need to get a lock
on
> the table just for a split of a second. We do not have any chance
locking
> the table since there are always several transactions using it.
> What is your experience? Since it appeared only in 9i I am a bit
worried
> about using it.
I've never used it in production myself, but as much I've heard, it does
have its problems and limitations - thus extensive testing would be
needed.
It will increase your load, since it basically creates a materialized
view
log for your table (and you have to clone the table manually + build the
indexes + apply the changes incrementally later on).
Redefinition package will require a lock for short time on your table if
used properly. The temporary locking could be implemented by deferring
your
transactions for a short time if possible and/or having a tight pl/sql
loop
which retries the operation until it succeeds.
Btw, in 10g there is a "ddl_wait_for_locks" parameter which you can set
to
true on your session level - that way Oracle will execute DDL commands
in
wait mode, thus waiting until all incompatible locks on given object are
released, instead of erroring out immediately.
Btw, why are you trying to move your tables?
Tanel.
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
