Spatial DBA - Oracle and ArcSDE: 2007

Sunday, December 30, 2007

ORA-13032

ORA-13032: Invalid NULL SDO_GEOMETRY object

Cause: There are invalid SDO_POINT_TYPE or SDO_ELEM_INFO_ARRAY or SDO_ORDINATE_ARRAY fields in the SDO_GEOMETRY object.

Action: Verify that the geometries have valid fields. To specify a NULL geometry, specify the whole SDO_GEOMETRY as NULL instead of setting each field to NULL.

Note: Oracle Database Error Messages 10g Release 2 (10.2)

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-13031

ORA-13031: Invalid Gtype in the SDO_GEOMETRY object for point object

Cause: There is an invalid SDO_GTYPE in the SDO_GEOMETRY object where the VARRAYs are NULL but the SDO_GTYPE is not of type POINT.

Action: Verify that the geometries have valid gtypes.

Note: Oracle Database Error Messages 10g Release 2 (10.2)

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-13030

ORA-13030: Invalid dimension for the SDO_GEOMETRY object

Cause: There is a mismatch between the dimension in the SDO_GTYPE and dimension in the SDO_GEOM_METADATA for the SDO_GEOMETRY object.

Action: Verify that the geometries have valid dimensionality.

Note: Oracle Database Error Messages 10g Release 2 (10.2)

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-13029

ORA-13029: Invalid SRID in the SDO_GEOMETRY object

Cause: There is an invalid SDO_SRID in the SDO_GEOMETRY object. The specified SRID may be outside the valid SRID range.

Action: Verify that the geometries have valid SRIDs.

Note: Oracle Database Error Messages 10g Release 2 (10.2)

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-13028

ORA-13028: Invalid Gtype in the SDO_GEOMETRY object

Cause: There is an invalid SDO_GTYPE in the SDO_GEOMETRY object.

Action: Verify that the geometries have valid gtypes.

Note: Oracle Database Error Messages 10g Release 2 (10.2)

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-13027

ORA-13027: unable to read dimension definition from string

Cause: There was a problem reading the dimension definition from the _SDODIM table.

Action: Verify that the _SDODIM table exists and that the appropriate privileges exist on the table. Address any other errors that might appear with the message.

Note: Oracle Database Error Messages 10g Release 2 (10.2)

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-13026

ORA-13026: unknown element type for element string.string.string

Cause: The SDO_ETYPE column in the _SDOGEOM table contains an invalid geometric element type value.

Action: Redefine the geometric element type in the _SDOGEOM table for the specified geometric element using one of the supported SDO_ETYPE values. See the Oracle Spatial documentation for an explanation of SDO_ETYPE and its possible values.

Note: Oracle Database Error Messages 10g Release 2 (10.2)

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-13025

ORA-13025: polygon does not close

Cause: The coordinates defining a polygonal geometric element represent an open polygon.

Action: Redefine the coordinates of the polygon.

Note: Oracle Database Error Messages 10g Release 2 (10.2)

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-13024

ORA-13024: polygon has less than three segments

Cause: The coordinates defining a polygonal geometric element represent less than three segments.

Action: Redefine the coordinates for the polygon.

Note: Oracle Database Error Messages 10g Release 2 (10.2)

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-13023

ORA-13023: interior element interacts with exterior element

Cause: An interior element of a geometric object interacts with the exterior element of that object.

Action: Redefine coordinates for the geometric elements.

Note: Oracle Database Error Messages 10g Release 2 (10.2)

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-13022

ORA-13022: polygon crosses itself

Cause: The coordinates defining a polygonal geometric element represent crossing segments.

Action: Redefine coordinates for the polygon.

Note: Oracle Database Error Messages 10g Release 2 (10.2)

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-13021

ORA-13021: element not continuous

Cause: The coordinates defining a geometric element are not connected.

Action: Redefine coordinates for the geometric element.

Note: Oracle Database Error Messages 10g Release 2 (10.2)

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-13020

ORA-13020: coordinate is NULL

Cause: A vertex coordinate has a NULL value.

Action: Redefine vertex coordinate to have non-NULL value.

Note: Oracle Database Error Messages 10g Release 2 (10.2)

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-13019

ORA-13019: coordinates out of bounds

Cause: Vertex coordinates lie outside the valid range for specified dimension.

Action: Redefine vertex coordinates within specified boundaries.

Note: Oracle Database Error Messages 10g Release 2 (10.2)

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-13017

ORA-13017: unrecognized line partition shape

Cause: The shape of a 2-D line partition could not be determined.

Action: This is an internal error. Contact Oracle Support Services.

Note: Oracle Database Error Messages 10g Release 2 (10.2)

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-13016

ORA-13016: specified topology [string] is invalid

Cause: The specified topology did not exist in the database, or some components of the topology were missing from the database.

Action: Check the specified topology by executing the SDO_TOPO.validate_topology function.

Note: Oracle Database Error Messages 10g Release 2 (10.2)

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-13014

ORA-13014: a topology identifier outside the range of 1 to 8 was specified

Cause: A topology identifier outside the range of 1 to 8 was specified.

Action: Specify a topology in the range of 1 to 8.

Note: Oracle Database Error Messages 10g Release 2 (10.2)

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-13013

ORA-13013: the specified topology was not INTERIOR or BOUNDARY

Cause: A topology was specified that was not INTERIOR or BOUNDARY.

Action: Make sure that INTERIOR or BOUNDARY is used to describe an HHCODE’s topology.

Note: Oracle Database Error Messages 10g Release 2 (10.2)

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-13012

ORA-13012: an invalid window type was specified

Cause: An invalid window type was specified.

Action: Valid window types are RANGE, PROXIMITY, POLYGON.

Note: Oracle Database Error Messages 10g Release 2 (10.2)

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-13011

Thank you for visiting Spatial DBA - Oracle and ArcSDE.

Please visit Oracle DBA Tips (http://www.oracledbatips.com) for more Oracle DBA Tips.

===================================================================

ORA-13011: value is out of range

Cause: A specified dimension value is outside the range defined for that
dimension.

Action: Make sure that all values to be encoded are within the defined dimension range.

Note: Oracle Database Error Messages 10g Release 2 (10.2)

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-13010

ORA-13010: an invalid number of arguments has been specified

Cause: An invalid number of arguments was specified for an SDO function.
Action: Verify the syntax of the function call.

Note: Oracle Database Error Messages 10g Release 2 (10.2)

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

Thursday, December 27, 2007

Oracle errors

I compiled a list of Oracle errors I encountered while I worked with Oracle database. Some of the Oracle errors appeared in ArcSDE sde log file and giomgr log file. So I put them together in one post.

ORA-13032
ORA-13031
ORA-13030
ORA-13029
ORA-13028
ORA-13027
ORA-13026
ORA-13025
ORA-13024
ORA-13023
ORA-13022
ORA-13021
ORA-13020
ORA-13019
ORA-13017
ORA-13016
ORA-13014
ORA-13013
ORA-13012
ORA-13011
ORA-13010
ORA-28000
ORA-13226
ORA-06550
ORA-04063
ORA-04044
ORA-04031
ORA-03135
ORA-03127
ORA-03114
ORA-03106
ORA-02441
ORA-02391
ORA-02292
ORA-02275
ORA-02266
ORA-02264
ORA-02260
ORA-02201
ORA-02035
ORA-02019
ORA-01795
ORA-01950
ORA-01917
ORA-01861
ORA-01858
ORA-01847
ORA-01843
ORA-01779
ORA-01775
ORA-01752
ORA-01749
ORA-01747
ORA-01927
ORA-01756
ORA-01758
ORA-01722
ORA-01720
ORA-01659
ORA-01658
ORA-01653
ORA-01652
ORA-01650
ORA-01543
ORA-01536
ORA-01502
ORA-01455
ORA-01441
ORA-01427
ORA-01405
ORA-01403
ORA-01400
ORA-01149
ORA-01119
ORA-01089
ORA-01036
ORA-01034
ORA-01031
ORA-01008
ORA-01003
ORA-01000
ORA-00980
ORA-00959
ORA-00947
ORA-00942
ORA-00936
ORA-00933
ORA-00923
ORA-00920
ORA-00918
ORA-00917
ORA-00908
ORA-00907
ORA-00904
ORA-00903
ORA-00604
ORA-00439
ORA-00054
ORA-00001

All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

More Oracle DBA tips, please visit Oracle DBA Tips

ArcSDE Errors

ORA-28000

Thank you for visiting Spatial DBA - Oracle and ArcSDE.

Please visit Oracle DBA Tips (http://www.oracledbatips.com) for more Oracle DBA Tips.

====================================================================

ORA-28000: the account is locked

Cause: The user has entered wrong password consequently for maximum number of times specified by the user's profile parameter FAILED_LOGIN_ATTEMPTS, or the DBA has locked the account

Action: Wait for PASSWORD_LOCK_TIME or contact DBA

Sde.log:
[05/07/2007 15:29:47;SdeId=0;Client=GIOMGR] Error (-51):Couldn't Start Server Task.
DB_open_instance()::db_connect (OCI8) error: 28000
CAN'T OPEN INSTANCE: ESRI_SDE.

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-13226

ORA-13226: interface not supported without a spatial index

Cause: The geometry table does not have a spatial index.

Action: Verify that the geometry table referenced in the spatial operator has a spatial index on it.

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-03127

ORA-03127: no new operations allowed until the active operation ends

Cause: An attempt was made to execute a new operation before the active non-blocking operation completed or a new operation was attempted before all the pieces of a column were inserted or fetched.

Action: Execute the new operation after the non-blocking operation completes. If piecewise binds/defines were done, execute the new operation after all the pieces have been inserted or fetched.

sde log:
db_array_fetch_attrs OCI Fetch Error (3127)
load_buffer error -51
db_sda_execute_stmt::OCIStmtExecute (3127)

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-03114

ORA-03114: not connected to ORACLE

Cause: A call to Oracle was attempted when no connection was established. Usually this happens because a user-written program has not logged on. It may happen if communication trouble causes a disconnection. In addition, this message could occur when ALTER SYSTEM KILL SESSION or ALTER SYSTEM DISCONNECT SESSION were issued with the IMMEDIATE qualifier because, in those cases, the client's connection to the database is terminated without waiting for the client to issue a request.

Action: Try again. If the message recurs and the program is user written, check the program.

sde log:
Describe Cache Table [SDE.GDB_ObjectClasses] Reg_Id [4] NumCols [2]
[02/28/2006 12:28:18;SdeId=432;Client=GISPC] SDE_Oracle Error: 3114 ORA-03114: not connected to ORACLE
[02/28/2006 12:28:18;SdeId=432;Client= GISPC] db_array_fetch_attrs OCI Fetch Error (3114)
[02/28/2006 12:28:18;SdeId=432;Client= GISPC] SDE_Oracle Error: 3114 ORA-03114: not connected to ORACLE

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-03106

Thank you for visiting Spatial DBA - Oracle and ArcSDE.

Please visit Oracle DBA Tips (http://www.oracledbatips.com) for more Oracle DBA Tips.

===================================================================

ORA-03106: fatal two-task communication protocol error

Cause: The communication path between Oracle and the user task has stopped. This is an internal error message not usually issued.

Action: Contact Oracle Support Services.

Note: There is an ESRI whitepaper about this error.
Subject: Converting from LONG RAW to BLOB in an ArcSDE for Oracle geodatabase
http://support.esri.com/index.cfm?fa=knowledgebase.whitepapers.viewPaper&PID=66&MetaID=1272

The errors can be seen in the sde error log (with SDEVERBOSE turned on).

[Thu Jul 06 12:39:27 2006] [1360] [olive] db_sda_execute_stmt::OCIStmtExecute (3106)
.[Thu Jul 06 12:39:27 2006] [1360] [olive] SDE_Oracle Error: 3106 ORA-03106: fatal two-task communication protocol error
[Thu Jul 06 12:39:27 2006] [1360] [olive] db_array_fetch_spix_recs OCI Fetch Error (3106)
[Thu Jul 06 12:39:27 2006] [1360] [olive] SDE_Oracle Error: 3106 ORA-03106: fatal two-task communication protocol error
[Thu Jul 06 12:39:27 2006] [1360] [olive] db_get_spix_fidlist Fetch Error

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-02441

ORA-02441: Cannot drop nonexistent primary key

Cause: alter table drop primary key - primary key does not exist.

Action: None

Note: get the error in sde.log when deleting a layer without a primary key in ArcCatalog
db_sda_execute_stmt::OCIStmtExecute (2441)

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-02391

Thank you for visiting Spatial DBA - Oracle and ArcSDE.

Please visit Oracle DBA Tips (http://www.oracledbatips.com) for more Oracle DBA Tips.
==================================================================

ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit

Cause: An attempt was made to exceed the maximum number of concurrent sessions allowed by the SESSION_PER_USER clause of the user prfile.

Action: End one or more concurrent sessions or ask the database administrator to increase the SESSION_PER_USER limit of the user profile.

Sde.log:
Instance initialized for GISUSER . . .
DB_open_instance()::db_connect (OCI8) error: 2391
CAN'T OPEN INSTANCE: ESRI_SDE.
Spatial Engine Connection Failed (-51).
Cannot Get Access to Instance ESRI_SDE

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-02292

ORA-02292: integrity constraint violated - child record found

Cause: You tried to DELETE a record from a parent table (as referenced by a foreign key), but a record in the child table exists.

Action: The options to resolve this Oracle error are:
This error commonly occurs when you have a parent-child relationship established between two tables through a foreign key. You then have tried to delete a value into the parent table, but the corresponding value exists in the child table.
To correct this problem, you need to update or delete the value into the child table first and then you can delete the corresponding value into the parent table.

Sde.log:
db_sda_execute_stmt::OCIStmtExecute (2292)

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-02275

ORA-02275: such a referential constraint already exists in the table

Cause: Self-evident.

Action: Remove the extra constraint.

Sde.log:
db_sda_execute_stmt::OCIStmtExecute (2275)

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-02266

ORA-02266: unique/primary keys in table referenced by enabled foreign keys

Cause: An attempt was made to truncate a table with unique or primary keys referenced by foreign keys enabled in another table. Other operations not allowed are dropping/truncating a partition of a partitioned table or an ALTER TABLE EXCHANGE PARTITION.

Action: Before performing the above operations the table, disable the foreign key constraints in other tables. You can see what constraints are referencing a table by issuing the following command:

SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = "tabnam";

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-02264

Thank you for visiting Spatial DBA - Oracle and ArcSDE.

I have stopped updating the blog.

Please visit Oracle DBA Tips (http://www.oracledbatips.com) for more Oracle DBA Tips.
==================================================================

ORA-02264: name already used by an existing constraint

Cause: The specified constraint name has to be unique.

Action: Specify a unique constraint name for the constraint

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-02260

Thank you for visiting Spatial DBA - Oracle and ArcSDE.

I have stopped updating the blog.

Please visit Oracle DBA Tips (http://www.oracledbatips.com) for more Oracle DBA Tips.
==================================================================

ORA-02260: table can have only one primary key

Cause: Self-evident.

Action: Remove the extra primary key.

Sde.log:
db_sda_execute_stmt::OCIStmtExecute (2260)

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-02035

ORA-02035: illegal bundled operation combination

Cause: User requested that the UPI bundled execution call perform an illegal combination of operations.

Action: See documentation for legal operation combinations.

Note: There are some discussions about this error in the following link:
Subject: -2035 is a low-level "self-intersecting" error
http://forums.esri.com/Thread.asp?c=2&f=1720&t=152985&mc=4

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-01795

ORA-01795: maximum number of expressions in a list is 1000

Cause: More than 254 columns or expressions were specified in a list.

Action: Remove some of the expressions from the list.

Sde.log:
Instance initialized for GISUSER . . .
db_sda_execute_stmt::OCIStmtExecute (1795)

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-01950

ORA-01950: no privileges on tablespace "name"

Cause: You tried to give the user a tablespace quota, but it failed because the user does not have the necessary system privileges. Most likely, the user has no quota granted on the tablespace in which he wants to create a table or index.

Action: The options to resolve this Oracle error are:
Grant the user the necessary system privileges to create objects in the tablespace.
Grant the user a specific space resource in the tablespace.

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-01917

Thank you for visiting Spatial DBA - Oracle and ArcSDE.

I have stopped updating the blog.

Please visit Oracle DBA Tips (http://www.oracledbatips.com) for more Oracle DBA Tips.
==================================================================

ORA-01917: user or role 'string' does not exist

Cause: There is not a user or role by that name.

Action: Re-specify the name.

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-01843

Thank you for visiting Spatial DBA - Oracle and ArcSDE.

Please visit Oracle DBA Tips (http://www.oracledbatips.com) for more Oracle DBA Tips.
==================================================================

ORA-01843: not a valid month

Cause: A date specified an invalid month. Valid months are: January-December, for format code MONTH, and Jan-Dec, for format code MON.

Action: Enter a valid month value in the correct format.

Sde.log:
Instance initialized for csat . . .
db_sda_execute_stmt::OCIStmtExecute (1405)
.
db_sda_execute_stmt::OCIStmtExecute (1843)
.
[04/02/2007 12:56:26;SdeId=625;Client=GISPC] db_array_fetch_attrs OCI Fetch Error (1843)
[04/02/2007 12:56:26;SdeId=625;Client= GISPC] load_buffer error -51
db_sda_execute_stmt::OCIStmtExecute (1843)
.
[04/02/2007 12:56:37;SdeId=625;Client= GISPC] db_array_fetch_attrs OCI Fetch Error (1843)
[04/02/2007 12:56:37;SdeId=625;Client= GISPC] load_buffer error -51
db_sda_execute_stmt::OCIStmtExecute (936)
.
[04/02/2007 13:19:34;SdeId=625;Client= GISPC] db_array_fetch_attrs OCI Fetch Error (936)
[04/02/2007 13:19:34;SdeId=625;Client= GISPC] load_buffer error -51

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-01779

ORA-01779: cannot modify a column which maps to a non key-preserved table

Cause: An attempt was made to insert or update columns of a join view which map to a non-key-preserved table.

Action: Modify the underlying base tables directly.

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-01775

ORA-01775: looping chain of synonyms

Cause: Through a series of CREATE synonym statements, a synonym was defined that referred to itself. For example, the following definitions are circular:
CREATE SYNONYM s1 for s2 CREATE SYNONYM s2 for s3 CREATE SYNONYM s3 for s1

Action: Change one synonym definition so that it applies to a base table or view and retry the operation.

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-01752

ORA-01752: cannot delete from view without exactly one key-preserved table

Cause: The deleted table either had no key perserved tables, had more than one key-preserved table, or the key-preserved table was an unmerged view or a table from a read-only view.

Action: Redefine the view or delete it from the underlying base tables.

ORA-01749

Thank you for visiting Spatial DBA - Oracle and ArcSDE.

I have stopped updating the blog.

Please visit Oracle DBA Tips (http://www.oracledbatips.com) for more Oracle DBA Tips.

====================================================================

ORA-01749: you may not GRANT/REVOKE privileges to/from yourself

Cause: Grantor is not allowed to grant or revoke object or system privileges to self.

Action: Issue the GRANT or REVOKE of system privileges from another database administrator account.

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-01747

ORA-01747: invalid user.table.column, table.column, or columns specification

Cause: You tried to reference a column name, but the column name used is a reserved word in Oracle.

Action:
The options to resolve this Oracle error are:
Try redefining your table so that none of your column names are reserved words.
Try enclosing the reserved word in double quotes.
For example, if you had a supplier table with a column named number, and you
tried to update this field as follows:
UPDATE suppliers
SET number = 10000;
You would receive the following error message.
You could correct this error by enclosing the column name in double quotes as follows:
UPDATE suppliers
SET "number" = 10000;

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-01927

ORA-01927: cannot REVOKE privileges you did not grant

Cause: You can only revoke privileges you granted.

Action: Don't revoke these privileges.

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-01756

ORA-01756: quoted string not properly terminated

Cause: A quoted string must be terminated with a single quote mark (').

Action: Insert the closing quote and retry the statement.

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-01758

ORA-01758: table must be empty to add mandatory (NOT NULL) column

Cause: It is not possible to define a new column as NOT NULL if rows already exist in the table being modified.

Action: Retry the statement without the NOT NULL specification.

Note: ESRI Technical Article 26456
http://support.esri.com/index.cfm?fa=knowledgebase.techarticles.articleShow&d=26456

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-01722

ORA-01722: invalid number

Cause: The attempted conversion of a character string to a number failed because the character string was not a valid numeric literal. Only numeric fields or character fields containing numeric data may be used in arithmetic functions or expressions. Only numeric fields may be added to or subtracted from dates.

Action: Check the character strings in the function or expression. Check that they contain only numbers, a sign, a decimal point, and the character "E" or "e" and retry the operation.

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-01720

ORA-01720: grant option does not exist for "string.string"

Cause: A grant was being performed on a view and the grant option was not present for an underlying object.

Action: Obtain the grant option on all underlying objects of the view.

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-01658

ORA-01658: unable to create INITIAL extent for segment in tablespace string

Cause: Failed to find sufficient contiguous space to allocate INITIAL extent for segment being created.

Action: Use ALTER TABLESPACE ADD DATAFILE to add additional space to the tablespace or retry with a smaller value for INITIAL

Note: That error is a "disk full" message, which will require the DBA to correct, either by increasing the tablespace allocation or specifying a different default tablespace.

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-01653

ORA-01653: unable to extend table string.string by string in tablespace string

Cause: Failed to allocate an extent of the required number of blocks for a table segment in the tablespace indicated.

Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

sde.log:
db_sda_execute_stmt::OCIStmtExecute (1653)

giomgr.log:
Tue Feb 13 09:50:41 2007 - SDE Server Pid 17028 Registered, User: GISUSER.
Tue Feb 13 09:58:31 2007 - SDE Server 17028 went down on signal 11
Tue Feb 13 09:58:31 2007 - SDE Server Pid 17028 Stopped, User: GISUSER.

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-01652

ORA-01652: unable to extend temp segment by string in tablespace string

Cause: Failed to allocate an extent of the required number of blocks for a temporary segment in the tablespace indicated.

Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

Note: When you try to create a new table or index in a tablespace, it is first created as a temporary segment inside the tablespace before the actual segment is created. There was not enough space left inside the tablespace to successfully complete the creation

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-01536

ORA-01536: space quota exceeded for tablespace "string"

Cause: The space quota for the segment owner in the tablespace has been exhausted and the operation attempted the creation of a new segment extent in the tablespace.

Action: Either drop unnecessary objects in the tablespace to reclaim space or have a privileged user increase the quota on this tablespace for the segment owner.

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-01455

ORA-01455: converting column overflows integer datatype

Cause: The converted form of the specified expression was too large for the specified datatype.

Action: Define a larger datatype or correct the data.

Sde.log: try to set objectid > 2 147 483 647
[03/13/2007 11:24:43;SdeId=33417;Client=GISUSER] db_array_fetch_spix_recs OCI Fetch Error (1455)
[03/13/2007 11:24:43;SdeId=33417;Client=GISUSER] load_buffer error -51
db_sda_execute_stmt::OCIStmtExecute (1455)

ORA-01427

ORA-01427: single-row subquery returns more than one row

Cause:
You tried to execute an SQL statement that contained a subquery that returns more than one row.

Action:
The options to resolve this Oracle error are:
Rewrite your query so that the subquery only returns one row.
Change your query to use one of the following functions against your subquery results:
ANY, ALL, IN, NOT IN

sde log:
db_array_fetch_attrs OCI Fetch Error (1427)
load_buffer error -51

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-01405

Thank you for visiting Spatial DBA - Oracle and ArcSDE.

Please visit Oracle DBA Tips (http://www.oracledbatips.com) for more Oracle DBA Tips.

===================================================================

ORA-01405: fetched column value is NULL

Cause:
The INTO clause of a FETCH operation contained a NULL value, and no indicator was used. The column buffer in the program remained unchanged, and the cursor return code was +2. This is an error unless you are running Oracle with DBMS=6, emulating version 6, in which case it is only a warning.

Action:
You may do any of the following:
-Use the NVL function to convert the retrieved NULL to another value, such as zero or blank. This is the simplest solution.
-Use an indicator to record the presence of the NULL. You probably should use this option when you want a specific action to be taken when a NULL arises.
-Revise the cursor definition so that no columns possibly containing NULL values are retrieved.

sde.log: db_sda_execute_stmt::OCIStmtExecute (1405)

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-01403

ORA-01403: no data found

Cause:
You tried one of the following:
You executed a SELECT INTO statement and no rows were returned.
You referenced an uninitialized row in a table.
You read past the end of file with the UTL_FILE package.

Action:
The options to resolve this Oracle error are:
Terminate processing of the data.

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-01400

ORA-01400: cannot insert NULL into (string)

Cause:
An attempt was made to insert a NULL into the column "USER"."TABLE"."COLUMN".

Action:
Retry the operation with a value other than NULL.

SDE.log
Instance initialized for GISUSER . . .
[02/15/2007 14:19:35;SdeId=3470990;Client=GISPC] Attempting to reset ids for layer 1008.
db_sda_execute_stmt::OCIStmtExecute (1400)
.
[02/15/2007 14:19:36;SdeId=3470990;Client=GISPC] DB_execute_insert_table OCI Execute Error (1400).

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-01036

ORA-01036: illegal variable name/number

Cause: Unable to find bind context on user side

Action: Make sure that the variable being bound is in the sql statement.

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-01034

ORA-01034: ORACLE not available

Cause:
Oracle was not started up. Possible causes include the following:
- The SGA requires more space than was allocated for it.
- The operating-system variable pointing to the instance is improperly defined.

Action:
Refer to accompanying messages for possible causes and correct the problem mentioned in the other messages. If Oracle has been initialized, then on some operating systems, verify that Oracle was linked correctly. See the platform specific Oracle documentation.

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-01031

ORA-01031: insufficient privileges

Cause: An attempt was made to change the current username or password without the appropriate privilege. This error also occurs if attempting to install a database without the necessary operating system privileges. When Trusted Oracle is configure in DBMS MAC, this error may occur if the user was granted the necessary privilege at a higher label than the current login.

Action: Ask the database administrator to perform the operation or grant the required privileges. For Trusted Oracle users getting this error although granted the the appropriate privilege at a higher label, ask the database administrator to regrant the privilege at the appropriate label.

Sde.log:
[03/07/2007 11:11:06;SdeId=33266;Client=GISPC] DB_table_create_view() Execute Error (1031)

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-01008

ORA-01008: not all variables bound

Cause: You tried to execute an SQL statement that contained substitution variables where all variables were not bound.

Action: The options to resolve this Oracle error are:
In OCI, try using an OBIND or OBINDN call to substitute the values.

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-01003

ORA-01003: no statement parsed

Cause: A host language program call referenced a cursor with no associated parsed SQL statement. A SQL call (for example, OSQL3) must be used to pass a SQL statement to Oracle and to associate the statement with an open cursor. A cursor must already have an associated SQL statement if referenced in any of the following calls: DESCRIBE, NAME, DEFINE, BIND, EXECUTE, and FETCH.

Action: Do the SQL call, for example, OSQL, to pass the required SQL statement before referencing the cursor.

Sde log:
db_sda_execute_stmt::OCIStmtExecute (1003)

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-01000

ORA-01000: maximum open cursors exceeded

Cause: A host language program attempted to open too many cursors. The initialization parameter OPEN_CURSORS determines the maximum number of cursors per user.

Action: Modify the program to use fewer cursors. If this error occurs often, shut down Oracle, increase the value of OPEN_CURSORS, and then restart Oracle.

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-00959

ORA-00959: tablespace 'string' does not exist

Cause: A statement specified the name of a tablespace that does not exist.

Action: Enter the name of an existing tablespace. For a list of tablespace names, query the data dictionary.

ArcSDE:
shp2sde returns the following error without using -k option:
SDE code 959: *** INVALID_ERROR_CODE ***

The reason is that in $SDEHOME/etc/dbtune.sde, in the #DEFAULTS keyword section, one of the tablespace names is not spelled correctly, that is, it does not match the name of the desired tablespace name.

Refer to http://support.esri.com/index.cfm?fa=knowledgebase.techarticles.articleShow&d=13763

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-00947

ORA-00947: not enough values

Cause: This error occurs when a SQL statement requires two sets of values equal in number, but the second set contains fewer items than the first set. This can occur in a WHERE or HAVING clause in which a nested SELECT returns too few columns as in WHERE (A,B) IN (SELECT C FROM ...). Another common cause of this error is an INSERT statement in which the VALUES or SELECT clause does not contain enough values needed for the INSERT, as in INSERT INTO EMP(EMPNO,ENAME) VALUES('JONES')

Action: Check the number of items in each set and change the SQL statement to make them equal.

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-00942

ORA-00942: table or view does not exist

Cause: The table or view entered does not exist, a synonym that is not allowed here was used, or a view was referenced where a table is required. Existing user tables and views can be listed by querying the data dictionary. Certain privileges may be required to access the table. If an application returned this message, the table the application tried to access does not exist in the database, or the application does not have access to it.

Action: Check each of the following:
the spelling of the table or view name.
that a view is not specified where a table is required.
that an existing table or view name exists.
Contact the database administrator if the table needs to be created or if user or application privileges are required to access the table.
Also, if attempting to access a table or view in another schema, make certain the correct schema is referenced and that access to the object is granted.

ArcSDE:
Verify whether the reported tables or view really exist or not. If not, remove those entries from the table TABLE_REGISTRY and see if you are still getting the error.

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

More Oracle DBA tips, please visit Oracle DBA Tips

ORA-00936

ORA-00936: missing expression

Cause: A required part of a clause or expression has been omitted. For example, a SELECT statement may have been entered without a list of columns or expressions or with an incomplete expression. This message is also issued in cases where a reserved word is misused, as in SELECT TABLE.

Action: Check the statement syntax and specify the missing component.

Sde.log
Instance initialized for GISUSER . . .
db_sda_execute_stmt::OCIStmtExecute (936)
.
[03/27/2007 13:31:22;SdeId=4107103;Client=GISPC] db_array_fetch_spix_recs OCI Fetch Error (936)
[03/27/2007 13:31:22;SdeId=4107103;Client=GISPC] load_buffer error -51

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-00933

ORA-00933: SQL command not properly ended

Cause: The SQL statement ends with an inappropriate clause. For example, an ORDER BY clause may have been included in a CREATE VIEW or INSERT statement. ORDER BY cannot be used to create an ordered view or to insert in a certain order.

Action: Correct the syntax by removing the inappropriate clauses. It may be possible to duplicate the removed clause with another SQL statement. For example, to order the rows of a view, do so when querying the view and not when creating it. This error can also occur in SQL*Forms applications if a continuation line is indented. Check for indented lines and delete these spaces.

ORA-00923

ORA-00923: FROM keyword not found where expected

Cause: In a SELECT or REVOKE statement, the keyword FROM was either missing, misplaced, or misspelled. The keyword FROM must follow the last selected item in a SELECT statement or the privileges in a REVOKE statement.

Action: Correct the syntax. Insert the keyword FROM where appropriate. The SELECT list itself also may be in error. If quotation marks were used in an alias, check that double quotation marks enclose the alias. Also, check to see if a reserved word was used as an alias.

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-00920

ORA-00920: invalid relational operator

Cause: You tried to execute an SQL statement, but the WHERE clause contained an invalid relational operator.

Action: The options to resolve this Oracle error are:
Correct the WHERE clause. Valid relational operators are as follows:
= != ^= <> < <= > >= ALL ANY
BETWEEN NOT BETWEEN EXISTS NOT EXISTS IN NOT IN IS NULL
IS NOT NULL LIKE NOT LIKE

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-00918

ORA-00918: column ambiguously defined

Cause: A column name used in a join exists in more than one table and is thus referenced ambiguously. In a join, any column name that occurs in more than one of the tables must be prefixed by its table name when referenced. The column should be referenced as TABLE.COLUMN or TABLE_ALIAS.COLUMN. For example, if tables EMP and DEPT are being joined and both contain the column DEPTNO, then all references to DEPTNO should be prefixed with the table name, as in EMP.DEPTNO or E.DEPTNO.

Action: Prefix references to column names that exist in multiple tables with either the table name or a table alias and a period (.), as in the examples above.

sde.log
Instance initialized for GISUSER . . .
db_sda_execute_stmt::OCIStmtExecute (918)
.
[10/23/2007 10:26:57;SdeId=5256998;Client=GISPC] db_array_fetch_spix_recs OCI Fetch Error (918)
[10/23/2007 10:26:57;SdeId=5256998;Client=GISPC] load_buffer error -51
db_sda_execute_stmt::OCIStmtExecute (918)

ORA-00917

ORA-00917: missing comma

Cause: A required comma has been omitted from a list of columns or values in an INSERT statement or a list of the form ((C,D),(E,F), ...).

Action: Correct the syntax.

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-00908

ORA-00908: missing NULL keyword

Cause: You tried to execute an SQL statement, but you missed entering the NULL keyword.

Action: This error can occur if you try to execute an SQL statement using the IS NULL or IS NOT NULL clause, but miss entering the NULL keyword.

sde log:
db_array_fetch_attrs OCI Fetch Error (908)
load_buffer error -51

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-00904

ORA-00904: string: invalid identifier

Cause: The column name entered is either missing or invalid.

Action: Enter a valid column name. A valid column name must begin with a letter, be less than or equal to 30 characters, and consist of only alphanumeric characters and the special characters $, _, and #. If it contains other characters, then it must be enclosed in double quotation marks. It may not be a reserved word.

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-00903

ORA-00903: invalid table name

Cause: You tried to execute an SQL statement that included an invalid table name or the table name does not exist.

Action: The options to resolve this Oracle error are:
Rewrite your SQL to include a valid table name. To be a valid table name the following criteria must be met:
The table name must begin with a letter.
The table name can not be longer than 30 characters.
The table name must be made up of alphanumeric characters or the following special characters: $, _, and #.
The table name can not be a reserved word.

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-00604

ORA-00604: error occurred at recursive SQL level string

Cause: An error occurred while processing a recursive SQL statement (a statement applying to internal dictionary tables).

Action: If the situation described in the next error on the stack can be corrected, do so; otherwise contact Oracle Support.

Sde log:
db_sda_execute_stmt::OCIStmtExecute (604)

Note: Oracle® Database Error Messages 10g Release 2 (10.2)

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-00054

ORA-00054: resource busy and acquire with NOWAIT specified

Cause: Resource interested is busy.

Action: Retry if necessary.

Sde log:
db_sda_execute_stmt::OCIStmtExecute (54)

MetaLink:
Note:18245.1

Note: Oracle® Database Error Messages 10g Release 2 (10.2)

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

ORA-00001

ORA-00001: unique constraint (string.string) violated

Cause: An UPDATE or INSERT statement attempted to insert a duplicate key. For Trusted Oracle configured in DBMS MAC mode, you may see this message if a duplicate entry exists at a different level.

Action: Either remove the unique restriction or do not insert the key.

Note: Oracle® Database Error Messages 10g Release 2 (10.2)

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

Monday, December 24, 2007

ArcSDE Administration Commands

Sometime ArcSDE administration commands are referred to as ArcSDe command line utilities. These commands are used by ArcSDE administrator to manage and monitor the use of an ArcSDE service and geodatabase. The commands are divided into two categories: server administration commands and data management commands.

Server administration commands:

Sdeconfig: manages the ArcSDE server configuration table (SERVER_CONFIG). SERVER_CONFIG stores parameters and values that define how the ArcSDE server uses memory.

Sdedbtune: manages parameters of the DBTUNE table. DBTUNE contains parameters and values, grouped by configuration keywords to specify how data is stored in the database.

Sdegcdrules: manages geocoding rules.

Sdegdbrepair: identifies and repairs any inconsistencies between the adds (A) and the deletes (D) tables of a versioned geodatabase.

Sdelocator: manages locators.

Sdelog: administers log files (used primarily for shared log files).

Sdemon: monitors and manages the ArcSDE service.

Sdeservice: manages the ArcSDE service on Windows.

Sdesetup: does the initial geodatabase creation within the DBMS, upgrades the geodatabase, and updates your license file.

Data management commads:

sdeexport: Creates an ArcSDE export file.

sdeimport: Imports data from an ArcSDE export file.

sdegroup: Merges features by combining their geometries into multipart shapes. Features are grouped by tiles or by a business table attribute.

sdelayer: Administers feature classes, including getting ArcSDE geometry information.

sderaster: Manages raster layers.

sdetable: Administers business tables and their data.

sdexinfo: Describes an ArcSDE export file.

sdexml: Administers XML columns.

cov2sde: Converts ArcInfo coverages to geodatabase feature classes.

sde2cov: Converts geodatabase feature classes to ArcInfo coverages.

sde2shp: Extracts features from a geodatabase feature class or log file and writes them to a shapefile.

sde2tbl: Converts geodatabase tables to INFO or dBASE Tables.

sdeversion: Manages geodatabase versions.

shp2sde: Converts shapefiles to geodatabase feature classes.

tbl2sde: Creates a table in the geodatabase, appends data to an existing table in the geodatabase, or replaces records in an existing table in the geodatabase.

Up to ArcSDE 9.2, some operations of data management commands can not be performed by ArcGIS Desktop software (ArcMap and ArcCatalog), including sdeexport, sdeimport, sdegroup, sdelayer, sderaster, sdetable, sdexinfo, sdexml. Functions of commands (cov2sde, sde2cov, sde2shp, sde2tbl, sdeversion, shp2sde, tbl2sde) can be performed by using ArcGIS Desktop software.

Note:
1. ArcSDE data management commands for loading data, such as shp2sde and cov2sde, cannot be used to load data into a feature class that is contained within a Geodatabase feature dataset. Only an ArcObjects application, such as ArcCatalog, ArcMap, and custom-built ArcObjects applications, can be used to load data into a feature class contained within a Geodatabase feature dataset.

2. If a feature class is created in ArcCatalog, using a command to delete it would leave orphaned records in the geodatabase system tables, as the ArcSDE commands are not geodatabase aware. The best way would be to manage them using ArcCatalog itself.

3. It is recommended not to manage standalone feature classes with command line utilities. Layers created with command line should be registered with the Geodatabase to avoid unnecessary issues (such as OBJECTID).

More Oracle DBA tips, please visit Oracle DBA Tips

Wednesday, December 19, 2007

Why ArcSDE?

This posting lists some interesting points why using ArcSDE:

First, one of the primary functions of ArcSDE is that it provides the connectivity to the DBMS; meaning that the out-of-the-box ArcGIS Desktop clients, ArcIMS and ArcEngine and ArcGIS Server development environments don't need to know which DBMS you are working with or how the data is stored in the database.

Second, ArcSDE enables ArcIMS to scale by managing access by many users to subsets of a large, continuous data set.

Third, ArcSDE combined with ArcCatalog provides the mechanism to load and maintain data in an enterprise geodatabase (which is a geodatabase stored in a relational DBMS like Oracle, SQL Server, DB2, Informix).

Fourth, ArcSDE with ArcIMS provides ArcIMS Metadata Services.

Fifth, ArcSDE provides pyramiding for improved raster/image data access.

Sixth ArcSDE provides the transaction model that is required for a multi-user editing environment (long transaction, versions, disconnected editing, geodatabase replication (at 9.2), and history archiving (at 9.2)).

Lastly, ArcSDE provides low level data integrity checks (check that polygons are closed, that lines don't intersect themselves, etc) that complement the higher level data integrity checks of the geodatabase (relationships, domains, topology, traversable networks, etc.).

When feature classes should not be put into a feature dataset?

In general, it is a bad idea using feature dataset to group feature classes for the following reasons:

1) While accessing just one feature class, the whole feature dataset is locked.

2) When one feature class in the feature dataset is used, all feature classes in the feature dataset will be cached, not just the layer being using.

3) Don't put any layer/ feature class into a feature dataset unless you need it for geodatabase functionality (e.g. geometric network, topology, etc.)

Show friendly time in ArcSDE system tables

I came across a posting about time conversion in ArcSDE system tables in ESRI Support web site:

http://support.esri.com/index.cfm?fa=knowledgebase.techarticles.articleShow&d=25700

In the ArcSDE system tables, the dates are stored as a number that represents the time when the table was registered, or the layer was created, in seconds since 1970.

SELECT registration_id, table_name, OWNER, TO_CHAR(NEW_TIME(TO_DATE('1970-01-01', 'YYYY-MM-DD'),'GMT','PDT') + registration_date / 86400.0, 'Month DD, YYYY HH:MI:SS am')
FROM sde.table_registry;

SELECT LAYER_id, table_name, OWNER, TO_CHAR(NEW_TIME(TO_DATE('1970-01-01', 'YYYY-MM-DD'),'GMT','PDT') + CDATE / 86400.0, 'Month DD, YYYY HH:MI:SS am')
FROM sde.LAYERS;

Monday, December 17, 2007

Calculate free space of a tablespace

The following script calculates the free space of a tablespace in an Oracle database:

SELECT TABLESPACE_NAME,
(SUM(BYTES)/1024) FREE_KB,
(SUM(BYTES)/(1024*1024)) FREE_MB
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME;

DBA_FREE_SPACE describes the free extents in all tablespaces in the database.

Saturday, December 15, 2007

Shape Validation in ArcSDE 9.1

ArcSDE maintains valid shapes internally by applying a set of rules to each shape type. All ArcSDE commands that create or update shapes use these rules.

Verification rules for point shapes are:
- The area and length of points are set to 0.0.
- A single point's envelope is equal to the point's x,y values.
- The envelope of a multipart point shape is set to the minimum bounding box.

Verification rules for simple lines, or linestring shapes, are:
- Sequential duplicate points are removed.
- Each part must have at least two distinct points.
- Each part may not intersect itself. The start and endpoints may be the same, but the resulting 'ring' is not treated as an area shape.
- Parts may touch each other at the endpoints.
- The length is the sum of all the parts.

Verification rules for lines, or spaghetti shapes, are:
- Lines can intersect themselves.
- Each part must have at least two distinct points.
- Sequential duplicate points are deleted.
- The length is the length of all of its parts added together.

Verification rules and operations on area shapes are:
- Delete duplicate sequential occurrences of a coordinate point.
- Delete dangles.
- Verify that the line segments close (z coordinates at start and endpoints must also be the same) and don't cross.
- Correct rotation to counterclockwise (see the previous section for an explanation of how ArcSDE stores area shapes).
- For area shapes with holes, ensure that holes reside wholly inside the outer boundary. ArcSDE eliminates any holes that are outside the outer boundary.
- Convert a hole that touches an outer boundary at a single common point into an inversion of the area shape.
- Combine multiple holes that touch at common points into a single hole.
- Multipart area shapes may not overlap. However, two parts may touch at a point.
- Multipart area shapes may not share a common boundary. Common boundaries are dissolved.
- If two rings have a common boundary, they are merged into one ring.
- Calculate the total geometry perimeter, including the boundaries of all holes in donut polygons, and store the perimeter as the length of the geometry.
- Calculate the area.
- Calculate the envelope.

How to obtain information about the geometry of the features in a layer?

Applies to: ArcSDE 9.1, 9.2

“sdelayer –o feature_info” included in ArcSDE package can be used to get information about the geometry of a feature.

The syntax for the command is as follows:

sdelayer -o feature_info -l [-V ] [-r {valid all invalid}] [-w <"where_clause">] [-c] [Spatial_Index] [-S ] [-q] [-i ] [-s ] [-D ] [-u [-p ] [-q]

“-o feature_info” operation reports information about the feature, such as shape validity, measurements and extent information, the FID, the presence of annotation, whether the feature contains CAD data, the presence of inclusions or cojoined inner rings, the minimum precision of the layer, the number of points, parts and subparts contained in the feature.

-l : the layer name and its spatial column name.

-r {valid all invalid}: Specifies if only valid shapes are read or all shapes are read. If all shapes read, error returned to indicate invalid shapes. If invalid is specified, only features with invalid geometry are returned with error code.

-w: Where clause
! -w "a = 1" double quote ".
! -w 'a = 1' wrong. ArcSDE error -42 (SE_INVALID_SQL) will be reported in the SDE error log file:
[03/13/2007 16:07:24;SdeId=33433;Client=Client_PC] load_buffer error -42

See here for the meaning of ArcSDE error code.

“sdelayer -o feature_info” operation returns up to 21 different characteristics of each feature in the layer. These characteristics are presented in a series of comma-delimited fields. Fields are returned in the following order:
1. Row ID (Integer) - The row ID of the table containing the spatial column. If the table does not have an ArcSDE or user-maintained row ID column (usually objectid), the Feature ID (FID) column value is returned here instead. If it has neither row ID nor FID, the value returned is 0.
2. FID (Integer) - The Feature ID of the shape. If there is no FID, the row ID is returned instead. If there is neither row ID nor FID, 0 is returned.
3. Entity Type (one character) - A single character indicating the entity type, either N (Nil), P (Point), S (Simple), L (Line), or A (Area).
4. Annotation (Boolean) - Indicates whether or not the shape has ArcSDE annotation. Values are either T (true) or F (false).
5. CAD Data (Boolean) - Indicates whether or not the shape contains ArcSDE CAD Data. Returned values are either T (true) or F (false).
6. Number of Points (Integer) - The total number of points in the shape.
7. Number of Parts (Integer) - The number of parts in the shape. If an error is encountered when attempting to obtain the parts, that error code, from sdeerno.h, is supplied instead.
8. Number of Subparts (Integer) - The number of subparts in the shape. If this is a type of shape that does not have subparts, the value is 0. If instead an error is encountered when attempting to obtain the subparts, that error code from sdeerno.h is supplied.
9. Self-Touching Rings (Boolean) - Indicates the presence (T) or absence (F) of inclusions or cojoined inner rings in the shape. T is always returned for area shapes.
10. Minimum Precision (Integer) - The minimum layer precision to contain this feature; either LOW (32-bit) or HIGH (64-bit).
11. Verification (Integer) - Indicates whether or not ArcSDE considers a shape valid. A value for this field is returned if you use the -r option to specify 'all' the features in the layer be evaluated for validity. Possible return values are 0 if the shape is verified as correct or a negative error code from the sdeerno.h file if it is incorrect. This information is most helpful when you want to determine which features in an Oracle Spatial database are not valid so you can correct this.
Values for numbers 12 through 21 are only returned if the [-c] [Spatial_Index] option is specified.
12. Area (Floating Point) - The area of the shape or 0.0 if this shape is not a polygon. Units depend on the coordinate system of the layer.
13. Length (Floating Point) - The length or perimeter of the shape or 0.0 if this shape is a point or multipoint. Units depend on the coordinate system of the layer.
14. Minimum X (Floating Point) - The minimum x-coordinate of this shape.
15. Maximum X (Floating Point) - The maximum x-coordinate of this shape.
16. Minimum Y (Floating Point) - The minimum y-coordinate of this shape.
17. Maximum Y (Floating Point) - The maximum y-coordinate of this shape.
18. Minimum Z (Floating Point) - The minimum z-coordinate of this shape. This field is only present if the layer has z-coordinates.
19. Maximum Z (Floating Point) - The maximum z-coordinate of this shape. This field is only present if the layer has z-coordinates.
20. Minimum Measure (Floating Point) - The minimum measure of this shape. This field is only present if the layer has measures.
21. Maximum Measure (Floating Point) - The maximum measure of this shape. This field is only present if the layer has measures.

Here is an example to report all invalid geometries in a layer where column land_code equals 1:

sdelayer -o feature_info -l LAND.LAND_POLY,SHAPE -r invalid -i 5151 -w "land_code = 1" -s sdeserver -u user1 -p user1

ArcSDE 9.1 Oracle10g Build 391 Tue Oct 24 11:44:47 PDT 2006
Layer Administration Utility
-----------------------------------------------------
Row Id,FID,Entity Type,Annotation,Cad Data,Number of Points,Number of Parts,Number of Subparts,Self-Touching Rings,Minimum Precision,Verification

1,2,A,F,T,29,2,3,F,32,-155
3,3,A,F,F,3682,2,2,F,32,-148
5,5,A,F,F,66,2,2,F,32,-148

These results indicate that three geometries are invalid. Take the first one for example:
- Has a Row ID of 1
- Has a Feature ID of 2
- Is an area entity (a polygon)
- Does not contain annotation
- Contains CAD data
- Contains 29 points
- Is made up of 2 parts and 3 subparts
- Does not contains self-touching rings
- Is stored in low precision (has a minimum precision of 32 bits)
- Is an invalid shape (error code -155: SE_SELF_INTERSECTING, a simple line or polygon boundary intersects itself.)

See here for the meaning of ArcSDE error code.

Sunday, December 30, 2007

Thursday, December 27, 2007

Monday, December 24, 2007

Wednesday, December 19, 2007

Monday, December 17, 2007

Saturday, December 15, 2007

Popular Posts in Last 30 Days

About Me

My favorites

Blog Archive

Labels

Disclaimer