Oracle SQL Developer – Force Use of IMP/EXP Command for Import/Export

oracleoracle-sql-developer

While importing or exporting oracle data dump using SQL Developer by default it is using impdp command for import and expdp command for export. But I want to import a dump which is exported using exp command, and for that i need to force SQL developer to use imp command instead of impdp command. And same for export.

Pleas help me if anyone knows how to in-force SQL developer to use imp for import instead of impdp and exp for export instead of expdp

Best Answer

Not going to happen

The Original exp utilitie became DESUPPORTED as of 11.1. Additionally, the 11.1 version of the database is no longer supported either. SQL Developer isn't going to be "patched" to support unsupported Database version or unsupported database features.

For more information you can read the Compatibility Matrix for exp/imp which has Oracle Doc ID 132904.1 NOTE : You must have a valid support contract (and account) to access the Document. (copy+pasting information is against Oracle's term of use)

The imp utility seems to still be around for importing old exports.

Workaround

Learn to use the command line.

Related Solutions

Oracle – Automated Scripting of DDL

Well, if sqlplus is screwing your dbms_metadata.get_ddl output, why not select the output in a CLOB and write the CLOB to filesystem.

e.g.

DECLARE
    data CLOB;
    objType varchar2(30) := 'TABLE';
    objSchema varchar2(30) := 'SCOTT';
    objName varchar2(30) := 'EMP';
    fname varchar2(256) := objType || '_' || objSchema || '_' || objName || '.sql';
BEGIN
    SELECT dbms_metadata.get_ddl(objType,objName,objSchema) into data from dual;
    DBMS_XSLPROCESSOR.CLOB2FILE(data,'DATA_PUMP_DIR',fname);
END;
/

This should get you correct DDL, w/o the output getting messed up. The only thing is that the script will be created on the DB server and not on the client from where you invoke sqlplus.

The script gets saved in the directory pointed to by the 'DATA_PUPM_DIR' entry on the DB Server. i.e.

select directory_path from all_directories where directory_name like 'DATA_PUMP_DIR';

What's more you can add some sort of iteration over all the tables/indexes etc of a schema, and get a complete schema's DDL in no time. I do it all the time.

Create dump file using Oracle SQL Developer

As far as I know, Import/Export and Data Pump are command-line-only types of tools. But if you really wanted to use that functionality from within SQL Developer (or SQL Plus), Data Pump uses the built-in DBMS_DATAPUMP and DBMS_METADATA packages. You should be able to call those packages directly, so I would start there. There's documentation available in the Oracle® Database PL/SQL Packages and Types Reference on both of them (DBMS_METADATA and DBMS_DATAPUMP).