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 ?
The export dump file includes the metadata for objects contained within the user-defined tablespaces and both the metadata and data for user-defined objects contained within the administrative tablespaces, such as SYSTEM and SYSAUX . Transport the export dump file.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With