RE: Partition Exchange Instantaneous?

From: Bobak, Mark <Mark.Bobak_at_proquest.com>
Date: Thu, 23 Jun 2011 11:38:44 -0400
Message-ID: <6AFC12B9BFCDEA45B7274C534738067F704827AF_at_AAPQMAILBX02V.proque.st>

Or, if you're on 11g, you can set ddl_lock_timeout, to slip ddl changes in while dml is running against a table.

-Mark

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Adam Musch Sent: Thursday, June 23, 2011 11:05 AM
To: japplewhite_at_austinisd.org
Cc: oracle-l_at_freelists.org
Subject: Re: Partition Exchange Instantaneous?

You can't lose the rows being inserted. They'll be inserted into the one and only one segment appropriate - or it'll throw an exception that there's no partition for that row.

However, DML can block a partition exchange operation, so it's best to wrap such changes in a loop akin to Jonathan Lewis' do_ddl function in order to eat ORA-00054 errors caused by an inability to get an exclusive lock on the partition being shuffled out.

On Wed, Jun 22, 2011 at 12:53 PM, <japplewhite_at_austinisd.org> wrote:

>

> Since the Partition Exchange is a Data Dictionary process only, I'm assuming
> that, should there be a DML transaction on the main table during the
> Exchange, the Audit row(s) will either go into the old Audit table segment
> or be put into the new, empty exchanged-in segment.  Have there been any
> reports or situations where a transaction's data got "lost" during a Segment
> Exchange?  I can't find anywhere that Oracle guarantees it won't happen.

-- 
Adam Musch
ahmusch_at_gmail.com
--
http://www.freelists.org/webpage/oracle-l




--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jun 23 2011 - 10:38:44 CDT