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.
imp-1358errors.log
imp-5193errors.log
thomaszhwangAsked:
Who is Participating?
 
sventhanConnect With a Mentor Commented:
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.
0
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
I would suggest only INCLUDING what you want instead of EXCLUDING everything you don't.

For the tablespace issue, you need REMAP_TABLESPACE:
http://docs.oracle.com/cd/B19306_01/server.102/b14215/dp_import.htm#sthref343
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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

SQL> connect scott/tiger@alias
Connected.
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?
0
 
sventhanConnect With a Mentor Commented:
Never mind
0
 
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.
0
 
sventhanConnect With a Mentor Commented:
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.
0
 
sventhanConnect With a Mentor Commented:
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
0
 
thomaszhwangAuthor Commented:
no luck!

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

no rows selected
0
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
Where to start?

First:  property_name is in UPPER case. So the query would be:
select property_value from database_properties
where property_name='DEFAULT_PERMANENT_TABLESPACE';


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.
0
 
Praveen Kumar ChandrashekatrConnect With a Mentor Database 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.

http://www.dbasupport.com/forums/showthread.php?t=56877
0
 
thomaszhwangAuthor Commented:
Thanks
0
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.

All Courses

From novice to tech pro — start learning today.