记一次Oracle数据故障排除过程

前天在Oracle生产环境中，自己的存储过程运行时间超过1小时，怀疑是其他job运行时间过长推迟了自己job运行时间，遂重新跑job，发现同测试环境的确不同，运行了25分钟。

之后准备在测试环境中制造同数量级的数据进行分析，写了大概如下的存储过程，

create or replace PROCEDURE PERFORMANCE_TEST AS 

v_date date;
v_start_date date;
v_end_date date;
v_start_date_str varchar2(10) := '2017-01-31';
v_end_date_str varchar2(10) := '2017-07-31';
v_date_str varchar2(10);

BEGIN
  v_start_date := to_date(v_start_date_str, 'yyyy-mm-dd');
  v_end_date := to_date(v_end_date_str, 'yyyy-mm-dd');
  v_date := v_start_date;
  while v_date < v_end_date loop
    v_date_str := to_char(v_date, 'yyyy-mm-dd');
    insert into datacore.df_customer_static_report
    (
      data_date,
      cty_code,
      party_id,
      party_name,
      ho_domicile_cty,
      rm_code,
      rm_name,
      business_division
    )(
      select 
        v_date_str,
        cty_code,
        party_id,
        party_name,
        ho_domicile_cty,
        rm_code,
        rm_name,
        business_division
      from datacore.df_customer_static_report
      where data_date = v_end_date_str
    );
    commit;
  end loop;
  
END PERFORMANCE_TEST;

犯了个致命错误，丢了v_date := v_date + 1; 存储过程陷入无限循环！在过了1个多小时后，意识到不对劲，遂查询了数据量，发现2017-01-31的数据量竟然达到了千万级。。。赶紧停止运行找原因，才发现无限循环插入数据。

剩下就是怎么删掉这些数据，毕竟千万级的数据占据存储空间太大了。简单的删除肯定不起作用，遂尝试分批删除，先试着删除1w条，结果运行很长时间后还是没有结束。这个时候，感觉之前那个无限循环应该还没有结束，在后台还在运行。因为其他事情捣乱，没来得及修正这个问题。第二天来，再次查询，发现数据量达到了快5千万条，欲哭无泪啊！赶紧删数据，分批次，1百万条的删，（这次加上累加条件了）

create or replace PROCEDURE DELETE_TEMP AS 

v_number number := 1;
v_number_end number := 50;

BEGIN

  while v_number <= v_number_end loop
    delete from datacore.df_customer_static_report nologging
    where data_date = '2017-01-31'
    and rownum < 1000000;
    commit;
    v_number := v_number + 1;
    dbms_output.put_line(v_number || ' end');
  end loop;
  
END DELETE_TEMP;

本想用TRUNCATE把所有数据都删掉，但是我这里只需要删掉表中‘2017-01-31’的数据，而且只是把千万条降低到万条。查了delete语句的优化，发现加上nologging会更快些（数据不做恢复）。

运行了大概1个小时后，感觉差不多了，遂手动终止了delete的运行。再次查询，‘2017-01-31’的数据降到9千多条。窃喜~

不过又想起昨天想到的“是否无限循环还在后台运行”？过了10几分钟后查询，发现数据又多了，欲哭无泪。。

怎么让这个讨厌的无限循环终止呢？因为使用的账号没有dba权限，所以想通过更改表的结构，让包含无限循环的存储过程异常终止。但估计不可行，因为数据库表一直被占用了。抱着试一试的想法，执行以下sql，

alter table datacore.df_customer_static_report
drop column rds_spread_code

报错“资源正忙, 但指定以 NOWAIT 方式获取资源, 或者超时失效；resource busy and acquire with NOWAIT specified”。

搜索后，果真有解决方案 - here，但还是需要dba权限（厚脸皮要吧）。

1. 用dba权限的用户查看数据库都有哪些锁

SELECT T2.USERNAME,T2.SID,T2.SERIAL#,T2.LOGON_TIME
FROM V$LOCKED_OBJECT T1,V$SESSION T2
WHERE T1.SESSION_ID=T2.SID ORDER BY T2.LOGON_TIME;

2. 根据sid查看具体的sql语句，如果sql不重要，可以kill

SELECT SQL_TEXT FROM V$SESSION A,V$SQLTEXT_WITH_NEWLINES B
WHERE DECODE(A.SQL_HASH_VALUE, 0, PREV_HASH_VALUE, SQL_HASH_VALUE)=B.HASH_VALUE
AND A.SID=&SID ORDER BY PIECE;

3. kill该事务

ALTER SYSTEM KILL SESSION '590,20839';

4. 再次查看数据库锁，发现锁消失。再次查询表数据，不再增加。

教训：以后写存储过程中的循环，千万注意条件的累加！

 

--------------------------------------------------------------------------------------------------------------------------------

-- index(索引)
select * from all_indexes;
select * from user_indexes;

select * from all_ind_columns;
select * from user_ind_columns;

select t.*,i.index_type
from user_ind_columns t,user_indexes i
where t.index_name = i.index_name
and t.table_name = i.table_name and t.table_name = 'DM_RR_GQ_FIN_FEDS';

select t.*,i.index_type
from all_ind_columns t,all_indexes i
where t.index_name = i.index_name
and t.table_name = i.table_name
and owner = 'FISP'
and t.table_name = 'FIS_OUT_FLEXI';

-- tables(表)
select * from user_tab_columns;
select * from all_tab_columns;

select * from user_col_comments;
select * from all_col_comments;

select * from user_tables;
select * from all_tables;