Detailed examples of Oracle data table import and export

This article brings you relevant knowledge about Oracle, which mainly organizes the related issues of importing and exporting data tables. If a large amount of data needs to be migrated, select "Export Table" and "Import Table" The Oracle method does not require writing a table structure. Let’s take a look at it. I hope it will be helpful to everyone.

Recommended tutorial: "Oracle Video Tutorial"

0. Background: I suddenly received a notice to work from home. All work data is on the intranet and needs to be copied out and taken away. The largest single table is about 3.7G. Based on usual experience, I exported the tables in the Oracle database to xlsx (to facilitate communication with colleagues). I found that the efficiency was too low, and due to the large amount of data, errors were often reported and could not be exported. Later, I tried to export it to a csv file, and it was prompted that the export was completed. However, when I found a new computer to import, I found that the data was seriously missing and could not be used.

1. The original stupid method

Right-click the table to be exported in the object window, click Query Data, and click the csv file or excel file. This method is usually used. The query results are very intuitive and suitable for small amounts of data.

1.Oracle data table export

Export table tool

There are two ways to open it in PL/SQL, method one is Right-click the table that needs to be exported in the object window, click Export Data, and the export table window will pop up. The second method is to click the tool at the top of the PL/SQL page, click Export Table, and the export table window will pop up. The export table tool can export multiple tables in batches

In the export table window, there are three specific export methods, and all three methods can export the table structure and data

(1) Oracle Export

The export result of "Oracle Export" is a binary file in dmp format, which cannot be previewed, but it is fast and can be cross-platform. We plan to use this method in the future. The first dmp file is about 3.61G and takes 3 minutes to export.

(2) SQL Insert

The export result of "SQL Insert" is a file in sql format, which can be previewed in text format, but the speed is not " Oracle export" is fast. For a 3.61G file in dmp format, the export time is 14 minutes and the size is 9.34G.

(3) PL/SQL Developer

The export result of "PL/SQL Developer" is a file in pde format, which is PL/SQL free File format, cannot be previewed in text format, speed

For a 3.61G file in dmp format, the export time is 10 minutes and the size is 0.4G. The amount of data is too small, so I don’t believe that all of it will be copied out, but the number of log prompts is consistent with the data table

2.Oracle data Table Import

ODBC Importer

ODBC should mean Open Database Connectivity (Open Data Interconnection). I often use the ODBC importer to import EXCEL documents and ACCESS database tables. Importing tables using the ODBC importer requires defining the table structure in advance.

(1) Create a new table

File-New-Create a table and set the table name and columns. Remarks can be added appropriately to facilitate subsequent use. There must be a type when setting the column. Try to set it as large as possible to prevent errors during subsequent imports. You can click the View SQL button to display the SQL statement for creating the table, so that you can directly execute the code to create the table next time. .

(2) Open the ODBC Importer

Click "Tools" at the top of the PL/SQL page, click "ODBC Importer", and a pop-up will appear ODBC Importer Window

# (3) Select the table to be imported

User/System DSN Select "Excel Files", click Connect, and select the table to be imported surface. Select the specific sheet to be imported and preview

(4) Determine the corresponding location of Oracle

Select the owner, table, and check the field matching. For fields that cannot be automatically matched, manually select the field in the right window Select the corresponding field and field type, and click the "Import" button to import the data.

Text Importer

As the name suggests, the text importer imports files in text format. I usually use the text importer to import files in csv format

(1) Create a new table, the specific method is the same as the ODBC importer

(2) Open the text importer

Click "Tools" at the top of the PL/SQL page, click "Text" Importer", the text importer window pops up

(3) Select the table to be imported

Click the Select File button, select the text file to be imported and preview it

(4) Determine the corresponding location of Oracle

Select the owner, table, and check the field matching. For fields that cannot be automatically matched, manually select the corresponding field in the right window Fields and field types, click the "Import" button to import data.

Import table tool

Click the tool at the top of the PL/SQL page, click Import table, and the import table window will pop up

(1) Oracle import

Select the dmp file to be imported, click the "Import" button, and there will be an import completion prompt at the bottom. After the import is completed, you can view the detailed import results on the rightmost log page

(2) SQL insertion

Select the sql file to be imported and click the "Import" button. The sqlplus.exe execution program window will pop up, showing the real-time progress of the import. After the import is completed, you can view the detailed import results on the rightmost log page

(3) PL/SQL Developer

Select the pde file that needs to be imported, click the "Import" button, and there will be an import completion prompt at the bottom. After the import is completed, you can view the detailed import results on the log page on the far right. The speed is really slow, slower than SQL insertion

3. Summary

(1) If you need to interact with other people with a small amount of daily data, Export query results to xlsx format

(2) ODBC importer and text importer need to write the table structure in advance

(3) If a large amount of data needs to be migrated, choose the Oracle method of "Export Table" and "Import Table". There is no need to write the table structure

(4) The backup data can be directly exported to a file in dmp format. Re-import when you need to restore

Recommended tutorial: "Oracle Video Tutorial"

The above is the detailed content of Detailed examples of Oracle data table import and export. For more information, please follow other related articles on the PHP Chinese website!