Resolving ORA-02374: conversion error loading table during impdp

Iam trying to do an import of a table into 11.2.0.2 database and facing the below mentioned error:

ORA-02374: conversion error loading table "MINE_USR"."POPUPS_PROJECT_BIN"

ORA-12899: value too large for column MINE_TEXT (actual: 66, maximum: 64)

ORA-02372: data for row: MINE_TEXT : 0X'45737061C3B16F6C2020202020202020202020202020202020'

Details:

1.        Used expdp and created a dump for the table: MINE_USR. POPUPS_PROJECT_BIN

2.        While doing a impdp on the target database :

ORA-02374: conversion error loading table "MINE_USR"."POPUPS_PROJECT_BIN"

ORA-12899: value too large for column MINE_TEXT (actual: 66, maximum: 64)

ORA-02372: data for row: MINE_TEXT : 0X'45737061C3B16F6C2020202020202020202020202020202020'

Solution:

When you describe the table, you will see that the column MINE_TEXT is char(64):

SQL> desc MINE_USR.POPUPS_PROJECT_BIN

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 MINE_IDX                                NOT NULL NUMBER(5)

 MINE_IDX                                 NOT NULL NUMBER(5)

 MINE_COLUMN                                     NUMBER(5)

 MINE_VALUE                                      NUMBER(5)

 MINE_TEXT                                         CHAR(64)

Execute the below to modify the column of the table:

SQL> alter table MINE_USR.POPUPS_PROJECT_BIN modify MINE_TEXT CHAR(66);

Table altered.

Now perform the import using the below command:

impdp directory=DATA_PUMP_DIR dumpfile=MINE_USR.dmp logfile=MINE_USR.log tables=MINE_USR.POPUPS_PROJECT_BIN table_exists_action=truncate

Change password using AFPASSWD utility in Oracle R12.1

AFPASSWD utlity in R12.1 is enhanced version of FNDCPASS having the below mentioned good and new  features:

1. Run it from any node : may it be database node or Mid-tier.

2. It will prompt you twice to enter the new password ..just to confirm .

We can still continue using FNDCPASS and mandatorily use FNDCPASS for migrating all oracle application user passwords to a non-reversible hash password scheme ( Note id : 457166.1).

How to use AFPASSWD:

AFPASSWD [-c <APPSUSER>[@<TWO_TASK>]] [-f <FNDUSER>]

AFPASSWD [-c <APPSUSER>[@<TWO_TASK>]] [-o <DBUSER>]

AFPASSWD [-c <APPSUSER>[@<TWO_TASK>]] [-a]

AFPASSWD [-c <APPSUSER>[@<TWO_TASK>]] [-l <ORACLEUSER> [<TRUE>] |

[<FALSE>]]

AFPASSWD [-c <APPSUSER>[@<TWO_TASK>]] [-L [<TRUE>] | [<FALSE>]]

AFPASSWD [-c <APPSUSER>[@<TWO_TASK>]] [-s] <APPLSYS>

• -c {APPSUSER}[@{TWO_TASK}] - Specifies the connection string to use, the Applications user, and/or the value of TWO_TASK. This option can be use in combination with others. If it is not specified, default values from the environment will be used.

Note: The password will be prompted for, and is not to be provided in the connection string.

• -f {FNDUSER} - Changes the password for an Applications user. A username that contains spaces must be enclosed in double quotation marks; for example, "SANDESH ACHAR".

• -o {DBUSER} - Changes the password for an Oracle E-Business Suite database user.

Note: This only applies to users listed in the FND_ORACLE_USERID table, not database users in general.

• -a - Changes all Oracle (ALLORACLE) passwords (except the passwords of APPS, APPLSYS, APPLSYSPUB) to the same password, in the same way as the ALLORACLE mode does in FNDCPASS.

• -l - Locks individual {ORACLE_USER} users (except required schemas). {TRUE} = LOCK, {FALSE} = UNLOCK.

• -L - Locks all Oracle (ALLORACLE) users (except required schemas). {TRUE} = LOCK, {FALSE} = UNLOCK.

• -s {APPLSYS} - Changes the password for the APPLSYS user and the APPS user.

This requires the execution of AutoConfig on all tiers.

• -h - Displays help