dellera@ORACLE9I> dellera@ORACLE9I> -- table t dellera@ORACLE9I> create table t ( 2 pk int, 3 data varchar2(20) 4 ); Table created. dellera@ORACLE9I> dellera@ORACLE9I> alter table t add constraint t_pk primary key(pk); Table altered. dellera@ORACLE9I> create unique index t_uq on t (pk, data); Index created. dellera@ORACLE9I> create index t_data_idx on t (data); Index created. dellera@ORACLE9I> dellera@ORACLE9I> insert into t (pk, data) 2 select rownum, 'data-' || rownum 3 from all_objects 4 where rownum <= 100; 100 rows created. dellera@ORACLE9I> dellera@ORACLE9I> create trigger t_trig 2 after insert or update or delete on t 3 for each row 4 begin 5 dbms_output.put_line (:new.pk); 6 end; 7 / Trigger created. dellera@ORACLE9I> dellera@ORACLE9I> create or replace procedure p 2 as 3 begin 4 for i in (select * from t) loop 5 dbms_output.put_line (i.pk); 6 end loop; 7 end p; 8 / Procedure created. dellera@ORACLE9I> show errors; No errors. dellera@ORACLE9I> dellera@ORACLE9I> -- table t_stage dellera@ORACLE9I> create table t_stage 2 partition by range (pk) ( 3 partition p_all_rows values less than (maxvalue) 4 ) 5 as 6 select * from t where 1=0; Table created. dellera@ORACLE9I> dellera@ORACLE9I> alter table t_stage 2 add constraint t_stage_pk primary key (pk) 3 using index local; Table altered. dellera@ORACLE9I> dellera@ORACLE9I> create unique index t_stage_uq on t_stage (pk, data) local; Index created. dellera@ORACLE9I> create index t_stage_data_idx on t_stage (data) local; Index created. dellera@ORACLE9I> dellera@ORACLE9I> insert /*+ append */ into t_stage 2 select -pk, data 3 from t; 100 rows created. dellera@ORACLE9I> commit; Commit complete. dellera@ORACLE9I> dellera@ORACLE9I> -- open and fetch a statement on t dellera@ORACLE9I> set echo off "------------------------------------------------------------------------------------" Now press a couple of times to fetch some rows from t, and keep the statement open. Then go in another session and issue: alter table t_stage exchange partition p_all_rows with table t including indexes without validation; Then, get back here, press until all the rows have been fetched; you will see that the old rows are fetched. "------------------------------------------------------------------------------------" dellera@ORACLE9I> set lines 100 dellera@ORACLE9I> set pages 13 dellera@ORACLE9I> set pause on dellera@ORACLE9I> select * from t; PK DATA ---------- ------------------------------------------------------------ 1 data-1 2 data-2 3 data-3 4 data-4 5 data-5 6 data-6 7 data-7 8 data-8 9 data-9 10 data-10 PK DATA ---------- ------------------------------------------------------------ 11 data-11 12 data-12 13 data-13 14 data-14 15 data-15 16 data-16 17 data-17 18 data-18 19 data-19 20 data-20 PK DATA ---------- ------------------------------------------------------------ 21 data-21 22 data-22 23 data-23 24 data-24 25 data-25 26 data-26 27 data-27 28 data-28 29 data-29 30 data-30 PK DATA ---------- ------------------------------------------------------------ 31 data-31 32 data-32 33 data-33 34 data-34 35 data-35 36 data-36 37 data-37 38 data-38 39 data-39 40 data-40 PK DATA ---------- ------------------------------------------------------------ 41 data-41 42 data-42 43 data-43 44 data-44 45 data-45 46 data-46 47 data-47 48 data-48 49 data-49 50 data-50 PK DATA ---------- ------------------------------------------------------------ 51 data-51 52 data-52 53 data-53 54 data-54 55 data-55 56 data-56 57 data-57 58 data-58 59 data-59 60 data-60 PK DATA ---------- ------------------------------------------------------------ 61 data-61 62 data-62 63 data-63 64 data-64 65 data-65 66 data-66 67 data-67 68 data-68 69 data-69 70 data-70 PK DATA ---------- ------------------------------------------------------------ 71 data-71 72 data-72 73 data-73 74 data-74 75 data-75 76 data-76 77 data-77 78 data-78 79 data-79 80 data-80 PK DATA ---------- ------------------------------------------------------------ 81 data-81 82 data-82 83 data-83 84 data-84 85 data-85 86 data-86 87 data-87 88 data-88 89 data-89 90 data-90 PK DATA ---------- ------------------------------------------------------------ 91 data-91 92 data-92 93 data-93 94 data-94 95 data-95 96 data-96 97 data-97 98 data-98 99 data-99 100 data-100 100 rows selected. dellera@ORACLE9I> set pause off dellera@ORACLE9I> set pages 9999 dellera@ORACLE9I> dellera@ORACLE9I> -- object defined on/dependent from t are still VALID dellera@ORACLE9I> select object_name, status from user_objects where object_name in (select name from user_dependencies where referenced_name='T'); OBJECT_NAME STATUS ------------------------------ --------------------- P VALID T_TRIG VALID dellera@ORACLE9I> dellera@ORACLE9I> spool off