Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Importing Materialized Views

user453991Mar 6 2009 — edited Mar 6 2009
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.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 3 2009
Added on Mar 6 2009
5 comments
6,837 views