Registering a view with a spatial column as layer using SDE command

[AndyOldham]

I attempted to run SDE command to register a view which contains a SHAPE column (in ST_GEOMETRY) as a layer.
The following lines are the command that I tried to run and the error messages.
>sdelayer -o register -l testview,shape -e p -t st_geometry -s servername -i servicename -u username -p password
ArcSDE 10.0  for Oracle11g Build 1937 Tue Aug 16 16:08:18  2011
Layer    Administration Utility
-----------------------------------------------------
Error: Underlying DBMS error (-51).
Error: Cannot Create Layer.
ORA-00942: table or view does not exist

With this error, I granted 'SELECT ANY TABLE' privilege to the user just in case it searches some tables in SDE schema...And then now I got different messages with the same command.

ArcSDE 10.0  for Oracle11g Build 1937 Tue Aug 16 16:08:18  2011
Layer    Administration Utility
-----------------------------------------------------
Error: Underlying DBMS error (-51).
Error: Cannot Create Layer.
ORA-01031: insufficient privileges

First I am not sure if 'Select Any Table' is required for this and why if yes.
And what are the other privileges to make this work..? or is there any other permission to grant to use any objects related to ST_GEOMETRY or sptial references..? The user currently has the following permissions:
CREATE INDEXTYPE
CREATE PROCEDURE
CREATE SEQUENCE
CREATE SESSION
CREATE TABLE
CREATE TRIGGER
CREATE VIEW
EXECUTE ANY LIBRARY

My SDE is at 10.0 SP3 w/ Oracle 11g R2.

[BirajaNayak]

Hi,

How the view created. can you provide us the command used to create view.
What is the data source from which view is created.

Also can you please check the following:


Please run the following sql statements on the original feature class and provide the query output.

Example
---------------
1.Query st_geometry_columns

SELECT UNIQUE(SRID) FROM SDE.ST_GEOMETRY_COLUMNS WHERE TABLE_NAME='TESTParcel' AND OWNER='GISADMIN';

SRID
-------
4


2. Check whether the entry exists in the st_spatial_references table.

SELECT SRID,SR_NAME FROM SDE.ST_SPATIAL_REFERENCES WHERE SRID=4;

3. Please run the following query to list all SRID values in the ST_SPATIAL_REFENCES table with matching records in the AUTH_SRID column of the SPATIAL_REFERENCES table.

SELECT A.SRID, A.AUTH_SRID, B.SRID FROM SDE.SPATIAL_REFERENCES A, SDE.ST_SPATIAL_REFERENCES B WHERE A.AUTH_SRID = B.SRID;

Can you please include -C in syntax of sdelayer -o register as per below example and let us know th eobservation:
sdelayer -o register -l testview,shape -e p -C OBJECTID,USER -t st_geometry -s servername -i servicename -u username -p password

[AndyOldham]

Thanks for the suggestion.
I created the view using SQL by selecting objectid, shape, and some other attribute columns from feature classes.
CREATE VIEW USER.TESTVIEW
(
OBJECTID,
COL_A,
COL_B,
SHAPE
)
AS
SELECT A.OBJECTID, A.COL_A, B.COL_B, A.SHAPE
FROM USER.TABLE_A A, USER.TABLE_B B
WHERE sde.ST_EnvIntersects (A.shape, B.shape) = 1;
From the first query in your posting, I didn't get any spatial reference ID returned...
One thing strange to me is that I can have this view registerd in the exact same process when the view is created in SDE schema.

I also tried to run 'Register as Geodatabase' tool in ArcCatalog and traced it in database-side then I found it stops at a similar statement:
  SELECT layer_id, ly.description, gsize1, gsize2, gsize3, minx, miny, maxx, maxy, minz, minm, maxz, maxm, eflags, layer_mask, database_name, table_name, owner, spatial_column, cdate, layer_config, optimal_array_size, stats_date, minimum_id, base_layer_id, sr.falsex, sr.falsey, sr.xyunits, sr.falsez,sr.zunits, sr.falsem, sr.munits, sr.srtext, ly.srid,sr.object_flags,sr.xycluster_tol, sr.zcluster_tol, sr.mcluster_tol,sr2.falsex, sr2.falsey, sr2.xyunits, sr2.falsez,sr2.zunits, sr2.falsem, sr2.munits, ly.secondary_srid,sr2.object_flags FROM SDE.LAYERS ly LEFT OUTER JOIN SDE.spatial_references sr2 ON ly.secondary_srid = sr2.srid, SDE.spatial_references sr WHERE ly.srid = sr.srid AND ly.table_name = 'table_name' AND ly.owner = 'owner_name'


This didn't get any rows as well. Am I missing any neccessary steps to register a view that created using SQL?
+ It resulted the same error with '-C option' and there were some SRID values matched with AUTH_SRID by running the query.
Any further help would be appreciated.

[AndyOldham]

I found another symptom that is probably a similar issue...
When I ran 'shp2sde -o create' command it gave me another error message:
SDE Code (-51): Underlying DBMS error
Extended DBMS error code: 1031
ORA-01031: insufficient privileges
Error in SE_layer_create.
again, the same command worked without any problems by SDE user, but the error occurs when other DB account used.
I assume that it is due to lack of permission somehow but am not sure what it is... or where should I look to find the current permission on 'SE_layer_create'..? any packages or objects?
I appreciate any help..!

[VinceAngelo]

The documentation defines the necessary permissions for a data owner in Oracle.
Similar pages exist for the other supported databases. 

While it may seem useful to attempt layer creation as the SDE user, it also increases the
probability of corrupting the entire instance.  In practice, I usually restrict my SDE user
privileges to *less* than that of data owners, so the attempt would be all risk and no
possible gain.  In an instance with more privileges, the risk increases, but it stilll wouldn't
solve the issue for the proper privileges for a data owner account.

- V

[AndyOldham]

Thanks for the reply Vince.
I don't have intention to store data in SDE schema but the sde commands to create a feature class do not work when it is excuted by any other user than SDE.
The user has the privileges described in the documentation as a data creator: �?�CREATE SESSION
�?�CREATE SEQUENCE
�?�CREATE TRIGGER
�?�CREATE VIEW
�?�CREATE TABLE
In addtion, it also has 'UNLIMITED TABLESPACE'. 'execute any library'.
But it seems to me that the user is still lacking some particular OBJECT PRIVILEGES on some objects in SDE schema...based on the previous error messages...
To narrow my problem...can you give me any explaination on SE_LAYER-CREATE? Is this something in package or objects?
Thank you...

[VinceAngelo]

No ArcSDE user ever requires unlimited tablespace or execute any library.

SE_layer_create is an API call -- the same call functions the same without
regard to database vendor.

If your instance doesn't perform in accordance with the documentation,
please contact Tech Support for assistance.

- V

[mmcrae]

I am experiencing the exact same issues. After promoting the user who owns the data and is creating the spatial layer to dba-level permissions, the layer registration was successful. Is there a full set of permissions that can be provided? We have gone over the link to documentation above thoroughly.

Thanks!