Oracle has several possibilities to back up the data. One possibility is backing up via data pump. This facility can be used by the command line. Let us discuss three different commands to use the data pump facility.
The commands use the expdp and impdp facility that are stored in the bin directory with Oracle Home. So you must have the possibility to access the Oracle server.
The data that are backed up are stored in a file. It is possible to link the directory where such data are stored to a user (say C##oldSCOTT). This is done via:
CREATE OR REPLACE DIRECTORY test_dir AS ‘D:\app\tmaanen\virtual\admin\orcl\dpdump’;
GRANT READ, WRITE ON DIRECTORY test_dir TO c##oldscott;
The first command is a command to backup several tables. It runs from the command line as:
expdp c##oldscott/pswpsw@orcl tables=EMPLOYEES,DEPARTMENTS directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log
One may restore these tables via:
impdp c##oldscott/pswpsw@orcl tables=EMPLOYEES,DEPARTMENTS directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=impdpEMP_DEPT.log
This restore may also be applied on other databases, as long as a user c##oldscott is created.
Second situation. Likewise, one may also backup and restore a whole scheme:
expdp c##oldscott/pswpsw@orcl schemas=c##oldscott directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log
impdp c##oldscott/pswpsw@orcl schemas=c##oldscott directory=TEST_DIR dumpfile=SCOTT.dmp logfile=impdpSCOTT.log
Third situation. A complete backup and restore. This can be done with:
expdp.exe FULL=Y DUMPFILE=exp.dmp USERID=SYSTEM/pswpsw
impdp.exe FULL=Y DUMPFILE=exp.dmp USERID=SYSTEM/pswpsw REUSE_DATAFILES=Y TABLE_EXISTS_ACTION=REPLACE
A final remark. Once I got this error:
This was solved after I realised that Oracle was running with a user account that had no write privs on the box. I changed the user that starts the oracle service with a user with more extended privs. That solved the issue. I got this solution from https://asktom.oracle.com/pls/apex/asktom.search?tag=unable-to-get-expdpimpdp-utility-run-successfully.