The problem background

Application in use exp Tools from Oracle The following error messages are received when exporting data from ：

EXP-00008: ORACLE error 1455 encountered
ORA-01455: converting column overflows integer datatype

Problem analysis

stay Oracle Search the official website and get the following information ：

Symptoms:
An ORA-1455 error is raised while attempting to export a table that uses more than 2^32 database blocks of space within the source database via the classic export utility (exp).

Cause:
Numeric overflow for an OCI variable associated to the exp utility used when the SYS.EXU9STO table contents are referenced from within the OCI code. The issue is investigated in bug 15985925 EXP: ORA-01455: CONVERTING COLUMN OVERFLOWS INTEGER DATATYPE.

Solution:
The workaround is to use the DataPump export utility (expdp) to export those tables that exceed 2^32 database blocks of space.

Simply speaking , There is a large table with more data than $2^{32}$ Data blocks , In the use of exp The derivative of the tool will report ORA-1455 error . The solution is to use the latest official derivative tool expdp.

Treatment method

Let the app use expdp Instrumental derivative . And exp The difference is , In the use of expdp You need to create a directory object first （Directory）, And the exported data must be stored in the operating system directory corresponding to the directory object .

To create local dump Catalog

mkdir -p /app/dump
chown -R scott:scott /app/dump
setfacl -R -m u:oracle:rwx /app/dump
setfacl -R -d -m u:oracle:rwx /app/dump

Create directory objects

-- create directory directory_name as 'directory_os_path';
create directory DMP_DIR as '/app/dump';

Grant user rights

-- grant read,write on directory directory_name to username;
grant read,write on directory DMP_DIR to SCOTT;

Check user permissions

set linesize 120
col grantor format a12
col grantee format a12
col table_schema format a16
col table_name format a16
col privilege format a16
select * from all_tab_privs where table_name='DMP_DIR';

Derived data

# expdp username/password DIRECTORY=directory_name DUMPFILE=file_name TABLES=table_name
expdp SCOTT/password DIRECTORY=DMP_DIR DUMPFILE=dump01.dmp TABLES=productinfo