MDSYS Spatial De-install

MDSYS Spatial, Oracle db 从10.2.0.3 到10.2.0.4 升级最容易出问题的地方。 测试库升级完后一切正常，生产库却有54个MDSYS的无效对象.以前也有整理过db 从3 到4 recompile 问题。 现在想那是MDSYS valid的时候， 这次是直接INVALID了， 在metalink上捣鼓了2天，总算搞定了。 总结如下(具体情况具体对待)：
１.　SQL> select comp_id,version,status from dba_registry
where comp_id in ('JAVAVM','ORDIM','XDB');
２. SQL> connect / as sysdba
SQL> spool spatial_installation.lst
SQL> @?/md/admin/mdinst.sql
SQL> spool off
３. SQL> connect / as sysdba
SQL> set serveroutput on
SQL> execute validate_sdo;
SQL> select comp_id, control, schema, version, status, comp_name from dba_registry
where comp_id='SDO';
SQL> select object_name, object_type, status from dba_objects
where owner='MDSYS' and status <> 'VALID'
order by object_name;


Steps for Manual De-installation of Oracle Spatial
https://metalink2.oracle.com/help/usaeng/Search/search.html#file: 179472.1
Manual deinstallation of Spatial objects
Before deinstalling Oracle Spatial, it is best to drop all Spatial indexes.
Check if Spatial indexes exist in the database:

connect / as sysdba
select owner,index_name from dba_indexes
where ityp_name = 'SPATIAL_INDEX';
Check if tables having Spatial columns (columns having datatype SDO_GEOMETRY) exist:
set pages 200
col owner for a20
col table_name for a30
col column_name for a25

select owner, table_name, column_name
from dba_tab_columns
where data_type = 'SDO_GEOMETRY'
and owner != 'MDSYS'
order by 1,2,3;
Note: Removing MDSYS will drop (!) existing SDO_GEOMETRY columns from above tables!
In case of a re-installation see:https://metalink2.oracle.com/metalink/plsql/showdoc?db=NOT&id=250791.1&blackframe=1Re-installing Spatial with Existing Tables Having an SDO_GEOMETRY Column

To drop Spatial indexes:

drop index <owner>.<indexname>;
-- If some indexes cannot be dropped use the FORCE option:
drop index <owner>.<indexname> force;

Then drop the user MDSYS:

drop user MDSYS cascade;

Optionally drop all remaining public synonyms created for Spatial:
set pagesize 0
set feed off
spool dropsyn.sql
select 'drop public synonym "' || synonym_name || '";' from dba_synonyms where table_owner='MDSYS';
spool off;
Spatial also creates a few user schemas during installation which can be dropped as well:

drop user mddata cascade;
-- Only created as of release 11g:
drop user spatial_csw_admin_usr cascade;
drop user spatial_wfs_admin_usr cascade;
Note: If Spatial has accidently been installed earlier in another schema seehttps://metalink2.oracle.com/metalink/plsql/showdoc?db=NOT&id=413693.1&blackframe=1to cleanup these objects as well.
References
Note 220481.1- Index: How To Setup Oracle Spatial
Note 250791.1- Re-installing Spatial with Existing Tables Having an SDO_GEOMETRY Column
Note 413693.1- Accidentally installed SPATIAL into SYS, SYSTEM or another Schema


Subject: Steps for Manual Installation / Verification of Spatial 10g / 11g
Doc ID:270588.1
Solution
General comments

If you create an Oracle database using the Database Configuration Assistant (DBCA) Spatial is
installed by default and you do not need to perform the installation steps described in this section.

If you created your database manually or you want to install Spatial later, then follow these steps.


Manual installation of Spatial 10g / 11g

Prerequisites

To be able to do a successful Spatial 10g / 11g installation you need to have the following products already installed:

JServer JAVA Virtual Machine
Oracle interMedia
Oracle XML Database

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 243554.1
To (re-)install interMedia:Note 337415.1

Installation of Spatial

If the MDSYS user does NOT exist:

create the user MDSYS by running following command:

SQL> create user MDSYS identified by <password> 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 / as sysdba
SQL> spool spatial_installation.lst
SQL> @?/md/admin/mdinst.sql
SQL> spool off

At the end of the installation some verification steps are automatically executed!

You can also manually run the the verification steps later on.
See the Spatial verification section further down this note.

It is strongly recommended that the MDSYS user account remains locked. The MDSYS user is
created with administrator privileges; therefore, it is important to protect this account from unauthorized
use.To lock the MDSYS user, connect as SYS and enter the following command:

SQL> alter user MDSYS account lock;


Verification of an Oracle 10g / 11g Spatial Installation

Execute the following steps to verify if Spatial is installed correctly:

SQL> connect / as sysdba
SQL> set serveroutput on
SQL> execute validate_sdo;
SQL> select comp_id, control, schema, version, status, comp_name from dba_registry
where comp_id='SDO';
SQL> select object_name, object_type, status from dba_objects
where owner='MDSYS' and status <> 'VALID'
order by object_name;

A sample valid 10.2.0.4.0 installation shows the following output:

SQL> execute validate_sdo;
PL/SQL procedure successfully completed.
SQL> select comp_id, control, schema, version, status, comp_name from dba_registry where comp_id='SDO';
References
Note 179472.1- Steps for Manual De-installation of Oracle Spatial
Note 220481.1- Index: How To Setup Oracle Spatial


Subject: Re-installing Spatial with Existing Tables Having an SDO_GEOMETRY Column
https://metalink2.oracle.com/help/usaeng/Search/search.html#file: 250791.1

Checked for relevance on 22-Jul-2008

PURPOSE
-------

This document is mainly for Application environments with an invalid
Spatial installation (after upgrade/migrate). Several Application environments
have tables with an empty Spatial column (SDO_GEOMETRY) which enables
an easy re-installation of the Spatial product with minimal impact.

SCOPE & APPLICATION
-------------------

DBAs


Re-installing Spatial with Existing Tables Having an SDO_GEOMETRY Column
------------------------------------------------------------------------

Background
----------

Spatial tables contain a column of type SDO_GEOMETRY. The SDO_GEOMETRY
object type is owned by MDSYS and re-installing Spatial means a recreation
of the MDSYS schema and as result of this a new SDO_GEOMETRY object type.
As the SDO_GEOMETRY object type is a new object with a new object_id the
tables contaning a column of type SDO_GEOMETRY become invalid.
(The behaviour seen differs between versions; in release 9.2 columns of
type SDO_GEOMETRY are dropped from the table(s) automatically)

Scenario 1
----------
You have Spatial tables which contain actual Spatial data.

Make an export of the Spatial tables to be able to import them again
after Spatial has been re-installed.
Alternatively you can contact Oracle Support Services to try to solve
the invalid Spatial installation without the need to re-install it.

Scenario 2
----------
You have Spatial tables, but they contain no Spatial data. That is, the column
of type SDO_GEOMETRY is empty.
In this scenario you can drop and add the column of type SDO_GEOMETRY instead
of having to backup and restore your tables.

Suppose you have a table SHAPES with column GEOM of type SDO_GEOMETRY.

- First verify the Spatial tables contain no Spatial data.
SQL> select count(*) from SHAPES where GEOM IS NOT NULL;

--> This needs to return 0
- Then verify there are no constraints on the Spatial columns.

SQL> select count(*) from user_cons_columns where table_name='SHAPES';

If constraints exist you need to drop and recreate them afterwards.

Now follow the de-installation steps described in
Note 179472.1Steps for Manual De-installation of Oracle Spatial

Before actually dropping the MDSYS schema drop the Spatial column(s)
by means of:
SQL> alter table SHAPES drop column GEOMETRY;
After Spatial is re-installed succesfully (seeNote 220481.1for details
on how to install Spatial) the column(s) can be added again by means of:

SQL> alter table SHAPES add GEOM mdsys.sdo_geometry;

Finally recreate constraints on this column if applicable.


Additional remark
-----------------
The Spatial metadata has to be inserted into the USER_SDO_GEOM_METADATA
view again after a re-installation of Spatial (MDSYS schema)!


RELATED DOCUMENTS
-----------------
Note 220481.1Index: How To Setup Oracle Spatial
Note 179472.1Steps for Manual De-installation of Oracle Spatial