0

I am inserting a row in the Oracle database table. The query looks like as below -

INSERT INTO <schema_name>.<Table_name> (SID, LABEL, DESCR, GTI_EXPTYP, GTI_MODUS, GTI_NAME) VALUES (22, '21.01.2019, 08:10:10', 'PDJHF q_sz_nlp (Update)', null, null, null);

I have executed the above query 3/4 days before. And the result of the query execution was -

1 row created.

But I do not see the newly added entry in the table. I thought, I need to commit. And now, when I am trying to execute the same query again and to commit, the query execution is not proceeding (stucks/hangs the terminal). Could someone please help me to understand what is the issue here?

I am new to database handling. I am using sqlplus tool.

Thanks in advance!!

1

If an insert into the table is hanging using the same primary key value then it suggests there is an open transaction which has inserted this value but not committed yet.

You can confirm this by checking for blocked sessions from a sufficiently privileged user:

select s.machine, s.sid, s.serial#, s.event, s.state, s.blocking_session, sql.sql_text
from   gv$session s
left join   gv$sql sql
  on   s.sql_id           = sql.sql_id
 and   s.sql_child_number = sql.child_number
 and   s.inst_id      = sql.inst_id
where  (s.blocking_session is not null
or     (s.inst_id, s.sid) in (select bs.blocking_instance, bs.blocking_session from gv$session bs where bs.blocking_session is not null)
       )

Locate your old open SQL*Plus session and run commit or rollback. Or you can use the output from the query to figure out which session you can kill (which will force a rollback) as a suitably privileged user.

1
  • Thank you!! It worked for me.
    – dEvOps
    Jul 11 at 19:42
1

After running the below query to find out blocked sessions -

select s.machine, s.sid, s.serial#, s.event, s.state, s.blocking_session, sql.sql_text
from   gv$session s
left join   gv$sql sql
  on   s.sql_id           = sql.sql_id
 and   s.sql_child_number = sql.child_number
 and   s.inst_id      = sql.inst_id
where  (s.blocking_session is not null
or     (s.inst_id, s.sid) in (select bs.blocking_instance, bs.blocking_session from gv$session bs where bs.blocking_session is not null)
       )

And then killing a session for an open transactions by executing the below query worked.

ALTER SYSTEM KILL SESSION 'sid,serial#';

Thank you!!

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.