ora-30757 cannot access type information – oracle spatial

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s