Oracle import issue

I am trying to import a DMP file into a NEW database which as no grants,users etc that are present in the export file. All I need is data and I don't care about users,grants,sequences etc.

Below is a simple import statement which I used,

IMPDP scott/********@alias directory=TEST_DIR dumpfile=godirectBackup.dmp content=all schemas=GODIRECT logfile=imp3.log ( refer to imp-5193errors log)

But after looking at the errors, I tried to exclude certain things from the import and then the errors reduced.

IMPDP scott/********@alias dumpfile=godirectBackup.dmp table_exists_action=replace schemas=godirect directory=TEST_DIR exclude=grant,user,sequence,view,role_grant,default_role,tablespace_quota logfile=mylog.log ( refer imp-1358errors log)

Even when I don’t add “exclude=tablespace” in the import statement it has the same errors as 1358 file. Now I think all the errors are pointing to godirect tablespace and the other errors are dependent on it.

Should I create a tablespace manually? Or please help me proceed. Thanks.
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.

Before the import you should have created the (same) tableSPACE manually. If its a different one your should use remap tablespace option with your impdp.

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
slightwv (䄆 Netminder) Commented:
I would suggest only INCLUDING what you want instead of EXCLUDING everything you don't.

For the tablespace issue, you need REMAP_TABLESPACE:
Ultimate Tool Kit for Technology Solution Provider

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 now.

thomaszhwangAuthor Commented:
Okay I checked for the default  tablespace in scott and there are none.

SQL> connect scott/tiger@alias
SQL> select property_value from database_properties
  2  where property_name='default_tablespace';

no rows selected

SQL> select property_value from database_properties
  2  where property_name='default_permanent_tablespace';

no rows selected.

So now I am trying to create a tablespace and I need to point it to a datafile. These are some of the datafiles I have by default- SYSAUX01.DBF,SYSTEM01.DBF,TEMP01.DBF,UNDOTBS01.DBF AND USERS01.DBF.

Which one should I point to? Or create a new one? How?
Never mind
thomaszhwangAuthor Commented:
No not there.

SQL> select tablespace_name from dba_segments where segment_name = 'SCOTT';

no rows selected.

This is really important for me. Any help would be appreciated. Thanks.
try this to find the tablespace

select tablespace_name from dba_segments where segment_name = 'EMPLOYEE';

segment_name should be any of the object owned by the user SCOTT.
Once you find the table space

Create it manually and try your impdp

IMPDP scott/********@alias dumpfile=godirectBackup.dmp table_exists_action=replace schemas=godirect directory=TEST_DIR
thomaszhwangAuthor Commented:
no luck!

SQL> select tablespace_name from dba_segments where segment_name = 'EMPLOYEE';

no rows selected
slightwv (䄆 Netminder) Commented:
Where to start?

First:  property_name is in UPPER case. So the query would be:
select property_value from database_properties

DEFAULT_TABLESPACE isn't an option in my system.

>>Okay I checked for the default  tablespace in scott and there are none.

Since you are running the impdp as scott, it will be scotts 'default' tablespace:
select default_tablespace from dba_users where username='SCOTT';

That said, you can REMAP_TABLESPACE to any tablespace SCOTT has quote on.

>>segment_name = 'SCOTT';

A segment_name is basically an object.  SCOTT is an OWNER not an object/segment.

>>SQL> select tablespace_name from dba_segments where segment_name = 'EMPLOYEE';

SCOTT owns a 'segment' name EMP, not EMPLOYEE.  In this case a segment is a TABLE.
Praveen Kumar ChandrashekatrDatabase Analysist Senior Commented:
AS slightwv mention the property_name is in upper case, you can also try this

SELECT * FROM   database_properties WHERE  property_name like '%TABLESPACE';

you can aslo create a new tablespace i.e

create tablespcae test datafile 'path_of_datafile' size 50M;

and use remap_tablespace command in impdp. check the below doc for expamles.
thomaszhwangAuthor Commented:
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.