| Error While Import [message #165973] |
Mon, 03 April 2006 16:30  |
faiz_hyd
Messages: 294
Registered: February 2006
|
Senior Member |
|
|
Hi
I did an export from Schema A and import to Schema B.
Using Toad, Export was succesful and while importing i got this
error
Warning: the objects were exported by R1APP50, not by you
import done in WE8MSWIN1252 character set and UTF8 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
IMP-00017: following statement failed with ORACLE error 2304:
"CREATE TYPE "CD_ISO_BAT_R" TIMESTAMP '2006-03-29:10:11:27' OID '10256EFEAB4"
"3359FE0440003BA7CEECD' AS OBJECT ("
" bat_id NUMBER,"
" cd_vol_nb NUMBER,"
" cd_iso_id NUMBER,"
" bat_data_size NUMBER,"
" bat_image_size NUMBER,"
" cr_dt DATE"
");"
IMP-00003: ORACLE error 2304 encountered
ORA-02304: invalid object identifier literal
IMP-00017: following statement failed with ORACLE error 2304:
"CREATE TYPE "CD_ISO_BAT_T" TIMESTAMP '2006-03-29:10:11:28' OID '10256EFEAB4"
"C359FE0440003BA7CEECD' AS TABLE OF cd_iso_bat_r;"
IMP-00003: ORACLE error 2304 encountered
ORA-02304: invalid object identifier literal
IMP-00017: following statement failed with ORACLE error 2304:
"CREATE TYPE "CD_ISO_R" TIMESTAMP '2006-03-29:10:11:28' OID '10256EFEAB50359"
"FE0440003BA7CEECD' AS OBJECT ("
" cd_iso_id NUMBER,"
" cd_iso_burn_id NUMBER,"
" cd_vol_nb NUMBER,"
" cd_vol_trans_count NUMBER"
")"
IMP-00003: ORACLE error 2304 encountered
ORA-02304: invalid object identifier literal
IMP-00017: following statement failed with ORACLE error 2304:
"CREATE TYPE "CD_ISO_T" TIMESTAMP '2006-03-29:10:11:28' OID '10256EFEAB57359"
"FE0440003BA7CEECD' AS TABLE OF cd_iso_r;"
IMP-00003: ORACLE error 2304 encountered
ORA-02304: invalid object identifier literal
IMP-00017: following statement failed with ORACLE error 2304:
"CREATE TYPE "ID_TYP" TIMESTAMP '2006-03-29:10:11:28' OID '10256EFEAB5B359FE"
"0440003BA7CEECD' AS TABLE OF NUMBER(20);"
IMP-00003: ORACLE error 2304 encountered
ORA-02304: invalid object identifier literal
IMP-00017: following statement failed with ORACLE error 2304:
"CREATE TYPE "LOCKBOX_NB_TYP" TIMESTAMP '2006-03-29:10:11:28' OID '10256EFEA"
"B5F359FE0440003BA7CEECD' AS TABLE OF NUMBER(20);"
IMP-00003: ORACLE error 2304 encountered
ORA-02304: invalid object identifier literal
IMP-00017: following statement failed with ORACLE error 2304:
"CREATE TYPE "T_IDARRAY" TIMESTAMP '2006-03-29:10:11:28' OID '10256EFEAB6335"
"9FE0440003BA7CEECD' as table of number;"
IMP-00003: ORACLE error 2304 encountered
ORA-02304: invalid object identifier literal
Rest all the things are imported successfuly,
I was going thorugh asktom
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1323002416801
for related errors, may be i couldn't understand from it Any thing am i missing here.
Thanks
|
|
|
|
| Re: Error While Import [message #165975 is a reply to message #165973] |
Mon, 03 April 2006 17:59  |
 |
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
Error is because the object you are trying to import has an OID that is matching the OID that already exists.
This OID we are talking about is a unique value within a database.
So when you export the objects from one schema and import it another schema,
it will carry the same OID.
One Workaround is to
Just run the import, log the errors.
Manually extract the DDL of those objects that failed (from the logfile) and re-run again on target
Please see session
1 CREATE TYPE CD_ISO_BAT_R
2 AS OBJECT (
3 bat_id NUMBER,
4 cd_vol_nb NUMBER,
5 cd_iso_id NUMBER,
6 bat_data_size NUMBER,
7 bat_image_size NUMBER,
8 Cr_dt DATE
9* )
scott@9i > /
Type created.
scott@9i > !exp scott/tiger owner=scott
Export: Release 9.2.0.4.0 - Production on Mon Apr 3 18:47:18 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SCOTT
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SCOTT
About to export SCOTT's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SCOTT's tables via Conventional Path ...
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.
scott@9i > !imp scott/tiger fromuser=scott touser=test
Import: Release 9.2.0.4.0 - Production on Mon Apr 3 18:47:43 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production
Export file created by EXPORT:V09.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing SCOTT's objects into TEST
IMP-00017: following statement failed with ORACLE error 2304:
"CREATE TYPE "CD_ISO_BAT_R" TIMESTAMP '2006-04-03:18:46:49' OID '108F90E40DF"
"A1010E0440003BA085F98' "
"AS OBJECT ("
"bat_id NUMBER,"
"cd_vol_nb NUMBER,"
"cd_iso_id NUMBER,"
"bat_data_size NUMBER,"
"bat_image_size NUMBER,"
"Cr_dt DATE"
")"
IMP-00003: ORACLE error 2304 encountered
ORA-02304: invalid object identifier literal
Import terminated successfully with warnings.
--
-- From above error log, you can see the object with OID '108F90E40DFA1010E0440003BA085F98' is failing
-- Let us the value of this From dictionary.
-- You can see, the object owned by scott has already taken this OID.
-- So the target user should have someother OID.
-- just manually extract thee DDL from log
-- run it in target
scott@9i > select type_name,type_oid from user_types;
TYPE_NAME TYPE_OID
------------------------------ --------------------------------
CD_ISO_BAT_R 108F90E40DFA1010E0440003BA085F98
scott@9i > connect test/test
Connected.
scott@9i > CREATE TYPE CD_ISO_BAT_R
AS OBJECT (
bat_id NUMBER,
cd_vol_nb NUMBER,
cd_iso_id NUMBER,
bat_data_size NUMBER,
bat_image_size NUMBER,
Cr_dt DATE
) 2 3 4 5 6 7 8 9
10 /
Type created.
Regards
[Updated on: Mon, 03 April 2006 18:00] Report message to a moderator |
|
|
|
| Re: Error While Import [message #166091 is a reply to message #165975] |
Tue, 04 April 2006 08:41 |
faiz_hyd
Messages: 294
Registered: February 2006
|
Senior Member |
|
|
Thanks a Lot!
It is clear, One more error i got was Unique constraints Voilation and some other constraints error while Import,
Before importing i am sure i cleaned up the schema and no body was loggen on while import was going on but still constraint voilation error came, even when export was going on no body was using source schema
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SYS.I_JOB_JOB) violated
ORA-06512: at "SYS.DBMS_JOB", line 97
ORA-06512: at line 1
IMP-00017: following statement failed with ORACLE error 1:
"BEGIN DBMS_JOB.ISUBMIT(JOB=>2077,WHAT=>'/* job_name: JOB_DAILY_PURGE_EMPT"
"Y_BAT */BEGIN pkg_bat.purge_empty_batches; END;',NEXT_DATE=>TO_DATE('2006-0"
"3-30:00:30:00','YYYY-MM-DD:HH24:MI:SS'),INTERVAL=>'TRUNC(SYSDATE + 1, ''MI'"
"')',NO_PARSE=>TRUE); END;"
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SYS.I_JOB_JOB) violated
ORA-06512: at "SYS.DBMS_JOB", line 97
ORA-06512: at line 1
About to enable constraints...
IMP-00017: following statement failed with ORACLE error 2298:
"ALTER TABLE "BILL_BAT" ENABLE CONSTRAINT "RI_BILBAT_PRCBAT_PRCBATID""
IMP-00003: ORACLE error 2298 encountered
ORA-02298: cannot validate (R1APP60.RI_BILBAT_PRCBAT_PRCBATID) - parent keys not found
IMP-00017: following statement failed with ORACLE error 2298:
"ALTER TABLE "PYMT_ALRT_TXN" ENABLE CONSTRAINT "RI_PYMALRTXN_ALRNTF_ALRNTFID"
"""
IMP-00003: ORACLE error 2298 encountered
ORA-02298: cannot validate (R1APP60.RI_PYMALRTXN_ALRNTF_ALRNTFID) - parent keys not found
IMP-00017: following statement failed with ORACLE error 2298:
"ALTER TABLE "PYMT_FLOAT" ENABLE CONSTRAINT "RI_PYMFLT_PYM_PYMID""
IMP-00003: ORACLE error 2298 encountered
ORA-02298: cannot validate (R1APP60.RI_PYMFLT_PYM_PYMID) - parent keys not found
IMP-00017: following statement failed with ORACLE error 2298:
"ALTER TABLE "PYMT" ENABLE CONSTRAINT "RI_PYM_IMG_IMGFRNID""
IMP-00003: ORACLE error 2298 encountered
ORA-02298: cannot validate (R1APP60.RI_PYM_IMG_IMGFRNID) - parent keys not found
IMP-00017: following statement failed with ORACLE error 2298:
"ALTER TABLE "PYMT" ENABLE CONSTRAINT "RI_PYM_IMG_IMGRERID""
IMP-00003: ORACLE error 2298 encountered
ORA-02298: cannot validate (R1APP60.RI_PYM_IMG_IMGRERID) - parent keys not found
IMP-00017: following statement failed with ORACLE error 2298:
"ALTER TABLE "PYMT" ENABLE CONSTRAINT "RI_PYM_TXN_TXNID""
IMP-00003: ORACLE error 2298 encountered
ORA-02298: cannot validate (R1APP60.RI_PYM_TXN_TXNID) - parent keys not found
IMP-00017: following statement failed with ORACLE error 2298:
"ALTER TABLE "TXN" ENABLE CONSTRAINT "RI_TXN_BAT_BATID""
IMP-00003: ORACLE error 2298 encountered
ORA-02298: cannot validate (R1APP60.RI_TXN_BAT_BATID) - parent keys not found
IMP-00017: following statement failed with ORACLE error 2298:
"ALTER TABLE "TXN" ENABLE CONSTRAINT "RI_TXN_PRCBAT_PRCBATID""
IMP-00003: ORACLE error 2298 encountered
ORA-02298: cannot validate (R1APP60.RI_TXN_PRCBAT_PRCBATID) - parent keys not found
Import terminated successfully with warnings.
Thanks Mahesh
|
|
|
|
| Re: Error While Import [message #166115 is a reply to message #166091] |
Tue, 04 April 2006 10:09 |
|
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
| Quote: |
IMP-00017: following statement failed with ORACLE error 1:
"BEGIN DBMS_JOB.ISUBMIT(JOB=>2077,WHAT=>'/* job_name: JOB_DAILY_PURGE_EMPT"
"Y_BAT */BEGIN pkg_bat.purge_empty_batches; END;',NEXT_DATE=>TO_DATE('2006-0"
"3-30:00:30:00','YYYY-MM-DD:HH24:MI:SS'),INTERVAL=>'TRUNC(SYSDATE + 1, ''MI'"
"')',NO_PARSE=>TRUE); END;"
|
Similar error as before.
You are exporting and importing into the same same database, but different schema.
certain thingies need to be UNIQUE within the database.
In this case a job with job number 2077 already exists in source schema.
You need to assign a new Job number in target schema.
So Ignore the error,get the specification for the job and submit it manually again.
| Quote: |
About to enable constraints...
IMP-00017: following statement failed with ORACLE error 2298:
"ALTER TABLE "BILL_BAT" ENABLE CONSTRAINT "RI_BILBAT_PRCBAT_PRCBATID""
IMP-00003: ORACLE error 2298 encountered
ORA-02298: cannot validate (R1APP60.RI_BILBAT_PRCBAT_PRCBATID) - parent keys not found
|
You are about enable child keys which does not have any parent key!.
Either it was the same before in source schema (check source for status of those constraints)
Or
This may be a result of your broken import sessions.
clean the target schema and Start over
If none of the above works, dump your toad/any gui tool you are using and get manual.
Also, Make sure you are exporting all the constraints during export.
[Updated on: Tue, 04 April 2006 10:30] Report message to a moderator |
|
|
|
| Re: Error While Import [message #166123 is a reply to message #166115] |
Tue, 04 April 2006 11:50 |
faiz_hyd
Messages: 294
Registered: February 2006
|
Senior Member |
|
|
Thanks Mahesh
Regarding What You said
You are about enable child keys which does not have any parent key!.
Either it was the same before in source schema (check source for status of those constraints)
No in source this constraint was there here is the table creation script.
ALTER TABLE BILL_BAT ADD (
CONSTRAINT RI_BILBAT_PRCBAT_PRCBATID
FOREIGN KEY (PROC_BAT_ID)
REFERENCES PROC_BAT (PROC_BAT_ID));
Now i cleaned the target schema and started new Export from Command line and will do import also from Command line,
Will let you know once done..
i will be using this command for import is it OK
imp userid=target_SCHMA file=SOURCE_SCHM.dmp log=imp.log fromuser=SOURCE_SCHMA touser=target_SCHMA
Thanks a Lot.
|
|
|
|
| Re: Error While Import [message #166124 is a reply to message #166123] |
Tue, 04 April 2006 12:02 |
|
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
I asked to check the status of constraints.
Look into user_constraints.status
And try this.
# export using a DBA user (will take care of certain privs
# or
# export as the user with EXP_FULL_DATABASE priv.
Exp dbauser/password@sourcedb owner=schema_to_export compress=n file=myfile.dmp
Followed by
imp dbauser/password@surcedb fromuser=schema_to touser=schema_to_import file=myfile.dmp
Edit:
Your import command line syntax is good.
Make sure you always export with compress=n
[Updated on: Tue, 04 April 2006 12:04] Report message to a moderator |
|
|
|
| Re: Error While Import [message #166134 is a reply to message #166124] |
Tue, 04 April 2006 13:47 |
faiz_hyd
Messages: 294
Registered: February 2006
|
Senior Member |
|
|
Yes Mahesh
Import was successful except with that DBMS.JOB constraint values,No Constraint Voilation errors what could be the problem with TOAD, it worked fine with Command line.
Thanks for your suggestions.
" One more thing which is not related to this previous question
From today morning Development Database Server is very slow in connecting it takes a while while i connect from SQL> and even when i type Exit at SQL> it takes time to exit, what could be the problem " (Same from Toad also very slow in Connecting ).
thanks
|
|
|
|
| Re: Error While Import [message #166142 is a reply to message #166134] |
Tue, 04 April 2006 15:17 |
|
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
>> what could be the problem with TOAD
I have no idea, I do not use TOAD (or any GUI, if that matters).
>> Database Server is very slow in connecting
It can be anything.
Just make sure your sqlnet connections are NOT traced.
Check your listener.ora/tnsnames.ora/sqlnet.ora
[Updated on: Wed, 05 April 2006 08:06] Report message to a moderator |
|
|
|
| Re: Error While Import [message #166298 is a reply to message #166142] |
Wed, 05 April 2006 11:00 |
faiz_hyd
Messages: 294
Registered: February 2006
|
Senior Member |
|
|
Thanks Mahesh
Well there is no modification in TNS or Listener files, they are
same, Since last 2 days only developers are telling it is slow in connectiong and even updates are slow.This is a development machine.
I found this regarding Trace of
at
SQL > show parameter sql trace
sql_trace boolean FALSE
Is there any other area(s) where i can point for the reason for slow.
Thanks
[Updated on: Wed, 05 April 2006 11:10] Report message to a moderator |
|
|
|
|
|
| Re: Error While Import [message #166315 is a reply to message #166309] |
Wed, 05 April 2006 12:35 |
faiz_hyd
Messages: 294
Registered: February 2006
|
Senior Member |
|
|
Sorry if i have to open a new post i will do
but to answer your questions
Yes Stats are updated daily.
It's a pure developement Database.
From last 2 days developers say,it's taking time in connecting,even Queries and DML are slow.
Sorry as i am new in this role and DB tuning please bear with me
as i don't know and couldn't run STATS Pack i tried to gather some information through GUI, if it could help you in getting some area please give some suggestion, After this i will continue in new Post, Image is attached from GUI tool.
Thanks A Lot
|
|
|
|
|
|
|
|
| Re: Error While Import [message #166510 is a reply to message #166324] |
Thu, 06 April 2006 14:54 |
madhusunkara
Messages: 59
Registered: March 2006
Location: hyderabad,india
|
Member |
|
|
what parameters did you use while export is there consistent=y and also you should have same nls_lang parameter on both machines (you can export the parameter before doing import)
Thanks
Madhu
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
