Skip to Main Content

Oracle Database Discussions

split partition while read only access

user4900730Apr 3 2014

We are considering the following potential partition strategy. A range-interval table, range on account id and then interval on month.  After the daily load the table is only accessed in read-only mode. It is a 24 hours by 7 days a week uptime requirement i.e. we cannot take any tables and/or tablespaces offline. We anticipate that after some time the account ranges may need to be re-balanced i.e the account ranges redefined. 

Q1.  If I want to re-balance a  few account range partitions, can the SPLIT and MERGE operations on the account id partitions (with the monthly sub partitions) be done while read-only access continues?

Q2. Does Oracle use the TEMP tablespace for the work segments during these operations?

Q3. If I want to completely redefine the entire table can I use DBMS_REDEFINE while providing read-only access to the table?

Q4. Which Oracle manual addresses the read-only aspect with regards to SPLIT and MERGE. I have looked but failed to find any explicit statement that these operations can be done while providing read-only access.

Please help with any specific Oracle version we need to be for any proposed solution


Post Details
Locked on May 1 2014
Added on Apr 3 2014