Export SQL statements from Oracle 11g database via command line

exportoracle

I'd like to export an Oracle 11g database as a series of SQL statements via the command line. I'd like the functionality of SQL Developer (the process described here https://dba.stackexchange.com/a/10071/7660) but via the command line.

Note that the data must be in a flat file and not a binary format (as with imp/exp or impdp/expdb (Datapump)).

Is this possible? If so, how?

Best Answer

Depends what you mean by 'from the command line', but you can generate the DDL via the dbms_metadata package (see here for example).

If you want the data as well then you'd then have to roll your own, unless you find a generic script somewhere on the web. Automating the script with dynamic SQL and the data disctionary is certainly possible - it's what SQL*Developer must do ultimtately - it will just need some work to get right, and to handle things like embedded quotes. No quick fix I'm afraid.

Related Solutions

Oracle 11g .DMP viewer

First of all you need to create a directory on the database.

Create or Replace Directory IMP_DIR as 'Path\Path\';

Ensure the user that will import the file has the privilige to Read & Write on the directory (In case you are not using SYS)

GRANT Read, Write ON DIRECTORY IMP_DIR TO user;

Then, include the directory parameter within the import statement

impdp 'sys AS SYSDBA' file=dump.dmp full=yes log=implog.txt directory=IMP_DIR

Db2 – How to run DB2 export command in shell

There are three (well, actually four...) ways to execute export in DB2.

There is the db2export API (.sh in Unix/Linux, .exe in Windows). This can be executed from anything that understands command shell.

There is the export command to the db2 command. This can be run from a DB2 interactive session, or from the command shell.

There is also the export command through the ADMIN_CMD stored procedure. This allows you to invoke the export from a stored procedure which is accessable through anything that can execute SQL against DB2. This is probably the option you are looking for if you want to do export via SQL through another tool.

There is also the db2move utility which can perform an export, but that is done via command shell as well.

Best Answer

Related Solutions

Oracle 11g .DMP viewer

Db2 – How to run DB2 export command in shell

Related Question