[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Oracle import issue

Posted on 2012-03-16
12
Medium Priority
?
667 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:thomaszhwang
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 18

Accepted Solution

by:
sventhan earned 1252 total points
ID: 37730966
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
 
LVL 18

Assisted Solution

by:sventhan
sventhan earned 1252 total points
ID: 37730975
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 500 total points
ID: 37730976
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:thomaszhwang
ID: 37731022
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
 
LVL 18

Assisted Solution

by:sventhan
sventhan earned 1252 total points
ID: 37731079
Never mind
0
 

Author Comment

by:thomaszhwang
ID: 37731083
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
 
LVL 18

Assisted Solution

by:sventhan
sventhan earned 1252 total points
ID: 37731110
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
 
LVL 18

Assisted Solution

by:sventhan
sventhan earned 1252 total points
ID: 37731115
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
 

Author Comment

by:thomaszhwang
ID: 37731118
no luck!

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

no rows selected
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 500 total points
ID: 37731678
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
 
LVL 12

Assisted Solution

by:Praveen Kumar Chandrashekatr
Praveen Kumar Chandrashekatr earned 248 total points
ID: 37736431
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
 

Author Closing Comment

by:thomaszhwang
ID: 37791444
Thanks
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question