Thursday 26 February 2009

GET ERROR ORA-00942 ON DATA MOVE STEP

For data move when you are not dba and not data move user additional privileges are required:

8228281 04-FEB-09 2 (null) 11 1875 MIGRATION NO RELEASE MYSQL QUICK
MIGRATION STOPED IN CONVERT STEP WHEN MIGRATE TABLE AND DATA

where hrcp2 is the user doing the data move:

Multi schema requires the following if you are not dba:

--disable triggers and disable table constraints
alter any table to hrcp2;

--disable primary keys also affects indexes.
create any index to hrcp2;
drop any index to hrcp2;
alter any index to hrcp2;

and if you are not the dba or the datamove user:

--select to get the datatypes (where 1 = 0) and insert to insert the data
grant select any table to hrcp2;
grant insert into table to hrcp2;

(optionally sqldeveloper created) datamove user is granted:
alter any trigger
and on each table is individually granted
select insert and alter

not sure how datamove user gets around:
create any index to hrcp2;
drop any index to hrcp2;
alter any index to hrcp2;
which is implicitly required as disabling a primary key constraint affects an
index.

No comments: