REVOKE OBJECT PRIVILEGE ERROR

[lemmor]

I granted a REFERENCES object privilege to a user and when the user created a foreign key referencing to the table with REFERENCES privilege an error occured "table does not exist". Then, i tried to revoke the REFERENCES privilege of the object in order to grant again, but an error occurred
ORA-00604 : ERROR OCCURED AT RECURSIVE SQL LEVEL 1
ORA-01406 : FETCHED COLUMN VALUE WAS TRUNCATED

Any idea on how to solve these errors.
Thank you very much

Lemmor

 

[rcurva]

Here's what I got from Oracle:

This is due to [BUG:1686483] which is fixed in release 9.0.1. A possible
workaround is to drop the constraints manually.


For example:

Log on as system/manager and find out owner of table T13:

SQL> connect system/manager

SQL> Select owner from dba_tables where table_name='T13';

=> owner is system

Find out the constraints that reference the table T13:

SQL> select CONSTRAINT_NAME from dba_constraints where table_name='T13';

=>SYS_C001777

If there are more rows, check the constraints where
length (table_name)+length(constraint_name) is largest;
repeat if necessary next steps.


Find out which constraints these are:

SQL> select owner, table_name, constraint_name
from dba_constraints where r_constraint_name='SYS_C001777';

OWNER TABLE_NAME
------------------------------ ------------------------------
CONSTRAINT_NAME
------------------------------
SCOTT T23456789012345678901234567890
R23456789012345678901234567890


Drop the constraints; check for the constraints where
length(table_name)+ length(owner_name) is largest:

SQL> Connect scott/tiger

SQL> alter table T23456789012345678901234567890
drop constraint R23456789012345678901234567890;

SQL> connect system/manager

SQL> revoke references on t13 from public;

If not succesfull repeat above steps until revoke command generates success.

Hope this helps.

 

[lemmor]

rcurva,

Thank you so much that was a very brilliant idea. I was able to revoke the object privelege by dropping the primary constraint of the table.

lemmor