arcgis sde ORA-28595: Extproc agent : Invalid DLL Path

[oracle@bddb dmp]$ sqlplus sde/sde@gists

SQL*Plus: Release 11.2.0.4.0 Production on Wed Apr 14 13:14:45 2021

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SELECT sde.st_linestring('LINESTRING(16621.5602000002150232.4605,16844.6043999996 150130.066600001)','2') FROM dual;
SELECT sde.st_linestring('LINESTRING(16621.5602000002150232.4605,16844.6043999996 150130.066600001)','2') FROM dual
       *
ERROR at line 1:
ORA-28595: Extproc agent : Invalid DLL Path
ORA-06512: at "SDE.ST_GEOMETRY_SHAPELIB_PKG", line 12
ORA-06512: at "SDE.ST_LINESTRING", line 58

SQL> select * from user_libraries;

LIBRARY_NAME
------------------------------
FILE_SPEC
--------------------------------------------------------------------------------
D STATUS
- -------
ST_SHAPELIB
/home/oracle/libst_shapelib.so
Y VALID

SQL> !ls -l /home/oracle/libst_shapelib.so
-rw-r--r-- 1 oracle oinstall 5652930 May 15  2010 /home/oracle/libst_shapelib.so

SQL> SELECT object_name FROM user_objects WHERE status = 'INVALID';

OBJECT_NAME
--------------------------------------------------------------------------------
LOCK_UTIL
VERSION_USER_DDL
PINFO_UTIL

SQL> EXECUTE sys.utl_recomp.recomp_serial('SDE');
BEGIN sys.utl_recomp.recomp_serial('SDE'); END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'SYS.UTL_RECOMP' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@bddb dmp]$ export ORACLE_SID=gists
[oracle@bddb dmp]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Apr 14 13:17:08 2021

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> EXECUTE sys.utl_recomp.recomp_serial('SDE');

PL/SQL procedure successfully completed.

SQL> SELECT object_name FROM dba_objects WHERE status = 'INVALID' and owner='SDE';

OBJECT_NAME
--------------------------------------------------------------------------------
PINFO_UTIL
VERSION_USER_DDL
LOCK_UTIL

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@bddb dmp]$ cd $ORACLE_HOME/hs/admin
[oracle@bddb admin]$ ll
total 16
-rw-r--r-- 1 oracle oinstall 1109 Aug 24  2013 extproc.ora
-rw-r--r-- 1 oracle oinstall  489 Aug 24  2013 initdg4odbc.ora
-rw-r--r-- 1 oracle oinstall  403 Apr 12 21:23 listener.ora.sample
-rw-r--r-- 1 oracle oinstall  244 Apr 12 21:23 tnsnames.ora.sample
[oracle@bddb admin]$ vi extproc.ora 

~
"extproc.ora" 38L, 1112C written
[oracle@bddb admin]$ cat extproc.ora 
# 
# extproc.ora is used by extproc in the default Oracle configuration.
#
# This is a sample extproc init file that contains a name-value(s) pair which
# is same as the value of ENVS parameter in listener.ora file.
#
# Syntax: SET name=value (environment variable name and value)
# 
# When specifying the EXTPROC_DLLS environment variable to restrict the DLLs 
# that extproc is allowed to load, you may set EXTPROC_DLLS to one of the 
# following values:
# 
# * ONLY (maximum security) 
#
#   When EXTPROC_DLLS=ONLY:DLL[:DLL], only the specified DLL(s) can be loaded.
# 
#   Syntax: SET EXTPROC_DLLS=ONLY:DLL[:DLL]
#
# * NULL (the default value)
#
#   When EXTPROC_DLLS=, only the DLL(s) in $ORACLE_HOME/bin and ORACLE_HOME/lib
#   can be loaded.
#
#   Syntax: SET EXTPROC_DLLS=
#
# * Colon-seperated list of the DLLs
#
#   When EXTPROC_DLLS=DLL[:DLL], the specified DLL(s) and the DLLs in 
#   $ORACLE_HOME/bin and ORACLE_HOME/lib can be loaded.
# 
#   Syntax: SET EXTPROC_DLLS=DLL:DLL
#
# * ANY 
#   When EXTPROC_DLLS=ANY, DLL checking is disabled.
# 
#   Syntax: SET EXTPROC_DLLS=ANY
#
SET EXTPROC_DLLS=ANY
[oracle@bddb admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Apr 14 13:19:23 2021

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> EXECUTE sys.utl_recomp.recomp_serial('SDE');

PL/SQL procedure successfully completed.

SQL> SELECT object_name FROM user_objects WHERE status = 'INVALID';

no rows selected

SQL> SELECT object_name FROM dba_objects WHERE status = 'INVALID' and owner='SDE';

OBJECT_NAME
--------------------------------------------------------------------------------
PINFO_UTIL
VERSION_USER_DDL
LOCK_UTIL

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@bddb admin]$ sqlplus sde/sde@gists

SQL*Plus: Release 11.2.0.4.0 Production on Wed Apr 14 13:19:59 2021

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select sde.ST_AsText(SDE.ST_Geometry('POINT (10 10)', 0)) from dual;

SDE.ST_ASTEXT(SDE.ST_GEOMETRY('POINT(1010)',0))
--------------------------------------------------------------------------------
POINT  ( 10.00000000 10.00000000)

SQL> Grant execute on dbms_pipe to sde;
Grant execute on dbms_pipe to sde
                 *
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> conn /as sysdba
Connected.
SQL> Grant execute on dbms_pipe to sde;

Grant succeeded.

SQL> Grant execute on dbms_lock to sde;

Grant succeeded.

SQL> EXECUTE sys.utl_recomp.recomp_serial('SDE');

PL/SQL procedure successfully completed.

SQL> SELECT object_name FROM dba_objects WHERE status = 'INVALID' and owner='SDE';

no rows selected

SQL> 
SQL> select sde.ST_AsText(SDE.ST_Geometry('POINT (10 10)', 0)) from dual;

SDE.ST_ASTEXT(SDE.ST_GEOMETRY('POINT(1010)',0))
--------------------------------------------------------------------------------
POINT  ( 10.00000000 10.00000000)

SQL> 

总结命令:

cd $ORACLE_HOME/hs/admin
vi extproc.ora 
SET EXTPROC_DLLS=ANY

conn /as sysdba
Grant execute on dbms_pipe to sde;
Grant execute on dbms_lock to sde;
EXECUTE sys.utl_recomp.recomp_serial('SDE');

SELECT object_name FROM dba_objects WHERE status = 'INVALID' and owner='SDE';
conn sde/sde
select sde.ST_AsText(SDE.ST_Geometry('POINT (10 10)', 0)) from dual;