Month: January 2014

Ora-21700 Object Does Not Exist Or Is Marked For Delete When Dropping a User With ST_GEOMETRY

arcsdegeoLeave a comment

When trying to drop the SDE user, you are getting the error:

ORA-21700: object does not exist or is marked for delete

A corruption is introduced in your ArcSDE Geodatabase instance:

1. Try to drop user SDE using drop user cascade command: DROP USER SDE CASCADE;
This will generate error if there are objects created using SDE.ST_GEOMETRY because of dependencies

2. Try to drop the tables in other schemas using the SDE.ST_GEOMETRY type
Get also error ORA-21700: object does not exist or is marked for delete

3. Back to SDE and try to drop the SDE.ST_GEOMETRY type gives error
ORA-21700: object does not exist or is marked for delete 

The Cause of this issue:

Dictionary gets corrupted when tables using a specific object type (SDE.ST_GEOMETRY) are tried to be dropped after the type was tried to get dropped 

The Solution:

the objects cannot be dropped after this type of corruption is introduced

The objects can be left there and create a new database for this cartridge to be reinstalled or create a new database and do full exp/imp

In order to avoid this problem the objects using the SDE.ST_GEOMETRY type have to be dropped before dropping the SDE schema. Also the drop user SDE cascade should not be used.

 

ESRI Reference Link: http://support.esri.com/en/knowledgebase/techarticles/detail/34483

Oracle Metal ink reference document number 9876

Advertisement

Oracle Compilation errors using “show errors” commands – no errors

arcsdegeoLeave a comment

sometimes when you try to recompile an oracle object such as a “package” for example using the command line in “sqlplus”, you face a message that compiled with errors.

when you type: show errors

in sqlplus , the prompt states “no errors found”.

while actually there are errors and issue with the oracle object, to resolve this either check this oracle object under the schema using TOAD or sqldeveloper and go the “errors” tab and check what exactly is the issue.

OR

use the following query to fetch the error information in sqlplus:

select text from dba_errors where name = ‘[OBJECT_NAME]‘ and owner = ‘[schema_object]‘;

Where [OBJECT_NAME] is then name of Database object which is having the problem.

Where [schema_object] is the name of the schema user.

ora-30757 cannot access type information – oracle spatial

arcsdegeoLeave a comment

i have faced the following error: ora-30757 cannot access type information

 while trying to copy data from non-oracle spatial layer to an oracle spatial layer in ESRI ArcGIS Desktop.

so, I Checked  oracle spatial component status (valid/invalid) in the database using this query:

select COMP_NAME,VERSION,STATUS from dba_registry;

and i found out that was invalid:

Image
spatial invalid

Check what is causing the oracle spatial to be invalid, using this query:

select owner, object_type, object_name from dba_objects where status <>’VALID’ and owner=’MDSYS’; 

if there are invalid objects such as (packages,procedures,views,..etc) , try to re-compile them. Then check what is the error generated for compilation errors.

then, next step is to find out what is the issue with each (package,procedure,…etc) you got from the previous query. You can do that either through sql developer or TOAD. by going under the MDSYS schema and trying to compile these objects and check the “error” tab it will show what is causing this issue (it could be a permission issue for ‘mdsys’ schema).

if you don’t have Toad or sql developer then, use the following query:

select text from dba_errors where name = ‘[OBJECT_NAME]‘ and owner = ‘MDSYS‘;

Where [OBJECT_NAME] is then name of Database object which is having the problem.

it could be missing execute permission to system packages , for example (dbms_random) so you need grant this package to mdsys user as shown here:

 grant execute on sys.dbms_random to mdsys;

Then try to recompile all database objects using utilrip script in oracle.

using sqlplus as ‘sys’ user execute the following:

@$ORACLE_HOME/rdbms/admin/utlrp.sql

After that re-check that oracle spatial is valid using this query:

select COMP_NAME,VERSION,STATUS from dba_registry;

Image

 

Remark: these instructions is for Oracle database hosted in “Unix” environment.

The geodatabase system table could not be created. [GDB_Items : Error (-51)]

arcsdegeo1 Comment

When i wanted to upgrade my ArcSDE Geodatabase from version 9.3.1 SP2 to 10.1 SP1. I ran the pre-requisite tool which scanns fully my geodatabase and stated the scan passed successfully and your geodatabase is ready for upgrade.

After running the actual upgrade, i faced the below error:

//———————————————————————————————————————–

]Running Pre-Requisite check (C:\Users\XXX\AppData\Roaming\ESRI\Desktop10.1\ArcCatalog\XXX.sde).
Instance supports XML type.
Server tables and stored procedures are up to date.
The geodatabase system table could not be created. [GDB_Items : Error (-51)]
The geodatabase system table could not be created. [GDB_Items : Error (-51)]Unpaused the database.
Failed to execute (UpgradeGDB).
Failed at Thu Dec 26 09:38:54 2013 (Elapsed Time: 13.00 seconds)

//——————————————————————————————————————-

so, at the begining i though it could be something related to “missing privileges”  , which was NOT !!!

after more digging, i checked my local ArcGIS Desktop Logs (located under your user profile: C:\Users\youruserprofile\AppData\Local\ESRI\Desktop10.1)

from sde_setup.log file:

***********************************************************************************************************************

[Sun Jan 05 07:58:31 2014] SQL Stmt: <alter indextype SDE.st_spatial_index using SDE.st_domain_methods>
[Sun Jan 05 07:58:44 2014] St_Geometry type created or updated…

[Sun Jan 05 07:58:48 2014] Error creating GDB_Items table…
[Sun Jan 05 07:58:48 2014] ERROR Creating Geodatabase tables
, Error = -511
,EXT_Error = 29855
,EXT_ERROR1 = ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-10700: preference does not exist: CTXSYS.DEFAULT_LEXER
ORA-06512: at “CTXSYS.DRUE”, line 160
ORA-06512: at “CTXSYS.TEXTINDEXMETHODS”, line 366

************************************************************************************************************************

The errors seems to me to reference two issues (st_spatial_index issue) and (CTXSYS which is the Oracle Text).

The first thing i have performed is to re-validate my Oracle Text Component, which was performed successfully.

from sdedc_Oracle.log file:

***********************************************************************************************

PLS-00201: identifier ‘SDE.GDB_UTIL’ must be declared
ORA-06550: line 1, column 26:
PL/SQL: Statement ignored

*********************************************************************************************************************

after examining the GDB_items table in another ArcSDE Geodatabase version 10, i found out that the table has a “domain index” associated with it being created by an indextype named st_spatial_index .

sooo BOOM i found the real issue which indextype is for some reason missing from my Geodatabase !!!

when i tried to create the index type using this query:

=========================================================================

CREATE OR REPLACE INDEXTYPE “SDE”.”ST_SPATIAL_INDEX” FOR ”SDE”.”ST_BUFFER_INTERSECTS” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”, NUMBER), ”SDE”.”ST_CROSSES” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”), ”SDE”.”ST_ENVINTERSECTS” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”),  “SDE”.”ST_ENVINTERSECTS” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”, VARCHAR2),”SDE”.”ST_ENVINTERSECTS” (“SDE”.”ST_GEOMETRY”, NUMBER, NUMBER, NUMBER, NUMBER, VARCHAR2),  “SDE”.”ST_ENVINTERSECTS” (“SDE”.”ST_GEOMETRY”, NUMBER, NUMBER, NUMBER, NUMBER), ”SDE”.”ST_EQUALS” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”), ”SDE”.”ST_INTERSECTS” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”), ”SDE”.”ST_ORDERINGEQUALS” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”),  “SDE”.”ST_OVERLAPS” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”), ”SDE”.”ST_RELATE” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”, VARCHAR2), ”SDE”.”ST_TOUCHES” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”),”SDE”.”ST_WITHIN” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”)USING “SDE”.”ST_DOMAIN_METHODS” WITH LOCAL RANGE PARTITION;

*********************************************************************************************************************

the error faced is the following:

Image

 

so the inextype could not be created because there are missing “operators” for some reason ??!!!

so i compared the list of operators between my current geodatabase that i need to upgrade to another 10.0 geodatabase instance, using the sql query: select * from user_operators;

Image

 

after comparing the two lists, i found out the “missing” operators and i have created them.

In order to extract the sql definition of the operator use the following query as an example:

select dbms_metadata.get_ddl(‘OPERATOR’,’ST_WITHIN’) from dual;

after creating all missing operators, try executing the previous sql query mentioned: “CREATE OR REPLACE INDEXTYPE “SDE”.”ST_SPATIAL_INDEX“

then use the following sql, to check indextype was created:

select * from user_indextypes;

Image

I re-ran the upgrade again, and it was SUCCESSFUL !

SQL Query Reference:

Query Reference:

select * from user_operators;

select * from user_indextypes;

select dbms_metadata.get_ddl(‘INDEXTYPE’,’ST_SPATIAL_INDEX’) from dual;

select dbms_metadata.get_ddl(‘OPERATOR’,’ST_WITHIN’) from dual;

SDE and DBMS_CRYPTO

arcsdegeoLeave a comment

starting from ArcGIS version 10.1 while upgrading your ArcSDE Geodatabase in Oracle, one of the pre-requisite required “sde” user privilege is direct execution to the Package DBMS_CRYPTO.

grant execute on SYS.DBMS_CRYPTO to sde;

according to esri documentation: http://resources.arcgis.com/en/help/main/10.1/index.html#//002n0000002v000000

This granting has a relation to st_geometry configuration:

1. Allows the creation of a map member function for the ST_Geometry type, which is called whenever a spatial union or intersection is performed.

2. Upgrade a map member function for the ST_Geometry type, which is called whenever a spatial union or intersection is performed.

the question is: has this privilege has any relation with data encryption ?

Most Probably ‘YES ‘ : according to Oracle Documentation “DBMS_CRYPTO provides an interface to encrypt and decrypt stored data, and can be used in conjunction with PL/SQL programs running network communications. It provides support for several industry-standard encryption and hashing algorithms, including the Advanced Encryption Standard (AES) encryption algorithm.”

So, if you enable data encryption at rest on your database management system, then sde will be able to decrypt data for your geodatabase system.

Oracle Reference Link:http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_crypto.htm

how to fetch or extract oracle database operator

arcsdegeoLeave a comment

To fetch (extract) sql definition of an Oracle Database Operator or Indextype, this can be done through the DBMS_METADATA package. Moreover,  DBMS_METADATA provides a way for you to retrieve metadata from the database dictionary as XML or creation DDL and to submit the XML to re-create the object.

select dbms_metadata.get_ddl(‘OPERATOR’,’operatorname‘) from dual;

operatorname : substitute it with the actual name of the operator

This can be done also for indextype, you can extract the sql definition using the below query:

select dbms_metadata.get_ddl(‘INDEXTYPE’,’indextypename’) from dual;

 

Underlying DBMS error [Layer not found] [Error executing PL/SQL Block db_stgeom_create_indexORA-29833: indextype does not exist]

arcsdegeo1 Comment

while upgrading ArcSDE Geodatabase from version 9.3.1 SP2 to version 10 SP5, the below error was faced:

Underlying DBMS error [Layer not found] [Error executing PL/SQL Block db_stgeom_create_indexORA-29833: indextype does not exist]

This error indicates that you are missing an indextype in your ArcSDE Geodatabase in Oracle, the indextype name is: “ST_SPATIAL_INDEX”

when attempting to execute the following Sql Query to create the index type:

*******************************************************************************************************

CREATE OR REPLACE INDEXTYPE “SDE”.”ST_SPATIAL_INDEX” FOR “SDE”.”ST_BUFFER_INTERSECTS” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”, NUMBER), “SDE”.”ST_CROSSES” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”), “SDE”.”ST_ENVINTERSECTS” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”),  “SDE”.”ST_ENVINTERSECTS” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”, VARCHAR2),”SDE”.”ST_ENVINTERSECTS” (“SDE”.”ST_GEOMETRY”, NUMBER, NUMBER, NUMBER, NUMBER, VARCHAR2),  “SDE”.”ST_ENVINTERSECTS” (“SDE”.”ST_GEOMETRY”, NUMBER, NUMBER, NUMBER, NUMBER), “SDE”.”ST_EQUALS” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”), “SDE”.”ST_INTERSECTS” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”), “SDE”.”ST_ORDERINGEQUALS” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”),  “SDE”.”ST_OVERLAPS” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”), “SDE”.”ST_RELATE” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”, VARCHAR2), “SDE”.”ST_TOUCHES” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”),”SDE”.”ST_WITHIN” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”)USING “SDE”.”ST_DOMAIN_METHODS” WITH LOCAL RANGE PARTITION;

**********************************************************************************************************

the following error was received:

ORA-29830: operator does not exist
ORA-29830: operator does not exist

This error indicates that for some reason there are missing operators in your ArcSDE geodatabase, you need to create them.

but, first you need to query the complete list of operators from another ArcSDE geodatabase that is intact and healthy, using the query: select * from user_operators;

Image

compare the list of operators where the ArcSDE geodatabase that has the upgrade issue, and create the missing ones.

In order to extract the sql definition of the operator use the following query as an example:

select dbms_metadata.get_ddl(‘OPERATOR’,’ST_WITHIN’) from dual;

after creating all missing operators, try executing the previous sql query mentioned: “CREATE OR REPLACE INDEXTYPE “SDE”.”ST_SPATIAL_INDEX”

then use the following sql, to check indextype was created:

select * from user_indextypes;

ST_SPATIAL_INDEX indextype
ST_SPATIAL_INDEX indextype

Re-Run the Upgrade, and it will be successful.

Query Reference:

select * from user_operators;

select * from user_indextypes;

select dbms_metadata.get_ddl(‘INDEXTYPE’,’ST_SPATIAL_INDEX’) from dual;

select dbms_metadata.get_ddl(‘OPERATOR’,’ST_WITHIN’) from dual;

If this is not the case you are facing….but facing the same error then try the following:

 

SQL> GRANT EXECUTE ON sde.st_spatial_index TO SCHEMA_NAME;

OR

SQL> GRANT EXECUTE ON sde.st_spatial_index TO PUBLIC;

AND

SQL> GRANT CREATE INDEXTYPE TO SCHEMA_NAME;

 

Oracle Temporary Table Space size

arcsdegeoLeave a comment

As a DBA you may face a problem with your database if TEMP tablespace gets full. Usually, TEMP table space is used for “sorting and aggregate operations“.

One of the reasons your TEMP tablspace get full and expanding, is that the application queries are not well optimized/tuned.

the following queries will eventually help you in diagnosis:

To Find SQL Statements that is causing TEMP SPACE TO BE FILLED:
//——————————————————————–

select s.sid || ‘,’ || s.serial# sid_serial, s.username,

o.blocks * t.block_size / 1024 / 1024 mb_used, o.tablespace,

o.sqladdr address, h.hash_value, h.sql_text
from v$sort_usage o, v$session s, v$sqlarea h, dba_tablespaces t
where o.session_addr = s.saddr
and o.sqladdr = h.address (+)
and o.tablespace = t.tablespace_name
order by s.sid;
/ /———————————————————————
To Find Current TEMP SPACE SIZE:

select * from (select a.tablespace_name,
sum(a.bytes/1024/1024) allocated_mb
from dba_temp_files a
where a.tablespace_name = upper(‘&&temp_tsname’) group by a.tablespace_name) x,
(select sum(b.bytes_used/1024/1024) used_mb,
sum(b.bytes_free/1024/1024) free_mb
from v$temp_space_header b
where b.tablespace_name=upper(‘&&temp_tsname’) group by b.tablespace_name);