[linux] How to export database schema in Oracle to a dump file

I have a database schema on Oracle server installed on a Linux machine. I need to export the schema related information in a dump file.

How to do this ?

This question is related to linux oracle export

The answer is


It depends on which version of Oracle? Older versions require exp (export), newer versions use expdp (data pump); exp was deprecated but still works most of the time.

Before starting, note that Data Pump exports to the server-side Oracle "directory", which is an Oracle symbolic location mapped in the database to a physical location. There may be a default directory (DATA_PUMP_DIR), check by querying DBA_DIRECTORIES:

  SQL> select * from dba_directories;

... and if not, create one

  SQL> create directory DATA_PUMP_DIR as '/oracle/dumps';
  SQL> grant all on directory DATA_PUMP_DIR to myuser;    -- DBAs dont need this grant

Assuming you can connect as the SYSTEM user, or another DBA, you can export any schema like so, to the default directory:

 $ expdp system/manager schemas=user1 dumpfile=user1.dpdmp

Or specifying a specific directory, add directory=<directory name>:

 C:\> expdp system/manager schemas=user1 dumpfile=user1.dpdmp directory=DUMPDIR

With older export utility, you can export to your working directory, and even on a client machine that is remote from the server, using:

 $ exp system/manager owner=user1 file=user1.dmp

Make sure the export is done in the correct charset. If you haven't setup your environment, the Oracle client charset may not match the DB charset, and Oracle will do charset conversion, which may not be what you want. You'll see a warning, if so, then you'll want to repeat the export after setting NLS_LANG environment variable so the client charset matches the database charset. This will cause Oracle to skip charset conversion.

Example for American UTF8 (UNIX):

 $ export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

Windows uses SET, example using Japanese UTF8:

 C:\> set NLS_LANG=Japanese_Japan.AL32UTF8

More info on Data Pump here: http://docs.oracle.com/cd/B28359_01/server.111/b28319/dp_export.htm#g1022624


Examples related to linux

grep's at sign caught as whitespace How to prevent Google Colab from disconnecting? "E: Unable to locate package python-pip" on Ubuntu 18.04 How to upgrade Python version to 3.7? Install Qt on Ubuntu Get first line of a shell command's output Cannot connect to the Docker daemon at unix:/var/run/docker.sock. Is the docker daemon running? Run bash command on jenkins pipeline How to uninstall an older PHP version from centOS7 How to update-alternatives to Python 3 without breaking apt?

Examples related to oracle

concat yesterdays date with a specific time ORA-28001: The password has expired how to modify the size of a column How to create a blank/empty column with SELECT query in oracle? Find the number of employees in each department - SQL Oracle Query to display all tablespaces in a database and datafiles When or Why to use a "SET DEFINE OFF" in Oracle Database How to insert date values into table error: ORA-65096: invalid common user or role name in oracle In Oracle SQL: How do you insert the current date + time into a table?

Examples related to export

Export multiple classes in ES6 modules Why Is `Export Default Const` invalid? How to properly export an ES6 class in Node 4? ES6 export all values from object Export a list into a CSV or TXT file in R How to export database schema in Oracle to a dump file Excel VBA to Export Selected Sheets to PDF How to export all data from table to an insertable sql format? Export pictures from excel file into jpg using VBA -bash: export: `=': not a valid identifier