Import Into Different Schema

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?
xoxomosAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
xoxomosAuthor Commented:
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

 
YZlatCommented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
YZlatCommented:
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
 
slightwv (䄆 Netminder) Commented:
OH, I was thinking you were the OP in this question.  Apologies.
0
 
YZlatCommented:
i figured that after I replied:) I am slow today
0
 
slightwv (䄆 Netminder) Commented:
Looks like we are both a little slow today...  ;)
0
 
xoxomosAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
xoxomosAuthor Commented:
:-) :-) ....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
 
slightwv (䄆 Netminder) Commented:
>>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
 
xoxomosAuthor Commented:
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
 
xoxomosAuthor Commented:
"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
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.