Oracle 11g spatial组件的删除与重建过程

教程发布:风哥 教程分类:ITPUX技术网 更新日期:2022-02-12 浏览学习:723

Oracle 11g spatial组件的删除:
Manual deinstallation of Spatial objectsBefore deinstalling Oracle Spatial, it is best to drop all Spatial indexes.[i]Check if Spatial indexes exist in the database:

connect / as sysdba
select owner,index_name from dba_indexes
where ityp_name = 'SPATIAL_INDEX';[i]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;[backcolor=rgb(255, 249, 215)]Note: Removing MDSYS will drop (!) existing SDO_GEOMETRY columns from above tables!
In case of a re-installation see: Note 250791.1 Re-installing Spatial with Existing Tables Having an SDO_GEOMETRY Column

To drop Spatial indexes:

drop index .;
-- If some indexes cannot be dropped use the FORCE option:
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.sqlIf 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 a25select 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.sqlset pages 200
col owner for a20
col table_name for a30
col column_name for a25select 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 a25select owner, table_name, column_name
from dba_tab_columns
where data_type = 'SDO_GEOMETRY'
and owner != 'MDSYS'
order by 1,2,3;
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;
@dropsyn.sql
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;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.

Oracle 11g spatial组件的重建:

dbca >>> configure database option >>> oracle spatial

本文标签:
网站声明:本文由风哥整理发布,转载请保留此段声明,本站所有内容将不对其使用后果做任何承诺,请读者谨慎使用!
【上一篇】
【下一篇】