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
Sincerely