正在事务中使用的Oracle重命名表或视图

Question

当您重命名未完成的事务中的查询所使用的表时，Oracle数据库中会发生什么情况？查询会失败吗？ 与视图相同吗？ 如果你删除了一个正在查询中使用的表，但是没有完成呢？

Answer 1

答案是肯定的，您可以在交易过程中重命名表或视图。它不会产生对正在进行的交易

下面的错误是一个脚本来测试这个

set serveroutput on; 

drop procedure test_rename_read 
/

drop sequence seq_test_rename_pk 
/

truncate table test_rename 
/

drop table test_rename cascade constraints 
/

truncate table test_rename_newname 
/

drop table test_rename_newname cascade constraints 
/

create table test_rename(
attribute_pk integer 
,text   varchar2(300) 
) 
/


alter table test_rename add constraint test_rename_pk primary key(attribute_pk) 
/

create sequence seq_test_rename_pk start with 1 increment by 1 minvalue 1 maxvalue 9999999 nocycle 
/

--insert dummy data 
begin 
    for i in 1..1000 loop 
    insert into test_rename 
    values ( seq_test_rename_pk.nextval -- no duplicates here 
       ,'Test '||i); 
    end loop; 
end; 
/


create or replace procedure test_rename_read (p_instance_no in number) 
is 

    cursor cu_test_rename is 
    select * from test_rename; 

    c_rec_test cu_test_rename%rowtype; 

begin 
    dbms_application_info.set_module('test_rename_read instance '||to_char(p_instance_no), null); 

    open cu_test_rename; 
    fetch cu_test_rename into c_rec_test; 

    DBMS_LOCK.sleep(seconds => 30); --sleep for 30 seconds to make sure that the other sessions 

    while cu_test_rename%found loop 
    -- read the cursor until the end 
    fetch cu_test_rename into c_rec_test; 
    end loop; 

exception 
    when others then 
    raise_application_error(-20000, 'Non expected error: '||sqlerrm); 
end; 
/


--- run the parallel test 
declare 
    l_max_concurrent_procs integer := 10; 
    l_instno    integer := 1; 
    l_jobno    number; 
begin 
    -- 10 CONCURRENT SESSIONS THAT READ THE TABLE 
    for i in 1..l_max_concurrent_procs loop 
     dbms_job.submit(job => l_jobno 
         ,what => 'declare ' 
           || ' v_text varchar2(200) := ''instance number '||to_char(i)||''';' 
           || 'begin ' 
           || ' test_rename_read(' 
           || ' p_instance_no => '||to_char(i) 
           || ');'   
           || 'end; ' 
         ,next_date => sysdate + (1/24/60/60)* 10 -- execute in 10 seconds from now 
         ,interval => null 
         ,no_parse => true 
         ,instance => l_instno 
         ,force  => true 
         ); 
    end loop; 
    -- RENAME THE TABLE 
    dbms_job.submit(job => l_jobno 
        ,what => 'begin '     
          || ' execute immediate ''rename test_rename to test_rename_newname''; ' 
          || '  /* test_rename_read */ ' -- to easily track all jobs 
          || 'end; ' 
       ,next_date => sysdate + (1/24/60/60)* 10 -- execute in 10 seconds from now 
       ,interval => null 
       ,no_parse => true 
       ,instance => l_instno 
       ,force  => true 
       ); 

commit; 
end; 
/

和一些有用的查询

-- check the jobs 
select * from dba_jobs where what like '% test_rename_read%' 
/

-- parallel sessions 
select * from v$session where module like 'test_rename_read%' 
/
-- waits for the parallel sessions 
select * from V$SESSION_WAIT where sid in (select sid from v$session where module like 'test_rename_read%') 
/

-- locked objects 
select l.sid, l.id1, o.OBJECT_NAME 
    from v$lock l 
    join dba_objects o 
    on (o.object_id = l.id1) 
where l.sid in (select sid from v$session where module like 'test_rename_read%') 
    and l.TYPE='TM' 
/




--- remove jobs in case of error 
DECLARE 
jobid NUMBER; 

CURSOR c1 
IS 
SELECT job 
FROM dba_jobs 
WHERE what like '% test_rename_read%'; 
BEGIN 
OPEN c1; 

LOOP 
FETCH c1 
INTO jobid; 

EXIT WHEN c1%NOTFOUND; 
DBMS_JOB.broken (jobid, TRUE); 
COMMIT; 
DBMS_JOB.remove (jobid); 
COMMIT; 
END LOOP; 

CLOSE c1; 
END; 
/