ORA-01031 Error When Loading Table Data into Another Schema
(Doc ID 3029371.1)
Last updated on JUNE 19, 2024
Applies to:
Oracle SQL Developer Web - Version 23.4 and laterInformation in this document applies to any platform.
Symptoms
When importing data into a new table owned by a different schema than the login user, creation of error table(SDW$ERR$_{TABLE_NAME}) fails with ORA-01031.
The problem occurs only on the first run. If the error table already exists, the write to the error table will succeed.
Error:
ORA-01031: insufficient privileges
Faild to create error logging table {SCHEMA_NAME}.SDW$ERR$_{TABLE_NAME}
Steps:
1. Create a user to perform the import operation and grant permissions as follows:
create user UserA identified by password;
grant resource, connect, dwrole to UserA;
grant create any table to UserA;
grant select any table to UserA;
grant insert any table to UserA;
grant update any table to UserA;
grant unlimited tablespace to UserA;
Then enable Web Access to UserA.
2. Log in to Database Actions using the user account created in the above step.
3. Move to Deveopment > SQL, then select a schema that is not the login user, assuming it is UserB here.
4. Create a new table in UserB schema.
create table UserB.testtab
(col1 number, col2 varchar2(10));
5. On Navigator pane, right click on {TABLE_NAME} then select "Data Loading" > Upload Data.
6. Here we want to generate an import error, so we upload a CSV that contains inappropriate data.
For example;
[test.csv]
COL1,COL2
a,b
As above, the import data is not appropriate, a table named SDW$ERR$_{TABLE_NAME} would normally be created to store the error contents, however ORA-01031 error is actually generated and no data is stored in the error table.
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Symptoms |
| Cause |
| Solution |
| References |