Using EXP/IMP Migrate Oracle 9i to 11g

Preparation

Check the size of the segment, the number of objects, the job, and the record count of all tables.

@db_migrate_precheck_v1.0.sql

Tablespaces.

@get_create_tablespace_v1.0.sql
@create_tablespace.sql

Users and privileges.

SET LONG 2000000
SET PAGES 0

exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE);
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'PRETTY', TRUE);

SELECT dbms_metadata.get_ddl('USER','&schema_owner') FROM dual;
SELECT dbms_metadata.get_granted_ddl('TABLESPACE_QUOTA','&schema_owner') FROM dual;
SELECT dbms_metadata.get_granted_ddl('ROLE_GRANT','&schema_owner') FROM dual;
SELECT dbms_metadata.get_granted_ddl('SYSTEM_GRANT', '&schema_owner') FROM dual;
SELECT dbms_metadata.get_granted_ddl('OBJECT_GRANT', '&schema_owner') FROM dual;

-- script
@get_create_user_ddl_v1.0.sql
@create_user_ddl.sql

Count schema objects record.

-- way 1
DEFINE schema_owner=JOVE
BEGIN
  DBMS_STATS.GATHER_SCHEMA_STATS(
    ownname => '&schema_owner',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    method_opt => 'FOR ALL COLUMNS SIZE AUTO'
  );
END;
/

SELECT table_name,num_rows FROM dba_tables WHERE owner = '&schema_owner' ORDER BY table_name;

-- way 2

SET SERVEROUTPUT ON
DECLARE
  row_count NUMBER;
BEGIN
  FOR u IN (SELECT username FROM dba_users WHERE username NOT IN ('SYS','SYSTEM') AND account_status = 'OPEN') LOOP
    FOR t IN (SELECT table_name FROM dba_tables WHERE owner = u.username ) LOOP
      BEGIN
        EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || u.username || '.' || t.table_name
        INTO row_count;
        DBMS_OUTPUT.PUT_LINE(u.username || '.' || t.table_name || ': ' || row_count);
      EXCEPTION
        WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE(t.table_name || ': Error - ' || SQLERRM);
      END;
    END LOOP;
  END LOOP;
END;
/

Purge recyclebin.

select * from dba_recyclebin;
purge recyclebin;

Check invalid objects.

select owner,object_name,object_type from dba_objects where status='INVALID' and OWNER='&schema_owner';

Export

Disable jobs.

select job,what,broken from dba_jobs where log_user='&schema_owner';
select 'exec dbms_ijob.broken('||job||',true);' from dba_jobs where log_user='&schema_owner';

alter systems set job_queue_processes=0 scope=both;

Lock user.

alter user &schema_owner account lock;

Export.

export ORACLE_SID=orcl
export NLS_LANG=AMERICAN_AMERICA.UTF8
exp system/oracle full=y file=/expdata/exp_full.dmp log=/expdata/exp_full.log buffer=819200000

Import

Disable archive mode.

shutdown immediate
startup mount
alter database noarchivelog;
alter database open;

Import.

export ORACLE_SID=orcl
export NLS_LANG=AMERICAN_AMERICA.UTF8
imp system/oracle file=/expdata/exp_full_date.dmp log=/expdata/imp_user_date.log fromuser=jove touser=jove commit=y buffer=819200000 ignore=y

Check the size of the segment, the number of objects, the job, and the record count of all tables.

@db_migrate_precheck_v1.0.sql

Recompile the invalid object.

select owner,object_name,object_type from dba_objects where status = 'INVALID' and OWNER='&schema_owner';

@?/rdbms/admin/utlrp.sql

Collect statistics.

-- Schema level
exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'&schema_owner',degree=>4);

-- Database level
exec DBMS_STATS.GATHER_DATABASE_STATS(degree=>4);

Post Tasks

Troubleshooting

Issue: EXP-00091: Exporting questionable statistics. ORA-01455: converting column overflows integer datatype.

~]$ exp system/oracle@orcl_11g full=y file=/nfs/dumpdir/exp_full_20251111.dmp log=/nfs/dumpdir/exp_full_20251111.log

Export: Release 9.2.0.8.0 - Production on Tue Nov 11 04:20:47 2025

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses UTF8 character set (possible charset conversion)

About to export the entire database ...
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting roles
. exporting resource costs
. exporting rollback segment definitions
. exporting database links
. exporting sequence numbers
. exporting directory aliases
. exporting context namespaces
. exporting foreign function library names
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions
. exporting system procedural objects and actions
. exporting pre-schema procedural objects and actions
. exporting cluster definitions
. about to export SYSTEM's tables via Conventional Path ...
. . exporting table                    DEF$_AQCALL          0 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                   DEF$_AQERROR          0 rows exported
. . exporting table                  DEF$_CALLDEST          0 rows exported
EXP-00091: Exporting questionable statistics.

...

EXP-00091: Exporting questionable statistics.
EXP-00008: ORACLE error 1455 encountered
ORA-01455: converting column overflows integer datatype
EXP-00000: Export terminated unsuccessfully

Workaround:

---

Issue: EXP-00008: ORACLE error 1406 encountered. ORA-01406: fetched column value was truncated.

 ~]$ export NLS_LANG=AMERICAN_AMERICA.UTF8
~]$ exp system/oracle@orcl_11g full=y file=/nfs/dumpdir/exp_full_20251111.dmp log=/nfs/dumpdir/exp_full_20251111.log

Export: Release 9.2.0.8.0 - Production on Tue Nov 11 04:14:41 2025

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in UTF8 character set and AL16UTF16 NCHAR character set

About to export the entire database ...
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting roles
EXP-00008: ORACLE error 1406 encountered
ORA-01406: fetched column value was truncated
EXP-00000: Export terminated unsuccessfully

Workaround:

---

Issue: IMP-00010: not a valid export file, header failed verification.

[oracle@oracle9i dumpdir]$ imp system/oracle file=/nfs/dumpdir/exp_full_20251111.dmp log /nfs/dumpdir/imp_full_20251111.log fromuser=jove touser=jove ignore=y

Import: Release 9.2.0.8.0 - Production on Tue Nov 11 09:06:40 2025

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

IMP-00010: not a valid export file, header failed verification
IMP-00000: Import terminated unsuccessfully

Cause:

IMP is not supported for high-version EXP dump files.

Workaround:

Modify the dmp file EXPORT version, for example:

9i: V09.02.00
11g: V11.02.00

[oracle@oracle9i dumpdir]$ head -n 1 exp_full_20251111.dmp
EXPORT:V09.02.00