I recommend users who are upgrading to oracle 11g to check the sys/system
duplicate objects before upgrade. Duplicate check step is not
part of the 11g upgrade doc or manual installation doc.
I recommend following all steps which are provided in your
installation doc and use the following procedure to find any
duplicate before you proceed with your upgrade.
Oracle should include this steps part of upgrade doc to make
upgrade successful.
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 database features (such as
replication or shared pool management) are needed. These scripts are usually
run manually after the database has been created.
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:
column object_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!
The following objects are duplicates that will show up (and should not be removed)
when running this script in 8.1.x and higher.
Without replication installed:
INDEX AQ$_SCHEDULES_PRIMARY
TABLE AQ$_SCHEDULES
If replication is installed by running catrep.sql:
INDEX AQ$_SCHEDULES_PRIMARY
PACKAGE DBMS_REPCAT_AUTH
PACKAGE BODY DBMS_REPCAT_AUTH
TABLE AQ$_SCHEDULES
When database is upgraded to 11g using DBUA, following duplicate
objects are also created
OBJECT_NAME OBJECT_TYPE
------------------------------ -------------
Help TABLE
Help_Topic_Seq Index
The objects created by sqlplus/admin/help/hlpbld.sql must be owned by SYSTEM
because when sqlplus retrieves the help information, it refers to the SYSTEM
schema only. DBCA runs this script as SYSTEM user when it creates the database
but DBUA runs this script as SYS user when upgrading the database (
reported as an unpublished BUG 10022360). You can drop the ones in SYS schema.
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
set pagesize 0
set feedback off
set verify off
spool dropsys.sql
select 'DROP ' || object_type || ' SYSTEM.' || object_name || ';'
from dba_objects
where object_name||object_type in
(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
SQL*Plus: Release 3.3.2.0.0 - Production on Thu May 1 14:54:20 1997
Copyright (c) Oracle Corporation 1979, 1994. All rights reserved.
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.
