Solved

Oracle : how to create the package - utl_recomp in a database

Posted on 2009-04-12
11
1,958 Views
Last Modified: 2013-12-19
When I tried to granting the execute privilege for the package- utl_recomp to one of  our database user through "sys", I am getting the below error message.

grant execute on sys.utl_recomp to david
                     *
ERROR at line 1:
ORA-04042: procedure, function, package, or package body does not exist

It is evident from the above error message that mentioned package is not available in the database.
I would need the expert advice ito resolve this issue. please suggest way forward on this issue.

0
Comment
Question by:mansur_mca
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24125779
Make sure that your Package name is correct
0
 
LVL 1

Author Comment

by:mansur_mca
ID: 24125841
as you can see from the above SQL statement which i have pasted, that is a valid package should be exist as default. As above grant statement works in other in my DB's, Unfortunately it is failing in in one of the DB.
The query now, If this package is not available in any database then how can  configured/installed?
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24125850
Then the user login which you try to execute this does not have permissions on that package
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 1

Author Comment

by:mansur_mca
ID: 24125935
please revisit the question i posted above. I am using the "SYS" user  to  grant the privilages to the user.
0
 
LVL 10

Accepted Solution

by:
ravindran_eee earned 250 total points
ID: 24127247
Below script should be executed to make use of this procedure. I don't think it would be available by default.

/rdbms/admin/utlrcmp.sql
0
 
LVL 48

Expert Comment

by:schwertner
ID: 24128874
You can not grant execute rights on SYS procedures.
Tables and executables on schema SYS are not for public use.

Use the script

/rdbms/admin/utlrp.sql
instead

Rem
Rem    NAME
Rem      utlrp.sql - Recompile invalid objects
Rem
Rem    DESCRIPTION
Rem     This script recompiles invalid objects in the database.

or utlcomp.sql if you need compression

REM utlcomp.sql
REM
REM Copyright (c) 2001, 2003, Oracle Corporation.  All rights reserved.  
REM
REM    NAME
REM      utlcomp.sql - PL/SQL Package for COMPRESSION (UTL_COMPRESS)
REM
REM    DESCRIPTION
REM      PL/SQL package to compress and uncompress RAW data strings.
REM
0
 
LVL 1

Author Comment

by:mansur_mca
ID: 24130067
As this issue occured because of unavailablilty of required package in the database, hence to overcome this issue I ran the urlrcmp.sql in the below mentioned order.

a) Shutdown the database
b) Set this parameters
_system_trig_enabled=false
job_queue_processes=0
aq_tm_processes=0

 

excuted the script  utlrcmp.sql as SYS user  - - -> it ran successfully.

 

shutdown  the database
removed below parameters


_system_trig_enabled=false which has set above.

job_queue_processes=Earlier value

aq_tm_processes=Earlier value
Startup the database.

but while compiling the objects i am getting the below error message.

---------------------------------------------------------------

SQL> ALTER TRIGGER sys.cdc_alter_ctable_before enable;

Trigger altered.


SQL> ALTER TRIGGER sys.cdc_create_ctable_after enable;

ALTER TRIGGER sys.cdc_create_ctable_after enable

*

ERROR at line 1:

ORA-04020: deadlock detected while trying to lock object

SYS.CDC_ALTER_CTABLE_BEFORE

-----------------------------------------------------
please advice how to overcome this issue

0
 
LVL 10

Expert Comment

by:ravindran_eee
ID: 24130167
Executing the utlrcmp.sql only creates the packages and procedures. The procedure will have to executed manually after that. Following commands could be used for that,

EXEC UTL_RECOMP.recomp_serial('SCOTT');
OR
EXEC UTL_RECOMP.recomp_parallel(4, 'SCOTT');

Above commands or for running them at schema level. They could be run at DB level as well.

Else you can try recompiling the trigger alone before enabling the same.
0
 
LVL 1

Author Comment

by:mansur_mca
ID: 24130921
is there any script using that we can compile the triggers?
0
 
LVL 10

Expert Comment

by:ravindran_eee
ID: 24130932
ALTER TRIGGER triggername COMPILE;

Above command should be used to recompile a trigger.
0
 
LVL 1

Author Comment

by:mansur_mca
ID: 24173854
as this issue has comming repeatedly,i am not able to proceed any more further and hence i make these disabled and then issue has resolved.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question