Bloqueo y desbloqueo de tablas en Oracle

Muchas veces estamos ejecutando un proceso que tarda mucho y no sabemos qué sentencia está ejecutando en Oracle.

En este blog post veremos cómo ver qué sentencia nos está produciendo el bloqueo, podremos matar la sesión, ver que SQL es el que está produciendo ese bloqueo y luego con la sentencia que recogeremos estudiarla para poder crear los posibles índices que necesitemos para que esa Select, Delete, Update o Insert nos tarde menos.

1.- Ver qué es lo que produce el bloqueo

2.- Ver qué SQL es el que está produciendo el bloqueo

3.- Matar sesión

Ver qué está bloqueado:

Para ver cual es el/los procesos que nos están bloqueando se puede ejecutar esta instrucción SQL que nos dirá que tabla es la que está bloqueada:

SELECT SESSION_ID “SID”

, SERIAL# “Serial”

, SUBSTR(OBJECT_NAME, 1, 25) “Object”

, SUBSTR(OS_USER_NAME, 1, 10) “Terminal”

, SUBSTR(ORACLE_USERNAME, 1, 10) “Locker”

, NVL(LOCKWAIT, ‘ACTIVE’) “Wait”

, DECODE(LOCKED_MODE

, 2, ‘Row Share’

, 3, ‘ROW EXCLUSIVE’

, 4, ‘SHARE’

, 5, ‘SHARE ROW EXCLUSIVE’

, 6, ‘EXCLUSIVE’, ‘UNKNOWN’) “LockMode”

, OBJECT_TYPE “Type”

FROM GV$LOCKED_OBJECT A, SYS.ALL_OBJECTS B, GV$SESSION C

WHERE A.OBJECT_ID = B.OBJECT_ID

AND C.SID = A.SESSION_ID

ORDER BY 3

Con esto tenemos el SID y el serial que nos servirá luego para identificar el SQL que se está ejecutando y también para poder matar la sesión que está produciendo el bloqueo

2.- Ver que SQL está produciendo el bloqueo:

Para poder ver qué SQL está produciendo el bloqueo deberemos ejecutar la siguiente sentencia, en esta select está metida la tabla PSPNLGRPDEFN que valdrá solo para usuarios de peoplesoft, si no eres usuario de peoplesoft puedes quitar esta tabla y todo lo referente a ella

SELECT A.inst_id,A.SID AS SID___,B.serial#,

CASE WHEN SUBSTr(B.program,1,7) = ‘PSAESRV’ THEN ‘A.Engine’

WHEN SUBSTr(B.program,1,3) = ‘sqr’ THEN ‘SQR’

WHEN SUBSTr(B.program,1,3) = ‘PSAPPSRV’ THEN ‘Online’

WHEN Nvl(PNLG.PNLGRPNAME,’ ‘) <> ‘ ‘ THEN ‘Menu –> OnLine’

WHEN SUBSTr(B.program,1,8) = ‘PSSUBDSP’ THEN ‘Peoplesoft -> Mensajeria’

else ‘OTHER’

END AS TIPO_PROCESO

, A.sql_text , A.sql_fulltext , A.first_load_time , To_Char(SYSDATE,’YYYY-MM-DD/hh24:mi:ss’) ,B.terminal, B.program , B.machine ,B.osuser ,b.module, B.action, B.client_info

from Gv$sqlarea A

, Gv$session B LEFT JOIN PSPNLGRPDEFN PNLG ON PNLG.PNLGRPNAME = B.MODULE

where A.users_executing > 0

AND A.inst_id = B.inst_id

and A.address = B.sql_address

AND A.sql_id = B.sql_id

AND B.status = ‘ACTIVE’

AND NOT (A.sql_fulltext LIKE ‘%Gv$sqlarea%’)

ORDER BY first_load_time

Aquí si vemos el SQL_TEST y lo copiamos nos aparece la instrucción que se está ejecutando.

En mi caso es esta:

INSERT INTO PS_BORRA_TABLA2 (BUSINESS_UNIT, CUST_ID, ITEM, ITEM_LINE, ITEM_SEQ_NUM, ENTRY_TYPE, ENTRY_REASON, ENTRY_AMT, ENTRY_EVENT, ACCOUNTING_DT, POST_DT, ASOF_DT, DOCUMENT, DOCUMENT_LINE, DEPOSIT_BU, DEPOSIT_ID, PAYMENT_SEQ_NUM, DRAFT_BU, DRAFT_ID, GROUP_BU, GROUP_ID, GROUP_SEQ_NUM, HIST_STATUS, SUBCUST_QUAL1, SUBCUST_QUAL2, SUBCUST_STATUS, ENTRY_CURRENCY, RT_TYPE, RATE_MULT, RATE_DIV, PAYMENT_AMT, PAYMENT_CURRENCY, PYMT_RT_TYPE, PYMT_RATE_MULT, PYMT_RATE_DIV, ENTRY_AMT_BASE, CURRENCY_CD, REAL_GAIN_LOSS, ENTRY_USE_ID, ORIGIN_ID, PAYMENT_ID, GROUP_TYPE, VOUCHER_ID, SENT_TO_AP, CONSOL_BUS_UNIT, CONSOL_INVOICE, CR_ANALYST, SALES_PERSON, COLLECTOR, PO_REF, PO_LINE, BILL_OF_LADING, ORDER_NO, CONTRACT_NUM, BUSINESS_UNIT_BI, BUSINESS_UNIT_OM, BANK_SETID, BANK_CD, BANK_ACCT_KEY, DD_BU, DD_ID, POSTED_PI, ARRE_STATUS, PROCESS_INSTANCE, USER_AMT1, USER_AMT2, USER_AMT3, USER_AMT4, USER_AMT5, USER_AMT6, USER_AMT7, USER_AMT8, USER_DT1, USER_DT2, USER_DT3, USER_DT4, USER1, USER2, USER3, USER4, USER5, USER6, USER7, USER8, USER9, USER10, ST_ID_NUM, VAT_ADVPAY_FLG, UNPOST_REASON, PC_DISTRIB_STATUS, WS_REASON, SUB_GROUP_ID, SENT_TO_PURCHASING, BUSINESS_UNIT_CA, CONTRACT_LINE_NUM, SUBROG_CASE_NUM, REMIT_ADDR_SEQ_NUM, INVOICE) SELECT a.BUSINESS_UNIT, a.CUST_ID, a.ITEM, a.ITEM_LINE, a.ITEM_SEQ_NUM, a.ENTRY_TYPE, a.ENTRY_REASON, a.ENTRY_AMT, a.ENTRY_EVENT, a.ACCOUNTING_DT, a.POST_DT, a.ASOF_DT, a.DOCUMENT, a.DOCUMENT_LINE, a.DEPOSIT_BU, a.DEPOSIT_ID, a.PAYMENT_SEQ_NUM, a.DRAFT_BU, a.DRAFT_ID, a.GROUP_BU, a.GROUP_ID, a.GROUP_SEQ_NUM, a.HIST_STATUS, a.SUBCUST_QUAL1, a.SUBCUST_QUAL2, a.SUBCUST_STATUS, a.ENTRY_CURRENCY, a.RT_TYPE, a.RATE_MULT, a.RATE_DIV, a.PAYMENT_AMT, a.PAYMENT_CURRENCY, a.PYMT_RT_TYPE, a.PYMT_RATE_MULT, a.PYMT_RATE_DIV, a.ENTRY_AMT_BASE, a.CURRENCY_CD, a.REAL_GAIN_LOSS, a.ENTRY_USE_ID, a.ORIGIN_ID, a.PAYMENT_ID, a.GROUP_TYPE, a.VOUCHER_ID, a.SENT_TO_AP, a.CONSOL_BUS_UNIT, a.CONSOL_INVOICE, a.CR_ANALYST, a.SALES_PERSON, a.COLLECTOR, a.PO_REF, a.PO_LINE, a.BILL_OF_LADING, a.ORDER_NO, a.CONTRACT_NUM, a.BUSINESS_UNIT_BI

En mi caso es una instrucción muy larga y solo me saca hasta 2049 caracteres, pero si añadimos substr(A.sql_fulltext, 2040, 2000) te cogería los siguientes caracteres:

, a.BUSINESS_UNIT_OM, a.BANK_SETID, a.BANK_CD, a.BANK_ACCT_KEY, a.DD_BU, a.DD_ID, a.POSTED_PI, a.ARRE_STATUS, a.PROCESS_INSTANCE, a.USER_AMT1, a.USER_AMT2, a.USER_AMT3, a.USER_AMT4, a.USER_AMT5, a.USER_AMT6, a.USER_AMT7, a.USER_AMT8, a.USER_DT1, a.USER_DT2, a.USER_DT3, a.USER_DT4, a.USER1, a.USER2, a.USER3, a.USER4, a.USER5, a.USER6, a.USER7, a.USER8, a.USER9, a.USER10, a.ST_ID_NUM, a.VAT_ADVPAY_FLG, a.UNPOST_REASON, a.PC_DISTRIB_STATUS, a.WS_REASON, a.SUB_GROUP_ID, a.SENT_TO_PURCHASING, a.BUSINESS_UNIT_CA, a.CONTRACT_LINE_NUM, a.SUBROG_CASE_NUM, a.REMIT_ADDR_SEQ_NUM, a.INVOICE FROM PS_BORRA_TABLA a WHERE INVOICE = ‘ ‘

Si hacemos un explain plan veríamos que hace un full scan en toda la tabla y si este proceso se ejecuta muchas veces a lo mejor habría que pensar en hacer un índice por el invoice.

3.- Matar la sesión

Para matar la sesión ejecutamos la siguiente instrucción:

ALTER SYSTEM KILL SESSION ‘SID,Serial’ IMMEDIATE

En mi caso el SID es el 1166 y el serial el 29318. Con lo cual en mi caso habría que hacer:

ALTER SYSTEM KILL SESSION ‘1166, 29318’ IMMEDIATE

Espero que esta información os haya resultado útil para poder desbloquear una sesión en Oracle. Cualquier duda no dudes en dejar tu consulta en los comentarios.

Enlaces recomendados:

Using Regular Expressions in Oracle Database

Introducción al uso de las Expresiones Regulares en una Base de Datos Oracle

Using Regular Expressions with Oracle Database

POSIX Extended Regular Expressions for Oracle

REGEXP_COUNT