How to Back up a Table in Oracle?

While working with the Oracle database, it is important to have a reliable backup strategy in place. One crucial aspect of that strategy is backing up individual database tables. Creating a backup of tables can help in protecting the data from being lost due to unintentional deletion, corruption, or other problems. The Oracle database provides many features for creating a backup.

This guide will explain how to back up a table in Oracle.

How to Back up a Table in Oracle?

To back up a table in Oracle, login to the database using the SQLPLUS command. After the successful login, the following methods can be used to back up a table in Oracle:

• CREATE Statement
• EXP Command
• SQL Developer Tool

How to Back up a Table in Oracle Using CREATE Statement?

The “CREATE” statement creates new database objects such as tables, indexes, views, sequences, and much more. To back up a table in the Oracle database simply create a copy of that specific table using the “CREATE” statement. The example is given below:

In the above example, “CREATE TABLE” is used to create a new (backup) table, named “CONTACTS_BACKUP”. The “SELECT *” is used to select all rows and columns from the table “CONTACTS”.

Output

The output showed that the table “CONTACTS_BACKUP” has been created.

How to Back up a Table in Oracle Using EXP command?

The “EXP” command is used to create a backup of the database or a subset of database objects, such as tables, indexes, views, and stored procedures by exporting it. Type the following command to back up a table in Oracle using the “EXP” command:

In the above command:

• “C##MD” and “md1234” is the username and password.
• The “TABLES” is used to specify the table name.
• The “FILE” is used to specify the output file name.

Output

The output shows that the backup of the “CONTACTS” table has been created.

How to Back up a Table in Oracle Using SQL Developer?

To back up a table in Oracle using SQL developer, the database connection must be established. After the connection creation, expand the database tree to view all database objects and find “Tables” from the list. Expand the “Tables” to view all the tables, present in the selected database:

Right-click on the table name to select the table and then click on “Export…” to create a backup:

Select the output file destination, leave the other setting as default, and click on the “Next >” button:

Note: While exporting the database objects, these settings can be changed according to the user’s requirements.

Specify the database object (table name) and click on the “Next >” button:

To create a backup of a table, click on the “Finish” button:

Wait until the process of exporting gets complete:

The new “.sql” file opens indicating that the backup file of the “CONTACTS” table has been created:

This is how to back up a table in Oracle using different methods.

Conclusion

To back up a table in Oracle, login to the database. After the successful login, the “CREATE” statement can be used to back up a table by creating a copy of the original table. Alternatively, the “EXP” command creates the backup of a table by exporting the specific table. The “SQL developer” tool is also a good way to create a backup of a table. This guide provided a complete demonstration of how to back up a table in Oracle.