Home » RDBMS Server » Server Administration » Taking partitions off line in tables (Oracle, 10.2.0.5, Solaris 10)
Taking partitions off line in tables [message #542121] Sat, 04 February 2012 09:53 Go to next message
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
Re: Taking partitions off line in tables [message #542122 is a reply to message #542121] Sat, 04 February 2012 10:02 Go to previous messageGo to next message
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/
Re: Taking partitions off line in tables [message #542124 is a reply to message #542122] Sat, 04 February 2012 10:51 Go to previous message
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.

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


Current Time: Thu Mar 28 09:37:33 CDT 2024