以下是建表及部分数据插入语句,都是从plc/sql里导出的 prompt PL/SQL Developer import file prompt Created on 星期六 2009年1月3日 by Administrator set feedback off set define off prompt Creating AB17... create table AB17 ( OAE001 NUMBER(18) not null, AAE151 VARCHAR2(3) not null, AAB200 VARCHAR2(20) not null, AAB240 NUMBER(12,2) not null, BAB141 NUMBER(12,2), OAE300 NUMBER(18), OAE301 NUMBER(18), AAE140 VARCHAR2(3) ) tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); comment on table AB17 is '单位缴费待转基金信息'; comment on column AB17.OAE001 is '唯一索引'; comment on column AB17.AAE151 is '缴费主体'; comment on column AB17.AAB200 is '主体编号'; comment on column AB17.AAB240 is '待转金额'; comment on column AB17.BAB141 is '历年待转金额'; comment on column AB17.OAE300 is '操作序号'; comment on column AB17.OAE301 is '前次操作序号'; comment on column AB17.AAE140 is '险种类型'; alter table AB17 add constraint PK_AB17 primary key (OAE001) using index tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); create unique index IDX_AB17_AAB200 on AB17 (AAB200, AAE151, AAE140) tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited );prompt Loading AB17... insert into AB17 (OAE001, AAE151, AAB200, AAB240, BAB141, OAE300, OAE301, AAE140) values (58670989, '2', '42900410002543', 0, 0, 190000000007132266, 100000002593158, '51'); insert into AB17 (OAE001, AAE151, AAB200, AAB240, BAB141, OAE300, OAE301, AAE140) values (95016639, '2', '42900410002517', 0, 0, 190000000008515072, 190000000006481679, '31'); insert into AB17 (OAE001, AAE151, AAB200, AAB240, BAB141, OAE300, OAE301, AAE140) values (95016978, '2', '42900410002914', 0, 0, 190000000009338430, 190000000009190197, '31'); insert into AB17 (OAE001, AAE151, AAB200, AAB240, BAB141, OAE300, OAE301, AAE140) values (95018123, '2', '42900410002902', 24.8, 0, 190000000006482351, null, '51'); commit; prompt 1696 records loaded set feedback on set define on prompt Done.
comment on column AB17.OAE300 is '操作序号'; comment on column AB17.OAE301 is '前次操作序号'; 那你是不是要在这两个字段进行查询呢? 序号的话,其实可以定义成varchar2试下; 或者你查的时候是不是系统资源占用的很厉害
非常大的可能是你的那几条特定数据记录被锁了~ select a.sid,a.serial#,a.username,a.program, c.owner, c.object_name ,a.MACHINE,a.TERMINAL,a.PADDR,a.STATUS,a.SQL_ADDRESS from v$session a, v$locked_object b, all_objects c where a.sid=b.session_id and c.object_id = b.object_id 用这个查查看~
这2条SQL你应该看的更明白些SELECT /*+ rule */ lpad(' ',decode(l.xidusn ,0,3,0))||l.oracle_username User_name, o.owner,o.object_name,o.object_type,s.sid,s.serial# FROM v$locked_object l,dba_objects o,v$session s WHERE l.object_id=o.object_id AND l.session_id=s.sid SELECT /*+ rule */ s.username, decode(l.type,'TM','TABLE LOCK', 'TX','ROW LOCK', NULL) LOCK_LEVEL, o.owner,o.object_name,o.object_type, s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser FROM v$session s,v$lock l,dba_objects o WHERE l.sid = s.sid AND l.id1 = o.object_id(+) AND s.username is NOT NULL
prompt PL/SQL Developer import file
prompt Created on 星期六 2009年1月3日 by Administrator
set feedback off
set define off
prompt Creating AB17...
create table AB17
(
OAE001 NUMBER(18) not null,
AAE151 VARCHAR2(3) not null,
AAB200 VARCHAR2(20) not null,
AAB240 NUMBER(12,2) not null,
BAB141 NUMBER(12,2),
OAE300 NUMBER(18),
OAE301 NUMBER(18),
AAE140 VARCHAR2(3)
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
comment on table AB17
is '单位缴费待转基金信息';
comment on column AB17.OAE001
is '唯一索引';
comment on column AB17.AAE151
is '缴费主体';
comment on column AB17.AAB200
is '主体编号';
comment on column AB17.AAB240
is '待转金额';
comment on column AB17.BAB141
is '历年待转金额';
comment on column AB17.OAE300
is '操作序号';
comment on column AB17.OAE301
is '前次操作序号';
comment on column AB17.AAE140
is '险种类型';
alter table AB17
add constraint PK_AB17 primary key (OAE001)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
create unique index IDX_AB17_AAB200 on AB17 (AAB200, AAE151, AAE140)
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);prompt Loading AB17...
insert into AB17 (OAE001, AAE151, AAB200, AAB240, BAB141, OAE300, OAE301, AAE140)
values (58670989, '2', '42900410002543', 0, 0, 190000000007132266, 100000002593158, '51');
insert into AB17 (OAE001, AAE151, AAB200, AAB240, BAB141, OAE300, OAE301, AAE140)
values (95016639, '2', '42900410002517', 0, 0, 190000000008515072, 190000000006481679, '31');
insert into AB17 (OAE001, AAE151, AAB200, AAB240, BAB141, OAE300, OAE301, AAE140)
values (95016978, '2', '42900410002914', 0, 0, 190000000009338430, 190000000009190197, '31');
insert into AB17 (OAE001, AAE151, AAB200, AAB240, BAB141, OAE300, OAE301, AAE140)
values (95018123, '2', '42900410002902', 24.8, 0, 190000000006482351, null, '51');
commit;
prompt 1696 records loaded
set feedback on
set define on
prompt Done.
is '操作序号';
comment on column AB17.OAE301
is '前次操作序号';
那你是不是要在这两个字段进行查询呢?
序号的话,其实可以定义成varchar2试下;
或者你查的时候是不是系统资源占用的很厉害
c.owner, c.object_name ,a.MACHINE,a.TERMINAL,a.PADDR,a.STATUS,a.SQL_ADDRESS
from v$session a, v$locked_object b, all_objects c
where a.sid=b.session_id and
c.object_id = b.object_id 用这个查查看~
我猜前台有程序在对这个表里的数据进行操作吧?
锁表一般是拙劣的事务设计引起的~
SID表示的是引起锁表会话的ID,其他字段通过英文字段名能很容易的断定出来~
既然问题找到了,那就杀锁吧alter system kill session 'sid,serial#' immediate
SID 和serial#就在你刚查出来的数据里
o.owner,o.object_name,o.object_type,s.sid,s.serial#
FROM v$locked_object l,dba_objects o,v$session s
WHERE l.object_id=o.object_id
AND l.session_id=s.sid
SELECT /*+ rule */ s.username,
decode(l.type,'TM','TABLE LOCK',
'TX','ROW LOCK',
NULL) LOCK_LEVEL,
o.owner,o.object_name,o.object_type,
s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser
FROM v$session s,v$lock l,dba_objects o
WHERE l.sid = s.sid
AND l.id1 = o.object_id(+)
AND s.username is NOT NULL
http://guyuanli.itpub.net/post/37743/468226
http://hi.baidu.com/cryspark/blog/item/1406b6d3e1da19043bf3cfd4.html