Oracle Spatial 安装与配置(1)
博客首页 » Oracle Spatial 安装与配置(1)
发布于 23 Aug 2013 11:36标签 blog lbs oracle spatial
正在安装Oracle Spatial，由于我们的生产库都是使用安装最小组件的策略。所以Oracle Spatial安装与配置上也遇到了一些麻烦。
在手册上，查到Oracle 10g 的Spatial Option是通过@?/md/admin/mdinst.sql安装的。
不过执行后，出现找不到mdsys的错误，并且出现大量的编译不过的DB对象。
SQL> column owner format a10 SQL> column object_tyoe format a20 SQL> column object_name format a30 SQL> select owner, object_name, object_type from dba_objects where object_name like 'SDO_%'; OWNER OBJECT_NAME OBJECT_TYPE ---------- ------------------------------ ------------------- SYS SDO_POINT_TYPE TYPE SYS SDO_ORDINATE_ARRAY TYPE SYS SDO_ELEM_INFO_ARRAY TYPE SYS SDO_GEOMETRY TYPE SYS SDO_DIM_ELEMENT TYPE SYS SDO_DIM_ARRAY TYPE SYS SDO_VPOINT_TYPE TYPE SYS SDO_MBR TYPE SYS SDO_ROWIDPAIR TYPE SYS SDO_ROWIDSET TYPE SYS SDO_SMPL_GEOMETRY TYPE SYS SDO_REGION TYPE SYS SDO_REGIONSET TYPE SYS SDO_REGAGGR TYPE SYS SDO_REGAGGRSET TYPE SYS SDO_NUMBER_ARRAY TYPE SYS SDO_STRING_ARRAY TYPE SYS SDO_GEOMETRY TYPE BODY PUBLIC SDO_KEYWORDARRAY SYNONYM SYS SDO_GEO_ADDR TYPE SYS SDO_GEO_ADDR TYPE BODY PUBLIC SDO_GEO_ADDR SYNONYM SYS SDO_ADDR_ARRAY TYPE PUBLIC SDO_ADDR_ARRAY SYNONYM PUBLIC SDO_GEOMETRY SYNONYM PUBLIC SDO_POINT_TYPE SYNONYM PUBLIC SDO_ELEM_INFO_ARRAY SYNONYM PUBLIC SDO_ORDINATE_ARRAY SYNONYM PUBLIC SDO_DIM_ELEMENT SYNONYM PUBLIC SDO_DIM_ARRAY SYNONYM PUBLIC SDO_VPOINT_TYPE SYNONYM PUBLIC SDO_MBR SYNONYM PUBLIC SDO_NUMBER_ARRAY SYNONYM PUBLIC SDO_STRING_ARRAY SYNONYM PUBLIC SDO_ROWIDPAIR SYNONYM PUBLIC SDO_ROWIDSET SYNONYM PUBLIC SDO_REGION SYNONYM PUBLIC SDO_REGIONSET SYNONYM PUBLIC SDO_REGAGGR SYNONYM PUBLIC SDO_REGAGGRSET SYNONYM SYS SDO_DROP_USER TRIGGER SYS SDO_DROP_USER_BEFORE TRIGGER SYS SDO_META PACKAGE SYS SDO_META PACKAGE BODY SYS SDO_VERSION FUNCTION PUBLIC SDO_VERSION SYNONYM SYS SDO_INDEX_METADATA_TABLE TABLE SYS SDO_IDX_MDATA_IDX INDEX PUBLIC SDO_INDEX_METADATA SYNONYM SYS SDO_TXN_IDX_INSERTS TABLE PUBLIC SDO_TXN_IDX_INSERTS SYNONYM SYS SDO_TXN_IDX_DELETES TABLE PUBLIC SDO_TXN_IDX_DELETES SYNONYM SYS SDO_TXN_IDX_EXP_UPD_RGN TABLE PUBLIC SDO_TXN_IDX_EXP_UPD_RGN SYNONYM SYS SDO_LRS PACKAGE SYS SDO_LRS_METADATA_TABLE TABLE SYS SDO_LRS_META_IDX INDEX PUBLIC SDO_SRID_CHAIN SYNONYM PUBLIC SDO_TRANSIENT_RULE_SET SYNONYM PUBLIC SDO_SRID_LIST SYNONYM SYS SDO_CS PACKAGE PUBLIC SDO_TFM_CHAIN SYNONYM SYS SDO_IDX PACKAGE SYS SDO_TOPO_GEOMETRY_LAYER TYPE PUBLIC SDO_TOPO_GEOMETRY_LAYER SYNONYM SYS SDO_TOPO_GEOMETRY_LAYER_ARRAY TYPE PUBLIC SDO_TOPO_GEOMETRY_LAYER_ARRAY SYNONYM SYS SDO_LIST_TYPE TYPE PUBLIC SDO_LIST_TYPE SYNONYM SYS SDO_TOPO_OBJECT TYPE PUBLIC SDO_TOPO_OBJECT SYNONYM SYS SDO_TOPO_NSTD_TBL TYPE PUBLIC SDO_TOPO_NSTD_TBL SYNONYM SYS SDO_TGL_OBJECT TYPE PUBLIC SDO_TGL_OBJECT SYNONYM SYS SDO_EDGE_ARRAY TYPE PUBLIC SDO_EDGE_ARRAY SYNONYM SYS SDO_TOPO_OBJECT_ARRAY TYPE PUBLIC SDO_TOPO_OBJECT_ARRAY SYNONYM SYS SDO_TGL_OBJECT_ARRAY TYPE PUBLIC SDO_TGL_OBJECT_ARRAY SYNONYM SYS SDO_TOPO_GEOMETRY TYPE PUBLIC SDO_TOPO_GEOMETRY SYNONYM SYS SDO_TOPO_METADATA_TABLE TABLE SYS SDO_TOPO_METADATA_IDX INDEX SYS SDO_TOPO_TRANSACT_SUBSEQ SEQUENCE SYS SDO_TOPO_TRANSACT_SEQ SEQUENCE PUBLIC SDO_TOPO_TRANSACT_DATA$ SYNONYM SYS SDO_TOPO_METADATA PACKAGE SYS SDO_TOPO_METADATA PACKAGE BODY PUBLIC SDO_TOPO_DATA$ SYNONYM SYS SDO_INDEX_METHOD_10I TYPE SYS SDO_TPIDX PACKAGE SYS SDO_TPIDX PACKAGE BODY SYS SDO_INDEX_METHOD_10I TYPE BODY SYS SDO_IDX PACKAGE BODY SYS SDO_RELATEMASK_TABLE VIEW PUBLIC SDO_RELATEMASK_TABLE SYNONYM SYS SDO_RELATE_MASK PACKAGE SYS SDO_RELATE_MASK PACKAGE BODY PUBLIC SDO_RELATE_MASK SYNONYM SYS SDO_3GL PACKAGE SYS SDO_ADMIN PACKAGE SYS SDO_ADMIN PACKAGE BODY PUBLIC SDO_ADMIN SYNONYM SYS SDO_TUNE PACKAGE SYS SDO_UTIL PACKAGE SYS SDO_IDX_TAB_SEQUENCE SEQUENCE SYS SDO_CONSTRUCT_DIM_ARRAY FUNCTION SYS SDO_GEOM PACKAGE SYS SDO_GEOM PACKAGE BODY PUBLIC SDO_GEOM SYNONYM PUBLIC SDO_3GL SYNONYM SYS SDO_3GL PACKAGE BODY SYS SDO_CATALOG PACKAGE SYS SDO_CATALOG PACKAGE BODY PUBLIC SDO_CATALOG SYNONYM SYS SDO_STATISTICS TYPE SYS SDO_STATISTICS TYPE BODY SYS SDO_DUMMY_FUNCTION FUNCTION PUBLIC SDO_NN SYNONYM PUBLIC SDO_NN_DISTANCE SYNONYM PUBLIC SDO_FILTER SYNONYM PUBLIC SDO_RELATE SYNONYM PUBLIC SDO_RTREE_FILTER SYNONYM PUBLIC SDO_RTREE_RELATE SYNONYM PUBLIC SDO_WITHIN_DISTANCE SYNONYM PUBLIC SDO_ANYINTERACT SYNONYM PUBLIC SDO_CONTAINS SYNONYM PUBLIC SDO_INSIDE SYNONYM PUBLIC SDO_TOUCH SYNONYM PUBLIC SDO_EQUAL SYNONYM PUBLIC SDO_COVERS SYNONYM PUBLIC SDO_ON SYNONYM PUBLIC SDO_COVEREDBY SYNONYM PUBLIC SDO_OVERLAPBDYDISJOINT SYNONYM PUBLIC SDO_OVERLAPBDYINTERSECT SYNONYM PUBLIC SDO_OVERLAPS SYNONYM SYS SDO_MIGRATE PACKAGE SYS SDO_MIGRATE PACKAGE BODY PUBLIC SDO_MIGRATE SYNONYM PUBLIC SDO_CS SYNONYM SYS SDO_CS PACKAGE BODY PUBLIC SDO_DATUM_ENGINEERING SYNONYM PUBLIC SDO_DATUM_GEODETIC SYNONYM PUBLIC SDO_DATUM_VERTICAL SYNONYM PUBLIC SDO_CRS_COMPOUND SYNONYM PUBLIC SDO_CRS_ENGINEERING SYNONYM PUBLIC SDO_CRS_GEOCENTRIC SYNONYM PUBLIC SDO_CRS_GEOGRAPHIC2D SYNONYM PUBLIC SDO_CRS_GEOGRAPHIC3D SYNONYM PUBLIC SDO_CRS_PROJECTED SYNONYM PUBLIC SDO_CRS_VERTICAL SYNONYM PUBLIC SDO_AREA_UNITS SYNONYM PUBLIC SDO_DIST_UNITS SYNONYM PUBLIC SDO_ANGLE_UNITS SYNONYM PUBLIC SDO_ELLIPSOIDS_OLD_FORMAT SYNONYM PUBLIC SDO_PROJECTIONS_OLD_FORMAT SYNONYM PUBLIC SDO_DATUMS_OLD_FORMAT SYNONYM PUBLIC SDO_COORD_OPS SYNONYM PUBLIC SDO_AVAILABLE_OPS SYNONYM PUBLIC SDO_AVAILABLE_ELEM_OPS SYNONYM PUBLIC SDO_AVAILABLE_NON_ELEM_OPS SYNONYM PUBLIC SDO_COORD_OP_PATHS SYNONYM PUBLIC SDO_PREFERRED_OPS_SYSTEM SYNONYM PUBLIC SDO_PREFERRED_OPS_USER SYNONYM PUBLIC SDO_COORD_REF_SYS SYNONYM PUBLIC SDO_COORD_REF_SYSTEM SYNONYM PUBLIC SDO_UNITS_OF_MEASURE SYNONYM PUBLIC SDO_PRIME_MERIDIANS SYNONYM PUBLIC SDO_ELLIPSOIDS SYNONYM PUBLIC SDO_DATUMS SYNONYM PUBLIC SDO_COORD_SYS SYNONYM PUBLIC SDO_COORD_AXES SYNONYM PUBLIC SDO_COORD_AXIS_NAMES SYNONYM PUBLIC SDO_COORD_OP_METHODS SYNONYM PUBLIC SDO_COORD_OP_PARAMS SYNONYM PUBLIC SDO_COORD_OP_PARAM_USE SYNONYM PUBLIC SDO_COORD_OP_PARAM_VALS SYNONYM PUBLIC SDO_TRANSIENT_RULE SYNONYM SYS SDORIDLIST TYPE PUBLIC SDO_RTREE_ADMIN SYNONYM SYS SDO_RTREE_ADMIN PACKAGE SYS SDO_RTREE_ADMIN PACKAGE BODY SYS SDO_TUNE PACKAGE BODY PUBLIC SDO_TUNE SYNONYM PUBLIC SDO_ELLIPSOIDS_OLD_SNAPSHOT SYNONYM PUBLIC SDO_PROJECTIONS_OLD_SNAPSHOT SYNONYM PUBLIC SDO_DATUMS_OLD_SNAPSHOT SYNONYM PUBLIC SDO_UTIL SYNONYM SYS SDO_UTIL PACKAGE BODY SYS SDO_JOIN FUNCTION PUBLIC SDO_JOIN SYNONYM SYS SDO_MAPS_TABLE TABLE SYS SDO_THEMES_TABLE TABLE SYS SDO_THEMES_IDX INDEX SYS SDO_NUMTAB TYPE SYS SDO_STAT TYPE SYS SDO_STATTAB TYPE SYS SDO_PRIDX PACKAGE PUBLIC SDO_PRIDX SYNONYM SYS SDO_PRIDX PACKAGE BODY PUBLIC SDO_LRS SYNONYM SYS SDO_LRS PACKAGE BODY SYS SDOAGGRTYPE TYPE PUBLIC SDOAGGRTYPE SYNONYM SYS SDOAGGR TYPE SYS SDOAGGR TYPE BODY SYS SDO_AGGR_UNION FUNCTION PUBLIC SDO_AGGR_UNION SYNONYM SYS SDO_AGGR_MBR FUNCTION PUBLIC SDO_AGGR_MBR SYNONYM SYS SDO_AGGR_LRS_CONCAT FUNCTION PUBLIC SDO_AGGR_LRS_CONCAT SYNONYM SYS SDO_AGGR_LRS_CONCAT_3D FUNCTION PUBLIC SDO_AGGR_LRS_CONCAT_3D SYNONYM SYS SDO_AGGR_CONVEXHULL FUNCTION PUBLIC SDO_AGGR_CONVEXHULL SYNONYM SYS SDO_AGGR_CENTROID FUNCTION PUBLIC SDO_AGGR_CENTROID SYNONYM SYS SDO_AGGR_CONCAT_LINES FUNCTION PUBLIC SDO_AGGR_CONCAT_LINES SYNONYM SYS SDO_XML_SCHEMAS TABLE PUBLIC SDO_XML_SCHEMAS SYNONYM SYS SDO_CART_TEXT TYPE PUBLIC SDO_CART_TEXT SYNONYM SYS SDO_TOPO PACKAGE SYS SDO_TOPO_MAP PACKAGE PUBLIC SDO_TOPO_MAP SYNONYM SYS SDO_TOPO_GEOMETRY TYPE BODY SYS SDO_TOPO PACKAGE BODY SYS SDO_TPFNS PACKAGE PUBLIC SDO_TOPO SYNONYM SYS SDO_TPFNS PACKAGE BODY PUBLIC SDO_TOPO_ANYINTERACT SYNONYM SYS SDO_GEORASTER TYPE SYS SDO_RASTER TYPE SYS SDO_RASTERSET TYPE SYS SDO_GEOR_METADATA TYPE SYS SDO_GEOR_SRS TYPE SYS SDO_GEOR_HISTOGRAM TYPE SYS SDO_GEOR_GRAYSCALE TYPE SYS SDO_GEOR_COLORMAP TYPE PUBLIC SDO_GEORASTER SYNONYM PUBLIC SDO_GEOR_METADATA SYNONYM PUBLIC SDO_RASTER SYNONYM PUBLIC SDO_RASTERSET SYNONYM PUBLIC SDO_GEOR_SRS SYNONYM PUBLIC SDO_GEOR_HISTOGRAM SYNONYM PUBLIC SDO_GEOR_GRAYSCALE SYNONYM PUBLIC SDO_GEOR_COLORMAP SYNONYM SYS SDO_GEOR_INT PACKAGE SYS SDO_GEOR_DEF PACKAGE SYS SDO_GEORX PACKAGE SYS SDO_GEOR_SYSDATA_TABLE TABLE SYS SDO_GEOR_IDX INDEX SYS SDO_GEOR_DROP_USER TRIGGER SYS SDO_GEOR_TRUNC_TABLE TRIGGER SYS SDO_GEOR_SEQ SEQUENCE SYS SDO_GR_MOSAIC_0 TABLE SYS SDO_GR_MOSAIC_1 TABLE SYS SDO_GR_MOSAIC_2 TABLE SYS SDO_GR_MOSAIC_3 TABLE SYS SDO_GR_RDT_1 TABLE SYS SDO_GR_RDT_1_PK INDEX SYS SDO_GEOR_PLUGIN_REGISTRY TABLE PUBLIC SDO_GEORX SYNONYM SYS SDO_GEORX PACKAGE BODY SYS SDO_GEOR_DEF PACKAGE BODY SYS SDO_GEOR_INT PACKAGE BODY PUBLIC SDO_GEOR_INT SYNONYM PUBLIC SDO_GEOR SYNONYM PUBLIC SDO_GEOR_UTL SYNONYM SYS SDO_GEOR_LIZARDTECH PACKAGE PUBLIC SDO_GCDR SYNONYM SYS SDO_NETWORK_METADATA_TABLE TABLE SYS SDO_NDM_NETWORK_ID_SEQ SEQUENCE SYS SDO_NETWORK_DROP_USER TRIGGER SYS SDO_NETWORK_CONSTRAINTS TABLE SYS SDO_NETWORK_CONS_DROP_TRIG TRIGGER SYS SDO_NETWORK_USER_DATA TABLE SYS SDO_NETWORK_UD_DROP_TRIG TRIGGER SYS SDO_NET PACKAGE SYS SDO_NET PACKAGE BODY PUBLIC SDO_NET SYNONYM SYS SDO_NETWORK_MANAGER_T TYPE SYS SDO_NETWORK_MANAGER_I TYPE PUBLIC SDO_NETWORK_MANAGER_T SYNONYM SYS SDO_NODE_T TYPE SYS SDO_NODE_I TYPE PUBLIC SDO_NODE_T SYNONYM SYS SDO_LINK_T TYPE SYS SDO_LINK_I TYPE PUBLIC SDO_LINK_T SYNONYM SYS SDO_PATH_T TYPE SYS SDO_PATH_I TYPE PUBLIC SDO_PATH_T SYNONYM SYS SDO_NETWORK_T TYPE SYS SDO_NETWORK_I TYPE PUBLIC SDO_NETWORK_T SYNONYM SYS SDO_NETWORK_MANAGER_I TYPE BODY SYS SDO_NODE_I TYPE BODY SYS SDO_LINK_I TYPE BODY SYS SDO_PATH_I TYPE BODY SYS SDO_NETWORK_I TYPE BODY SYS SDO_NET_MEM PACKAGE PUBLIC SDO_NET_MEM SYNONYM SYS SDO_RDF_INTERNAL PACKAGE SYS SDO_RDF PACKAGE SYS SDO_RDF_TRIPLE TYPE PUBLIC SDO_RDF_TRIPLE SYNONYM SYS SDO_RDF_TRIPLE_S TYPE PUBLIC SDO_RDF_TRIPLE_S SYNONYM SYS SDO_RDF_TRIPLE_S TYPE BODY PUBLIC SDO_RDF_MODELS SYNONYM PUBLIC SDO_RDF_RULEBASES SYNONYM PUBLIC SDO_RDF_ALIAS SYNONYM PUBLIC SDO_RDF_ALIASES SYNONYM PUBLIC SDO_RDF_INFERENCE SYNONYM PUBLIC SDO_RDF_MATCH SYNONYM SYS SDO_RDF_INTERNAL PACKAGE BODY PUBLIC SDO_RDF_INTERNAL SYNONYM SYS SDO_RDF PACKAGE BODY PUBLIC SDO_RDF SYNONYM SYS SDO_SAM PACKAGE SYS SDO_SAM PACKAGE BODY PUBLIC SDO_SAM SYNONYM SYS SDO_ROUTER_PARTITION PACKAGE SYS SDO_ROUTER_PARTITION PACKAGE BODY PUBLIC SDO_ROUTER_PARTITION SYNONYM
在mdinst.sql中调用catmd.sql中这句话是关键：
Alter session set current_schema=MDSYS;
因为ordinst.sql中创建mdsys，所以在没有mdsys的情况下，所有object都生成在了当前用户sys下。
create user MDSYS identified by mdsys default tablespace SYSAUX account unlock;
网上公开的方法是通过MDSYS的schema删除所有objects。
set pagesize 0 set feed off spool dropsyn.sql select 'drop public synonym "' || synonym_name || '";' from dba_synonyms where table_owner='MDSYS'; spool off; @dropsyn.sql
明显这个方法对于这次的问题不适用，所以经过比对正常库中的object名字，使用下面这个SQL生成drop文。
SQL> select 'drop '||object_type||' '||owner||'.'||object_name||';' from dba_objects where object_name like 'SDO%' and object_type not like '%BODY';
经过处理，大部分多余的db objects都被删除了，不过还是有下面这6个删除不掉。
'DROP'||OBJECT_TYPE||''||OWNER||'.'||OBJECT_NAME||';' ------------------------------------------------------------------------------------------------------------------------------------------------------ drop TYPE SYS.SDO_POINT_TYPE; drop TYPE SYS.SDO_ORDINATE_ARRAY; drop TYPE SYS.SDO_ELEM_INFO_ARRAY; drop TYPE SYS.SDO_GEOMETRY; drop TYPE SYS.SDO_ROWIDPAIR; drop TYPE SYS.SDO_ROWIDSET; SQL> drop TYPE SYS.SDO_POINT_TYPE; drop TYPE SYS.SDO_POINT_TYPE * 第 1 行出现错误: ORA-02303: 无法使用类型或表的相关性来删除或取代一个类型 SQL> drop TYPE SYS.SDO_ORDINATE_ARRAY; drop TYPE SYS.SDO_ORDINATE_ARRAY * 第 1 行出现错误: ORA-02303: 无法使用类型或表的相关性来删除或取代一个类型 SQL> drop TYPE SYS.SDO_ELEM_INFO_ARRAY; drop TYPE SYS.SDO_ELEM_INFO_ARRAY * 第 1 行出现错误: ORA-02303: 无法使用类型或表的相关性来删除或取代一个类型 SQL> drop TYPE SYS.SDO_GEOMETRY; drop TYPE SYS.SDO_GEOMETRY * 第 1 行出现错误: ORA-02303: 无法使用类型或表的相关性来删除或取代一个类型 SQL> drop TYPE SYS.SDO_ROWIDPAIR; drop TYPE SYS.SDO_ROWIDPAIR * 第 1 行出现错误: ORA-02303: 无法使用类型或表的相关性来删除或取代一个类型 SQL> drop TYPE SYS.SDO_ROWIDSET; drop TYPE SYS.SDO_ROWIDSET * 第 1 行出现错误: ORA-02303: 无法使用类型或表的相关性来删除或取代一个类型
是被某个db object参照了，可是dba_tab_columns和dba_source里面都没有相应的参照。
只能先放一放了。
Reference:
http://www.cnblogs.com/zlja/archive/2012/03/08/2449466.html
http://blog.csdn.net/tianlesoftware/article/details/4792774
http://www.orafaq.com/wiki/Spatial_FAQ
http://www.2cto.com/database/201108/101986.html
本页面的文字允许在知识共享 署名-相同方式共享 3.0协议和GNU自由文档许可证下修改和再使用，仅有一个特殊要求，请用链接方式注明文章引用出处及作者。请协助维护作者合法权益。
系列文章
文章列表
- Oracle Spatial 安装与配置(1)
这篇文章对你有帮助吗，投个票吧？
留下你的评论