ordba.net - Srinivas Maddali - What I learned from various RDBMS also from non-RDBMS supported by RDBMS

ORA-04045

What is being done?

I had Oracle 9.2.0.5.0 database
I had a remote 9.0.1.3.1 database

I was testing replication.

I saw some invalid SYSTEM schema replication objects in 9.0.1.3.1 database.When I tried to validate them I got errors, which I have not saved, to a log file. Thought it fit to re-run the catalog and catproc scripts in restricted mode. This Database is not used by anybody. I do not know what was the history.This is on windows 2000 platform. The Database has been dormant for over a year.I recreated the service using the service name from the init.ora file and started the database. The database looked OK and alert log file showed no errors.

When ran the catalog.sql no errors and but catproc.sql has been spitting the errors like these:

ORA-04045: errors during recompilation/revalidation of SYS.CDC_CREATE_CTABLE_BEFORE
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 2
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 2

CREATE OR REPLACE PACKAGE BODY dbms_ias_inst_utl_exp wrapped
*
ERROR at line 1:
ORA-04045: errors during recompilation/revalidation of SYS.CDC_CREATE_CTABLE_BEFORE
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 2
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 2

Repair the database with the following

My Friend told me he saved some info from a web site he does not remember.
Research on Goggle was full of reports like me with no answers.

====================================================
Dbforums.com has one entry, which helps really. This is what I followed

Thomas Kyte in orafaq forum said
(http://www.orafaq.net/archive/comp.databases.oracle.server/2002/04/07/142301.htm)

In article , "Alvaro says...

that trigger is part of the Change Data Capture (CDC) processing.

They can go "bad" if someone removes Java from the database (rmjvm)

As SYS you can:

DROP TRIGGER SYS.cdc_alter_ctable_before;
DROP TRIGGER SYS.cdc_create_ctable_after;
DROP TRIGGER SYS.cdc_create_ctable_before;
DROP TRIGGER SYS.cdc_drop_ctable_before;

if you have just removed the java stuff with rmjvm.

Change Data Capture is installed if you install Java -- but is not (yet) removed

when you remove it. It is in a later release (due to the above)

If you have not run the rmjvm (java still exists in your database), please contact support for debugging this one.
====================================================

SQL> alter system set "_system_trig_enabled"=FALSE ;
SQL> @catalog.sql
SQL> @catproc.sql

SQL> alter system set "_system_trig_enabled"=TRUE ;

Now the issue is

create or replace type ku$_ind_part_list_t as table of ku$_ind_part_t
*
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or table dependents

create sequence system.repcat$_user_parm_values_s
*
ERROR at line 1:
ORA-00955: name is already used by an existing object

When the catproc.sql is rerun these error are bound to come in Oracle 9i version
and they can be safely ignored

"
These errors are normal in 9.0.1.x as a result of re-running catproc.sql as part of catpatch.sql. You can safely ignore these errors.
"
refer to metalink and serach with this string or doc id 'ORA-2303 running catproc.sql 9i' or doc id 237450.996

And then apply method described in Metalink note 1030426.6 for removing SYS duplicate objects in SYSTEM schema.
=============================================
Problem Description:

If the same data dictionary object has been created under both user SYS and SYSTEM schema then errors will often occur when trying to use the database features associated with these objects.

Problem Explanation:
==============

During the installation of Oracle many scripts are run which create the underlying data dictionary objects. Most of these scripts are run at database creation time, but others are only run if specific databsae features (such as replication or shared pool management) are needed. These scripts are usually run manually after the database has been created.

Running SQL scripts manually increases the chance of error greatly. One such common problem is running the SQL script as the wrong Oracle user.

Most SQL scripts located in the $ORACLE_HOME/rdbms/admin directory should be run as SYS (or internal) and not SYSTEM.

If you happen to run a SQL as the wrong user it is very hard to clean up from this situation as the number of objects that a script creates can be very large as well as there are no delivered scripts to drop the incorrect objects.

Search Words:
=========

runcatalog.sqlcatproc.sql system catalogcatproc

Solution Description:
=============

In order to clean up the duplicate objects you need to issue a SQL script to find out the names of
the duplicate objects.

You can then manually drop the objects or use a 'SQL generating SQL' script to generate a list of drop commands.

Below is a SQL*Plus script that will list all objects that have been created in both the SYS and SYSTEM schema:

columnobject_name format a30
select object_name,
object_type
from dba_objects
where object_name||object_type in
(
select object_name||object_type
from dba_objects
where owner = 'SYS')
and owner = 'SYSTEM';

The output from this script will either be 'zero rows selected' or will look something like the following:

OBJECT_NAME OBJECT_TYPE
------------------------------ -------------
ALL_DAYS VIEW
CHAINED_ROWS TABLE
COLLECTION TABLE
COLLECTION_ID SEQUENCE
DBA_LOCKS SYNONYM
DBMS_DDL PACKAGE
DBMS_SESSION PACKAGE
DBMS_SPACE PACKAGE
DBMS_SYSTEM PACKAGE
DBMS_TRANSACTION PACKAGE
DBMS_UTILITY PACKAGE

If the select statement returns any rows then this is an indication that at least 1 script has been run as both SYS and SYSTEM.

Since most data dictionary objects should be owned by SYS (see exceptions below) you will want to drop the objects that are owned by SYSTEM in order to clear up this situation.

EXCEPTION TO THE RULE
=====================

THE REPLICATION SCRIPTS (XXX) CORRECTLY CREATES OBJECTS WITH THE SAME NAME IN THE SYS AND SYSTEM ACCOUNTS.

LISTED BELOW ARE THE OBJECTS USED BY REPLICATION THAT SHOULD BE CREATED IN BOTH ACCOUNTS.

DO NOT DROP THESE OBJECTS FROM THE SYSTEM ACCOUNT IF YOU ARE USING REPLICATION. DOING SO WILL CAUSE REPLICATION TO FAIL!

OBJECT_NAME OBJECT_TYPE
------------------------------ -------------
DBMS_REPCAT_AUTH PACKAGE
DBMS_REPCAT_AUTH PACKAGE BODY

Now that you have a list of duplicate objects you will simply issue the appropriate DROP command to get
rid of the object that is owned by the SYSTEM user.

If the list of objects is large then you may want to use the following SQL*Plus script to automatically generate an SQL script that contains the appropriate DROP commands:

set pause off set heading off

setpagesize 0
set feedback off
set verify off
spool dropsys.sql

select 'DROP ' || object_type || ' SYSTEM.' || object_name || ';'
from dba_objects
where object_name||object_typein
(
select object_name||object_type
from dba_objects
where owner = 'SYS')
and owner = 'SYSTEM';
spool off
exit

You will now have a file in the current directory named dropsys.sql that contains all of the DROP commands.
You will need to run this script as a normal SQL script as follows:

$ sqlplus

Enter user-name: system
Enter password: manager

SQL> @dropsys

Note:

You may receive one or more of the following errors:

ORA-2266 (unique/primary keys in table referenced by enabled foreign keys):

If you encounter this error then some of the tables you are dropping have constrints that prevent the table from being dropped. To fix this problem you will have to manually drop the objects in a different order than the script does.

ORA-2429 (cannot drop index used for enforcement of unique/primary key):

This is similar to the ORA-2266 error except that it points to an index.

You will have to manually disable the constraint associated with the index and then drop the index.

ORA-1418 (specified index does not exist):
This occurs because the table that the index was created on has already been dropped which also drops the index. When the script tries to drop the index it is no longer there and thus the

ORA-1418 error.

You can safely ignore this error.

I still got the export error. After a simple Metalink search, I found out that what
I had to do was simply: Metalink note 159695.1.
SQL> GRANT EXECUTE ON sys.lt_export_pkg TO PUBLIC;

ORA-04045

Download PDF

ORA-04045