“-g” option in some ArcSDE administration commands is used to specify particularities for creating a spatial index, such as “sdelayer -o add”.
For ArcSDE to store geometries in Oracle Spatial’s SDO_GEOMETRY data type, there are three types of spatial indexes: RTREE, FIXED and HYBRID. Oracle and ESRI recommend using RTREE spatial indexes since Oracle 9i. So “-g RTREE” is used in ArcSDE commands that accept “-g” option.
SDO_GEOMETRY layers cannot use the grid-based spatial index used with ArcSDE compressed binary storage.
Examples:
-- load shapefile to a layer of SDO_GEOMETRY
shp2sde -o create -l sdo_layer,shape -f shape_file -a all -g RTREE -e a -u SDE_USER
-- register a SDO_GEOMETRY table as an ArcSDE layer
sdelayer -o register -l sdo_layer,GEOM -e a+ -g RTREE -C OBJECTID,USER -R 2 -i 5151 -s sde_server -u sde_user
Refer to ESRI Technical Article 26511.
Tuesday, January 22, 2008
SDO_GEOMETRY Layers and -g Option in ArcSDE Commands
Get Area and Length of Features Stored in Oracle Spatial
The area and length of features are not pre-calculated and stored by Oracle’s SDO_GEOMETRY data type constructor functions. SDO_GEOMETRY type does not have places to store area and length values.
Geometric functions SDO_GEO.SDO_AREA and SDO_GEOM.SDO_LENGTH can be used to calculate the values in SQL queries when needed.
-- calculate the area
SELECT OBJECTID, SDO_GEOM.SDO_AREA(SHAPE, 0.005) FROM THE_LAYER;
-- calculate the length
SELECT OBJECTID, SDO_GEOM.SDO_LENGTH(SHAPE, 0.005) FROM THE_LAYER;
0.005 is the tolerance.
Saturday, January 19, 2008
Notes on Auditing in Oracle Database
Notes on Auditing in Oracle Database (10g)
Auditing purposes:
--auditing what kinds of privileges are being used to uncover abuse or misuse of privileges
--auditing what objects are being accessed
Auditing location depending on initialization parameter AUDIT_TRAIL (SYS.AUD$, OS file):
--NONE, FALSE: disable auditing
--OS: enable auditing. Send audit results to an OS file
--DB, TRUE: enable auditing and send to SYS.AUD$
--DB_EXTENDED: enable auditing and send results to SYS.AUD$, store additional info in BLOB columns SQLBIND and SQLTEXT.
Auditing types
1. Statement auditing: audit SQL statements by the type of statement regardless of the specific schema objects being accessed. One or more users can be specified to be audited for a particular statement.
AUDIT sql_statement_clause BY {SESSION ACCESS} WHENEVER [NOT] SUCCESSFUL;
--ACCESS: every time
--SESSION: once, default
--WHENEVER SUCCESSFUL: successful action, statement did not generate an error
--WHENEVER NOT SUCCESSFUL: unsuccessful action, statement fails for insufficient privilege, syntax error, running out of space in the tablespace
--statement option:
--all, includes CLUSTER, CONTEXT, DATABASE_LINK, DIMENSION, DIRECTORY, INDEX, MATERIALIZED VIEW, NOT EXISTS, PROCEDURE, PROFILE, PUBLIC SYNONYM, ROLE, ROLLBACK SEGMENT, SEQUENCE, SESSION, SYNONYM, SYSTEM AUDIT, SYSTEM GRANT, TABLE, TABLESPACE, TRIGGER, TYPE, USER, VIEW.
--explicitly specified statements: ALTER SEQUENCE, ALTER TABLE, COMMENT TABLE, DELETE TABLE, EXECUTE PROCEDURE, GRANT DIRECTORY, GRANT PROCEDURE, GRANT SEQUENCE, GRANT TABLE, GRANT TYPE, INSERT TABLE, LOCK TABLE, SELECT SEQUENCE, SELECT TABLE, UPDATE TABLE
--example 1: audit index by scott
SQL>audit index by scott whenever successful;
SQL>select username, to_char(timestamp, 'MM/DD/YY HH24:MI') timestamp, obj_name, action_name, sql_text from dba_audit_trail where username='SCOTT';
SQL>noaudit index by scott;
--example 2: login audit
SQL>audit session whenever successful;
SQL>audit session whenever not successful;
SQL>select username, to_char(timestamp, 'MM/DD/YY HH24:MI') timestamp, obj_name, returncode, action_name, sql_text from dba_audit_trail where action_name in ('LOGON','LOGOFF') order by timestamp desc;
--example 3: Oracle database startup and shutdown audit
--shutdown immediate in SYS.AUD$
--startup: folder $ORACLE_HOME/rdbms/audit/ (determined by init parameter audit_file_dest)
--example: protect audit trail
SQL>audit all on sys.aud$ by access;
2. Privilege auditing: audit system privileges, such as CREATE TABLE, ALTER INDEX. Can specify one or more particular users as target of the audit.
--audit SYSDBA and SYSOPER privilege:
--set initialization parameter audit_sys_operations=true
--audit OS files sent to audit_file_dest
--example 1: audit every time
SQL>audit ALTER TABLESPACE by access whenever successful;
3. Schema object auditing: audit specific statements operating on a specific schema object (UPDATE). Applies to all users in the database.
--AUDIT schema_audit_clause BY {SESSION ACCESS} WHENEVER [NOT] SUCCESSFUL;
--schema_audit_clause: ALTER, AUDIT, COMMENT, DELETE, FLASHBACK, GRANT, INDEX, INSERT, LOCK, READ, RENAME, SELECT, UPDATE
--example 1:
SQL>audit insert, update on hr.jobs by access whenever successful;
4. Fine-grained auditing (FGA): audit table access and privileges based on the contents of the objects being accessed. DBMS_FGA to set up a policy.
--ADD_POLICY, DROP_POLICY, DISABLE_POLICY, ENABLE_POLICY
--DBA_FGA_AUDIT_TRAIL
--example: audit access to salary column
begin
dbms_fga.add_policy(
object_schema => 'HR',
object_name => 'EMPLOYESS',
policy_name => 'SAL_SELECT_AUDIT',
audit_condition => 'instr(job_id, ''_MAN'') > 0',
audit_column => 'SALARY'
);
end;
Auditing-related dictionary views
AUDIT_ACTIONS
DBA_AUDIT_OBJECT
DBA_AUDIT_POLICIES
DBA_AUDIT_SESSION
DBA_AUDIT_STATEMENT
DBA_AUDIT_TRAIL
DBA_FGA_AUDIT_TRAIL
DBA_COMMON_AUDIT_TRAIL
DBA_OBJ_AUDIT_OPTS
DBA_PRIV_AUDIT_OPTS
DBA_STMT_AUDIT_OPTS
Notes:
1. A non-DBA user within an Oracle database cannot enable the auditing features.
2. If auditing has been enabled, there are data dictionary views that anyone can use to view the audit trail.
3. The single audit trail table: SYS.AUD$.
4. USER_AUDIT_TRAIL: all audit records for many different types of actions, many of the columns may be inapplicable for any given row.
USER_AUDIT_OBJECT: for statements concerning objects
USER_AUDIT_SESSION: for connections and disconnections
USER_AUDIT_STATEMENT: for grant, revoke, audit, noaudit, and alter system commands issued by the user
DBA_AUDIT_TRAIL:
DBA_AUDIT_OBJECT: for statements concerning objects
DBA_AUDIT_SESSION: for connections and disconnections
DBA_AUDIT_STATEMENT: for grant, revoke, audit, noaudit, and alter system commands
5. View audit options:
DBA_OBJ_AUDIT_OPTS: describes auditing options on all objects.
USER_OBJ_AUDIT_OPTS: describes auditing options on all objects owned by the current user. This view does not display the OWNER column.
ALL_DEF_AUDIT_OPTS: contains default object-auditing options that will be applied when objects are created.
-/-: no default auditing
S/-: auditing whenever successful
-/S: auditing whenever not successful
6. View commands that can be audited:
AUDIT_ACTIONS: describes audit trail action type codes. This table can be used to map action type numbers to action type names.
7. DBA views without USER counterparts:
DBA_AUDIT_EXISTS:
DBA_PRIV_AUDIT_OPTS:
DBA_STMT_AUDIT_OPTS:
STMT_AUDIT_OPTION_MAP:
Reference: Oracle 10g DBA Handbook
Wednesday, January 16, 2008
ORA-06550
Thank you for visiting Spatial DBA - Oracle and ArcSDE.
Please visit Oracle DBA Tips (http://www.oracledbatips.com) for more Oracle DBA Tips.
===================================================================
ORA-06550: line string, column string:string
Cause: A PL/SQL compilation error has occurred. The numbers given for line and column are the location in the PL/SQL block where the error occurred.
Action: Refer to the following PL/SQL messages for more information about the error.
sde.log:
db_sda_execute_stmt::OCIStmtExecute (6550)
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-04044
ORA-04044: procedure, function, package, or type is not allowed here
Cause: A procedure, function, or package was specified in an inappropriate place in a statement.
Action: Make sure the name is correct or remove it.
sde.log:
[01/15/2008 13:52:40;SdeId=5924336;Client=gispc] db_describe_select describe error (4044), column 1.
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-02201
ORA-02201: sequence not allowed here
Cause: An attempt was made to reference a sequence in a from-list.
Action: A sequence can only be referenced in a select-list.
sde.log:
[01/15/2008 13:52:26;SdeId=5924336;Client=gispc] db_describe_select describe error (2201), column 1.
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-02019
ORA-02019: connection description for remote database not found
Cause: An attempt was made to connect or log in to a remote database using a connection description that could not be found.
Action: Specify an existing database link. Query the data dictionary to see all existing database links. See your operating system-specific Net8 documentation for valid connection descriptors.
sde.log:
[01/15/2008 13:52:49;SdeId=5924336;Client=gispc] db_describe_select describe error (2019), column 1.
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-01543
ORA-01543: tablespace 'string' already exists
Cause: Tried to create a tablespace which already exists
Action: Use a different name for the new 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-01502
ORA-01502: index 'string.string' or partition of such index is in unusable state
Cause: An attempt has been made to access an index or index partition that has been marked unusable by a direct load or by a DDL operation
Action: DROP the specified index, or REBUILD the specified index, or REBUILD the unusable index partition
Metalink:
Note:281500.1
-- initialization parameter SKIP_UNUSABLE_INDEXES in Oracle Database
-- Setting this parameter to TRUE disables error reporting of indexes and index partitions marked UNUSABLE.
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-01119
Thank you for visiting Spatial DBA - Oracle and ArcSDE.
Please visit Oracle DBA Tips (http://www.oracledbatips.com) for more Oracle DBA Tips.
===============================================================
ORA-01119: error in creating database file 'string'
Cause: Usually due to not having enough space on the device.
Action: none
imp.log:
. importing SYSTEM's objects into SYSTEM
IMP-00017: following statement failed with ORACLE error 1119:
"CREATE TABLESPACE "RBSBIG" DATAFILE '/fs/u02/oracle_data/mydb/rbsbig01"
".dbf' SIZE 629145600 , '/fs/u02/oracle_data/mydb/rbsbig02.dbf' SIZ"
"E 629145600 , '/fs/u02/oracle_data/mydb/rbsbig03.dbf' SIZE 8388608"
"00 DEFAULT STORAGE(INITIAL 2097152 NEXT 2097152 MINEXTENTS 2 MAXEXTE"
"NTS 2147483645 PCTINCREASE 1) ONLINE PERMANENT "
IMP-00003: ORACLE error 1119 encountered
ORA-01119: error in creating database file '/fs/u02/oracle_data/mydb/rbsbig01.dbf'
ORA-27040: file create error, unable to create file
SVR4 Error: 2: No such file or directory
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-00980
ORA-00980: synonym translation is no longer valid
Cause: The synonym used is based on a table, view, or synonym that no longer exists.
Action: Replace the synonym with the name of the object it references or re-create the synonym so that it refers to a valid table, view, or synonym.
sde.log:
[01/15/2008 13:52:40;SdeId=5924336;Client=GISPC] db_describe_select describe error (980), column 1.
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-00907
ORA-00907: missing right parenthesis ")"
Cause: A left parenthesis has been entered without a closing right parenthesis, or extra information was contained in the parentheses. All parentheses must be entered in pairs.
Action: Correct the syntax and retry the statement.
sde.log
db_sda_execute_stmt::OCIStmtExecute (907)
.
[01/08/2008 06:57:27;SdeId=5794874;Client=GISPC] db_array_fetch_attrs OCI Fetch Error (907)
[01/08/2008 06:57:27;SdeId=5794874;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-00439
ORA-00439: feature not enabled: string
Cause: The specified feature is not enabled.
Action: Do not attempt to use this feature.
All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors
Saturday, January 12, 2008
ORA-04063
ORA-04063: %s has errors
Cause: Attempt to execute a stored procedure or use a view that has errors. For stored procedures, the problem could be syntax errors or references to other, non-existent procedures. For views, the problem could be a reference in the view’s defining query to a non-existent table. Can also be a table which has references to non-existent or inaccessible types.
Action: Fix the errors and/or create referenced objects as necessary.
sde.log
[12/02/2007 06:09:17;SdeId=5502900;Client=GISServer] db_describe_select describe error (4063), column 1.
All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors
