Home » RDBMS Server » Server Utilities » impdb error (linux Centos5 , oracle 10.2.0.1)
impdb error [message #279165] Wed, 07 November 2007 03:29 Go to next message
shiju_t_v
Messages: 56
Registered: May 2007
Location: kannur,kerala
Member
When i use the command
impdp \'sys/admin as sysdba\' directory=Datapump dumpfile=exp.dmp
logfile=impfull.log full=y TABLE_EXISTS_ACTION=REPLACE


The point is than when i use exp command for export and imp command for insert ,the operation was susseccfully completed .The only one problem is that data is not inserted to the table when it exists in the database

But in this case we can insert data to the table when it exists in the database,But the problem is that we cannot create procedures, triggers without errors

Re: impdb error [message #279167 is a reply to message #279165] Wed, 07 November 2007 03:30 Go to previous messageGo to next message
shiju_t_v
Messages: 56
Registered: May 2007
Location: kannur,kerala
Member
The output of the impdb command is

was stated below

[oracle@mcrbserver Datapump]$ impdp \'sys/admin as sysdba\' directory=Datapump dumpfile=expfull.dmp logfile=impfull.log full=y TABLE_EXISTS_ACTION=REPLACE

Import: Release 10.2.0.1.0 - Production on Wednesday, 07 November, 2007 14:43:08

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01": 'sys/******** AS SYSDBA' directory=Datapump dumpfile=expfull.dmp logfile=impfull.log full=y TABLE_EXISTS_ACTION=REPLACE
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"BANQUE" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "BANQUE"."CIF_PHOTO" 648.8 KB 1 rows
. . imported "BANQUE"."IBA_BRANCH_MASTER" 131.7 KB 877 rows
. . imported "BANQUE"."CHEQUE_STATUS" 346.0 KB 5888 rows
. . imported "BANQUE"."FLOW_ARCHIVE" 25.42 KB 4 rows
. . imported "BANQUE"."AC_DEPOSIT_FREEZING_DT" 9.507 KB 5 rows
. . imported "BANQUE"."AC_OPENING_BANK" 11.89 KB 2 rows
. . imported "BANQUE"."AC_OPENING_DEPOSIT" 30.04 KB 76 rows
. . imported "BANQUE"."AC_OPENING_DEPOSIT_DT" 10.17 KB 27 rows
. . imported "BANQUE"."AC_OPENING_DL_SECURITY" 7.109 KB 9 rows
. . imported "BANQUE"."AC_OPENING_JL_SECURITY" 7.820 KB 3 rows
. . imported "BANQUE"."AC_OPENING_JOINT_AC_HOLDERS" 8.828 KB 49 rows
. . imported "BANQUE"."AC_OPENING_LOAN" 22.26 KB 31 rows
. . imported "BANQUE"."AC_OPENING_LOCKER" 10.56 KB 3 rows
. . imported "BANQUE"."AC_OPENING_SUSPENSE" 10.96 KB 3 rows
. . imported "BANQUE"."AC_OPERATORS" 14.10 KB 50 rows
. . imported "BANQUE"."AC_PRODUCT_MASTER" 19.85 KB 17 rows
. . imported "BANQUE"."AC_RENEWAL_LOAN" 8.726 KB 3 rows
. . imported "BANQUE"."BILLS_BANK_BRANCH_MASTER" 12.55 KB 10 rows
. . imported "BANQUE"."BILLS_BANK_MASTER" 12.39 KB 60 rows
. . imported "BANQUE"."BRANCH_DD_ISSUE" 10.26 KB 2 rows
. . imported "BANQUE"."BRANCH_DD_PAYMENT" 8.460 KB 1 rows
. . imported "BANQUE"."BRANCH_DD_PENDING_PAYMENT" 7.914 KB 1 rows
. . imported "BANQUE"."BRANCH_DD_STOCK_RECEIPT" 7.804 KB 2 rows
. . imported "BANQUE"."BRANCH_EXTENSION_MASTER" 8.929 KB 7 rows
. . imported "BANQUE"."BRANCH_MASTER" 6.5 KB 5 rows
. . imported "BANQUE"."BRANCH_WISE_LAST_AC_NOS" 5.820 KB 12 rows
. . imported "BANQUE"."CASH_TXN_DT" 34.49 KB 826 rows
. . imported "BANQUE"."CASH_TXN_HD" 16.25 KB 80 rows
. . imported "BANQUE"."CHEQUE_BOOK_ISSUE" 9.335 KB 8 rows
. . imported "BANQUE"."CHEQUE_BOOK_RECEIPT_DT" 7.703 KB 12 rows
. . imported "BANQUE"."CHEQUE_BOOK_RECEIPT_HD" 7.273 KB 9 rows
. . imported "BANQUE"."CHEQUE_BOOK_SCHEME_WISE" 6.007 KB 4 rows
. . imported "BANQUE"."CHEQUE_BOOK_TYPE_MASTER" 6.796 KB 19 rows
. . imported "BANQUE"."CHEQUE_RETURN_DT" 8.585 KB 8 rows
. . imported "BANQUE"."CHEQUE_STOP_PAYMENT_REQUEST" 11.08 KB 11 rows
. . imported "BANQUE"."CIF" 26.95 KB 21 rows
. . imported "BANQUE"."CIF_INDIVIDUAL" 21.71 KB 8 rows
. . imported "BANQUE"."CLEARING_BANK_MASTER" 7.007 KB 5 rows
. . imported "BANQUE"."CLEARING_DAILY_DATA" 12.53 KB 2 rows
. . imported "BANQUE"."DAILY_TXN_DT" 9.976 KB 85 rows
. . imported "BANQUE"."DAILY_TXN_HD" 26.21 KB 87 rows
. . imported "BANQUE"."DAY_STATUS" 15.67 KB 170 rows
. . imported "BANQUE"."DEPOSIT_AGENT_MASTER" 8.218 KB 3 rows
. . imported "BANQUE"."FINE_RULE_MASTER" 8.875 KB 1 rows
. . imported "BANQUE"."GL_BASIC_GROUP" 6.265 KB 12 rows
. . imported "BANQUE"."GL_GROUP_MASTER" 6.164 KB 10 rows
. . imported "BANQUE"."GL_MASTER" 8.007 KB 17 rows
. . imported "BANQUE"."IBA_BANK_MASTER" 14.60 KB 76 rows
. . imported "BANQUE"."IBA_DD_CATEGORY_MASTER" 6.390 KB 13 rows
. . imported "BANQUE"."IBA_DD_ISSUE" 10.97 KB 2 rows
. . imported "BANQUE"."IBA_DD_PAYMENT" 9.414 KB 1 rows
. . imported "BANQUE"."IBA_DD_RECEIPT" 9.507 KB 9 rows
. . imported "BANQUE"."IBA_DD_STATUS" 9.382 KB 61 rows
. . imported "BANQUE"."IBA_DD_STOP_PAYMENT" 9.320 KB 3 rows
. . imported "BANQUE"."IBA_TYPE_MASTER" 7.273 KB 5 rows
. . imported "BANQUE"."INTEREST_TABLE_DT" 10.88 KB 8 rows
. . imported "BANQUE"."INTEREST_TABLE_HD" 8.218 KB 8 rows
. . imported "BANQUE"."INTEREST_TABLE_MASTER" 6.476 KB 7 rows
. . imported "BANQUE"."INTER_BRANCH_TXN" 8.773 KB 1 rows
. . imported "BANQUE"."JEWEL_MARKET_RATE" 6.046 KB 4 rows
. . imported "BANQUE"."JEWEL_MASTER" 6.187 KB 16 rows
. . imported "BANQUE"."JEWEL_OTHER_CHARGES_RATE" 8.812 KB 15 rows
. . imported "BANQUE"."LOAN_INTEREST_DISCOUNT_DT" 9.117 KB 9 rows
. . imported "BANQUE"."LOAN_INTEREST_RATE_TL" 7.007 KB 3 rows
. . imported "BANQUE"."LOAN_LIMIT_REGISTER" 7.312 KB 7 rows
. . imported "BANQUE"."LOAN_OD_INTEREST_RATE" 6.781 KB 6 rows
. . imported "BANQUE"."LOAN_REPAY_ALLOCATION" 7.023 KB 2 rows
. . imported "BANQUE"."LOAN_REPAY_SCHEDULE_DT" 9.828 KB 84 rows
. . imported "BANQUE"."LOAN_REPAY_SCHEDULE_HD" 9.773 KB 15 rows
. . imported "BANQUE"."LOAN_SECURITY_GROUP_MASTER" 6.296 KB 6 rows
. . imported "BANQUE"."LOAN_SECURITY_REGISTER" 13.36 KB 6 rows
. . imported "BANQUE"."LOCKER_LINKED_DEPOSIT" 6.742 KB 4 rows
. . imported "BANQUE"."LOCKER_MASTER" 6.539 KB 1 rows
. . imported "BANQUE"."LOCKER_RENT_TABLE" 7.281 KB 3 rows
. . imported "BANQUE"."LOCKER_TYPE_MASTER" 5.796 KB 4 rows
. . imported "BANQUE"."MAIN_MENU" 5.796 KB 2 rows
. . imported "BANQUE"."MATURED_DEPOSIT_CFG" 7.429 KB 5 rows
. . imported "BANQUE"."MENU_OPTIONS" 30.28 KB 84 rows
. . imported "BANQUE"."MODULE_GROUP_MASTER" 7.234 KB 15 rows
. . imported "BANQUE"."MODULE_MASTER" 33.08 KB 91 rows
. . imported "BANQUE"."NOMINATION_DT" 5.664 KB 2 rows
. . imported "BANQUE"."NOMINATION_MASTER" 10.47 KB 2 rows
. . imported "BANQUE"."NPA_EXEMPTION_ACCOUNTS" 7.601 KB 6 rows
. . imported "BANQUE"."NPA_EXEMPTION_PRODUCTS" 7.085 KB 11 rows
. . imported "BANQUE"."OTHER_CHARGES_DUE_DT" 7.437 KB 4 rows
. . imported "BANQUE"."OTHER_CHARGES_DUE_HD" 7.242 KB 5 rows
. . imported "BANQUE"."OTHER_CHARGES_MASTER" 7.054 KB 3 rows
. . imported "BANQUE"."OUTWARD_BILLS_DT" 17.16 KB 5 rows
. . imported "BANQUE"."OUTWARD_BILLS_REALISATION" 10.69 KB 10 rows
. . imported "BANQUE"."PAY_ORDER_PAYMENT" 8.359 KB 3 rows
. . imported "BANQUE"."PAY_ORDER_PENDING_PAYMENT" 6.906 KB 1 rows
. . imported "BANQUE"."PRESENT_LOGINS" 7.398 KB 12 rows
. . imported "BANQUE"."PRODUCT_GROUP_WISE_INT_TABLE" 6.078 KB 5 rows
. . imported "BANQUE"."PRODUCT_WISE_TABLE" 7.140 KB 24 rows
. . imported "BANQUE"."ROLE_MASTER" 6.140 KB 4 rows
. . imported "BANQUE"."ROLE_WISE_PERMISSIONS" 13.75 KB 109 rows
. . imported "BANQUE"."SINGLE_AUTH_LIMITS" 6.328 KB 2 rows
. . imported "BANQUE"."SI_REGISTRATION_DT" 8.742 KB 6 rows
. . imported "BANQUE"."SI_REGISTRATION_HD" 11.88 KB 3 rows
. . imported "BANQUE"."TABLE_CATALOG" 11.64 KB 115 rows
. . imported "BANQUE"."TABLE_TEST" 5.960 KB 1 rows
. . imported "BANQUE"."TABLE_WISE_LAST_KEY" 7.976 KB 35 rows
. . imported "BANQUE"."TEST" 4.929 KB 1 rows
. . imported "BANQUE"."TESTPDF" 27.32 KB 441 rows
. . imported "BANQUE"."TESTSHERI" 5.343 KB 8 rows
. . imported "BANQUE"."TT" 21.41 KB 19 rows
. . imported "BANQUE"."TXN_BANK_AC" 9.585 KB 4 rows
. . imported "BANQUE"."TXN_DEPOSITS_DD" 13.43 KB 45 rows
. . imported "BANQUE"."TXN_DEPOSITS_TD" 9.296 KB 9 rows
. . imported "BANQUE"."TXN_DEPOSIT_INT_TD" 7.843 KB 1 rows
. . imported "BANQUE"."TXN_LOANS_ODCC" 8.992 KB 3 rows
. . imported "BANQUE"."TXN_LOANS_TL" 11.67 KB 7 rows
. . imported "BANQUE"."TXN_LOAN_INT_DT" 6.593 KB 1 rows
. . imported "BANQUE"."TXN_LOCKER_RENT" 8.164 KB 1 rows
. . imported "BANQUE"."TXN_OTHER_CHARGES" 8.703 KB 3 rows
. . imported "BANQUE"."TXN_SUSPENSE_AC" 8.867 KB 8 rows
. . imported "BANQUE"."USER_FAV_MODULES" 7.812 KB 19 rows
. . imported "BANQUE"."USER_MASTER" 7.664 KB 9 rows
. . imported "BANQUE"."USER_RIGHTS" 14.75 KB 92 rows
. . imported "BANQUE"."USER_WISE_OPTIONS" 9.484 KB 17 rows
. . imported "BANQUE"."USER_WISE_ROLES" 7.546 KB 29 rows
. . imported "BANQUE"."USER_WISE_TXN_RIGHTS" 8.687 KB 3 rows
. . imported "BANQUE"."AC_CLOSING_DEPOSIT" 0 KB 0 rows
. . imported "BANQUE"."CLOSING_WITHDRAWAL_DT" 0 KB 0 rows
. . imported "BANQUE"."DD_AC_STATUS" 0 KB 0 rows
. . imported "BANQUE"."DD_INOPERATIVE_TRANSFER_DT" 0 KB 0 rows
. . imported "BANQUE"."EXAMP" 0 KB 0 rows
. . imported "BANQUE"."FLOW_ARCHIVE_AC_NOS" 0 KB 0 rows
. . imported "BANQUE"."HOLIDAY_MASTER_GENERAL" 0 KB 0 rows
. . imported "BANQUE"."LOAN_DCB_DT" 0 KB 0 rows
. . imported "BANQUE"."LOAN_DCB_SUMMARY" 0 KB 0 rows
. . imported "BANQUE"."LOAN_MIS_DT" 0 KB 0 rows
. . imported "BANQUE"."LOAN_MIS_NPA_SUMMARY" 0 KB 0 rows
. . imported "BANQUE"."LOAN_MIS_OD_SUMMARY" 0 KB 0 rows
. . imported "BANQUE"."LOAN_MIS_SUMMARY" 0 KB 0 rows
. . imported "BANQUE"."MATURED_DEPOSIT_TRANSFER_DT" 0 KB 0 rows
. . imported "BANQUE"."MATURED_DEPOSIT_TXN" 0 KB 0 rows
. . imported "BANQUE"."MEMBER_MASTER" 0 KB 0 rows
. . imported "BANQUE"."PAY_ORDER_ISSUE" 0 KB 0 rows
. . imported "BANQUE"."SI_PROCESS_DT" 0 KB 0 rows
. . imported "BANQUE"."SI_PROCESS_HD" 0 KB 0 rows
. . imported "BANQUE"."SI_TXN_SUMMARY" 0 KB 0 rows
. . imported "BANQUE"."SI_TXN_SUMMARY_SLIP_DT" 0 KB 0 rows
. . imported "BANQUE"."TXN_ACC_DIRECT" 0 KB 0 rows
. . imported "BANQUE"."TXN_DEPOSIT_INT_DD_DT" 0 KB 0 rows
. . imported "BANQUE"."TXN_DEPOSIT_INT_DD_HD" 0 KB 0 rows
. . imported "BANQUE"."TXN_IBA" 0 KB 0 rows
. . imported "BANQUE"."TXN_LOAN_INT_HD" 0 KB 0 rows
. . imported "BANQUE"."USER_GROUP_MASTER" 0 KB 0 rows
. . imported "BANQUE"."USER_WISE_BANNED_MODULES" 0 KB 0 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
ORA-39082: Object type ALTER_PACKAGE_SPEC:"BANQUE"."USERRIGHTSPACKAGE" created with compilation warnings
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
ORA-39082: Object type ALTER_PROCEDURE:"BANQUE"."USERRIGHTS" created with compilation warnings
ORA-39082: Object type ALTER_PROCEDURE:"BANQUE"."TLDCBPROCESSING" created with compilation warnings
ORA-39082: Object type ALTER_PROCEDURE:"BANQUE"."MODULE_DETAILS" created with compilation warnings
ORA-39082: Object type ALTER_PROCEDURE:"BANQUE"."TEMP_TABLE_CREATION" created with compilation warnings
ORA-39082: Object type ALTER_PROCEDURE:"BANQUE"."ODCC_INTEREST" created with compilation warnings
ORA-39082: Object type ALTER_PROCEDURE:"BANQUE"."FD_AUTO_RENEWAL" created with compilation warnings
ORA-39082: Object type ALTER_PROCEDURE:"BANQUE"."TD_MISS_PROCESSING" created with compilation warnings
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
ORA-39082: Object type TRIGGER:"BANQUE"."AC_OPENING_DL_SECURITY_INSERT" created with compilation warnings
ORA-39082: Object type TRIGGER:"BANQUE"."AC_OPENING_LOAN_DELETE" created with compilation warnings
ORA-39082: Object type TRIGGER:"BANQUE"."AC_OPENING_LOAN_INSERT" created with compilation warnings
ORA-39082: Object type TRIGGER:"BANQUE"."NOMINATION_DT_DELETE" created with compilation warnings
ORA-39082: Object type TRIGGER:"BANQUE"."NOMINATION_DT_INSERT" created with compilation warnings
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS"."SYS_IMPORT_FULL_01" completed with 31 error(s) at 14:44:31
Re: impdb error [message #279168 is a reply to message #279165] Wed, 07 November 2007 03:32 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Quote:



The point is than when i use exp command for export and imp command for insert ,the operation was susseccfully completed .The only one problem is that data is not inserted to the table when it exists in the database


Have you tried with ignore=y or destroy=y

Re: impdb error [message #279169 is a reply to message #279167] Wed, 07 November 2007 03:34 Go to previous messageGo to next message
Michel Cadot
Messages: 59422
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Try to recompile your object (alter ... compile) and then use "show error" to see what errors you have.
Finaly fix them.

Regards
Michel
Re: impdb error [message #279170 is a reply to message #279165] Wed, 07 November 2007 03:35 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Quote:

But the problem is that we cannot create procedures, triggers without errors



check out the procedures/ compile the objects and see...
Re: impdb error [message #279178 is a reply to message #279170] Wed, 07 November 2007 04:36 Go to previous messageGo to next message
shiju_t_v
Messages: 56
Registered: May 2007
Location: kannur,kerala
Member
i recompiled and created my procedures and triggers ,
there is no error
Re: impdb error [message #279179 is a reply to message #279165] Wed, 07 November 2007 04:41 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

In which database you did it? The database you just imported or the one from which you took export?
Re: impdb error [message #279180 is a reply to message #279178] Wed, 07 November 2007 04:47 Go to previous messageGo to next message
shiju_t_v
Messages: 56
Registered: May 2007
Location: kannur,kerala
Member
By using impdp , we can have to recreate tables , but we cannot have to recreate procedure or triggers ,etc
Is there any way because i have two servers
one database server and
one database standby server.
In the first import appempt we can procedures or triggers ,
In the second import attemt we cannot create procedures or triggers

ORA-31684: Object type PROCEDURE:"BANQUE"."STANDING_INSTRUCTIONS" already exists occurs

The impotant point is that We only change procedures or triggers in the database server , not in the database standby server , we want to replace database standby server procedures with database server procedures . But By using impdp we are unable to replace it.

Is there any way ?

regards
SHIJU
Re: impdb error [message #279181 is a reply to message #279179] Wed, 07 November 2007 04:51 Go to previous messageGo to next message
shiju_t_v
Messages: 56
Registered: May 2007
Location: kannur,kerala
Member
I exported from the oracle 10.2.0.1 database at centos 4 and
I imported to oracle 10.2.0.1 database at centos 5
Re: impdb error [message #279184 is a reply to message #279165] Wed, 07 November 2007 04:52 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Quote:

ORA-31684: Object type PROCEDURE:"BANQUE"."STANDING_INSTRUCTIONS" already exists occurs


I don't see it is a problem...

If you got error message while importing then,
Have you tried with
alter procedure "BANQUE"."STANDING_INSTRUCTIONS" compile?
Then what is the result?
If it it error free then no problem.
And if not then check the procedure script. It may happen that the procedure is missing some columns of a table or missing a table or etc...
Re: impdb error [message #279185 is a reply to message #279165] Wed, 07 November 2007 04:56 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

What is centos 4 or centos 5?
Re: impdb error [message #279186 is a reply to message #279184] Wed, 07 November 2007 04:56 Go to previous messageGo to next message
shiju_t_v
Messages: 56
Registered: May 2007
Location: kannur,kerala
Member
The problem is that we want to replace procedures by using backups
Re: impdb error [message #279187 is a reply to message #279185] Wed, 07 November 2007 04:57 Go to previous message
shiju_t_v
Messages: 56
Registered: May 2007
Location: kannur,kerala
Member
Linux versions open source
Previous Topic: SQLLDR Error : Field in data file exceeds maximum length
Next Topic: imp dump with object type ORA-02304
Goto Forum:
  


Current Time: Thu Oct 23 06:14:55 CDT 2014

Total time taken to generate the page: 0.09640 seconds