重新创建mdsys用户,以及OCI-22303: type "SYS"."SDO_GEOMETRY" not found错误解决

一. 问题描述

通过DBLINK 访问远程数据库的表对象时,报:OCI-22303: type "SYS"."SDO_GEOMETRY" not found.
在远程数据库访问相同的表,没有任何问题.

二. 问题分析

SDO_GEOMETRY是MDSYS的对象,在本地库没有安装相关的数据库组件,不存在MDSYS用户,所以使用dblink访问远程的相关对象时,就会报OCI-22303对象找不到的错误.

开始的时候,通过两个脚本,想创建mdsys用户及其对象,脚本名称为:
?/ord/admin/ordinst.sql，创建用户
?/md/admin/mdinst.sql ,创建mdsys对象

[@more@]

都使用sys用户执行上面的两个脚本,后来发现很多mdsys的对象都建立到了sys用户上.
仔细检查?/md/admin/mdinst.sql文档的开头说明,这个文档是需要system用户来运行的.

于是尝试重新创建这个用户,结果无法删除,操作如下:
SQL> drop user mdsys cascade;
drop user mdsys cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-04098: trigger 'SYS.SDO_TOPO_DROP_FTBL' is invalid and failed re-validation

将SYS.SDO_TOPO_DROP_FTBL的状态设置为disable后,再次尝试删除mdsys,结果报了另外一个错误:
SQL> DROP USER MDSYS CASCADE;
DROP USER MDSYS CASCADE
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist
ORA-06512: at line 7

尝试dump相关的日志
SQL> alter session set events '942 trace name errorstack level 3';
Session altered.
SQL> DROP USER MDSYS CASCADE;
DROP USER MDSYS CASCADE
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist
ORA-06512: at line 7

在日志文件中,发现了以下的内容:
more /oracle/admin/ypjkdb/udump/ypjkdb_ora_1318984.trc
......
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /oracle/product/10.2.0/db
System name: AIX
Node name: odidb
Release: 1
Version: 6
Machine: 00C215C54C00
Instance name: ypjkdb
Redo thread mounted by this instance: 1
Oracle process number: 225
Unix process pid: 1318984, image: oracle@odidb (TNS V1-V3)

ksedmp: internal or fatal error
ORA-00942: table or view does not exist
Current SQL statement for this session:
DELETE FROM SDO_GEOM_METADATA_TABLE WHERE SDO_OWNER = :owner
----- PL/SQL Call Stack -----
object line object
handle number name
70000039a1fbee0 7 anonymous block
......

问题的焦点落在了DELETE FROM SDO_GEOM_METADATA_TABLE WHERE SDO_OWNER = :owner ,这句执行失败.
在metalink上就这个问题,进行了搜索,找到了ID=303975.1的文档,文章提示需要drop trigger sdo_drop_user;
于是通过pl/sql将这儿trigger,disable了,然后删除mdsys用户就ok了

SQL> drop user mdsys cascade;
User dropped.

因为之前很多SDO的对象都建立在sys用户,需要手工进行清楚,具体的脚本见metalink的文章ID=413693.1 .

三. 问题的解决

解决了上面的问题,本打算开始重新创建mdsys,后来去metalink上查找相关的mdsys创建的文档,发现有一些先决条件必须提前安装,否者mdsys创建后,会存在很多invalid的对象.
metalink的文章"Steps for Manual Installation / Verification of Spatial 10g / 11g" ,ID=270588.1提到下面的内容
To verify if the products are installed and valid run:

SQL> select comp_id,version,status from dba_registry
where comp_id in ('JAVAVM','ORDIM','XDB');

To (re-)install JServer see: Note:276554.1
To (re-)install XDB see: Note:1292089.1
To (re-)install interMedia: Note:337415.1

于是根据276554.1的内容重新安装了jserver
根据1292089.1 的内容重新安装了 xdb
根据337415.1 的内容重新安装了intermedia.

然后:
SQL> create user MDSYS identified by default tablespace SYSAUX account lock;

grant the required privileges to MDSYS by running:

SQL> @?/md/admin/mdprivs.sql

If the MDSYS user already exists then you are advised to verify if the installation has been done correctly and de-install Spatial first in case of re-installation.

See the verification checks at the bottom of this note and a link to de-installation note.

Install Spatial by executing the steps shown below. Note you need to run this as a SYSDBA user!

SQL> connect system/oracle
SQL> spool spatial_installation.lst
SQL> @?/md/admin/mdinst.sql
SQL> spool off


最后mdsys用户及其对象安装成功,就有3个对象的状态是invalid,不影响使用.

本地库通过dblink访问远程库的表对象时,不再报错了,问题解决.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/32980/viewspace-1058225/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/32980/viewspace-1058225/