Solved

Import Into Different Schema

Posted on 2013-06-24
15
620 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
  • 7
  • 5
  • 3
15 Comments
 
LVL 76

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 76

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
 
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 76

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 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39275000
OH, I was thinking you were the OP in this question.  Apologies.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 35

Expert Comment

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

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 76

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 76

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

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…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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.

758 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now