nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

One to many view woes

MelissaKrieg

Is anyone successfully using 1:M views in ArcSDE 9.3.1 and Oracle 10g (10.2.0.2)?

I have been providing several 1:M views to users for years. They stopped working correctly at SDE 9.2 and now work inconsistently at SDE 9.3.1. By that I mean that the 1:M relationships show quite plainly as stacked polygons in ArcCatalog, but not in ArcMap. And also that the 1:M views export incorrectly, i.e a user has 1500 features selected on the view and exports his selection to a shapefile only 500 features export.

As an example, I have a 1:M view where the '1' part of the view is a county feature class, and the 'M' is a table which has one or more rows per county. In the view, I'm using an OBJECTID field from the M table in order to have a unique OBJECTID for the view.   In fact, all fields are coming from the 'M' table except for SHAPE which is coming from the '1' feature class.

I've eliminated the following as contributors to the problem:
Oracle client version
BLOB vs. LONGRAW geometry storage
outer join (+) added to 'M' side of business table view post sde command line creation
OBJECTID for view coming from '1' feature class (results in duplicate OBJECTID)

Running traces in the database for views that mysteriously work versus views that do not tells me that an additional select on SHAPE.FID from the F view (see below) seems to make the difference. But, I have no idea how ArcGIS generates this SQL from the sde view I created. Does ArcGIS Desktop generate the SQL or does ArcSDE?

You can probably tell from the bind variables at the end of the SQL, but the below was captured from a DB trace while I was in ArcMap and used the identify tool on a county that has 2 records in the many table.

SELECT /*+ LEADING INDEX(S_ S3060_IX1) INDEX(SHAPE) INDEX(COUNTIES_MAP_STATUS_VW A3060_IX1) */
       objectid,
       st_name,
       cnty_name,
       stcnty,
       agmt_type,
       incomplete_legal,
       arcmap_requested,
       update_requested,
       needs_review,
       polygen_error_general,
       polygen_error_no_grid,
       polygen_error_no_legal,
       polygen_error_no_lot,
       shape,
       shape.area,
       shape.len,
       shape.fid,
       s_.eminx,
       s_.eminy,
       s_.emaxx,
       s_.emaxy,
       shape.fid,
       shape.numofpts,
       shape.entity,
       shape.points,
       shape.ROWID
FROM (SELECT /*+ INDEX(SP_ S3060_IX1) */
            DISTINCT
             sp_fid, eminx, eminy, emaxx, emaxy
      FROM lease.s3166 sp_
      WHERE     sp_.gx >= :1
            AND sp_.gx <= :2
            AND sp_.gy >=

            AND sp_.gy <= :4
            AND sp_.eminx <= :5
            AND sp_.eminy <= :6
            AND sp_.emaxx >= :7
            AND sp_.emaxy >= :8) s_,
     lease.counties_map_status_vw,
     lease.f3166 shape
WHERE s_.sp_fid = shape.fid
      AND s_.sp_fid = lease.counties_map_status_vw.shape
...

I'm looking for help diagnosing why our 1:M views no longer work correctly, or help understanding how the spatial view is used by ArcGIS, or to hear from anyone using 1:M views successfully.

Find more posts tagged with

Accepted answers

All comments

MelissaKrieg

For what it's worth, we finally figured this one out. The root cause is that my feature class' geometry storage was SDEBINARY. Once I converted the FC to ST_GEOMETRY, my views work perfectly.

I had earlier tested converting the SDEBINARY to SDELOB, but that didn't improve things. So, our SDE admin and DBAs are now creating a plan to convert all of our SDEBINARY and SDELOB to ST_GEOMETRY (which I understand we should migrate to in any case).

VinceAngelo

Can you put 10.2.0.2 on a different box, populate the tables and view to reproduce the
problem, then upgrade Oracle and try again? I've found 10.2.0.3 to be *way* more stable
than 10.2.0.2, but since Oracle isn't supporting 10g anymore, you need to be careful with
upgrades (two backups wouldn't be a bad plan).

- V

MelissaKrieg

I've been familiar with ArcObjects' requirements for a unique registered rowid column
since 8.0 went beta. If you look at my view construction commands, both here and in the
old Forums, you'll see that I always take the objectid column from the many side, which
circumvents the Knowledge Base issue. Note that it's not ArcSDE which has an issue
with non-unique objectids -- Desktop has to juggle the relationship between attribute
metadata and graphics in the display pane; without a unique feature key to associate
them, that task becomes nearly impossible.
.
.
.

If a spatial view with a unique registered rowid column doesn't behave properly while using a supported
database build, please bring it to the attention of Tech Support.

- V

Well, I'm pulling the registered rowid column from the many side to ensure it's unique, so it sounds like maybe the Oracle version is my problem afterall.

CiaranDoyle

thanks for the tip, vince

I've updated the knowledge base article with a link to this thread

VinceAngelo

Doh! Yes. - V

RobertHu

Hi Vince,

Thanks for your advice!

BTW: Robert - You probably don't need to place the layer in load_only I/O mode before a truncate and append -- the 'init' option of 'sdeexport' does the truncate, load_only, insert, and normal_io in one command.

You mean "sdeimport -o init ....", right?

VinceAngelo

I've been familiar with ArcObjects' requirements for a unique registered rowid column
since 8.0 went beta. If you look at my view construction commands, both here and in the
old Forums, you'll see that I always take the objectid column from the many side, which
circumvents the Knowledge Base issue. Note that it's not ArcSDE which has an issue
with non-unique objectids -- Desktop has to juggle the relationship between attribute
metadata and graphics in the display pane; without a unique feature key to associate
them, that task becomes nearly impossible.

I just ran a trivial test of 1:M views with ArcSDE 10 (SQL-Server 2008) and ArcGIS 10,
and my spatial views continue to function as expected --

C:\Incoming>more 1toMone.txt 1toMone.ctl 1toMmany.txt 1toMmany.ctl | cat
::::::::::::::
1toMone.txt
::::::::::::::
AAAA|box:-10,6,-6,10
BBBB|polygon:6,6,10,6,8,10,6,6
CCCC|circle:0,0,3
DDDD|polygon:-6,-6,-10,-6,-8,-10,-6,-6
EEEE|box:6,-10,10,-6
::::::::::::::
1toMone.ctl
::::::::::::::
COORDSYS        GCS_WGS_1984
COORDREF_XY     -210,-120,1000000
EFLAGS          "na+"
DELIMITERS      "|"
REGISTER        objectid
COLUMNS
objectid        Sequence()                      -  10   N
keyval          String                          -   4   N
shape           Shape                           -   1   Y
END
::::::::::::::
1toMmany.txt
::::::::::::::
AAAA
BBBB
CCCC
DDDD
EEEE
BBBB
CCCC
DDDD
EEEE
CCCC
DDDD
EEEE
DDDD
EEEE
EEEE
::::::::::::::
1toMmany.ctl
::::::::::::::
REGISTER        objectid(SDE)
COLUMNS
objectid        Sequence()                      -  10   N
keyval          String                          -   4   N
END
 
C:\>asc2sde -o create -l otm_layer,shape -g 5 -f 1toMone.txt
ASCII to ArcSDE 10.0 Loader Utility      Thu Dec 02 07:57:28 2010
------------------------------------------------------------------------
Results:
        Records read: 5
        Rows created: 5
         Insert time: 218.00 ms (22.94 TPS)
        Elapsed time: 2.95 secs
 
C:\>asc2sde -o create -t otm_table -f 1toMmany.txt
ASCII to ArcSDE 10.0 Loader Utility      Thu Dec 02 07:58:53 2010
------------------------------------------------------------------------
Results:
        Records read: 15
        Rows created: 15
         Insert time: 16.00 ms (937.50 TPS)
        Elapsed time: 422.00 ms
 
C:\>sdetable -o create_view -T otm_view1 -t otm_layer,otm_table -w "otm_table.keyval = otm_layer.keyval" -c otm_table.objectid,otm_layer.keyval,otm_layer.shape
 
ArcSDE 10.0  for SQL Server Build 775 Fri Sep 17 11:45:27  2010
Attribute        Administration Utility
-----------------------------------------------------
Successfully created view otm_view1.
 
C:\>sdetable -o create_view -T otm_view2 -t otm_layer,otm_table -w "otm_table.keyval = otm_layer.keyval" -c otm_layer.objectid,otm_table.keyval,otm_layer.shape
 
ArcSDE 10.0  for SQL Server Build 775 Fri Sep 17 11:45:27  2010
Attribute        Administration Utility
-----------------------------------------------------
Successfully created view otm_view2.
 
C:\>sdequery -t otm_view1
ArcSDE 10.0 Generic Query Tool           Thu Dec 02 08:04:56 2010
------------------------------------------------------------------------
         1
            objectid: 1
              keyval: AAAA
               shape: Area shape (1 part, 5 vertices)
         2
            objectid: 2
              keyval: BBBB
               shape: Area shape (1 part, 4 vertices)
         3
            objectid: 3
              keyval: CCCC
               shape: Area shape (1 part, 37 vertices)
         4
            objectid: 4
              keyval: DDDD
               shape: Area shape (1 part, 4 vertices)
         5
            objectid: 5
              keyval: EEEE
               shape: Area shape (1 part, 5 vertices)
         6
            objectid: 6
              keyval: BBBB
               shape: Area shape (1 part, 4 vertices)
         7
            objectid: 7
              keyval: CCCC
               shape: Area shape (1 part, 37 vertices)
         8
            objectid: 8
              keyval: DDDD
               shape: Area shape (1 part, 4 vertices)
         9
            objectid: 9
              keyval: EEEE
               shape: Area shape (1 part, 5 vertices)
        10
            objectid: 10
              keyval: CCCC
               shape: Area shape (1 part, 37 vertices)
        11
            objectid: 11
              keyval: DDDD
               shape: Area shape (1 part, 4 vertices)
        12
            objectid: 12
              keyval: EEEE
               shape: Area shape (1 part, 5 vertices)
        13
            objectid: 13
              keyval: DDDD
               shape: Area shape (1 part, 4 vertices)
        14
            objectid: 14
              keyval: EEEE
               shape: Area shape (1 part, 5 vertices)
        15
            objectid: 15
              keyval: EEEE
               shape: Area shape (1 part, 5 vertices)
15 rows found in 375.00 ms
 
C:\>sdequery -t otm_view2
ArcSDE 10.0 Generic Query Tool           Thu Dec 02 08:05:04 2010
------------------------------------------------------------------------
         1
            objectid: 1
              keyval: AAAA
               shape: Area shape (1 part, 5 vertices)
         2
            objectid: 2
              keyval: BBBB
               shape: Area shape (1 part, 4 vertices)
         3
            objectid: 3
              keyval: CCCC
               shape: Area shape (1 part, 37 vertices)
         4
            objectid: 4
              keyval: DDDD
               shape: Area shape (1 part, 4 vertices)
         5
            objectid: 5
              keyval: EEEE
               shape: Area shape (1 part, 5 vertices)
         6
            objectid: 2
              keyval: BBBB
               shape: Area shape (1 part, 4 vertices)
         7
            objectid: 3
              keyval: CCCC
               shape: Area shape (1 part, 37 vertices)
         8
            objectid: 4
              keyval: DDDD
               shape: Area shape (1 part, 4 vertices)
         9
            objectid: 5
              keyval: EEEE
               shape: Area shape (1 part, 5 vertices)
        10
            objectid: 3
              keyval: CCCC
               shape: Area shape (1 part, 37 vertices)
        11
            objectid: 4
              keyval: DDDD
               shape: Area shape (1 part, 4 vertices)
        12
            objectid: 5
              keyval: EEEE
               shape: Area shape (1 part, 5 vertices)
        13
            objectid: 4
              keyval: DDDD
               shape: Area shape (1 part, 4 vertices)
        14
            objectid: 5
              keyval: EEEE
               shape: Area shape (1 part, 5 vertices)
        15
            objectid: 5
              keyval: EEEE
               shape: Area shape (1 part, 5 vertices)
15 rows found in 250.00 ms
 
C:\>

The first attachment is a JPG of an identify on the lower-left triangle from using ArcSDE 10 with SQL-Server.
I repeated the process with ArcSDE 9.3.1sp2 on Oracle 10.2.0.3, and got the same behavior (second image).

If a spatial view with a unique registered rowid column doesn't behave properly while using a supported
database build, please bring it to the attention of Tech Support. They'll need a copy of your data (or the
smallest sanitized subset thereof which still exhibits the issue) to attempt reproduction and resolution.

BTW: Robert - You probably don't need to place the layer in load_only I/O mode before a truncate and
append -- the 'init' option of 'sdeimport' [nsic] does the truncate, load_only, insert, and normal_io in one
command.

- V

1toMsql100.JPG

1toMora931.JPG

CiaranDoyle

I also hit this problem recently and it is very annoying.

According to the last line of the "Using Database Views" page in the esri web-help, it would seem that this type of one-to-many view is not supported in ArcMap (although confusingly it appears to work as expected when viewed through ArcCatalog).
http://webhelp.esri.com/arcgiSDEsktop/9.3/index.cfm?TopicName=Using_database_views

This is backed up by this knowledge base article
http://resources.arcgis.com/content/kbase?fa=articleShow&d=35945

This behaviour is in direct contradiction of my understanding of what a database view should do and should be more explicit in the web help.

Even the esri staff-member that responded to this post doesn't seem to be aware of the shortcomings of spatial views.

Rant ends.

MelissaKrieg

Yup, that's what we're doing as well, but with Python instead of C (rebuild index works fine in Python). We drive the Python script from a BAT file so we can schedule it in the OS of the applications server where the script lives. We also pipe output to a text file and use BLAT to email it to our team for verification.

RobertHu

It is roughly like these,
1. Create a GDB feature class (FC) and make sure the field schema matchs the spatial view (SV).
2. Build indexes on the FC as necessary.
3. Run sdeexport and directly pipe into sdeimport to populate the FC. In the attribute file, I will not include OBJECTID field,
sdeexport -o create -l <SV>,shape -a file=<att_file> -f - .... | sdeimport -o append -f - -l ....
4. Rebuild index (sdetable -o rebuild_index) and statistics (sdetable -o update_dbms_stats).

In the subsequent update, it is roughly as these,
1. Put FC in load only mode (sdelayer -o load_only_io)
2. Truncate it (sdetable -o truncate)
3. Step 3 above
4. Put FC back to normal mode (sdelayer -o normal_io)
5. Step 4 above.

I am doing these using C-shell script in Unix and batch file in windows. I think if you leave your views in the DB (I dynamically create the views before update and drop them after update in my script), you can easily accomplish all these using python/GP tools, except the rebuild_index.

Thanks!

JuliePaine

How did you 'materialize' the view to featureclass? (And get it to update daily?) I have a spatial view using a table view and a fc in a full outer join and was ready to abandon it because of drawing speed until I saw this thread. Any suggestions would be helpful. Thanks

MelissaKrieg

Just this morning I was thinking about converting the view into a feature class. I'd need to create an automated process to keep it updated, but we already have several such proceses in place for other data. Unfortunately, I've decided that ESRI no longer supports 1:M views and will move on. 😞

My views perform very well and I'll have the added maintenance for the process, so I'm reluctant to go this route. But, I see no other choice.

RobertHu

I have a 1:M spatial view, too. The M is on the shape side. My solution is to create a feature class by "materializing" the spatial view, and use the view to update the FC periodically, say once a day at night or once a week in weekend. My users can only access the FC, so they do not have the ArcMap/ArcCatalog inconsistance.

Another benefit is that the performance of the FC is much better than the view. Because on my table side, it is a table view joining several tables in a complicated way. The spatial view actually is a view of a FC and a table view. Very slow.

Thanks!

Sorry. The M is on the table view side.

MelissaKrieg

The feature class (1 side of the view) changes only quarterly when we receive vendor updates. The data table (the M side of the view) is truncated daily and repopulated, but does not change throughout the day. Why do you ask?

RobertHu

Hi Melissa,

Are there lots of changes on the feature class and the joined tables in your main work hours (say, 8:00am - 5:30pm) every day? If so, does your business flow require those spatial view users to see live changes?

If you've figured out a solution for your issue, forget my questions. Thanks!

Robert

MelissaKrieg

I switched the -t parameters around, but there was no effect in the view. I'm curious what should that have changed and why would it effect performance?

The data is in GCS NAD27, it is not projected. This coordinate system definition is old yes, but it's available in ArcGIS, so I still don't understand why you say it's incorrect?

We're pretty happy with how we're using lcoal environment variables, so let's shelve that one unless you think it could somehow contribute to the issues I'm seeing with the 1:M view.

For the M table, I did specify NUMBER(5) for the STCNY field, for example. However, for the feature class it comes to us as an SDE export file and we load as-is.

VinceAngelo

Try driving your view from the many side -

... -t COUNTIES_MAP_STATUS_DATA,BOUNDARY.COUNTIES_TOBIN ...

This may impact spatial query performance, though.

Dealing with an incorrect coordinate system is a very complex issue. Your first
task is to determine what the proper coodsys of the source data was, and whether
the data has been reprojected from that. There may be Clarke 1866 data around,
but it isn't common (or precise, or accurate, especially for applications outside the
continental United States).

Your code *is* using the supported environment variables (except for SDEPASSWORD);
it's just cluttering the script to include "-i %SDEINSTANCE%" when SE_connection_create
will use the contents from SDEINSTANCE if no -i flag is specified.

The easiest way to have types map correctly is to define width limits at column
creation ("NUMBER(1)"). Correctling this after creation is possible, but difficult
(and if you hack the COLUMN_REGISTRY table, unsupported).

- V

MelissaKrieg

I'll start with your first suggestion. I substituted the aliases for all columns with the fully qualified name, but there was no effect in the view.

My view creation command is now:
sdetable -o create_view -T COUNTIES_MAP_STATUS_VW -t BOUNDARY.COUNTIES_TOBIN,COUNTIES_MAP_STATUS_DATA -c "COUNTIES_MAP_STATUS_DATA.OBJECTID,COUNTIES_MAP_STATUS_DATA.ST_NAME,COUNTIES_MAP_STATUS_DATA.CNTY_NAME,COUNTIES_MAP_STATUS_DATA.STCNTY,COUNTIES_MAP_STATUS_DATA.AGMT_TYPE,COUNTIES_MAP_STATUS_DATA.INCOMPLETE_LEGAL,COUNTIES_MAP_STATUS_DATA.ARCMAP_REQUESTED,COUNTIES_MAP_STATUS_DATA.UPDATE_REQUESTED,COUNTIES_MAP_STATUS_DATA.NEEDS_REVIEW,COUNTIES_MAP_STATUS_DATA.POLYGEN_ERROR_GENERAL,COUNTIES_MAP_STATUS_DATA.POLYGEN_ERROR_NO_GRID,COUNTIES_MAP_STATUS_DATA.POLYGEN_ERROR_NO_LEGAL,COUNTIES_MAP_STATUS_DATA.POLYGEN_ERROR_NO_LOT,BOUNDARY.COUNTIES_TOBIN.SHAPE" -w "BOUNDARY.COUNTIES_TOBIN.STCTY=COUNTIES_MAP_STATUS_DATA.STCNTY" -s %SDESERVER% -i %SDEINSTANCE% -u %SDEUSER% -p %SDEPASS%

We're using the 'GCS_North_American_1927' coordinate system definition in ArcGIS. How is that undefined?

We don't set up environment variables because we have several SDE databases we flip between, i.e. dev, test, and prod.

For the data types, what we see when we load the data is NUMBER, VARCHAR2, etc. Where are the other definitions stored, DBTUNE?

VinceAngelo

It certainly wouldn't hurt to use the supported Oracle release, but I'm not willing
to assign "cause" status until the problem goes away.

The only obvious thing I see is the use of an alias for the shape column. ArcSDE
has a history of not handling aliases on the geometry column, so I'm more likely
to structure my view with:

f=nga_geonames_feature_p_3
n=nga_geonames_name_3
sdetable -o create_view -T nga_geonames_name_p_v3 -t %n%,%f% ^
-c %n%.objectid,%n%.ufi,%n%.uni,%f%.shape... ^
-w "%f%.ufi = %n%.nfi"

The projection defined on your layer is the "no projection defined" GCS from Arc/Info
days. Since no one really used that projection for anything, especially global mapping,
you probably want to do some research to determine the actual datum used.

If the SDEUSER/... environment variables are defined, there's no real reason to specify
the -u/-i/-p/-s flags on your command directives.

It probably wouldn't hurt to alter the layer envelope to -180,-90,180,90.

Do you really want your flag variables defined FLOAT64,38.10 when you could map
them to just about any type, including INT16,1?

- V

MelissaKrieg

You might notice that the data type on the two fields I'm using for the join are different. I forgot to mention in my original post that this has been tested as well. We updated the data type for STCTY in the M table to NUMBER(5), but it had no effect. Here's the new describe on the M table.

'sdetable -o describe' output for the 'M' table
Table counties_map_status_data:
Column name             Attribute type   Null?      Length,DPs    RowID Column?
-------------------------------------------------------------------------------
OBJECTID                SE_INT32         NOT NULL       10
ST_NAME                 SE_STRING        NULL           30
CNTY_NAME               SE_STRING        NULL           30
STCNTY                  SE_INT32         NULL            5
AGMT_TYPE               SE_STRING        NULL            3
INCOMPLETE_LEGAL        SE_FLOAT64       NULL           38,10
ARCMAP_REQUESTED        SE_FLOAT64       NULL           38,10
UPDATE_REQUESTED        SE_FLOAT64       NULL           38,10
NEEDS_REVIEW            SE_FLOAT64       NULL           38,10
POLYGEN_ERROR_GENERAL   SE_FLOAT64       NULL           38,10
POLYGEN_ERROR_NO_GRID   SE_FLOAT64       NULL           38,10
POLYGEN_ERROR_NO_LEGAL SE_FLOAT64       NULL           38,10
POLYGEN_ERROR_NO_LOT    SE_FLOAT64       NULL           38,10

MelissaKrieg

'sdetable -o describe' output for the 'M' table
Table counties_map_status_data:
Column name             Attribute type   Null?      Length,DPs    RowID Column?
-------------------------------------------------------------------------------
OBJECTID                SE_INT32         NOT NULL       10
ST_NAME                 SE_STRING        NULL           30
CNTY_NAME               SE_STRING        NULL           30
STCNTY                  SE_STRING        NULL            6
AGMT_TYPE               SE_STRING        NULL            3
INCOMPLETE_LEGAL        SE_FLOAT64       NULL           38,10
ARCMAP_REQUESTED        SE_FLOAT64       NULL           38,10
UPDATE_REQUESTED        SE_FLOAT64       NULL           38,10
NEEDS_REVIEW            SE_FLOAT64       NULL           38,10
POLYGEN_ERROR_GENERAL   SE_FLOAT64       NULL           38,10
POLYGEN_ERROR_NO_GRID   SE_FLOAT64       NULL           38,10
POLYGEN_ERROR_NO_LEGAL SE_FLOAT64       NULL           38,10
POLYGEN_ERROR_NO_LOT    SE_FLOAT64       NULL           38,10

'sdetable -o describe' output for the '1' table
Table counties_tobin:
Column name             Attribute type   Null?      Length,DPs    RowID Column?
-------------------------------------------------------------------------------
OBJECTID                SE_INT32         NOT NULL       10        SDE Set
SNAME                   SE_NSTRING       NULL           20
CNAME                   SE_NSTRING       NULL           40
STATE                   SE_INT16         NULL            2
COUNTY                  SE_INT16         NULL            3
CLASS                   SE_INT16         NULL            2
TYPE                    SE_INT16         NULL            2
STCTY                   SE_INT32         NULL            5
MODDATE                 SE_DATE          NULL            0
SHAPE                   SE_SHAPE         NULL            0

'sdelayer -o describe_long' output for the feature class
ArcSDE 9.3.1 for Oracle10g Build 1632 Thu Feb 26 12:05:37 2009
Layer    Administration Utility
-----------------------------------------------------
Layer Description ....: <None>
Table Owner ..........: BOUNDARY
Table Name ...........: COUNTIES_TOBIN
Spatial Column .......: SHAPE
Layer Id .............: 3060
SRID .................: 329
Minimum Shape Id .....: 1
Offset ...............:
falsex:       -400.000000
falsey:       -400.000000
System Units .........: 1000000000.000000
Z Offset..............:          0.000000
Z Units ..............:          1.000000
Measure Offset .......: <None>
Measure Units ........: <None>
XY Cluster Tolerance .:          0.000000008983
Spatial Index ........:
parameter:    SPIDX_GRID,GRID0=3.3,FULL
exist:        Yes
array form:   3.3,0,0
Layer Envelope .......:
minx:      -179.22783,        miny:        24.54290
maxx:       -66.88582,        maxy:        71.46868
Entities .............: nac+
Layer Type ...........: SDE-LOB
Creation Date ........: 09/24/10 16:11:28
I/O Mode .............: NORMAL
Autolocking ..........: Enabled
Precision.............: High
User Privileges ......: SELECT, UPDATE, INSERT, DELETE
Coordinate System ....: GEOGCS["GCS_North_American_1927",DATUM["D_North_American_1927",SPHEROID["Clarke_1866",6378206.4,294.9786982]],PRIMEM["Greenwich",0.0],UNIT["Degree",0.0174532925199433]]

Layer Configuration ..: GIS_DATA_BLOB

'sdelayer -o describe_long' output for the view
ArcSDE 9.3.1 for Oracle10g Build 1632 Thu Feb 26 12:05:37 2009
Layer    Administration Utility
-----------------------------------------------------
Layer Description ....: <None>
Table Owner ..........: LEASE
Table Name ...........: COUNTIES_MAP_STATUS_VW
Spatial Column .......: SHAPE
Layer Id .............: 3176
SRID .................: 329
Minimum Shape Id .....: 1
Offset ...............:
falsex:       -400.000000
falsey:       -400.000000
System Units .........: 1000000000.000000
Z Offset..............:          0.000000
Z Units ..............:          1.000000
Measure Offset .......: <None>
Measure Units ........: <None>
XY Cluster Tolerance .:          0.000000008983
Spatial Index ........:
parameter:    SPIDX_GRID,GRID0=3.3,FULL
exist:        Yes
array form:   3.3,0,0
Layer Envelope .......:
minx:      -179.22783,        miny:        24.54290
maxx:       -66.88582,        maxy:        71.46868
Entities .............: nac+
Layer Type ...........: SDE-LOB
Creation Date ........: 11/02/10 09:24:56
I/O Mode .............: NORMAL
Autolocking ..........: Enabled
Precision.............: High
User Privileges ......: SELECT
Coordinate System ....: GEOGCS["GCS_North_American_1927",DATUM["D_North_American_1927",SPHEROID["Clarke_1866",6378206.4,294.9786982]],PRIMEM["Greenwich",0.0],UNIT["Degree",0.0174532925199433]]

Layer Configuration ..: GIS_DATA_BLOB

I'm using a script to create the view (see below), which I have used to recreate the view many times.

set SDESERVER=ftwuxsdetst
set SDEINSTANCE=5154
set SDEUSER=BOUNDARY
set SDEPASS=%1

REM## The owner of the schema containing the view must have the privileges
REM## necessary to either select, insert, update, or delete rows from all the
REM## tables or views on which the view is based that are not in the same schema.
REM## The owner must be granted these privileges directly, rather than through a
REM## role. Also, the grant needs to include the WITH GRANT option (the -I
REM## parameter in SDE syntax).
sdetable -o grant -t COUNTIES_TOBIN -I -U LEASE -A SELECT -s %SDESERVER% -i %SDEINSTANCE% -u %SDEUSER% -p %SDEPASS%

REM## Change user to LEASE
set SDEUSER=lease
set SDEPASS=%2

REM## Delete the view if it exists
sdetable -o delete -t COUNTIES_MAP_STATUS_VW -s %SDESERVER% -i %SDEINSTANCE% -u %SDEUSER% -p %SDEPASS% -N

REM## Create map status view
sdetable -o create_view -T COUNTIES_MAP_STATUS_VW -t "BOUNDARY.COUNTIES_TOBIN c ,COUNTIES_MAP_STATUS_DATA d" -c

d.OBJECTID,d.ST_NAME,d.CNTY_NAME,d.STCNTY,d.AGMT_TYPE,d.INCOMPLETE_LEGAL,d.ARCMAP_REQUESTED,d.UPDATE_REQUESTED,d.NEEDS_REVIEW,d.POLYGEN_ERROR_GENERAL,d.POLYG

EN_ERROR_NO_GRID,d.POLYGEN_ERROR_NO_LEGAL,d.POLYGEN_ERROR_NO_LOT,c.SHAPE -w "c.STCTY=d.STCNTY" -s %SDESERVER% -i %SDEINSTANCE% -u %SDEUSER% -p %SDEPASS%

REM## Grant SELECT on the new views to LAND_USER
sdetable -o grant -t COUNTIES_MAP_STATUS_VW -U LAND_USER -A SELECT -s %SDESERVER% -i %SDEINSTANCE% -u %SDEUSER% -p %SDEPASS%

Do you think our Oracle version is the cause of our issue?

VinceAngelo

It would help if you posted the 'sdetable -o describe' output for the '1' and 'M' tables,
plus that of the view, and also posted the 'sdelayer -o describe_long' output for the
layer and the view.

What command did you use to create the view? Have you tried deleting and recreating
the view?

Note that Oracle 10.2.0.2 is not supported with ArcSDE 9.3.1 -- the minimum supported
release is Oracle 10.2.0.3.

- V

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.