My Oracle Support Banner

After Export And Import The Error ORA-942 Is Raised While Accessing Tables (Doc ID 947974.1)

Last updated on APRIL 03, 2020

Applies to:

Oracle Database - Enterprise Edition - Version and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.


After exporting a 9.2 database and then importing into an 11.1 database you find that privileges that are granted via a role not working for the user as they did in 9.2.

A simple select on a table that has privileges granted via a role will return an ORA-942.

The issue is that after import into, a user receives ORA-942 when doing a SELECT from a table in another schema,  even though the user was granted a role that in turn has SELECT on the table.

This behavior only occurs immediately after import.

NOTE: Problem does NOT occur after dropping/recreating the user and re-granting the role. 

Notice that there is no active roles in SCOTT session even if this user has two roles granted.

Looking for "active" roles immediately after login:

When a role is initially granted (as when the role is recreated the user and re-granted the role), the role is enabled at that time -- which explains why it works when we drop/recreate the user and re-grants the role.

The role will need to be enabled after every login using the SET ROLE command. If the role is supposed to be default, then use ALTER USER to set the desired role(s) as default.


To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.