不知道大家遇没遇到过往一个表格里面插入数据却插不进去的情况。我用语句insert into mid select XX 往mid表插数据,显示mid表为空。后来我新建一个和mid表同样结构的新表aa,同时用insert into aa select XX往aa插入数据,却显示aa表有大量数据。谁知道这种情况说下吧。。谢谢。
insert into mid select XX from table --加上from table commit;--如果还不行,看看mid表中是否有触发器,回滚了
本人已经测试过你的建表语句以及插入没问题肯定是有触发器,你的插入被触发器回滚了 SQL> insert into DW_A_USER_GPRS_MID 2 select '201006','wkc168','168','a','a','a','m','a','a','aa','10' from dual 3 /已创建 1 行。SQL> commit;提交完成。SQL> select * from DW_A_USER_GPRS_MID 2 /ACCT_M USER_ID SERV_NUMBER CHANNEL_1_ID C CHANNEL_2_ID C ------ ---------------- ---------------- ---------------- - ---------------- - AGE GEND PRIO CUST ---- ---- ---- ---- 201006 wkc168 168 a a a m a a aa 10
这个没法用valus啊。。这个values的值是通过select XX from table得到的
查看表上是否有触发器,以及你怎么个插入的插入语句 select trigger_name,table_name from all_triggers where table_name='DW_A_USER_GPRS_MID'
你自己隨便寫一個 insert into DW_A_USER_GPRS_MID values ('a','b','c','d','e','f','g','h','i','j','k');
好像也不能插入,插入一条语句执行了1分钟多了。没 执行完,而且我对于mid表进行清表语句也显示错误resource busy and acquire with nowait specified
没呢,。。纠结中,往mid表插数据插不了,清表也不让请,出错误,resource busy and acquire with nowait specified,我以为是限制我不让我清表,但是我却还能清空别的表格
是不是有其他session鎖住了該表?
怎么看那个session锁住这个表了?select * from v$session,这个语句我显示表或试图不存在
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;删除 alter system kill session 't2.sid,t2.serial#,';
你有没有commit?
先mid表create table DW_A_USER_GPRS_MID
(
ACCT_MONTH VARCHAR2(6),
USER_ID VARCHAR2(16),
SERV_NUMBER VARCHAR2(16),
CHANNEL_1_ID VARCHAR2(16),
CHANNEL_1_TP VARCHAR2(1),
CHANNEL_2_ID VARCHAR2(16),
CHANNEL_2_TP VARCHAR2(1),
AGE VARCHAR2(4),
GENDER_ID VARCHAR2(4),
PRIORITY VARCHAR2(4),
CUST_RULE_COUNT VARCHAR2(4)
)
partition by list (ACCT_MONTH)
(
partition PART201003 values ('201003')
tablespace TBS_USER_01
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 2M
next 2M
minextents 1
maxextents unlimited
pctincrease 0
),
partition PART201004 values ('201004')
tablespace TBS_USER_01
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 2M
next 2M
minextents 1
maxextents unlimited
pctincrease 0
),
partition PART201005 values ('201005')
tablespace TBS_USER_01
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 2M
next 2M
minextents 1
maxextents unlimited
pctincrease 0
),
partition PART201006 values ('201006')
tablespace TBS_USER_01
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 2M
next 2M
minextents 1
maxextents unlimited
pctincrease 0
),
partition PART201007 values ('201007')
tablespace TBS_USER_01
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 2M
next 2M
minextents 1
maxextents unlimited
pctincrease 0
),
partition PART201008 values ('201008')
tablespace TBS_USER_01
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 2M
next 2M
minextents 1
maxextents unlimited
pctincrease 0
)
);
然后新教表
create table SAGA123
(
ACCT_MONTH VARCHAR2(6),
USER_ID VARCHAR2(16),
SERV_NUMBER VARCHAR2(16),
CHANNEL_1_ID VARCHAR2(16),
CHANNEL_1_TP VARCHAR2(1),
CHANNEL_2_ID VARCHAR2(16),
CHANNEL_2_TP VARCHAR2(1),
AGE VARCHAR2(4),
GENDER_ID VARCHAR2(4),
PRIORITY VARCHAR2(4),
CUST_RULE_COUNT VARCHAR2(4)
)
partition by list (ACCT_MONTH)
(
partition PART201003 values ('201003')
tablespace TBS_USER_01
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 2M
next 2M
minextents 1
maxextents unlimited
pctincrease 0
),
partition PART201004 values ('201004')
tablespace TBS_USER_01
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 2M
next 2M
minextents 1
maxextents unlimited
pctincrease 0
),
partition PART201005 values ('201005')
tablespace TBS_USER_01
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 2M
next 2M
minextents 1
maxextents unlimited
pctincrease 0
),
partition PART201006 values ('201006')
tablespace TBS_USER_01
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 2M
next 2M
minextents 1
maxextents unlimited
pctincrease 0
),
partition PART201007 values ('201007')
tablespace TBS_USER_01
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 2M
next 2M
minextents 1
maxextents unlimited
pctincrease 0
),
partition PART201008 values ('201008')
tablespace TBS_USER_01
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 2M
next 2M
minextents 1
maxextents unlimited
pctincrease 0
)
);
commit;--如果还不行,看看mid表中是否有触发器,回滚了
本人已经测试过你的建表语句以及插入没问题肯定是有触发器,你的插入被触发器回滚了
SQL> insert into DW_A_USER_GPRS_MID
2 select '201006','wkc168','168','a','a','a','m','a','a','aa','10' from dual
3 /已创建 1 行。SQL> commit;提交完成。SQL> select * from DW_A_USER_GPRS_MID
2 /ACCT_M USER_ID SERV_NUMBER CHANNEL_1_ID C CHANNEL_2_ID C
------ ---------------- ---------------- ---------------- - ---------------- -
AGE GEND PRIO CUST
---- ---- ---- ----
201006 wkc168 168 a a a m
a a aa 10
select trigger_name,table_name from all_triggers where table_name='DW_A_USER_GPRS_MID'
insert into DW_A_USER_GPRS_MID
values
('a','b','c','d','e','f','g','h','i','j','k');
好像也不能插入,插入一条语句执行了1分钟多了。没 执行完,而且我对于mid表进行清表语句也显示错误resource busy and acquire with nowait specified
查出锁
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;删除
alter system kill session 't2.sid,t2.serial#,';