Tag Archives: PL/SQL

ORA-04021: Timeout occurred while waiting to lock object

When developer is trying to recompile PL/SQL package session hangs until get this error:

ORA-04021: timeout occurred while waiting to lock object

Basically, whilst someone or something else (a scheduled job perhaps?) is executing the package, then you won’t be able to perform the recompile. To get around this, you need to identify the locking session and kill it.

Executing this script as SYS (or another user with the appropriate privileges) will prompt you for the package name and reveal the culprit(s):

SELECT s.sid,
       l.lock_type,
       l.mode_held,
       l.mode_requested,
       l.lock_id1,
       'alter system kill session '''|| s.sid|| ','|| s.serial#|| ''' immediate;' kill_sid
FROM   dba_lock_internal l,
       v$session s
WHERE  s.sid = l.session_id
AND    UPPER(l.lock_id1) LIKE '%&package_name%'
AND    l.lock_type = 'Body Definition Lock'
/

NOTE: If your dba_lock_internal view doesn’t exist, you can create this by running: $ORACLE_HOME/rdbms/admin/catblock.sql
check what is doing this session

SELECT s.sid,
       NVL(s.username, 'ORACLE PROC') username,
       s.osuser,
       p.spid os_pid,
       s.program,
       t.sql_text
FROM   v$session s,
       v$sqltext t,
       v$process p
WHERE  s.sql_hash_value = t.hash_value
AND    s.paddr = p.addr
AND    s.sid = &session_id
AND    t.piece = 0 -- optional to list just the first line
ORDER BY s.sid, t.hash_value, t.piece
/

run kill command from the output of first script

alter system kill session 'SID,SERIAL#' immediate;

after that developer can recompile package.

EXECUTE IMMEDIATE ORA-01031: insufficient privileges

EXECUTE IMMEDIATE Feature allows you to execute DYNAMIC SQL Statements inside the PL/SQL Procedure, but here i show you interesting thing about this feature.
Here is Video of these procedures
let’s demonstrate small demo

SQL> create user qobesa identified by qobesa;

User created.

SQL>  grant connect,resource to qobesa;

Grant succeeded.

Our user called qobesa has privileges to connect database, create table and others because resource role contains privileges such as ‘create table’, test it

sqlplus qobesa/qobesa

SQL> create table test (id number);

Table created.

let’s create simple procedure which creates table with execute immediate

SQL> create or replace procedure test_proc
  2  is
  3  begin
  4  EXECUTE IMMEDIATE 'CREATE TABLE TEST1(ID NUMBER)';
  5  end;
  6  /

Procedure created.

and execute it

SQL> exec test_proc
BEGIN test_proc; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "QOBESA.TEST_PROC", line 4
ORA-06512: at line 1

got an error “insufficient privileges” – Do we have this privilege?, YES. we have. It has been grouped under RESOURCE role and the RESOURCE role has been granted to this user. Then, What is stopping us?
In STORED PROCEDURE, roles are DISABLED. Hence any PRIVILEGES granted by a ROLE to USER, will NOT be in effect. The PRIVILEGES MUST be directly granted to the user.
Therefore we can grant create table privilege to our user and will work, test it

SQL> conn sys / as sysdba
Enter password: 
Connected.
SQL> grant create table to qobesa;

Grant succeeded.
SQL> conn qobesa/qobesa
Connected.
SQL> exec test_proc

PL/SQL procedure successfully completed.

It’s all, good luck.