Things are different in Oracle Database 12c with multitenancy option. My most recent example:
I tried to import a schema (new name “NEWSCHEMA”) with datapump IMPDP and REMAP_SCHEMA into the same pluggable database it has been exported from with EXPDP immediately before (name “OLDSCHEMA”), running as SYSTEM. I’m doing things like that with DBA permissions, since my users have lots of grants and stuff in the schemas, and when a DBA does the export and import, all settes right. (See the details for commands and parfiles below.)
But IMPDP fails with
ORA-39083: Object type INDEX failed to create with error:
ORA-31625: Schema NEWSCHEMA is needed to import this object, but is unaccessible
ORA-01031: insufficient privileges
So what? I’m SYSTEM and thus, DBA, and the user NEWSCHEMA is there. And SYSTEM of course has the “IMPORT FULL DATABASE” privilege, it’s a DBA! So you may think.
But maybe… My first attempt to fix this, pre-creating the user NEWSCHEMA and granting “imp_full_database”, did not help either.
In 12c, you can’t disable Database Vault (MOS Doc ID 948061.1). And with Database Vault, “imp_full_database” does not contain the “BECOME USER” privilege. So a DBA can’t create INDEX objects for another user. Good to know and to keep that in mind…
Check if that’s really the case:
where role in ('DBA', 'IMP_FULL_DATABASE')
and privilege like 'BECOME%';
If no rows returned, you found the culprit.
Fix it with:
alter session set container=YOUR_PDB_IN_QUESTION;
grant become user to imp_full_database;
==> Oracle says: Works as designed.
I have to misquote Tom Kyte here: “One guy’s feature may be a thousand guy’s bug…”
So keep your eyes open
My DB version: 220.127.116.11 Enterprise Edition on Linux x86_64 (Oracle Enterprise Linux 6)
"TO_TIMESTAMP(TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS')"
My import command:
impdp system@internal parfile=impdp_newschema.par