Before changing or making a significant update on a database table in all databases and Oracle specifically, we have to take a backup from the table.
So in this article, we’ll cover different approaches to performing this task :

Now let’s take them one by one in more detail.

How to Backup a Table in Oracle using SQL

Suppose we must create another copy of a table structure with all data. In our example below, we are using the Employees table.

CREATE TABLE EMP_BKP14082021
AS
  SELECT * FROM EMPLOYEES;

Executing the above SQL statement successfully will create a new table called “EMP_BKP14082021“. as it appears here, we are using a shortcut of a table name; after that, BKP it’s the shorthand of Backup and today’s date. This is our naming convention when we are backing up the database table. Yours may be different. This how basically create backup table in oracle using the above statement.

Note: Creating a new table backup using the above SQL statement will not copy all constraints, indexes, triggers, or comments on the table or columns available on the original table. But we didn’t say all constraints because the Above SQL statement will back up only the not null check constraints.

How to Backup Specific Columns of a Table in Oracle using SQL

To create a new copy of a table but with specific columns. Let’s take a backup of two columns of the Department table (DEPARTMENT_ID, DEPARTMENT_NAME).

CREATE TABLE DEPT_BKP14082021
AS
  SELECT DEPARTMENT_ID,DEPARTMENT_NAME FROM DEPARTMENTS;

Executing the above SQL statement successfully will create a new table called ” DEPT_BKP14082021 ” with only two columns of the original table “DEPARTMENTS,” which is DEPARTEMENT_ID and DEPARTEMENT_NAME with its data.

How to Backup Specific Rows of a Table in Oracle using SQL

Suppose we need to take a backup of a table but with a particular set of rows. That meets our criteria. Suppose we need to take a backup of the “DEPARTMENTS” table when the department’s name is (Payroll, IT Helpdesk); we have to use where condition.

CREATE TABLE DEPT_BKP14082021
AS
  SELECT DEPARTMENT_ID, DEPARTMENT_NAME
  FROM   DEPARTMENTS
  WHERE  DEPARTMENT_ID IN (270, 230);

Executing the above SQL statement successfully will create a new table called ” DEPT_BKP14082021 ” with only rows that its department name is ( Payroll, IT Helpdesk ) of the original table “DEPARTMENTS.” here, we have used the DEPARTMENT_ID column in the where clause because conditions with IDs are really faster and better than names, especially if the original table has huge data.

How to Backup a Table Without Data in Oracle using SQL

To create a new copy of the table but without any data, copy the table’s structure instead of writing CREATE TABLE statement again. We have to use the where clause by specifying a condition that always evaluates with FALSE.

CREATE TABLE DEPT_BKP14082021
AS
  SELECT *
  FROM   DEPARTMENTS
  WHERE  1=2;

Executing the above SQL statement successfully will create a new table called ” DEPT_BKP14082021 ” without any rows inside. Just copy the structure of the original table. Also, without indexes or constraints except for check, NOT NULL constraints.

You need the primary key columns so you could map the data back to the original table if you needed to.

Conner McDonald

How to Backup a Table in Oracle using the EXP Command

The EXP command is used to export the whole schema and specific table/s. Here we want to export the EMPLOYEES table and store the table’s structure and data in a file on the server with a .dmp extension.

exp userid=hr/hrEgorClomo tables=EMPLOYEES file=EMP_BKP14082021.dmp

The output of the above command would be :

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 AR8MSWIN1256 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path …
. . exporting table EMPLOYEES 107 rows exported
Export terminated successfully without warnings.

The above export command using EXP will create a new dump file with the name EMP_14082021.dmp as specified in the statement above and store it in a specified location on the server. As you see above, exporting table EMPLOYEES has been done successfully without any warnings.

How to Backup Multiple Tables in Oracle using the EXP Command

We can export more than one table using the EXP command, so if we want to export two tables, “EMPLOYEES & DEPARTMENTS,” and store their structures and data into one dump file on the server. We can use the following command.

exp tables=EMPLOYEES,DEPARTMENTS file=EMP_DEPT_BKP14082021.dmp

The output of the above command would be :

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 AR8MSWIN1256 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path …
. . exporting table EMPLOYEES 107 rows exported
. . exporting table DEPARTMENTS 27 rows exported
Export terminated successfully without warnings.

The above export command using EXP will create a new dump file with the name EMP_DEPT_14082021.dmp as specified in the statement above and store it on a specified location on the server. As you see above, exporting two tables EMPLOYEES & DEPARTMENTS have been done successfully without any warnings.

How to Backup a Table in Oracle using SQL Developer

SQL Developer provides the ability to export one or more than one database tables. Suppose we need to export the EMPLOYEES table from the HR schema. We need to follow the below steps :

1. Go to the main menu and select Tools->Database Export.

2. An Export wizard will open. At the bottom of the screen, enter a directory and file name. e.g. E:\TableBackup\export.sql. select the Connection, e.g, HR schema. Select Export Data and choose to include the commit command every x rows to export the data of the table. Set the DDL Options for this Export.

3. Select which object types you want to export e.g. Tables Click Next.

4. Specify the objects to export. Enter EMP then click Lookup to search.

5. Shuttle EMPLOYEES table to the right list. then click Next

6. Specify the data to export. Make sure your table HR.EMPLOYEES is selected in the database object area. you can write where conditions restrict the data that will be exported. then click Next

7. In this final step, you can review your Export Summary. Click Finish.

8. The SQL file which contains all export scripts will open into a SQL Worksheet.

How to Backup a Table in Oracle using Toad

Toad provides the ability to export one or more than one database table. Suppose we need to export the LOCATIONS table from the HR schema. We need to follow the below steps. But before running the export utility wizard, we have to configure toad options for export.

Configure Toad Options for Export

1. Click on the menu View > Toad Options.
2. Select Executables from the left-hand side of the options screen. then from the right-hand side, go to Export, then click on the auto find button, or from the three dots button, you can select the exp.exe manually.

Steps to Take Backup of a Table in Oracle Using Toad

1. Go to the main menu, select Database-> Export -> Export Utility Wizard.

2. An Export Utility Wizard will open. Select Export Tables, then click Next

3. Select which Table you want to export in our case LOCATIONS table. then click Next

4. Select which objects to export. then click Next

5. In this step, enter an output file name LOCATIONS.DMP. then click Next

6. Select whether you want to execute the export now or schedule it to run later. then select Compress export file ( .zip). then click Finish

That’s all you need to export a table using Toad. And you will get the message of successful export.

Conclusion

There are multiple ways to take a table backup, whether with or without data. This article has covered most of the tools to perform this task. And it’s highly recommended to take a backup from any table before making any updates or deleting or changing the structure of the table/s. By this, you can revert the changes quickly and avoid any data loss due to your last action against any database table.

If you have any questions, please don’t hesitate to ask them in the comments section below. or ask your question from up right corner and an expert will reply your inquery.
If you have an addition to this guide to add extra value for the readers, kindly let me know.

Hopefully, it was clear and concise.