Importing Materialized Views
Can anyone tell me what privileges one must have in order to import materialized views from schema PADOHSNAP in Database A to PADOHSNAP in Database B.
I use the following user to do the import and it imports 2 of the MV's as MV's and the other 339 as tables.
CREATE USER EXP_IMP_USER
IDENTIFIED BY VALUES 'XXXXXXXXXXXXXX'
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
-- 4 Roles for EXP_IMP_USER
GRANT EXECUTE_CATALOG_ROLE TO EXP_IMP_USER WITH ADMIN OPTION;
GRANT EXP_FULL_DATABASE TO EXP_IMP_USER;
GRANT CONNECT TO EXP_IMP_USER;
GRANT IMP_FULL_DATABASE TO EXP_IMP_USER;
ALTER USER EXP_IMP_USER DEFAULT ROLE ALL;
-- 4 System Privileges for EXP_IMP_USER
GRANT CREATE SESSION TO EXP_IMP_USER;
GRANT CREATE MATERIALIZED VIEW TO EXP_IMP_USER WITH ADMIN OPTION;
GRANT CREATE ANY MATERIALIZED VIEW TO EXP_IMP_USER;
GRANT CREATE TABLE TO EXP_IMP_USER;
-- 1 Tablespace Quota for EXP_IMP_USER
ALTER USER EXP_IMP_USER QUOTA UNLIMITED ON USERS;
-- 1 Object Privilege for EXP_IMP_USER
GRANT READ, WRITE ON DIRECTORY SYS.EXPORT_DUMP_DIR TO EXP_IMP_USER;
It fails with error :
ORA-31685: Object type MATERIALIZED_VIEW:"PADOHSNAP"."ACTOR_PARTICIPATION" failed due to insufficient privileges. Failing sql is:
CREATE MATERIALIZED VIEW "PADOHSNAP"."ACTOR_PARTICIPATION" USING ("ACTOR_PARTICIPATION", (8, 'NEDARX', 1, 0, 0, "PADOH", "ACTOR_PARTICIPATION", '2009-03-04 19:30:38', 0, 64457, '1950-01-01 12:00:00', '', 0, 10526025149, 0, NULL, (1, "ACTOR_PARTICIPATION_ID", "ACTOR_PARTICIPATION_ID", 0, 321, 0)), 1572961, 5, ('1950-01-01 12:00:00', 1410, 0, 0, 10526025149, 0, 0, 2, NULL, NULL),
I am most curious why NEDARX shows up in the error message since NEDARX is the database the export was taken from.