Solved

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

Posted on 2009-04-12
11
1,982 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle collections 15 39
Oracle Nested table uses ? 2 45
return value based on substr 10 51
Need help installing oracle client,it is not installing. 3 26
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

730 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