风哥教程

培训 . 交流 . 分享
Make progress together!

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

[复制链接]
内容发布:风哥| 发布时间:2015-10-26 09:47:20
Oracle 11g spatial组件的删除:
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: Note 250791.1 Re-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;
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;

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






上一篇:转:在Linux中Oracle安装成功后,首次启动使用时问题总结
下一篇:server2012R2怎么安装10G64位的数据库
专业提供Oracle/MySQL/NoSQL/Linux数据库培训与技术支持服务,QQ号:113257174
关注风哥教程微信公众号itpux_com  ,了解本站最新技术资料的分享.

欢迎加QQ群,提供超多高质量Oracle/Unix/Linux技术文档与视频教程的下载。

Oracle/MySQL/Linux群4-5:189070296  150201289  
Oracle/MySQL/Linux群6-8:244609803   522261684   522651731
备注:请勿重复加群,另请注明 from itpux
回复

使用道具 举报

1框架
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

热门文章教程

  • Navicat for MySQL最新版下载地址及注册码
  • MySQL5权威指南(第3版)PDF电子版下载
  • PostgreSQL数据库中文培训手册
  • Oracle Database 12c 数据库100个新特性与
  • oracle 12c RAC 日志频繁切换 checkpoint n
  • 风哥Oracle数据库巡检工具V1.0(附2.6网页
快速回复 返回顶部 返回列表