SQLloader: IMP-00013: only a DBA can import a file exported by another DBA

Problem

Some time ago I created a dump file on a database. When it came to importing into a different database some time later I hit the above error.

I needed to create an equivalent user on my new database with the necessary privileges.  In my case I chose to have all of the data in the Apex tablespace.

Solution

I needed to create the tables first, but in a different tablespace.
If I did not do this, I would get "ORA-00959: tablespace 'A_TABLSPACE' does not exist"

To achieve this I had to generate a script for the table creation from the DMP file. I then needed to edit it an run it. Finally I needed to import the DMP file.

Generating the create table script

1. With your .DMP file, create a SQL file containing the structure (Tables):

imp <xe_username>/<password>@XE file=<filename.dmp> indexfile=index.sql full=y

2. Open the indexfile (index.sql) in a text editor that can do find and replace over an entire file, and issue the following find and replace statements IN ORDER (ignore the single quotes.. '):

Find: 'REM<space>' Replace: <nothing>

Find: '"<source_tablespace>"' Replace: '"USERS"'

Find: '...' Replace: 'REM ...'

Find: 'CONNECT' Replace: 'REM CONNECT'

3. Save the indexfile, then run it against your Oracle Express Edition account (I find it's best to create a new, blank XE user account - or drop and recreate if I'm refreshing):

sqlplus <xe_username>/<password>@XE @index.sql

I now have the tables created in the correct tablespaces.

Creating the users

create user fwlive identified by oracle default tablespace APEX_4774507955513063;

grant create session to fwlive;

grant create table to fwlive;

grant unlimited tablespace to fwlive;

In my case, the "From" user was a powerful Sys user, so I needed to temporarily grant some big privileges to my new user. I'm not recommending this as a permanent solution!

grant imp_full_database to fwlive;

ALTER USER fwlive DEFAULT ROLE ALL;

ORA-01950: no privileges on tablespace 'APEX_4774507955513063'

grant unlimited tablespace to fwlive;

Importing the Data

Now you want to tell the SQLLoader to import the data, and ignore the fact that the tables already exist - go ahead and load the data anyway.

imp system/oracle fromuser=fwlive touser=fwlive grants=N file=EXPdat.dmp ignore=Y log=EXPdat.log

Acknowledgements

http://stackoverflow.com/questions/61963/howto-import-an-oracle-dump-in-an-different-tablespace