Re: dbms_lock package (help, I ran a script to replace it!)

From: dorine_r_perry_at_navtrans.navy.mil >> we are on our own right now. While trying to fix a problem with undeleted locks, I ran a script called dbmslock.sql, that comes with Oracle 7.3. The script went out and replaced the sys.dbms_lock package and created a synonym dbms_lock to point to it.

Everything appeared to be ok ..... but when we try to update ANYTHING in the repository, we get these error messages:

RME-00223: Failed to open new activity
RME-00222: Failed to dispatch operation to Repository
RME-00011: Operation 'open' on ACTIVITY has failed
RME-00854: Activity package initialisation failed

Here are some suggestions:
1) With 7.3 the specification and the body for Oracle provided packages like dbms_lock come in two scripts. The dbmsXXXX script is the specification. Did you also run the body. I am at home and do not remember the exact naming standard but I think the body scripts end in the same four characters, i.e., xxxxlock.sql.

2) I take it you were 'sys' or connect internal when you ran dbmslock.sql. If not this is a problem and you should rerun the script as sys. When you droped the package you lost all grants on it. Did you re-issue them? Or issue execute on dbms_lock to public..

3) When you ran dbmslock.sql you probably invalidated several of the repository packages. Oracle provides a script that will re-compile them in dependency order. You can find invalid packages with the query:

col segment_name format a30
select segment_name, owner, segment_type from sys.dba_objects
where object_type = 'PACKAGE'
or object_type = 'PACKAGE BODY';

If after considering and checking the above as necessary if you still have a problem you can try calling me if you think I may be able to help (even if it just to look up the correct script names mentioned above) I will be away from my desk most of the morning, but I have voice mail and will check it probably around noon. (313) 592-5148

Mark Powell -- Oracle 7 Certified DBA
- The only advice that counts is the advice that you follow so follow your own advice - Received on Tue Apr 21 1998 - 20:16:47 CDT