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.
Ess DahtAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
do you have privileges on the test_jar tablespace?

and who are you doing the import as?

is it coming from test_user to test_user?
0
Ess DahtAuthor Commented:
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
0
sventhanCommented:
try to create a temp table on "test_user" and see you've the privileage to do it.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

schwertnerCommented:
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;
......
0
sdstuberCommented:
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")

0
Ess DahtAuthor Commented:
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




0
Ess DahtAuthor Commented:
Hello? Is anybody available to help or has this problem got you stumped?
0
schwertnerCommented:
Do you use FROM_USER and TO_USER syntax in the Import rarameters?
0
schwertnerCommented:
Please try to assign directly the priveleges to the user.
Roles have sone side effects.
0
Ess DahtAuthor Commented:
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;


0
schwertnerCommented:
Please look at this set of commands that is known as perfectly working:

CREATE TABLESPACE "TSDDOCUMENTS"
LOGGING
DATAFILE  '/u02/oradata/oradata/o10f/tsdDocuments00.dbf'  SIZE 200M
AUTOEXTEND ON    NEXT 200M   MAXSIZE UNLIMITED
PERMANENT   ONLINE
EXTENT  MANAGEMENT LOCAL
;

DROP USER LSDOCUMENTS CASCADE;

CREATE USER LSDOCUMENTS  IDENTIFIED BY LSDOCUMENTS
DEFAULT TABLESPACE phr7users   QUOTA UNLIMITED ON phr7users;

GRANT create procedure, create session,create table,create type,create view,create synonym TO LSDOCUMENTS;
 
set linesize 10000
SQL> select PRIVILEGE from dba_sys_privs where upper(GRANTEE)='LSDOCUMENTS';

PRIVILEGE
----------------------------------------
CREATE PROCEDURE
CREATE SESSION
CREATE VIEW
CREATE TABLE
CREATE TYPE
CREATE SYNONYM

6 rows selected.


Now make the needed changes according your user, tablespace name and
specific set of privileges.




0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ess DahtAuthor Commented:

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';
0
sdstuberCommented:
profiles and roles aren't the same.

select * from dba_role_privs where grantee = 'JAR001' to see what roles are assigned by default to the user.


0
schwertnerCommented:
Why you don't export/Import via SYS account?
SYS has all rights over the DB.

To see the roles and profiles you have to use Oracle Enterpise Manager
But this is the long way.
Play with SYS user.
0
Ess DahtAuthor Commented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.