0

I have a plsql code in session1 that makes redefinishion using dbms_redefinition.

Another session that running a select query on the original table just failes when the finish procedure of the redefinition invoke.. with exception of no-longer-exists.

Isn't it the goal of this package to allow online-redefinition during someone still able to access the table???

user2671057
  • 1,411
  • 2
  • 25
  • 43
  • AFAICT there's a moment between the start of the redefinition process and the moment when the process has created a snapshot when the table cannot be accessed. So a transaction running during this period will fail. Transactions (including updates) should continue accessing the old tale most of the time while the new table is being built. Look at [these tips](http://www.dba-oracle.com/t_dbms_redefinition.htm). – 9000 Mar 16 '17 at 18:19
  • Can you post your script to do the `DBMS_REDEFINITION`? Don Burleson's wisdom aside, I don't think you should get ORA-08103 unless you drop the interim table too soon after the call to `FINISH_REDEF_TABLE` ends. – Matthew McPeak Mar 16 '17 at 19:00
  • -- session 1 declare v_target varchar2(40) := 't2_load'; v_temp varchar2(40) := 't1'; v_user varchar2(20) := 'dp99712a'; begin dbms_redefinition.CAN_REDEF_TABLE(v_user, v_target,dbms_redefinition.cons_use_rowid); DBMS_REDEFINITION.START_REDEF_TABLE (v_user, v_target, v_temp, options_flag => dbms_redefinition.cons_use_rowid); DBMS_REDEFINITION.FINISH_REDEF_TABLE (v_user, v_target, v_temp); end; -- session 2 declare v_cnt number; begin loop SELECT 1 INTO V_CNT FROM T1 where rownum = 1; end loop; end; "no longer exists" – user2671057 Mar 19 '17 at 07:15
  • 9000, according to those tips, the only moment of problem of access is only a moment of exclusive mode - it's mean select-operations are ok, isn't it? – user2671057 Mar 19 '17 at 07:50

0 Answers0