卸载oracle exp 组件,oracle Spatial 组件卸载

Oracle的组件需要一个一个卸载，而且方法也有差异。这里说一下Spatial 组件卸载。

参考文档：

MOS：Steps for Manual De-installation of Oracle Spatial (Doc ID 179472.1)

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=320026192621105&id=179472.1&_afrWindowMode=0&_adf.ctrl-state=19n0vc396r_4

适用的版本：

Oracle Spatial and Graph - Version 9.2.0.1 to 11.2.0.4 [Release 9.2 to 11.2]

Manual deinstallation of Spatial objects

--手动删除Spatial对象

1、环境检查(检查索引和表)

Before deinstalling Oracle Spatial, it is best to drop all Spatial indexes.

--在卸载Oracle Spatial组件前，最好删除所有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:--检查是否有表使用Spatial 的列，即数据类型为SDO_GEOMETRY。

可以用下面的语句来检查

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;

2、删除索引和表Note: Removing MDSYS will drop (!) existing SDO_GEOMETRY columns from above tables!

In case of a re-installation see:Note 250791.1Re-installing Spatial with Existing Tables Having an SDO_GEOMETRY Column

注意：在删除MDSYS 会drop所有存在SDO_GEOMETRY 类型列的表。如果存在表可以参考MOS：250791.1文档处理

To drop Spatial indexes:

--删除Spatial indexes

drop index .;

-- If some indexes cannot be dropped use the FORCE option:

--如果有索引不能删除就加FORCE参数强制删除

drop index . force;

To automate:

--使用脚本批量删除

set pagesize 0

spool DropIndexes.sql

select 'drop index ' || owner || '.' || index_name ||';'

from dba_indexes where ityp_name = 'SPATIAL_INDEX';

spool off

@DropIndexes.sql

If want to just remove the tables without backing them up:

--如果需要删除表就如下操作

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;

set pagesize 0

spool DropTables.sql

select 'drop table '|| owner ||'.'|| table_name||';'

from dba_tab_columns

where data_type = 'SDO_GEOMETRY'

and owner != 'MDSYS';

spool off

@DropTables.sql

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;

connect to each user and execute:

purge recyclebin;

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;

3、删除用户

Then drop the user MDSYS:

-----删除MDSYS用户

drop user MDSYS cascade;

Optionally drop all remaining public synonyms created for Spatial:

---删除所有到MDSYS的同义词(可选操作)

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

Spatial also creates a few user schemas during installation which can be dropped as well:

---Spatial 在安装期间会创建一些新的用户，需要删除

drop user mddata cascade;

-- Only created as of release 11g:

--下面两个用户只有在11g版本才有

drop user spatial_csw_admin_usr cascade;

drop user spatial_wfs_admin_usr cascade;

4、其他注意事项

IMPORTANT NOTE: Spatial will still appear in V$OPTION but this is expected behavior as explained in Note:273573.1 - Removed Spatial Option But Spatial Still Appears In V$Option

IMPORTANT NOTE: After having de-installed Spatial and if there are no plans to re-install the Spatial product you should at least install Oracle Locator (subset of Spatial) again to prevent possible issues with future upgrades due to dependencies with for example the XDB product.

Note: If Spatial has accidentally been installed earlier in another schema see Note 413693.1 to cleanup these objects as well.