| 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
|
|
|
|
|
|
| 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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
