Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Import Into Different Schema

Posted on 2013-06-24
15
Medium Priority
?
720 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 1200 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

705 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