10 Exporting and Importing Metadata and Data. Save and close the Microsoft Excel.xls file. In SQL Developer. Import the dump file, schema.dmp. You learn how to use the data pump import wizard in SQL Developer. SQL Developer Data Pump Import. How to import oracle database or dmp file.
10 Exporting and Importing Metadata and Data This chapter describes how to export (unload) from and import (load) into Oracle Database XE. Activex Windows 8.1. You can export and import metadata (database object definitions), data, or both metadata and data. It contains the following topics: Data can be exported for later importing (loading) into another Oracle database or into a non-Oracle database. Data that has been unloaded from a non-Oracle database can be loaded into an Oracle database, if the data is in a suitable format for loading. This chapter includes the following topics: • • For convenience and the range of features available, you may want to use SQL Developer for export and import operations unless you need to use another tool (command-line utility). Using SQL Developer for Exporting and Importing SQL Developer provides convenient wizards for exporting and importing metadata and data: • To export metadata or data, or both, use the Export Wizard: click Tools, then Database Export. • To import metadata or data, or both, use an appropriate method depending on how the material to be imported was created, or the format of the data to be imported.
This method might be running a script file, or using the Data Import Wizard to import from a data file (such as a.csv file or a Microsoft Excel.xls file). See the following examples of using SQL Developer for performing export and import operations: • • • •. Figure 10-1 Export Wizard: Source/Destination • Accept the default values for the Source/Destination page options, except as follows: Connection: Select HR. Show Schema: Deselect (uncheck) this option, so that the HR schema name is not included in CREATE and INSERT statements in the.sql script file that will be created. (This enables you to re-create the table in a schema with any name, such as one not named HR.) Save As location: Enter or browse to a desired folder on your local hard drive, and specify the file name for the script file. (In the figure, this file is C: temp export.sql.) The script file containing CREATE and INSERT statements will be created in this location.
Note: For explanations of the options on this or any other wizard page, click the Help button. For example, Format has other possible values besides the default insert, which causes SQL INSERT statements to be included to insert the data. Other values include loader to cause SQL*Loader files to be created, and xls to cause a Microsoft Excel.xls file to be created. • Click Next. • On the Types to Export page, deselect Toggle All, then select only Tables (because you only want to export a table). • Click Next. • On the Specify Objects page, click Lookup, then double-click the REGIONS table on the left to move it to the right-hand column.
Shows the result of these actions. Figure 10-2 Export Wizard: Specify Objects • Click Next. • On the Specify Data page, accept the defaults and click Next. By default, all data from the specified table or tables is exported; however, if you want to limit the data to be exported, you can specify one or more ' WHERE clauses' in the bottom part of this page. • On the Summary page, review the information; and if it is what you want, click Finish. (Given what you specified, this causes the export script to be created as C: temp export.sql.) If you need to make any changes, go back to the appropriate page or pages and make them, and then move forward to the Summary page again. Example: Importing Metadata and Data Using a Script File Assume that you wanted to re-create the REGIONS table that you exported in, but in a different schema.