Solved

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

Posted on 2009-04-12
11
1,896 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
Comment Utility
Make sure that your Package name is correct
0
 
LVL 1

Author Comment

by:mansur_mca
Comment Utility
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
Comment Utility
Then the user login which you try to execute this does not have permissions on that package
0
 
LVL 1

Author Comment

by:mansur_mca
Comment Utility
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
Comment Utility
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
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 47

Expert Comment

by:schwertner
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
is there any script using that we can compile the triggers?
0
 
LVL 10

Expert Comment

by:ravindran_eee
Comment Utility
ALTER TRIGGER triggername COMPILE;

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

Author Comment

by:mansur_mca
Comment Utility
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.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
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…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

744 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now