Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2009-04-12
11
Medium Priority
?
2,171 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 750 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

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 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Suggested Courses

773 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