Views in Oracle
Creating views can improve query response times by restricting the number of tables and columns searched when a data request is made. You can create views that appear to client applications as feature classes or nonspatial tables. An ArcSDE view can reference a single table or feature class, or you can create views to join data from multiple tables.
Any user that has the required permissions can create a view using SQL or the ArcSDE sdetable command. Additionally, views can be used to edit data if the view is created to allow edits. For information on privileges needed to create or edit views, consult the Oracle for your release.
You can access view definitions in Oracle by querying the ALL_VIEWS data dictionary view.
The Oracle data dictionary stores view information
When you create views in an Oracle database using SQL or the sdetable command, the views are defined in the Oracle data dictionary. Therefore, you can query the Oracle data dictionary to see view definitions.
Oracle maintains a user-accessible view, ALL_VIEWS, that describes all views to which the connected user has access rights. The following is a definition of the ALL_VIEWS view.
DESCRIBE all_views;
|
Name |
Null? |
Type |
|---|---|---|
|
OWNER |
NOT NULL |
VARCHAR2(30) |
|
VIEW_NAME |
NOT NULL |
VARCHAR2(30) |
|
TEXT_LENGTH |
NUMBER |
|
|
TEXT |
LONG |
|
|
TYPE_TEXT_LENGTH |
NUMBER |
|
|
TYPE_TEXT |
VARCHAR2(4000) |
|
|
OID_TEXT_LENGTH |
NUMBER |
|
|
OID_TEXT |
VARCHAR2(4000) |
|
|
VIEW_TYPE_OWNER |
VARCHAR2(30) |
|
|
VIEW_TYPE |
VARCHAR2(30) |
|
|
SUPERVIEW_NAME |
VARCHAR2(30) |
|
|
EDITIONING_VIEW* |
VARCHAR2(1) |
|
|
READ_ONLY* |
VARCHAR2(1) |
*Present only in Oracle 11g databases.
Views have owners, names, and text. The TEXT column of ALL_VIEWS contains the text of the query that defines the view.
Besides restricting access to certain records, you would also use views on tables that contain more than one SDO_Geometry or ST_Geometry column so you can register them with ArcSDE. ArcSDE does not support layers with multiple geometry columns, so to register them, you must first create a view that contains only one geometry column. To learn how to do this for Oracle Spatial tables, refer to the Knowledge Base article Create an Oracle view of an Oracle Spatial layer (containing multiple geometry columns) and register it with ArcSDE. For instructions on doing this with tables containing multiple ST_Geometry columns, see Using spatial views on tables with an ST_Geometry column.