ITPUX技术网

交流 . 资讯 . 分享
Make progress together!
Oracle数据库DBA高级工程师培训视频
Oracle数据库培训-备份恢复-性能优化-集群容灾
mysql数据库视频教程

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






上一篇:如何诊断Oracle RAC系统中的等待事件&quot;gc cr multi block request&quot;?
下一篇:查询Oracle正在执行的sql语句及当前被锁对象
189070296,150201289

专业提供Oracle数据库服务、主机、存储、备份、中间件等相关技术支持服务,QQ号:176140749
关注ITPUX技术网微信公众号itpux_com  ,了解本站最新技术资料的分享.

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

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

加群分享视频教程部分如下:

1、公开课视频:Oracle/MySQL数据库工程师职业发展前景讲解(免费)
http://edu.51cto.com/course/7015.html

2、51CTO学院Oracle数据库高级工程师培训(高薪就业.课程介绍)
http://edu.51cto.com/px/train/131?xiaotu

3、Oracle DBA数据库高级工程师培训视频课程1.1(系列78套+七大阶段+上千案例)
套餐视频地址: http://edu.51cto.com/topic/1121.html

4、MySQL数据库(终身门徒)套餐:http://edu.51cto.com/sd/1e1a6

回复

使用道具 举报

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

本版积分规则

快速回复 返回顶部 返回列表