Home » SQL » How to Backup a Table in Oracle?

How to Backup a Table in Oracle?

By

In this tutorial, I am giving some examples to backup a table in Oracle.

Example 1 - Backup an Oracle Table With Data

It will Create a copy of the EMP table in EMP_BACKUP table.

CREATE TABLE emp_backup
AS
   SELECT * FROM Emp;

Example 2 - Backup Specific Columns of a Table

The following example will copy only columns EMPNO and SAL from EMP table to EMP_SAL_BACKUP table.

CREATE TABLE emp_sal_backup
AS
   SELECT empno, sal FROM Emp;

Example 3 - Backup Specific Rows of a Table

Backup only data for DEPTNO is equal to 20 from EMP table to EMP_DEPT20_BKP table.

CREATE TABLE emp_dept20_bkp
   AS
SELECT empno,
   ename,
   job,
   sal,
   comm,
   hiredate
FROM Emp
   WHERE deptno = 20;

Example 4 - Backup a Table Using EXP Command in Oracle

The following EXP command will back-up the EMP table in EMP.DMP file of SCOTT user in Oracle.

exp userid=scott/[email protected] tables=emp file=emp.dmp

Output

Export: Release 11.1.0.7.0 - Production on Mon Sep 24 09:33:59 2018

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in UTF8 character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table EMP 14 rows exported
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.

Example 5 - Backup Multiple Tables Using EXP Command in Oracle

The following EXP command will create the backup for EMP and DEPT tables of SCOTT user in SCOTT_TABLES.DMP file.

exp userid=scott/[email protected] tables=emp,dept file=scott_tables.dmp

Output

Export: Release 11.1.0.7.0 - Production on Mon Sep 24 10:07:21 2018

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in UTF8 character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table EMP 14 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table DEPT 4 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.

See also: