Oracle 11.2.0配置ST_Geometry

 

二、Oracle 11.2.0配置ST_Geometry

1. 环境
   (1) SDE(windows)
   (2) Oracle11g(Linux)
2. 配置
   (1) 将libst_shapelib.so文件上传到/gis/soft/app/oracle/product/11.2.0/db_1/lib
   目录下.      PS：arcgis 10.3或更新版本，没有了sde产品的安装包（也就没sde安装目录），那st_shapelib.dll可以在arcgis desktop或arcgis server的安装目录下找到（具体文件所在位置自行搜索）

[agssvrYX@inmshgis04 ~]$ cp /home/agssvrYX/libst_shapelib.so /gis/soft/app/oracle/product/11.2.0/db_1/lib/

(2) 增加执行权限

[agssvrYX@inmshgis04 ~]$ sudo chmod 777 /gis/soft/app/oracle/product/11.2.0/db_1/lib/libst_shapelib.so 

• (3)进入/gis/soft/app/oracle/product/11.2.0/db_1/hs/admin/

[agssvrYX@inmshgis04 ~]$ cd /gis/soft/app/oracle/product/11.2.0/db_1/hs/admin/
[agssvrYX@inmshgis04 admin]$ cp extproc.ora extproc.ora.bak
[agssvrYX@inmshgis04 admin]$ vi extproc.ora
SET EXTPROC_DLLS=ONLY:/data/app/oracle/product/11.2.0/db_1/lib/libst_shapelib.so

(4) 编辑listener.ora和tnsname.ora文件

[agssvrYX@inmshgis04 db_1]$ cd network/admin/
[agssvrYX@inmshgis04 admin]$ ll
总用量 24
-rw-rw-r-- 1 agssvrYX agssvrYX  668 3月  23 12:31 listener.ora
-rw-rw-r-- 1 agssvrYX agssvrYX  780 3月  23 12:28 listener.ora.bak
drwxr-xr-x 2 agssvrYX agssvrYX 4096 3月  17 18:26 samples
-rw-r--r-- 1 agssvrYX agssvrYX 1441 8月  28 2015 shrept.lst
-rw-rw-r-- 1 agssvrYX agssvrYX  192 3月  17 18:31 sqlnet.ora
-rw-r----- 1 agssvrYX agssvrYX  611 3月  23 17:33 tnsnames.ora
[agssvrYX@inmshgis04 admin]$ vi listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = gismap)
      (ORACLE_HOME = /gis/soft/app/oracle/product/11.2.0/db_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:/gis/soft/app/oracle/product/11.2.0/db_1/lib/libst_shapeli
b.so")
    )
  )
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.130.0.174)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /gis/soft/app/oracle

[agssvrYX@inmshgis04 admin]$ vi tnsname.ora
EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = gismap)
      (PRESENTATION = RO)
    )
  )

GISDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.130.0.174)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = gisdb)
    )
  )

(5) 监听状态更改

[agssvrYX@inmshgis04 admin]$ lsnrctl status
[agssvrYX@inmshgis04 admin]$ lsnrctl stop
[agssvrYX@inmshgis04 admin]$ lsnrctl start
[agssvrYX@inmshgis04 admin]$ lsnrctl reload

(6) 以事先创建好的sde用户登录数据库PS：arcgis 10.3或更新版本，没有了sde产品的安装包（也就没sde安装目录），那st_shapelib.dll可以在arcgis desktop或arcgis server的安装目录下找到（具体文件所在位置自行搜索）

[agssvrYX@inmshgis04 admin]$ sqlplus sde@gisdb

可以尝试检查user_libraries表，ST_SHAPELIB的记录是否存在；

sql > select * from user_libraries;  
说明: 估计这功夫查询是没有记录的；表为空 
在user_libraries表中创建或更新ST_SHAPELIB对应的外部函数库文件所在的路径

sql > create or replace library ST_SHAPELIB  as '/gis/soft/app/oracle/product/11.2.0/db_1/lib/libst_shapelib.so'; 

SQL> select * from user_libraries;

LIBRARY_NAME
--------------------------------------------------------------------------------
FILE_SPEC
--------------------------------------------------------------------------------
DYN STATUS
--- ---------------------
ST_SHAPELIB
/gis/soft/app/oracle/product/11.2.0/db_1/lib/libst_shapelib.so
Y   VALID

编译失效的存储过程
# 编译SDE用户下所有失效的存储过程
sql > exec dbms_utility.compile_schema('SDE');

试验成功效果：
sql > select sde.st_point (10.01, 20.03, 0) from dual;
SDE.ST_POINT(10.01,20.03,0)(GEOM(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_EL
--------------------------------------------------------------------------------
ST_POINT(SDO_GEOMETRY(2001, 0, SDO_POINT_TYPE(10.01, 20.03, NULL), NULL, NULL))