CODEWITHSUNDEEP
sqloracle-database
venkata krishna
venkata krishna

Reputation: 1

SQL taking so long time to update the table

I am just updating one of the SQL table by using a simple update query (just 5 records), it's running so long time and not results.

Please help me out.

UPDATE ETL_Phone_Number 
SET 
status_flag = 'N' 
WHERE 
status_flag = 'P';

Table create Query:

create table ETL_Phone_Number_Updated
(
CRM_ID  varchar2 (250),
Addr_Hash_Key   varchar2(250),
Phone_Number varchar2 (250),
status varchar2 (250),
status_flag varchar2 (250)
)

Upvotes: 0

Views: 3559

Answers (3)

Jaldhi Mehta
Jaldhi Mehta

Reputation: 721

It may have blocked the labels, so you need to free up some spaces. You can use this basic syntax for killing a session:

ALTER SYSTEM KILL SESSION 'sid,serial#';

Kindly also refer this link if it doesn't work: https://oracle-base.com/articles/misc/killing-oracle-sessions

Upvotes: 1

ekochergin
ekochergin

Reputation: 4129

the most probable thing causing such a behaviour could be an another session trying to alter the data within the table without committing it.

In order to find it out who has blocked the object ask the database for the following.

select s.sid, s.serial# 
  from dba_locks l,
       all_objects o,
       v$session s
 where l.lock_id1 = o.object_id
   and l.session_id = s.SID
   and o.object_name = upper('ETL_Phone_Number');

Then if there were rows returned, perform the following command alter system kill session 'sid, serial#' immediate;

The last statement will kill the sessions and will release the table for update

Upvotes: 0

Muhammad Tahir
Muhammad Tahir

Reputation: 325

Please try to kill all your sessions related to this table and try again.

Upvotes: 0

Related Questions