How to export oracle table

In Oracle database, exporting tables is a common operation. It can export data in one table or multiple tables to other formats, such as SQL scripts, CSV files, etc. Export tables are a very useful tool if you need to migrate data in a different database environment or back up a database. In this article, we will introduce how to use Oracle's own tools to export tables.

First of all, we need to understand the basic operations of Oracle export tables. In Oracle, you can use expdp or exp two tools to export tables. expdp is a data pump tool that comes with Oracle. It can choose different formats when exporting data, and has higher performance and compression ratio. Exp is Oracle's classic export tool. It can export tables into file formats such as SQL scripts or insert statements. It supports schema, table or conditions as the export unit. Below we will demonstrate how to use these two tools to export a table.

Use expdp to export tables

expdp is a data pump tool that comes with Oracle. It can be run under sysdba permissions and can export a single table, multiple tables or tables in the entire schema. Data can be stored in binary format. Below we demonstrate how to use expdp to export a table.

Step 1: Log in to the Oracle database with sysdba authority

sqlplus / as sysdba

Step 2: Execute the expdp command

expdp username/password@connect_string tables=table_name directory=dir_name dumpfile=filename.dmp logfile=filename.log

Among them:

• username: needs to be exported The user name of the table;
• password: the password of the user;
• connect_string: the connection string;
• tables: the name of the table that needs to be exported, use commas for multiple table names Delimited;
• directory: the directory where the exported file is located;
• dumpfile: the name of the exported file;
• logfile: the name of the exported log file.

For example, we need to export the employees table in the HR schema:

expdp hr/hr@localhost tables=employees directory=dpump dumpfile=expdp_employees.dmp logfile=expdp_employees.log

If you need to export multiple tables, you can use commas to separate them:

expdp hr/hr@localhost tables=employees,departments directory=dpump dumpfile=expdp_hr.dmp logfile=expdp_hr.log

Step 3: View export results

The export files and log files will be saved in the specified directory. You can use a text editor or Oracle's impdp tool to view the export file.

Use exp to export tables

exp is Oracle’s own classic export tool. It can export tables into file formats such as SQL scripts or insert statements. It supports exporting with schema, tables or conditions. unit. Below we demonstrate how to use exp to export a table.

Step 1: Log in to the Oracle database with sysdba authority

sqlplus / as sysdba

Step 2: Execute exp command

exp userid=username/password file=filename.dmp tables=table_name

Among them:

• username: needs to be exported The user name of the table;
• password: the password of the user;
• file: the export file name;
• tables: the name of the table to be exported.

For example, we need to export the employees table in HR schema:

exp userid=hr/hr file=exp_hr.dmp tables=employees

Step 3: View the export results

The export file will be saved in the current directory. You can use a text editor or Oracle's imp tool to view the export file.

Summary

Whether using expdp or exp, exporting Oracle tables is very easy. Depending on the export format and export content, you can choose different tools according to your needs. Whether you are performing data backup or data migration, exporting Oracle tables is an essential operation. Through the introduction of this article, I believe you have understood how to use Oracle's own tools to export tables.

The above is the detailed content of How to export oracle table. For more information, please follow other related articles on the PHP Chinese website!