OraFAQ Forum: Server Administration » Taking partitions off line in tables

Home » RDBMS Server » Server Administration » Taking partitions off line in tables (Oracle, 10.2.0.5, Solaris 10)

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

Taking partitions off line in tables [message #542121]

Sat, 04 February 2012 09:53

z1hou1
Messages: 2
Registered: February 2012
Location: Houston, TX

Junior Member

Hi,
In an attempt to take older data off line and allow database refreshes to be faster, tablespaces associated with partitioned table data for a given time period was taken off line, leaving only tablespaces that relate to the current time period online. In effect, tablespaces related to 2010 and earlier were taken offline from a table.

There are two issues we face.
1. Without giving a filter on the partition key (the business date) to scan for data greater than the dates in the off lined tablespace partition, we get a ORA-376/ORA-1110 error (data file cannot be read at this time).

2. Materialized views using fast refresh or refresh on commit, will also not work because of the partitions being off line.

Queries directly querying the tables are manageable from an application point of view.

But the materialized views failing to aggregate is a bigger problem.

Any ideas on how we can manage this situation? I know that I can move the partitions to a different table in a tablespace to be taken off line. But if possible, we wanted to solve this without doing a move partition.

Regards,
z1hou1

Report message to a moderator

Re: Taking partitions off line in tables [message #542122 is a reply to message #542121]

Sat, 04 February 2012 10:02

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

so let us review the situation.
You have dug yourself a DEEP hole.
You realize that you need a ladder to climb out of the hole, but decide not to use it.
Now you ask for an alternative to the ladder.

Consider asking somebody to refill the hole so you can extricate yourself.

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: Taking partitions off line in tables [message #542124 is a reply to message #542122]

Sat, 04 February 2012 10:51

z1hou1
Messages: 2
Registered: February 2012
Location: Houston, TX

Junior Member

Thank you for your prompt response. I guess it is a resounding "No".

Not much of a ladder really, or a hole for that matter. The move partition was already in place in development and it does not take much time either. Was just exploring if there was an alternative outside of moving the partitions.

The materialized views, no problem there either, since they are grouped on the date as the leading column. We just recreated them so the effects of aggregation are the same.

Report message to a moderator

Previous Topic:	Oracle 11g Installation on RHEL-5
Next Topic:	Disable partition feature

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Apr 14 05:19:33 CDT 2025