Cloud & Migration

ORA-00942 After a Data Pump Migration: The Table Is There. The Grant Isn't.

· 10 min read

The migration went to plan. The export and import finished, the row counts match, and the application owner can see every table. Then the first users log in to the new database:

ORA-00942: table or view does not exist

The table is there. You can select from it as its owner. What didn’t arrive is the path to it: the synonym the application uses for its name, the role that carried the privilege, or the grant that failed because the account it was for didn’t exist yet. A schema-mode export carries what the schema owns, and none of those belong to it.

This post covers what a schema-mode export leaves behind, how to read the error on 26ai to tell which one you hit, the query that lists the gaps on the source before you export, and how to carry them over with Data Pump. It ends with a lab that proves each step on Oracle Database Free 26ai.

What a schema export carries, and what it doesn’t

expdp SCHEMAS=APP exports what APP owns: its tables and data, views, code, sequences, the user definition itself (when a privileged user runs the export), its system privileges, and the grants on its objects. The export log in the lab listed exactly those object types.

What it doesn’t carry is everything that lives outside the schema, even when it points at it:

objectowned byin SCHEMAS=APP export?after import
APP.ORDERS, its data and indexesAPPyesthere
GRANT SELECT ON app.orders TO reportingAPP’s objectyesapplied, if REPORTING exists
GRANT SELECT ON app.customers TO app_readAPP’s objectyesfails if role APP_READ doesn’t exist
role APP_READ, and who holds itthe databasenomissing
PUBLIC synonym ORDERS for APP.ORDERSPUBLICnomissing, no error
REPORTING’s private synonym for APP.ORDERSREPORTINGnomissing, no error
GRANT SELECT ON ref.currencies TO appREF’s objectonly in REF’s exportapplied if APP exists when REF is imported

That last column is the problem. Data Pump reports what failed. It can’t report what it never had. In the lab the import finished with one error, and two of four readers were broken.

The lab’s migration

The source has two application schemas and three readers, each reaching the tables a different way:

REPORTING   select count(*) from app.orders       direct grant from APP
WEBAPP      select count(*) from orders           public synonym ORDERS + direct grant
ANALYST     select count(*) from app.customers    grant to role APP_READ, which ANALYST holds
APP         select count(*) from order_totals     its own view, joins REF.CURRENCIES (grant from REF)

All four work on the source. The migration is the common one: the accounts the readers use are created on the new database ahead of time, then expdp SCHEMAS=REF,APP and one impdp of the same. The import log:

. . imported "APP"."CUSTOMERS"                             7.4 KB     100 rows
. . imported "APP"."ORDERS"                               24.7 KB    1000 rows
. . imported "REF"."CURRENCIES"                            5.5 KB       2 rows
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'APP_READ' does not exist
Failing sql is:
GRANT SELECT ON "APP"."CUSTOMERS" TO "APP_READ"
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s)

One error, and it’s easy to file under “a role we’ll sort out later”. The same four reads on the new database:

REPORTING   1000
WEBAPP      ORA-00942: table or view "WEBAPP"."ORDERS" does not exist
ANALYST     ORA-00942: table or view "APP"."CUSTOMERS" does not exist
APP         2

The direct grant travelled with APP. The view worked because REF and APP were in the same job. The public synonym is simply gone, with nothing in the log. The role is gone, so the grant to it failed, so ANALYST has no privilege.

Read the message: 26ai names the object

On 26ai, ORA-00942 includes the object the database looked for, and that name tells you which problem you have.

An unqualified name shows the caller’s own schema. WEBAPP ran select ... from orders. Name resolution checks the user’s own schema first, then public synonyms. With neither, the message reports the name in the caller’s schema: "WEBAPP"."ORDERS". When you see the caller’s own schema in the message and the table belongs to someone else, look for a missing synonym.

A qualified name shows the real object. ANALYST ran select ... from app.customers and got "APP"."CUSTOMERS". The table exists, so this is a privilege problem: a missing grant, or a grant to a role the user doesn’t hold or that doesn’t exist.

A missing grant and a missing table look the same. In the lab, a user with no privilege on APP.CUSTOMERS and a query on a table that doesn’t exist at all both got ORA-00942: table or view "APP"."..." does not exist. That’s deliberate: the database doesn’t tell a user without privileges whether an object exists. Check from a DBA account:

SELECT owner, object_type, status FROM dba_objects WHERE object_name = 'CUSTOMERS';
SELECT grantee, privilege FROM dba_tab_privs WHERE owner = 'APP' AND table_name = 'CUSTOMERS';

Then compare the grantees with what the user actually holds (DBA_ROLE_PRIVS for its roles).

On 26ai the ORA-00942 message names the object the database resolved. The caller's own schema points at a missing synonym; the real owner's schema points at a missing privilege. A table missing as a DBA sees it is a genuinely missing object.

Separate jobs, wrong order

Large migrations often import one schema at a time: smaller jobs, easier restarts. Data Pump orders the objects inside a job, but it can’t order two jobs for you.

The lab imported REF and then APP as separate jobs, before the reader accounts existed:

REF job:  GRANT SELECT ON "REF"."CURRENCIES" TO "APP"       ORA-01917  (APP doesn't exist yet)
APP job:  GRANT SELECT ON "APP"."CUSTOMERS" TO "APP_READ"   ORA-01917
          GRANT SELECT ON "APP"."ORDERS" TO "REPORTING"     ORA-01917
          GRANT SELECT ON "APP"."ORDERS" TO "WEBAPP"        ORA-01917
          ORA-39082: VIEW "APP"."ORDER_TOTALS" created with compilation warnings

Now APP itself is broken, inside its own schema:

select count(*) from ref.currencies   ORA-00942: table or view "REF"."CURRENCIES" does not exist
select count(*) from order_totals     ORA-04063: view "APP.ORDER_TOTALS" has errors

Creating the reader accounts afterwards doesn’t help. REPORTING could log in, but still got ORA-00942: the grant was attempted once, during the import, and failed. Nothing retries it.

The repair is to re-run only the grants from the same dump file, once every grantee exists:

impdp system@target directory=MIG dumpfile=app.dmp schemas=REF,APP include=OBJECT_GRANT

That brought REPORTING back, and the view came back too: it was VALID on its next use, with no manual recompile. Better still is not needing the repair: create the accounts first, and import every schema that grants to another in one job.

Before the export: list what schema mode leaves behind

Run this on the source, with the list of schemas you’re migrating. Every row is something the import won’t create, or a grant that depends on something existing first:

WITH migrated AS (SELECT column_value AS owner FROM TABLE(sys.odcivarchar2list('REF', 'APP')))
SELECT 'role (not exported)' kind, p.grantee || ' has ' || p.privilege || ' on ' || p.owner || '.' || p.table_name detail
FROM   dba_tab_privs p JOIN dba_roles r ON r.role = p.grantee
WHERE  p.owner IN (SELECT owner FROM migrated)
UNION ALL
SELECT 'role member (not exported)', m.grantee || ' holds ' || m.granted_role
FROM   dba_role_privs m
WHERE  m.granted_role IN (SELECT grantee FROM dba_tab_privs WHERE owner IN (SELECT owner FROM migrated))
AND    m.grantee NOT IN ('SYS', 'SYSTEM')
UNION ALL
SELECT 'public synonym (not exported)', 'PUBLIC.' || s.synonym_name || ' -> ' || s.table_owner || '.' || s.table_name
FROM   dba_synonyms s WHERE s.owner = 'PUBLIC' AND s.table_owner IN (SELECT owner FROM migrated)
UNION ALL
SELECT 'synonym in another schema', s.owner || '.' || s.synonym_name || ' -> ' || s.table_owner || '.' || s.table_name
FROM   dba_synonyms s
WHERE  s.owner <> 'PUBLIC' AND s.table_owner IN (SELECT owner FROM migrated)
AND    s.owner NOT IN (SELECT owner FROM migrated)
UNION ALL
SELECT 'grantee must exist first', p.grantee || ' (' || p.privilege || ' on ' || p.owner || '.' || p.table_name || ')'
FROM   dba_tab_privs p JOIN dba_users u ON u.username = p.grantee
WHERE  p.owner IN (SELECT owner FROM migrated) AND p.grantee NOT IN (SELECT owner FROM migrated)
UNION ALL
SELECT 'grant between migrated', p.owner || '.' || p.table_name || ' -> ' || p.grantee
FROM   dba_tab_privs p
WHERE  p.owner IN (SELECT owner FROM migrated) AND p.grantee IN (SELECT owner FROM migrated)
ORDER  BY 1, 2;

On the lab’s source it returned every gap the import hit:

KIND                        DETAIL
grant between migrated      REF.CURRENCIES -> APP
grantee must exist first    REPORTING (SELECT on APP.ORDERS)
grantee must exist first    WEBAPP (SELECT on APP.ORDERS)
public synonym (not exp.)   PUBLIC.ORDERS -> APP.ORDERS
role (not exported)         APP_READ has SELECT on APP.CUSTOMERS
role member (not exported)  ANALYST holds APP_READ

Two things it doesn’t cover, which belong on the same checklist: system privileges like SELECT ANY TABLE held by the readers (look in DBA_SYS_PRIVS), and database links the application uses by name.

Carry it over

Roles and public synonyms: a filtered full-mode export. A full export carries both, and INCLUDE with a subquery restricts it to the ones that point at your schemas. Metadata only, a few seconds:

# extras.par
full=y
content=metadata_only
directory=MIG
dumpfile=extras.dmp
include=ROLE:"IN (SELECT grantee FROM dba_tab_privs WHERE owner IN ('REF','APP'))"
include=PUBLIC_SYNONYM/SYNONYM:"IN (SELECT synonym_name FROM dba_synonyms WHERE owner = 'PUBLIC' AND table_owner IN ('REF','APP'))"
expdp system@source parfile=extras.par
impdp system@target directory=MIG dumpfile=extras.dmp

Use a parameter file. On the command line, the quotes in those filters need shell escaping, which is easy to get wrong (more on that in Data Pump, Done Right). Run impdp ... sqlfile=extras.sql first if you want to read the DDL before it runs. In the lab the file held one CREATE ROLE and one CREATE PUBLIC SYNONYM.

Who holds the role: generate it. ROLE_GRANT can’t be filtered by name in a full export. The lab tried and got ORA-31600: invalid input value NAME_EXPR. Unfiltered, it exports every role grant in the database, including ones for accounts that won’t exist on the target. Generate the statements on the source instead:

SELECT 'GRANT ' || granted_role || ' TO ' || grantee ||
       CASE admin_option WHEN 'YES' THEN ' WITH ADMIN OPTION' END || ';'
FROM   dba_role_privs
WHERE  granted_role IN (SELECT grantee FROM dba_tab_privs WHERE owner IN ('REF', 'APP'))
AND    grantee NOT IN ('SYS', 'SYSTEM');

Then re-run the object grants, now that the role exists:

impdp system@target directory=MIG dumpfile=app.dmp schemas=REF,APP include=OBJECT_GRANT

Grants that already succeeded are applied again without an error. After these three steps, all four readers worked on the new database.

The alternative is a full export of the whole database, which carries all of this by design. It also carries everything else: every account, every role, every public synonym, including the ones you were leaving behind. For moving a few application schemas into an existing database, schema mode plus the extras above is the controlled version.

The one that isn’t the migration

One more case comes up during the clean-up. ANALYST can now read APP.CUSTOMERS in SQL, through the role. Then a procedure that does the same SELECT fails to compile:

PL/SQL: ORA-00942: table or view "APP"."CUSTOMERS" does not exist

The lab compiled the same procedure on the source and got the same error. That’s not migration damage. A definer’s-rights procedure is compiled without the owner’s roles, so a privilege that comes only through a role doesn’t exist for it. It worked before because someone, at some point, gave the owner a direct grant. A GRANT SELECT ON app.customers TO analyst made the procedure VALID. If the code on the new database won’t compile, check whether the source had direct grants that the role-based setup on the target is standing in for.

Prove it

The lab runs Oracle Database Free in a container. FREEPDB1 is the source; the lab creates a second pluggable database, TARGET, as the new database, and runs the migration between them with expdp and impdp:

drill      what happens                                           result
import     accounts first, one impdp SCHEMAS=REF,APP              1 error in the log; public-synonym and
                                                                  role readers ORA-00942, other two fine
order      REF then APP as separate jobs, no accounts yet         4 failed grants; APP: ORA-00942 on REF,
                                                                  ORA-04063 on its view; OBJECT_GRANT re-run fixes
message    the 26ai text                                          unqualified: caller's schema; no grant and
                                                                  no table read the same
preflight  the source query                                       1 role, 1 member, 1 public synonym,
                                                                  2 grantees, 1 grant between schemas
fix        filtered full export, role grant, OBJECT_GRANT re-run  4 of 4 reads work
plsql      role works in SQL, not in a procedure                  ORA-00942 on source and target;
                                                                  direct grant makes it VALID

Don’t take my word for it — run it. The migration-ora-00942 lab asserts every row: exactly one error in the import log and nothing about the synonym; which reader fails and the object named in each message; the four failed grants, the cross-schema ORA-00942 and the ORA-04063 when the jobs run in the wrong order; the view VALID again after the grants are re-run; the preflight counts; all four reads after the fix; and the procedure failing on both databases until the direct grant. If any of that doesn’t reproduce, the run fails. Proven on every CI push.

What teams get wrong

  • Trusting “completed with 1 error”. The log lists failures, not omissions. A missing public synonym produces no error at all.
  • Reading the error as “the table didn’t migrate”. On 26ai, read the name in the message: the caller’s schema means a synonym, the owner’s schema means a privilege.
  • Importing schemas in separate jobs without an order. A grant to a schema or account that doesn’t exist yet fails once and is never retried. Accounts first, related schemas in one job, or re-run INCLUDE=OBJECT_GRANT at the end.
  • Fixing it with SELECT ANY TABLE. It makes the error go away and gives the account every table in the database. Recreate the grants the source had.
  • Testing the migration as the schema owner. The owner can see everything it owns. Test as each account the application and its users actually connect with, with the same SQL they run.

Frequently asked questions

Why do I get ORA-00942 after a Data Pump import when the table exists?

Because the table arrived but the path to it didn't. A schema-mode export carries what the schema owns: its objects and the grants on them. It doesn't carry public synonyms, roles, other users, or synonyms owned by other schemas. So a user who reached the table through a public synonym or a role gets ORA-00942 on the new database. In the companion lab, a schema-mode import that reported one error left two of four readers broken.

Does expdp SCHEMAS export public synonyms?

No. Public synonyms are owned by PUBLIC, not by the schema, so a schema-mode export doesn't include them, and the import gives no error about them. Export them separately with a full-mode, metadata-only export filtered with INCLUDE=PUBLIC_SYNONYM/SYNONYM and a subquery on DBA_SYNONYMS for the schemas you're moving, or create them from a script generated on the source.

Does a schema-mode export include grants?

It includes the grants on the schema's own objects, and they are applied on import if the grantee exists. If the grantee is a role that doesn't exist on the target, or a user not yet created, the grant fails with ORA-39083 and ORA-01917 and is not retried. Grants made on other schemas' objects to this schema are exported with those other schemas, not with this one.

How do I re-run grants after a Data Pump import?

Import the same dump file again with INCLUDE=OBJECT_GRANT, after the missing users and roles exist. Only the grants are processed; ones that already succeeded don't cause errors. In the companion lab this fixed a reader that had failed with ORA-00942 and made an invalid view valid again without a manual recompile.

What does the ORA-00942 message show on Oracle 26ai?

It names the object the database tried to resolve, for example: table or view "APP"."CUSTOMERS" does not exist. For an unqualified name with no matching synonym, it shows the caller's own schema, which points to a missing synonym. A missing privilege and a missing table produce the same message, so check DBA_OBJECTS and DBA_TAB_PRIVS from a DBA account to tell them apart.

Why does my query work in SQL but fail with ORA-00942 in a stored procedure?

Because the privilege comes from a role. Definer's-rights PL/SQL is compiled without the owner's roles, so a SELECT that works in SQL through a role fails inside a procedure. Grant the privilege directly to the procedure's owner. This isn't caused by a migration: in the companion lab the same procedure failed on the source database too.

ORA-00942 after a migration is rarely a missing table. It’s a missing synonym, role or grant: the things around a schema that a schema export doesn’t own. List them on the source before the export, read the name in the 26ai message when something breaks, and re-run the grants from the dump file once every account exists. It’s the same lesson as sequences after cutover: the data moves, and the state around it has to be moved on purpose. Then prove it with the migration-ora-00942 lab, where one import with one error leaves half the readers broken.

Have a question or some feedback?

I write here in a personal capacity and enjoy comparing notes with other Oracle folks. Say hello.

Get in touch