asked on

IMPORT OF ORACLE 9 EXP FILE FAILS ON ORACLE 10G RELEASE 10.2.0.1.0

Good Morning Team,

Here's my problem:

This sql fails with an ORA-01031:

CREATE TABLE SCENARIO (
            SCENARIO_OBJECT LONG RAW,
            LONG_RAW_SIZE NUMBER (*,0) NOT NULL ENABLE,
            SCENARIO_NAME VARCHAR2(255) NOT NULL ENABLE)
            PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
            STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1)
            TABLESPACE TEST_JAR NOLOGGING NOCOMPRESS'

The failure occurs during an IMP command done by TEST_USER.. The export file was created by EXPORT:V09.02.00. The database is Oracle 10g Release 10.2.0.1.0.

HERE IS A DESCRIPTION OF HOW THE DATABASE WAS SET UP:

1) Create role TEST_ROLE

2) execute this sql as sysdba:
GRANT ALTER PROFILE TO TEST_ROLE;
GRANT ALTER USER TO TEST_ROLE;
GRANT CREATE ANY INDEX TO TEST_ROLE;
GRANT CREATE ANY SEQUENCE TO TEST_ROLE;
GRANT CREATE ANY TABLE TO TEST_ROLE;
GRANT CREATE ANY VIEW TO TEST_ROLE;
GRANT CREATE USER TO TEST_ROLE;
GRANT DELETE ANY TABLE TO TEST_ROLE;
GRANT DROP ANY INDEX TO TEST_ROLE;
GRANT DROP ANY SEQUENCE TO TEST_ROLE;
GRANT DROP ANY TABLE TO TEST_ROLE;
GRANT DROP ANY VIEW TO TEST_ROLE;
GRANT GRANT ANY ROLE TO TEST_ROLE;
GRANT INSERT ANY TABLE TO TEST_ROLE;
GRANT SELECT ANY SEQUENCE TO TEST_ROLE;
GRANT SELECT ANY TABLE TO TEST_ROLE;
GRANT UPDATE ANY TABLE TO TEST_ROLE;

3) execute this sql:

CREATE USER TEST_USER
IDENTIFIED BY "PASSWORD"
DEFAULT TABLESPACE test_primary
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON test_primary
QUOTA UNLIMITED ON test_scenariocomponent
QUOTA UNLIMITED ON test_instruments
QUOTA UNLIMITED ON test_environment
QUOTA UNLIMITED ON test_index
QUOTA UNLIMITED ON test_jar;
GRANT TEST_ROLE to TEST_USER;

AND OF COURSE THIS IS WHAT I GET....

4) this sql fails with an ORA-01031:

'CREATE TABLE SCENARIO (
            SCENARIO_OBJECT LONG RAW,
            LONG_RAW_SIZE NUMBER (*,0) NOT NULL ENABLE,
            SCENARIO_NAME VARCHAR2(255) NOT NULL ENABLE)
            PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
            STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1)
            TABLESPACE TEST_JAR NOLOGGING NOCOMPRESS'

The failure occurs during an IMP command done by TEST_USER.. The export file was created by EXPORT:V09.02.00. The database is Oracle 10g Release 10.2.0.1.0.

ANY SUGGESTIONS??? WHAT AM I DOING WRONG?? PLEASE HELP!!!! Thank you sooo much :-)

-The Oracle Dude.

SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ess Daht

ASKER

do you have privileges on the test_jar tablespace?

I am not sure what you mean by privileges. The user test_user -can- write (create a table) to the test_jar tablespace, if that is what you mean. Send me some sql to execute that would show what you want.

and who are you doing the import as?

test_user

is it coming from test_user to test_user?

yes

SOLUTION

sventhan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

schwertner

I do not see

GRANT create session to TEST_USER;

Try to grant privileges directly, not with roles:

GRANT CREATE ANY INDEX TO TEST_USER;
GRANT CREATE ANY SEQUENCE TO TEST_USER;
GRANT CREATE ANY TABLE TO TEST_USER;
......

Sean Stuber

haha, create session, I missed that,
but I don't see how a 1031 error would be generated by missing that. You shouldn't be able to log in.

Is test_role defaulted for test_user?

select * from dba_role_privs where grantee = 'TEST_USER' (look for DEFAULT_ROLE = "YES")

Ess Daht

ASKER

1) The user TEST_USER is actually user JWARS, TEST_ROLE is JWARSDB_ROLE, and TEST_JAR is JWARS_JAR.

2) granting CREATE SESSION to the role JWARSDB_ROLE made no difference.

3) Below is the original error log.

Connected to: Oracle Database 10g Release 10.2.0.1.0 - Production

Export file created by EXPORT:V09.02.00 via conventional path

Warning: the objects were exported by JAR009, not by you

Import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing JAR009's objects into JAR001
. importing JAR009's objects into JAR001
IMP-0017: following statement failed with ORACLE error 1031:
'CREATE TABLE SCENARIO (
SCENARIO_OBJECT LONG RAW,
LONG_RAW_SIZE NUMBER (*,0) NOT NULL ENABLE,
SCENARIO_NAME VARCHAR2(255) NOT NULL ENABLE)
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1)
TABLESPACE JWARS_JAR NOLOGGING NOCOMPRESS'
IMP-00003: ORACLE error 1031 encountered
ORA-01031: insufficient privileges

4) As suggested, I granted all the privileges belonging to the role JWARSDB_ROLE directly to the user JWARS and I still got the error.

5) Here are the successful reults of creating a test table when logged on as user JWARS:

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Feb 13 11:52:08 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> create table test(id number);

Table created.

SQL> drop table test;

Table dropped.

SQL> create table test(id number) tablespace jwars_jar;

Table created.

SQL> drop table test;

Table dropped.

SQL> CREATE TABLE SCENARIO (
2 SCENARIO_OBJECT LONG RAW,
3 LONG_RAW_SIZE NUMBER (*,0) NOT NULL ENABLE,
4 SCENARIO_NAME VARCHAR2(255) NOT NULL ENABLE)
5 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
6 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1)
7 TABLESPACE JWARS_JAR NOLOGGING NOCOMPRESS;

Table created.

6) Here are the results of some other queries that I have run as sysDba:

SQL> select * from dba_role_privs where grantee = 'JWARSDB_ROLE';

GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
JWARSDB_ROLE CONNECT NO YES
JWARSDB_ROLE SELECT_CATALOG_ROLE NO YES

SQL> select * from role_role_privs where role = 'JWARSDB_ROLE';

ROLE GRANTED_ROLE ADM
------------------------------ ------------------------------ ---
JWARSDB_ROLE CONNECT NO
JWARSDB_ROLE SELECT_CATALOG_ROLE NO

SQL> select * from role_role_privs where role = 'JWARSDB_ROLE';

ROLE GRANTED_ROLE ADM
------------------------------ ------------------------------ ---
JWARSDB_ROLE CONNECT NO
JWARSDB_ROLE SELECT_CATALOG_ROLE NO

SQL> select * from role_sys_privs where role = 'JWARSDB_ROLE';

ROLE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
JWARSDB_ROLE CREATE ANY VIEW NO
JWARSDB_ROLE CREATE ANY TABLE NO
JWARSDB_ROLE UPDATE ANY TABLE NO
JWARSDB_ROLE CREATE ANY INDEX NO
JWARSDB_ROLE GRANT ANY ROLE NO
JWARSDB_ROLE INSERT ANY TABLE NO
JWARSDB_ROLE DELETE ANY TABLE NO
JWARSDB_ROLE SELECT ANY SEQUENCE NO
JWARSDB_ROLE CREATE ANY SEQUENCE NO
JWARSDB_ROLE DROP ANY SEQUENCE NO
JWARSDB_ROLE CREATE USER NO

ROLE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
JWARSDB_ROLE DROP ANY TABLE NO
JWARSDB_ROLE DROP ANY VIEW NO
JWARSDB_ROLE ALTER USER NO
JWARSDB_ROLE SELECT ANY TABLE NO
JWARSDB_ROLE DROP ANY INDEX NO
JWARSDB_ROLE ALTER PROFILE NO

17 rows selected.

SQL> select * from dba_role_privs where grantee = 'JWARS';

GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
JWARS JWARSDB_ROLE NO YES

Ess Daht

ASKER

Hello? Is anybody available to help or has this problem got you stumped?

SOLUTION

schwertner

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

schwertner

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ess Daht

ASKER

Debugging this problem is complicated by the fact that our user is 45 miles away and in a classified environment, so we have to retype all the error messages he gets.

1) do you use FROM_USER and TO_USER in the import parameters?

No. The imp command we execute is this:

'imp JWARS/PASSWORD@JAS BUFFER=30720 IGNORE=Y FILE=JWARS.DMP log=archive.log TABLES=JAR_TABLE,JAR_TABLE_COLUMN,JAR_TABLE_STRUCTURE'.

Both the exp command that created the dump file and the imp command are executed from inside an appication, and in both cases the application is logged into Oracle as user JWARS. According to this: http://www.orafaq.com/wiki/Import

Required Privileges
Privilages necessary for Imports are listed Below:
To use import, you need the CREATE SESSION privilege. The CONNECT role will have this privilege established during database creation.
If user XYZ has created a export, then any user with IMP_FULL_DATABASE role granted to him (usually a DBA role will be granted this privilege at database creation) can import the export file.
If user XYZ happens to be a DBA (user granted DBA role), then users only with IMP_FULL_DATABASE (dba role) can import this file.
If user XYZ has created a export file, then the same userid (XYZ) on a different database can import this file.
A user with IMP_FULL_DATABASE privilege, can only do a full database import.
To be able to import privileges that a user has granted to others, the user initiating the import must either own the object or have object privilege with the WITH GRANT OPTION.

FROM_USER and TO_USER are not necessary if the exporting and the importing user are the same.

2) I had my user add the 17 privileges below to the JWARS user directly and Oracle responded with 'grant succeeded' 17 times. But we still get the error. Where can I confirm that user JWARS has the correct privileges? Exectuing Select * from user_sys_privs where username = 'JWARS' returned 0 rows.

grant drop any table to JWARS;
grant create any index to JWARS;
grant drop any index to JWARS;
grant insert any table to JWARS;
grant select any table to JWARS;
grant delete any table to JWARS;
grant update any table to JWARS;
grant create any view to JWARS;
grant create any sequence to JWARS;
grant drop any view to JWARS;
grant drop any sequence to JWARS;
grant select any sequence to JWARS;
grant create user to JWARS;
grant grant any role to JWARS;
grant alter user to JWARS;
grant alter profile to JWARS;

ASKER CERTIFIED SOLUTION

schwertner

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ess Daht

ASKER

More info from our remote user on the import problem. The user executing the imp command is called JAR001. This user was created programmatically by the user JWARS by this sql:

CREATE USER JAR001 PROFILE DEFAULT IDENTIFIED BY password DEFAULT TABLESPACE JWARS_JAR TEMPORARY TABLESPACE TEMP QUOTA UNLIMITED ON JWARS_JAR ACCOUNT UNLOCK

But it seems JAR001's privileges are not great enough. I assume the PROFILE DEFAULT gives him the default profile, which I think/hope is the role JWARSDB_ROLE.

2 more questions:

1) how do I show what is the default profile?
2) how can I list what has been 'grant'ed to a user so that I can see where JWARS and JAR001 differ? in pseudo-sql:
select privs from user_privs where username = 'JWARS';

SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

schwertner

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ess Daht

ASKER

Colleagues,

Thank you so much for all of your timely responses.

They were all very helpful. Unfortunately, even with the wealth of information that you provided, i still wasn't able to find a sure solution.

Although you guys did get me CLOSER to arriving at a solution that I was before I sought your advice.

Thank you so much again.

I'll try to distribute the points as fair as possible.

Respectfully,

Frankie Baby