DBA Tools

    A lot of people complain the speed of Oracle exp/imp utility, seems the only thing we can do is to specify large buffer size. Actually there are some other methods to improve the performance a little. For export utility, you can change the table's parallel setting before export, and also change some parameter setting for the export session, you can change it after you start the export with DBMS_SYSTEM package.

SELECT SID, SERIAL# FROM V$SESSION WHERE PROGRAM LIKE 'exp%';
exec dbms_system.set_int_param_in_session( sid, serial#,
                'db_file_multiblock_read_count', 128);
exec dbms_system.set_bool_param_in_session( sid, serial#,
                '_serial_direct_read', true);

    For import, usually I don't create indexes and constraints (primary key or unique) with imp utility, manually create them after import. If you don't want to do these job manually, try to modify some session level parameters value with DBMS_SYSTEM package.

SELECT SID, SERIAL# FROM V$SESSION WHERE PROGRAM LIKE 'imp%';
exec dbms_system.set_int_param_in_session( sid, serial#,
                'db_file_multiblock_read_count', 128);
exec dbms_system.set_int_param_in_session( sid, serial#,
                'sort_area_size', 104857600);
exec dbms_system.set_int_param_in_session( sid, serial#,
                'sort_area_retained_size', 104857600);
exec dbms_system.set_int_param_in_session( sid, serial#,
                'sort_multiblock_read_count', 128);

    Anyway change the table's parallel degree is not good for OLTP system, but Oracle did not provide us an interface to force parallel query for another session (not sure, maybe I just don't know it). Why Oracle did not add a "HINT" or "PARALLEL" command line option to export utility.