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

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 

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;

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.

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.

Loading spatial data between layers with different spatial columns

If you create a feature class in ArcCatalog, the default spatial column name to store spatial data is automatically called SHAPE. There are some existing layers whose spatial column is called GEOMETRY. Sdelayer is capable of creating feature classes by specifying GEOMETRY as the spatial column.

How you do load spatial data from a layer with SHAPE column to a layer with GEOMETRY column? Let us examine the following options.

Option 1: Use sdeexport / sdeimport.

First use the following script to create an ArcSDE export file:
sdeexport -o create -l source_layer,shape -a all -f source.sdx -i 5151 -s sde_server -u user1 -p user1

Then use this script to import ArcSDE export data into the target layer:
sdeimport -o init -l destination_layer,geometry -f source.sdx -i 5151 -s sde_server -u user2 -p user2
Warning: SDEX's column "SHAPE" not found in target table.

Exactly as the warning message says, only the attribute data are loaded into the target layer. Geometry column is null for all loaded records.

If the append operation is used, it is the same thing, which makes sense:
sdeimport -o append -l destination_layer,geometry -f source.sdx -i 5151 -s sde_server -u user2 -p user2
Warning: SDEX's column "SHAPE" not found in target table.

So this option does not work.

Option 2: Use FME / Data Interoperability Extension.
FME is the spatial ETL (extract, transform and load) tool provided by Safe software from Surrey of British Columbia in Canada. Data Interoperability Extension is an extension in ArcGIS desktop package. It is FME actually. Matching SHAPE and GEOMETRY will do the job: load spatial data in SHAPE into GEOMETRY column.

Option 3: Use Simple Data Loader in ArcCatalog.

In the dialog of Simple Data Loader, it provides the option to match attribute columns, but no option to match the spatial column.

I will check it out tomorrow and write the result here.

Dec 08: I tried this today in ArcCatlog. Values in SHAPE column are loaded into GEOMETRY column. Since ArcCatalog is based on ArcObjects, it makes sense. Actually it can automatically match them.

Of course there are other options, such as writing an ArcObjects utility.

ITIL - Information Technology Infrastructure Library

I had an ITIL training in Victoria at the beginning of Novermber. It is a three-day intensive course. The afternoon in the third day was for a test. The test was quite formal, which reminded me of CET4 and CET6 tests when I was in my university. The training and test are not easy. A thick book needed to be read, and you really need to use your brain to analyze the scenario and use your IT service management experience to understand ITIL and pass the test. For ESL people, it is an English language test to some extent. My manager got the result and she sent me her congratulations immediately because she said it was difficult, even though she has more than 20 years of ITSM experience. I got my formal certificate in about two weeks.

If you are interested in ITIL, here is the web site:
http://en.wikipedia.org/wiki/ITIL

BTW, my course is v2. v3 is coming.