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.
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.
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
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.
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.
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
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.
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.
Click the tool at the top of the PL/SQL page, click Import table, and the import table window will pop up
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
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
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
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!