OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Partition (11g)

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

Partition [message #571479]

Mon, 26 November 2012 07:34

thiagesh
Messages: 73
Registered: September 2006
Location: singapore

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.

Report message to a moderator

Re: Partition [message #571485 is a reply to message #571479]

Mon, 26 November 2012 08:24

Solomon Yakobson
Messages: 1407
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.

Report message to a moderator

Re: Partition [message #571488 is a reply to message #571485]

Mon, 26 November 2012 08:31

thiagesh
Messages: 73
Registered: September 2006
Location: singapore

Member

Hi Solomon,

Many Thanks for the quick solution.

Report message to a moderator

Re: Partition [message #571685 is a reply to message #571488]

Thu, 29 November 2012 02:35

thiagesh
Messages: 73
Registered: September 2006
Location: singapore

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.

Report message to a moderator

Re: Partition [message #571884 is a reply to message #571685]

Mon, 03 December 2012 09:28

BlackSwan
Messages: 20109
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/

Report message to a moderator

Previous Topic:	invalid number
Next Topic:	Partitioned Index-Organized table

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 25 12:46:26 CDT 2013

Total time taken to generate the page: 0.33115 seconds