CODEWITHSUNDEEP
oracle-databaselockingoracle8i
Praphul Viswan
Praphul Viswan

Reputation: 11

Insufficient privileges on oracle 8i while LOCK TABLE command

I have a package in which I need to put a lock on a table.

I am using the below statement at the beginning of main proc. 

PROCEDURE proc_main IS
BEGIN

-- added by praphul to test locking 

        EXECUTE IMMEDIATE 'LOCK TABLE schema.XYZ IN EXCLUSIVE MODE NOWAIT';

Getting following error : 

SQL> exec ssss.abcd.proc_main();
BEGIN ssss.abcd.proc_main(); END;

*
ERROR at line 1:
ORA-20001: ERROR OCCURED AT STEP:500
REASON: abcd FAILED
SQLCODE: -1031
SYS-MSG: ORA-01031: insufficient privileges
ORA-06512: at "ssss.PROC_ERROR_RAISE", line 31
ORA-06512: at "ssss.abcd", line 156
ORA-06512: at line 1

Upvotes: 0

Views: 1579

Answers (1)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50047

To successfully lock a table it must be in the same schema you're logged in to, or you must have the LOCK ANY TABLE privilege, or you must be granted any object privilege on the table in question. Apparently the ssss user hasn't been granted the appropriate privilege(s) on schema.XYZ. Talk to you DBA about this.

Best of luck.

Upvotes: 1

Related Questions