我写了一个shell脚本创建表,脚本如下,中途我kill掉这个进程,然后select * from b_manager_income没有返回,估计这个表创建不成功,被锁住了.由于在后台,我找不到这个sessionid,所以也没有办法杀掉这个session,我在all_objects表中查到了b_manager_income的object_id,用object_id到v$lock_object中查找没有记录,我该怎么办,我怎样才能根据表名b_manager_income找到对应的sessionid,然后kill掉这个session.
sqlplus user/pwd@db<<EOF
set serverout on;
declare
gs_ErrMsg varchar(100);
sql_text varchar2(2000);
begin
begin
sql_text := 'drop table b_manager_income';
execute immediate sql_text;
exception
when others then
gs_ErrMsg := substr(sqlerrm, 1, 100);
insert into CONVER_ERROR
(state_date , error_msg, proc_desc)
values
(sysdate, gs_ErrMsg, 'fail to drop table b_manager_income');
commit;
end;
begin
sql_text := 'create table b_manager_income nologging as
SELECT a.stat_cycle_id,
a.area_id,
a.devel_manager_id devel_staff_id,
nvl(sum(charge), 0)/100 val
FROM dss_acct_item_serv_stat a
WHERE not exists (select ''X'' from cust_msg cm
where cm.parent_cust_id is null
and cm.custom_type_id in(1001,1002,1003,1004,1005,1006,1007,1,2005,2006,2007)
start with cm.cust_id=a.cust_id
connect by prior cm.parent_cust_id=cm.cust_id)
and a.data_source_id <> 16
and a.serv_type_id not in(11010200,11010201,11010307,14010401,
14010502,14019919,11010202)
and a.devel_manager_id <> -1
and a.completed_date >= 200801
and a.completed_date <= 200812
and a.area_id in (select area_id from area_tm ai where ai.state = ''A''
start with ai.area_id in(select area_id from cl_staff_info)
connect by prior ai.area_id = ai.sum_area_id)
and a.latn_id = 72
group by a.stat_cycle_id,
a.devel_manager_id,
a.area_id,
a.devel_manager_id';
execute immediate sql_text;
exception
when others then
gs_ErrMsg := substr(sqlerrm, 1, 100);
insert into CONVER_ERROR
(state_date , error_msg, proc_desc)
values
(sysdate, gs_ErrMsg, 'fail to create table b_manager_income ');
commit;
end;
end;
/
exit;
EOF
sqlplus user/pwd@db<<EOF
set serverout on;
declare
gs_ErrMsg varchar(100);
sql_text varchar2(2000);
begin
begin
sql_text := 'drop table b_manager_income';
execute immediate sql_text;
exception
when others then
gs_ErrMsg := substr(sqlerrm, 1, 100);
insert into CONVER_ERROR
(state_date , error_msg, proc_desc)
values
(sysdate, gs_ErrMsg, 'fail to drop table b_manager_income');
commit;
end;
begin
sql_text := 'create table b_manager_income nologging as
SELECT a.stat_cycle_id,
a.area_id,
a.devel_manager_id devel_staff_id,
nvl(sum(charge), 0)/100 val
FROM dss_acct_item_serv_stat a
WHERE not exists (select ''X'' from cust_msg cm
where cm.parent_cust_id is null
and cm.custom_type_id in(1001,1002,1003,1004,1005,1006,1007,1,2005,2006,2007)
start with cm.cust_id=a.cust_id
connect by prior cm.parent_cust_id=cm.cust_id)
and a.data_source_id <> 16
and a.serv_type_id not in(11010200,11010201,11010307,14010401,
14010502,14019919,11010202)
and a.devel_manager_id <> -1
and a.completed_date >= 200801
and a.completed_date <= 200812
and a.area_id in (select area_id from area_tm ai where ai.state = ''A''
start with ai.area_id in(select area_id from cl_staff_info)
connect by prior ai.area_id = ai.sum_area_id)
and a.latn_id = 72
group by a.stat_cycle_id,
a.devel_manager_id,
a.area_id,
a.devel_manager_id';
execute immediate sql_text;
exception
when others then
gs_ErrMsg := substr(sqlerrm, 1, 100);
insert into CONVER_ERROR
(state_date , error_msg, proc_desc)
values
(sysdate, gs_ErrMsg, 'fail to create table b_manager_income ');
commit;
end;
end;
/
exit;
EOF
解决方案 »
- oracle 10g 安装后expdb ,impdb命令在cmd命令行不能使用! 请大侠指点迷津
- conn sys/密码 as sysdba 总是提示已连接到空闲进程 在线等
- 如何创建触发器
- 关于一个省市查询的问题~~!!请教大家
- 关于数据库表空间的一个怪问题
- 初学oracle前提必须懂得那些最基本的东西?
- java连接oracle的问题,请大家帮忙
- 谁有关于ERwin使用方面的文档或电子书籍?
- d6+ado+oracle 我采用的是ado直接连接数据源
- 怎样得到一个表的字段定义?
- 有关to_number的问题,恭候牛人来访
- oracle长时间使用odbc连接不上!但oem和sqlplus却能正常使用!郁闷中!
from v$session a,v$sqlarea b
where a.sql_hash_value=b.hash_value and
first_load_time ... 你执行的时间段
或者是 last_load_time ... 你执行的时间段
从里面找找。
再kill