Home » SQL & PL/SQL » SQL & PL/SQL » Partition (11g)
Partition [message #571479] Mon, 26 November 2012 07:34 Go to next message
TRaj
Messages: 80
Registered: September 2006
Member

Hi,

We have a table with interval partition. This table is accessed very frequently.

We are suppose to exchange partitions between this actual table from it's corresponding staging table.

In order to keep the newly created partitions empty, is there a way to restrict other applications from using it before we push data from staging table to it's actual table.

Thanks.
Re: Partition [message #571485 is a reply to message #571479] Mon, 26 November 2012 08:24 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1956
Registered: January 2010
Senior Member
Interval partition can be created explicitly or implicitly when someone inserts corresponding row(s). So you need to explain "in to keep the newly created partitions empty". If you want to create partition explicily and restrict access to it until you populate it use:

LOCK TABLE your_table
PARTITION FOR(date-within-new-partition)
IN EXCLUSIVE MODE
/
--populate partition
.
.
.
COMMIT; -- if needed


SY.
Re: Partition [message #571488 is a reply to message #571485] Mon, 26 November 2012 08:31 Go to previous messageGo to next message
TRaj
Messages: 80
Registered: September 2006
Member

Hi Solomon,

Many Thanks for the quick solution.
Re: Partition [message #571685 is a reply to message #571488] Thu, 29 November 2012 02:35 Go to previous messageGo to next message
TRaj
Messages: 80
Registered: September 2006
Member

Hi,

Locking is not agreed.

My requirement is ....

I have a actual and staging tables in production db.

After we populate data to staging tables, we need to push to actual tables which are accessed continuosuly round the clock.

We have tables with range partition on date and range-list(date as range and hours odd/even as list) composite partitions.

So for non-composite table partitions, we are going to have normal tables without any partitions and for range-list, we need to have an intermediate table defined with only list partition.

Data in staging tables will have date (partition key) values for sysdate to max 6 days in future. So, what's the best way to exchange the partitions from staging to actuals when the actuals are being accessed 24/7.

Pls advise on what partition type we can use for these scenarios or it is not possible to exchange in these cases....

Thanks.
Re: Partition [message #571884 is a reply to message #571685] Mon, 03 December 2012 09:28 Go to previous message
BlackSwan
Messages: 22500
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Previous Topic: invalid number
Next Topic: Partitioned Index-Organized table
Goto Forum:
  


Current Time: Fri Jul 25 11:42:53 CDT 2014

Total time taken to generate the page: 0.14649 seconds