How to Backup a Table in Oracle?

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

In the world of databases, ensuring the safety and integrity of your data is paramount. One of the most common ways to safeguard your data is through regular backups. If you're using Oracle, one of the leading relational database management systems, you might be wondering how to backup a specific table. This tutorial is designed to walk you through the process of backing up a table in Oracle. Whether you're a seasoned database administrator or a beginner just getting started with Oracle, this guide will provide you with a step-by-step approach to ensure your data remains secure and accessible, even in the face of unexpected data loss or system failures. Let's dive in and explore how to effectively 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;

Here's a breakdown of what each part of the statement does:

• CREATE TABLE emp_backup AS: This part of the statement creates a new table named emp_backup. The AS keyword indicates that the structure and data of the new table will be based on the result of the query that follows.
• SELECT * FROM Emp: This is a standard SQL query that selects all records (*) from the Emp table.

So, in essence, CREATE TABLE emp_backup AS SELECT * FROM Emp; creates a new table called emp_backup that includes all the data and the same structure as the original Emp table. This is a simple and effective way to create a backup of a table in an Oracle database.

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;

Here's a breakdown of what each part of the statement does:

• CREATE TABLE emp_sal_backup AS: This part of the statement creates a new table named emp_sal_backup. The AS keyword indicates that the structure and data of the new table will be based on the result of the query that follows.
• SELECT empno, sal FROM Emp: This is a SQL query that selects only the empno and sal columns from the Emp table.

So, in essence, CREATE TABLE emp_sal_backup AS SELECT empno, sal FROM Emp; creates a new table called emp_sal_backup that includes only the empno and sal columns from the original Emp table. This is a useful way to create a backup of specific columns from a table in an Oracle database.

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;

Here's a detailed explanation:

• CREATE TABLE emp_dept20_bkp AS: This portion of the statement is instructing the database to create a new table named emp_dept20_bkp. The AS keyword is used to indicate that the structure and data of the new table will be defined by the query that follows.
• SELECT empno, ename, job, sal, comm, hiredate FROM Emp: This is a SQL query that selects the empno, ename, job, sal, comm, and hiredate columns from the Emp table.
• WHERE deptno = 20: This is a condition that filters the rows included in the new table. Only rows where the deptno is equal to 20 will be included in the emp_dept20_bkp table.

So, to summarize, CREATE TABLE emp_dept20_bkp AS SELECT empno, ename, job, sal, comm, hiredate FROM Emp WHERE deptno = 20; creates a new table named emp_dept20_bkp that includes the empno, ename, job, sal, comm, and hiredate columns from the Emp table, but only for rows where deptno is equal to 20. This is a handy way to create a backup of specific columns from a table in an Oracle database, while also applying a filter to the rows that are included.

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/tiger@orcl 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/tiger@orcl 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:

• How to Import DMP File in Oracle Using Command Prompt