I was wondering if there are changes made to granting roles on ORACLE 9.2.0? I imported a user and his schema into ORACLE 9.2.0 with the option grants = Y after having imported it from ORACLE 9.0.1 with the same option grants = Y.
However when I try to select from any of the tables under that schema I get the error "ORA-00942: table of view does not exist. I have granted select on all the tables in that user's schema and still can select from it unless I put the schema name then the table name.
I have run in to similar problems in 8i also and sometimes the solution has been to grant select on every single table, one by one. This has been an issue when installing third party Oracle tools where some of the objects are owned by sys.
Originally posted by smallpin ... select from it unless I put the schema name then the table name.
As pando said, it has nothing to do with grants and roles, its the SYNONYMS isue. In your 9.0.1 you probably have public synonyms for those tables, but public synonyms were not imported into 9.2 database because you did user mode import.
You must create synonyms for those tables if you don't want to use schema name prefixes.
Jurij Modic ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Thanks for you help guys. I have created the sysnonyms for the tables and everything works fine now. However I will like to know how I can get around the problem of exp/imp a user and still exp/imp the the synonyms? Do I have to exp/imp the sys user in order to get the synonyms? Thanks in advance.