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.