Solved

Import Into Different Schema

Posted on 2013-06-24
15
679 Views
Last Modified: 2013-07-02
Trying to import into a different schema using
impdp system/zztop directory=datapump_directory dumpfile=AA_2012_06.dmp remap_schema=bb_bb60_stats:csuh_custom full=y
I get
"Failing sql is:
CREATE TABLE "CSUH_CUSTOM"."ACTIVITY_ACCUMULATOR" ("PK1" NUMBER(38,0) CONSTRAINT "STATS_AA_PK1_NN" NOT NULL ENABLE, "EVENT_TYPE" VARCHAR2(30 BYTE) CONSTRAINT "STATS_AA_TYPE_NN" NOT NULL ENABLE, "USER_PK1" NUMBER(38,0), "COURSE_PK1" NUMBER(38,0), "GROUP_PK1" NUMBER(38,0), "FORUM_PK1" NUMBER(38,0), "INTERNAL_HANDLE" VARCHAR2(255 BYTE), "CONTENT
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 11:22:15


How do I use the .dmp file from an export of a table in one schema to import that table into a second schema?
0
Comment
Question by:xoxomos
[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
  • 7
  • 5
  • 3
15 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39272564
Check the log file for a more descriptive error.

>>How do I use the .dmp file from an export of a table in one schema to import that table into a second schema?

Just like you are.
0
 

Author Comment

by:xoxomos
ID: 39272608
OK.  Right
tats:csuh_custom logfile=import_log
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE:"CSUH_CUSTOM"."ACTIVITY_ACCUMULATOR" failed to create with error:
ORA-02264: name already used by an existing constraint
The export .dmp file includes some named constraints.  Is there a way to specify on the impdp command line to just ignore those?
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 300 total points
ID: 39272619
You 'can' use EXCLUDE=CONSTRAINT.

I'm just not sure this is a good idea.  You might miss something important in the rest of the import.  If you are just after tables, I would probably just go after the tables I wanted and not do a FULL.
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.

 
LVL 35

Expert Comment

by:YZlat
ID: 39274966
did csuh_custom schema exist previously?

REMAP_SCHEMA will create a new user/schema if it does not already exist. To avoid any issues, you can spool all privileges of csuh_custom schema, including password etc., and then drop the schema with

drop user csuh_custom cascade;

and let impdp with REMAP_SCHEMA re-create the schema for you
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39274981
>>did csuh_custom schema exist previously?

I don't know.  Did it?  It's your database.  I have no way of knowing what is in it.  All I can say is the error states that there is an object name conflict.
0
 
LVL 35

Expert Comment

by:YZlat
ID: 39274990
It is not my database, I have as much info about it as you do. I was actually asking the OP if the schema existed
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39275000
OH, I was thinking you were the OP in this question.  Apologies.
0
 
LVL 35

Expert Comment

by:YZlat
ID: 39275006
i figured that after I replied:) I am slow today
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39275023
Looks like we are both a little slow today...  ;)
0
 

Author Comment

by:xoxomos
ID: 39275687
There's an additional problem actually.  The applications people somehow got duplicates into a table with a primary key.  First I must somehow get rid of the duplicates into some table, then either make an export of that and import or do a lot of inserts.  To get rid of duplicates I've tried running :
DUMPFILE=AA_2012_06.dmp
TABLES=(activity_accumulator:AA_2012_06)
logfile=exp_aa_log
data_options=skip_constraint_errors
directory=datapump_directory
table_exists_action=append

Results so far say i need to increase memory_target and streams parameter and try again.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39275720
>>The applications people somehow got duplicates into a table with a primary key.

They might have enabled the constraint with novalidate.

http://docs.oracle.com/cd/E11882_01/server.112/e16508/datainte.htm#CHDJCHEI

This is one of many reasons you don't let app developers do DBA tasks.
0
 

Author Comment

by:xoxomos
ID: 39275801
:-) :-) ....well they let me do DBA tasks and i'm not even a developer :-)
Anyhow i've wondered where to draw the line there.  I would say no to things like changing parameters, adding new tablespaces etc but adding, dropping tables, import/export
i thought should be up to the developers no?
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39275824
>>but adding, dropping tables, import/export

Depends on your shop and what your role is.  I've worked in all types of shops.

One where the DBA was there just to provide a play-ground for developers and they created everything they needed.  Others where the developers worked hand-in-hand with the DBAs so the DBA could guide 'proper' development to keep them from shooting themselves later.

Both work.

Unfortunately some developers don't know anything about database design and even less about Oracle.  In this case, I wouldn't let developers do anything that I, as the DBA, might be responsible for later.

If you work in the first example, then why are 'you' doing this export/import?  They dug the hole, let them dig their way out.
0
 

Author Closing Comment

by:xoxomos
ID: 39279124
Actually you CAN, but normally it is not a good idea.  Don't know how they got duplicates into the table, but excluding the constraint in my operation took one of each duplicate with the second being rejected.
0
 

Author Comment

by:xoxomos
ID: 39295056
"If you work in the first example, then why are 'you' doing this export/import?  They dug the hole, let them dig their way out."

:-) :-)
It depends.  Normally, it's the first example.
They're hoping the problem will time off the database before it's discovered ......in about four months.  
Now if it does cause a problem before that time, then  it's '....a problem with the database"
:-)
0

Featured Post

Veeam gives away 10 full conference passes

Veeam is a VMworld 2017 US & Europe Platinum Sponsor. Enter the raffle to get the full conference pass. Pass includes the admission to all general and breakout sessions, VMware Hands-On Labs, Solutions Exchange, exclusive giveaways and the great VMworld Customer Appreciation Part

Question has a verified solution.

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

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

630 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