ArcSDE 10 SP2 create user-schema fail on Oracle 11.2.0.x

PaoloFontana — Wed, 12 Oct 2011 08:11:07 GMT

When I try to create a user-schema on ArcSDE 10 SP2 with Oracle 11.2.0.1 or 11.2.0.2 I get the following error:
sdesetup -o install -i 5151:sdeidro -d ORACLE11G -u sdeidro -p sdeidro

ESRI ArcSDE Server Setup Utility Tue Oct 11 14:47:12 2011
----------------------------------------------------------------
Install or update ArcSDE, GDB schema objects: Are you sure? (Y/N): y
Checking INSTALL privileges for geodatabase ...
Current user has privilege to install geodatabase instance.
Checking geodatabase XML datatype support...
Underlying RDBMS database instance supports XML data type.
Creating ArcSDE schema.....
WARNING: Parameter "TEMP" not found in giomgr.defs file!
WARNING: Setting TEMP to "C:\DOCUME~1\a2pf0077\IMPOST~1\Temp"
Successfully created ArcSDE schema.
Installing St_Geometry ....
Successfully installed St_Geometry.
Creating geodatabase schema.....
Error: Underlying DBMS error (-51).
       Error installing GDB schema.
Error: Underlying DBMS error (-51).
       Geodatabase schema object install not completed.
Check SDEHOME\etc\sde_setup.log or
      SDEHOME\etc\sdedc_oracle11g.log for more details.

In sde_setup.log I find:
[Thu Sep 15 13:37:34 2011] Instance initialized for ((sdeidro)) . . .
[Thu Sep 15 13:37:36 2011] Successfully created GDB_Tables_Last_Modified table...
[Thu Sep 15 13:37:43 2011] Error creating GDB_Items table...
[Thu Sep 15 13:37:45 2011] ERROR Creating Geodatabase tables
, Error = -51
,EXT_Error = -29855
,EXT_ERROR1 = Error executing PL/SQL Block db_stgeom_create_index
,EXT_ERROR2 = ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-06502: PL/SQL: numeric or value error: character to number conversion error

Cause:
The database has a regional settings AMERICAN (decimal separator .) and the Operating system has regional settings ITALIAN (decimal separator ,)

I can verify Oracle settings with:
select * from nls_database_parameters where PARAMETER='NLS_NUMERIC_CHARACTERS';

Solution:

I set on prompt dos the as following
set NLS_NUMERIC_CHARACTERS=",."

Then I execute sucessfuly the user-schema creation
sdesetup -o install -i 5151:sdeidro -d ORACLE11G -u sdeidro -p sdeidro

Re: ArcSDE 10 SP2 create user-schema fail on Oracle 11.2.0.x

AntonioSergio — Wed, 09 Nov 2011 12:52:12 GMT

Hi Paolo,

in ArcSDE 10 SP3 if the Oracle Database has a regional settings Western European (decimal separator ,) the layers spatial indexes ignores the decimal separator value. If the layer spatial index value is below one (eg:0,83) the layer is never displayed, if the value is greater then one (eg:1,34) the layer is displayed but it seems the decimal value is ignored, with obvious impact in the display performance.

topic Re: ArcSDE 10 SP2 create user-schema fail on Oracle 11.2.0.x in Data Management Questions

ArcSDE 10 SP2 create user-schema fail on Oracle 11.2.0.x

Re: ArcSDE 10 SP2 create user-schema fail on Oracle 11.2.0.x