ora-30757 cannot access type information ??? oracle spatial

2104
1
Jump to solution
01-30-2014 06:11 AM
EmadAl-Mousa
Occasional Contributor III
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.
0 Kudos
1 Solution

Accepted Solutions
EmadAl-Mousa
Occasional Contributor III
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".

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;

it should be valid.

blog reference: http://geodatamaster.wordpress.com/2014/01/30/ora-30757-cannot-access-type-information-oracle-spatia...

regards,

View solution in original post

0 Kudos
1 Reply
EmadAl-Mousa
Occasional Contributor III
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".

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;

it should be valid.

blog reference: http://geodatamaster.wordpress.com/2014/01/30/ora-30757-cannot-access-type-information-oracle-spatia...

regards,
0 Kudos