sql 临时表 和索引的关系 临时表建索引

在编写一个test存储过程中出现一个错误报告:ORA-14452: 试图创建, 变更或删除正在使用的临时表中的索引,代码如下

create or replace PROCEDURE TMP_TRANSCRIPT AS 
  str_sql varchar2(500);
  v_flag number:=0; --标识
begin

  --判断临时表是否存在
  SELECT COUNT(*) into v_flag FROM User_Tables WHERE table_name = 'TEMP_TABLE';
  
  if v_flag=0  then
  -- 创建临时表
  str_sql := 'create global temporary table temp_table (   
       name varchar2(20),
       yuwen number,
       shuxue number,
       yingyu number
    ) on commit preserve rows';
  execute immediate str_sql; 

  -- 使用临时表
  str_sql := 'insert into temp_table select name,
              sum(case when kecheng='||'''语文'''||' then fenshu else 0 end) as "语文",
              sum(case when kecheng='||'''数学'''||' then fenshu else 0 end) as "数学",
              sum(case when kecheng='||'''英语'''||' then fenshu else 0 end) as "英语"
              from TRANSCRIPT group by name';
  execute immediate str_sql;
  
  --提交事务
  --commit;
  
  else 
    dbms_output.put_line('ok');
    -- 删除临时表
    str_sql := 'drop table temp_table';
    execute immediate str_sql;
  --提交事务
  --commit;
   END IF;
 
END;

在第二遍运行的时候提示在drop table时出错

解决方法:

1.  truncate table temp_table  

2.  如果上面这种方法没用可以尝试  

     a.先从 dba_objects / user_objects中查询到该表的object_id:

        select object_id from dba_objects where object_name=upper('temp_table');

     b.根据查到的object_id知道使用该表的session：
        select * from v$lock where id1=&object_id;
     c.在从v$session视图中查到该session的SID和SERIAL#：
        select * from v$session where sid=3;
     d.杀掉这些进程:
        alter system kill session ‘SID,SERIAL#’;

但在使用第二种方法时出现了一个新问题:SQL 错误: ORA-00027: 无法终止当前会话

 

直接重启sqldeveloper就能解决

 

问题分析:起初分析是因为在存储过程insert结束后未执行commit导致事务未提交进而无法终止当前会话,所以在第二遍执行的时候无法进行drop操作

但是在insert后面添加commit之后发现依旧会出现ORA-14452: 试图创建, 变更或删除正在使用的临时表中的索引

这是什么原因呢?经过一番查找发现在drop后面添加commit就不会再出错误提示了

个人猜测会不会是因为drop有隐式提交,第一次执行完后虽然drop成功但是还未提交成功导致

所以在存储过程的最后临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定;

至于为什么truncate又可以呢,这就涉及到了truncate和drop的区别

truncate table(截断表) 是清空一个表，是一个DDL语言，效率高，它与delete有如下区别。
delete是DML语言;
delete时会触发与表相关的触发器，而truncate不会;

在Oracle中如果删除了表中的某一条数据，还可以通过回滚操作(rollback)进行回滚，假如想清空一张表的数据，但是又不想使其能进行回滚操作，就可以立刻释放资源，这时就需要使用截断表了。它的主要功能就是彻底删除数据，使其不能进行回滚。这里我打个比方大家就立刻能明了它的作用。大家众所周知，当我们在自己的PC（person computer）上删除某一个文件，它并没有彻底删除而是进入了回收站，你要在回收站中再将其删除才算彻底清除。截断表就相当于直接将数据从pc上删除，而不会放入回收站。