Skip to main content

Kill sessions which Locking Table 

Find the locking sessions from following 

select s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;

Get Session details from this sql  
example : if session id = 421select * from V$Session where sid = 421; 

Kill Sessions from following
alter system kill session '421,7951';



Temporary Tables

Get the out put from following sql and run the out put.

SELECT 'ALTER SYSTEM KILL SESSION '''||SID||','||SERIAL#||''';' FROM V$SESSION WHERE SID IN (
SELECT SID FROM V$LOCK
WHERE id1 = (SELECT object_id FROM all_objects WHERE owner ='CMDC' AND object_name ='TMP_EQUIPSTATUS'));









































Comments











Post a Comment




















Popular posts from this blog



















Insert script with multiple cursors and condition check




























  DECLARE  CURSOR C1  IS    SELECT ID FROM TABLE_NAME_1 WHERE COLUMN IN ('');    CURSOR C2      IS        SELECT ID FROM TABLE_NAME_2 WHERE COLUMN IN ('');                CURSOR C3 (CP_TABLE_01_ID NUMBER,CP_TABLE_02_ID NUMBER)          IS            SELECT COUNT(*) AS COUNT_UP            FROM TABLE_NAME_3            WHERE COLUMN_CONDITION_01=CP_TABLE_02_ID            AND COLUMN_CONDITION_02=CP_TABLE_01_ID;  COUNT_UP NUMBER;  BEGIN  FOR R1 IN C1  LOOP      FOR R2 IN C2      LOOP            OPEN C3(R1.ID,R2.ID);         FETCH C3 INTO COUNT_UP;         CLOSE C3;                 IF (COUNT_UP=0) THEN            INSERT            INTO TABL...




















Post a Comment









Read more




























SELECT DELETE STORED PROCEDURE 




























This stored procedure will help to select and delete records from table with passing variables.   create  or  replace  PROCEDURE  SP_DELETE_DATA (ID IN  INTEGER ,NAME IN  VARCAHR2) AS   CUR_01_F_COUNT NUMBER  ;   CURSOR  CUR_01 IS  SELECT  COUNT ( * ) FROM  TABLE_NAME CF WHERE  ID         =  ID AND  NAME = NAME;  BEGIN     OPEN  CUR_01; FETCH  CUR_01 INTO  CUR_01_F_COUNT; CLOSE  CUR_01; dbms_output.put_line( ' COUNT FOR  IS :' || CUR_01_F_COUNT);  IF  CUR_01_F_COUNT >  0  THEN   DELETE  FROM  TABLE_NAME WHERE  ID = ID AND  NAME = NAME;  dbms_output.put_line( 'REMOVED COUNT IS :' || sql % ROWCOUNT );  IF  SQL % ROWCOUNT > 0  THEN  COMMIT ; END  IF ; END  IF ;  




















Post a Comment









Read more




























Oracle Data Dump Import and Export




























Login to server :  sqlplus / as sysdba     exp SYSTEM/password FULL=y FILE=dba.dmp LOG=dba.log CONSISTENT=y   or   exp SYSTEM/password PARFILE=param.dat     where param.dat contains the following information  FILE=dba.dmp   GRANTS=y   FULL=y   ROWS=y   LOG=dba.log     To dump a single schema to disk.     exp / FILE=dump.dmp OWNER=production     Export Specific tables to disk     exp SYSTEM/password FILE=export.dmp TABLES=(user1.table,user2.table)     Export Data in one user      exp / FILE=export.dmp TABLES=(table1,table2)       Using imp:       To import the full database exported in the example above.   imp SYSTEM/password FULL=y FIlE=dba.dmp   To import just the dept and emp tables from the scott schema   imp SYSTEM/password FIlE=dba.dmp FROMUSER=scott TABLES=(dept,emp)   To import tables and change the owner      imp SYSTEM/password FROMUSER=blake TOUSER=scott FILE=blake.dmp TABLES=(unit,manager)   To import just the scott schema exported in t...




















Post a Comment









Read more