dc2447
asked on
ORA-01031: insufficient privileges on import between two Oracle 8i databases
I'm doing an import of one database into another database.
Both are running 8i.
The database that the db was exported from had a single schema, the database I'm importing into has several other schema already.
I'm tring to consolidate the oracle instances.
The error on import is:
. . importing table "WWW_POLL" 24347 rows imported
IMP-00017: following statement failed with ORACLE error 1031:
"CREATE INDEX "TTPERSPROF_EMAIL" ON "TRUSTEE_PERSONALPROFILE" (LOWER("EMAIL""
") ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 17530880 NEXT 87244"
"80 MINEXTENTS 1 MAXEXTENTS 505 PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1"
" BUFFER_POOL DEFAULT) TABLESPACE "ARSENAL_IDX" LOGGING"
IMP-00003: ORACLE error 1031 encountered
ORA-01031: insufficient privileges
IMP-00017: following statement failed with ORACLE error 1031:
"CREATE INDEX "TTPERSPROF_USERNAME" ON "TRUSTEE_PERSONALPROFILE" (LOWER("USE"
"RNAME") ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 11714560 NEXT"
" 5816320 MINEXTENTS 1 MAXEXTENTS 505 PCTINCREASE 50 FREELISTS 1 FREELIST GR"
"OUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "ARSENAL_IDX" LOGGING"
IMP-00003: ORACLE error 1031 encountered
ORA-01031: insufficient privileges
About to enable constraints...
Import terminated successfully with warnings.
The user was created like this:
create user arsenal_build002 identified by password
default tablespace ARSENAL_DATA
temporary tablespace temp;
GRANT connect, resource TO arsenal_build002;
GRANT SELECT ANY TABLE TO arsenal_build002;
GRANT UNLIMITED TABLESPACE TO arsenal_build002;
The arsenal_idx tablesapce was created thus:
CREATE TABLESPACE ARSENAL_IDX
DATAFILE '/global/dbdg/u03/oradata/
AUTOEXTEND ON NEXT 50M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
Thanks
Both are running 8i.
The database that the db was exported from had a single schema, the database I'm importing into has several other schema already.
I'm tring to consolidate the oracle instances.
The error on import is:
. . importing table "WWW_POLL" 24347 rows imported
IMP-00017: following statement failed with ORACLE error 1031:
"CREATE INDEX "TTPERSPROF_EMAIL" ON "TRUSTEE_PERSONALPROFILE" (LOWER("EMAIL""
") ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 17530880 NEXT 87244"
"80 MINEXTENTS 1 MAXEXTENTS 505 PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1"
" BUFFER_POOL DEFAULT) TABLESPACE "ARSENAL_IDX" LOGGING"
IMP-00003: ORACLE error 1031 encountered
ORA-01031: insufficient privileges
IMP-00017: following statement failed with ORACLE error 1031:
"CREATE INDEX "TTPERSPROF_USERNAME" ON "TRUSTEE_PERSONALPROFILE" (LOWER("USE"
"RNAME") ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 11714560 NEXT"
" 5816320 MINEXTENTS 1 MAXEXTENTS 505 PCTINCREASE 50 FREELISTS 1 FREELIST GR"
"OUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "ARSENAL_IDX" LOGGING"
IMP-00003: ORACLE error 1031 encountered
ORA-01031: insufficient privileges
About to enable constraints...
Import terminated successfully with warnings.
The user was created like this:
create user arsenal_build002 identified by password
default tablespace ARSENAL_DATA
temporary tablespace temp;
GRANT connect, resource TO arsenal_build002;
GRANT SELECT ANY TABLE TO arsenal_build002;
GRANT UNLIMITED TABLESPACE TO arsenal_build002;
The arsenal_idx tablesapce was created thus:
CREATE TABLESPACE ARSENAL_IDX
DATAFILE '/global/dbdg/u03/oradata/
AUTOEXTEND ON NEXT 50M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
Thanks
What was the import statement that you used.
Usually, if I exporting and then importing into a different schema I use the following
imp userid=sys/password@sid fromuser=FROMUSER touser=TOUSER file=my-export-file
This will import the full export file, but be aware that if a table already exists you will get an error
and the table (nor data) will not be imported, but you can add ignore=y if you want the import to ignore the error
and import any data for that table.
imp userid=sys/password@sid fromuser=FROMUSER touser=TOUSER file=my-export-file
This will import the full export file, but be aware that if a table already exists you will get an error
and the table (nor data) will not be imported, but you can add ignore=y if you want the import to ignore the error
and import any data for that table.
ASKER
The import syntax used was:
imp arsenal_build002/passwod file=/global/dbdg/archive/
"The user who is doing import needs to have role
IMP_FULL_DATABASE/BECOME USER"
Can you expand on this please?
"imp userid=sys/password@sid fromuser=FROMUSER touser=TOUSER file=my-export-file"
The user arsenal_build002 exists in both databases, or am I misunderstanding?
thankyou all for your responses
imp arsenal_build002/passwod file=/global/dbdg/archive/
"The user who is doing import needs to have role
IMP_FULL_DATABASE/BECOME USER"
Can you expand on this please?
"imp userid=sys/password@sid fromuser=FROMUSER touser=TOUSER file=my-export-file"
The user arsenal_build002 exists in both databases, or am I misunderstanding?
thankyou all for your responses
Hi dc2447,
The error is being given due to arsenal_build002 not having enough privileges. The privileges for the user in the destination database must be the same as in the source database unless you use SYS to do the import.
The error message points to an error while trying to create a function based index. You would have to connect as SYS and grant the following:
GRANT QUERY REWRITE TO arsenal_build002;
You should also have the following parameters set in the init.ora for the destination database
QUERY_REWRITE_ENABLED=TRUE
QUERY_REWRITE_INTEGRITY=TR
You may then import the schema again or just rerun the create index commands from your import error log.
-HTH
SDutta
The error is being given due to arsenal_build002 not having enough privileges. The privileges for the user in the destination database must be the same as in the source database unless you use SYS to do the import.
The error message points to an error while trying to create a function based index. You would have to connect as SYS and grant the following:
GRANT QUERY REWRITE TO arsenal_build002;
You should also have the following parameters set in the init.ora for the destination database
QUERY_REWRITE_ENABLED=TRUE
QUERY_REWRITE_INTEGRITY=TR
You may then import the schema again or just rerun the create index commands from your import error log.
-HTH
SDutta
ASKER
SDutta,
thanks for the response.
I'm not keen to restart the database to do these changes
QUERY_REWRITE_ENABLED=TRUE
QUERY_REWRITE_INTEGRITY=TR
Is it possible to import as sys but retain ownership of objects to arsenal_build002?
thanks for the response.
I'm not keen to restart the database to do these changes
QUERY_REWRITE_ENABLED=TRUE
QUERY_REWRITE_INTEGRITY=TR
Is it possible to import as sys but retain ownership of objects to arsenal_build002?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
SDutta,
many many thanks the GRANT QUERY REWRITE TO arsenal_build002 worked beautifully.
Excellent.
Dave
many many thanks the GRANT QUERY REWRITE TO arsenal_build002 worked beautifully.
Excellent.
Dave
IMP_FULL_DATABASE/BECOME USER