Table 10-1 Summary of Other Export/Import Data Options, Command-line interface, invoked with sqlldr command, Bulk-loads data into the database from external files, Supports numerous input formats, including delimited, fixed record, variable record, and stream, Data Pump Export and Data Pump Import utilities, Command-line interface, invoked with expdp and impdp commands, Exports and imports from one Oracle database to another (proprietary binary format), Imports/exports entire database, entire schema, multiple schemas, multiple tablespaces, or multiple tables, Command-line interface, invoked with exp and imp commands, Does not support the FLOAT and DOUBLE data types, Capabilities similar to Data Pump; Data Pump is preferred unless you must import or export XMLType data. In a network export, the data from the source database instance is written to a dump file set on the connected database instance. You can now work in the HRDEV schema without affecting your production data in the HR schema. This is done by using Data Pump parameters to specify export and import modes, as well as various filtering criteria. Selectively load data (you can load records based on the records' values). (You can also select a subset of columns. The numbers in these columns indicate the number of rows that were successfully loaded or that caused an error.
Because the dump files are written by the database, rather than by the Data Pump client application, you must create directory objects for the directories to which files will be written. Figure 10-4 Export Wizard: Specify Objects for Exporting Data, On the Summary page, review the information; and if it is what you want, click Finish. You use the same Database Export wizard, but export only the data, and not the DDL (Data Definition Language statements for creating database objects). Save the file regions.txt to the Desktop or to a directory of your choice. These utilities provide support for XMLType data, whereas the Data Pump Export and Import utilities do not. This might be a REGIONS table in another schema (either in the same Oracle database or another Oracle database). When you run the Export utility against an Oracle database, objects (such as tables) are extracted, followed by their related objects (such as indexes, comments, and grants), if any. If a table already exists, it is replaced with the table in the export file. After the table is created, SQL*Loader will be used to load data about the dependents from a flat data file into the dependents table. The files are written in a proprietary, binary format, which means that the dump file set can be imported only by the Data Pump Import utility. Like Data Pump Import and Export, data exported with the Export utility can be imported with the Import utility into the same or a different Oracle database. On the Data Load/Unload page, click the Load icon, and then click the Load Text Data icon. (In this example, there is not yet any benefits information available so the column is shown as NULL in the data file, dependents.dat.). You want to import or export an entire schema from or to another Oracle database. This causes the first row unloaded to be the column names, rather than the first row of data. SQL*Loader supports many text file formats and includes advanced features. You now see the NEW_REGIONS table. Load data from disk, tape, or named pipe. See Oracle Database Administrator's Guide for more information on external tables. You have tab-delimited text data to load, and there are more than 10 tables. You can load by copying and pasting from a spreadsheet. Data Pump Export and Import also provide a set of interactive commands so that you can monitor and modify ongoing export and import jobs. Load data from disk, tape, or named pipe. The load proceeds, and when it is complete, the Text Data Load Repository page appears, showing the regions.txt file at the top of the list of loaded files. Table 10-1 provides a summary of these options. If you did not want to create a new primary key, and wanted to instead use the existing REGION_ID field as the primary key, you would do the following: In the Primary Key list, select REGION_ID(NUMBER).
It can also be slower because when SQL*Loader performs a conventional path load, it competes equally with all other processes for buffer resources. A directory object is a database object that is an alias for a directory in the host operating system's file system. (For information about the options on any wizard page, click the Help button.). Other values include loader to cause SQL*Loader files to be created, and xls to cause a Microsoft Excel .xls file to be created. You use the REMAP_SCHEMA command parameter to indicate that objects are to be imported into a schema other than their original schema. It should have the following content: On Linux: Start a terminal session and log in to the Oracle Database XE host computer with the oracle user account. In this example, suppose that you want to make some changes to the HR sample schema and then test those changes without affecting the current HR schema. See the following examples of using SQL Developer for performing export and import operations: Example: Exporting Metadata and Data for a Table, Example: Importing Metadata and Data Using a Script File, Example: Exporting Data to a Microsoft Excel File, Example: Importing Data from a Microsoft Excel File. This chapter describes how to export (unload) from and import (load) into Oracle Database XE. Scripting on this page enhances content navigation, but does not change the content in any way. (Optional) Open the regions.txt file with a text editor or spreadsheet application to verify that the REGIONS table was unloaded properly. Figure 10-3 shows the first page of the Export Wizard, but with entries reflecting selections that you will make. 
If so, take the option to save the file to disk. The constraint on the relative_id column indicates that it must match a value in the employee_id column of the employees table. This section describes how to import and export data and load and unload data with Oracle Database Express Edition (Oracle Database XE). 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. Assume that you wanted to import the data that was exported in "Example: Exporting Data to a Microsoft Excel File", into a new table that has the same column definitions as the original (REGIONS) table. Click Finish. SQL Developer provides the ability to export user data to a variety of formats: CSV, XML, LOADER, TEXT, INSERT, HTML and XLS. Because you can unload from your own schema only, you cannot change this selection. This chapter includes the following topics: Using SQL Developer for Exporting and Importing, Using Other Tools for Exporting and Importing Data. (In this example, there is not yet any benefits information available so the column is shown as NULL in the data file, dependents.dat.). On the Data Load/Unload page, click the Unload icon, and then click the Unload to Text icon. This user wants to take the exported data, add one row in the Excel file, and import it into a new table that has the same column definitions as the REGIONS table. For example, assume that you created a user named NICK following the instructions in "Example: Creating a User". SQL*Loader uses three different methods to load data, depending on the situation: conventional path, direct path, and external tables.
In this example, suppose that you want to make some changes to the HR sample schema and then test those changes without affecting the current HR schema. The Data Pump Export utility exports data and metadata into a set of operating system files called a dump file set. This wizard step displays a Schema list, in which HR is selected. They are invoked with the exp and imp commands, respectively. The data from the .xls file is loaded into the NEW_REGIONS table and is committed. The wizards load and unload table data only. Use secondary datafiles for loading LOBs and collections. The load executes INSERT statements to insert the data from the datafile into the target table. Click, To review the file created, go to the main menu and select. If a .sql script file is generated (which will not happen in this example), it will not contain any CREATE statements, but only INSERT statements. Create the data file, dependents.dat, in your current working directory. You can create this file using a variety of methods, such as a spreadsheet application or by simply typing it into a text editor. In this example, we'll take the default of all the records and columns. Manipulate the data before loading it, using SQL functions. You now see the REGIONS table. It will contain information about dependents of employees listed in the employees table of the HR schema. You have fewer than 10 tables to load, the data is in spreadsheets or tab- or comma-delimited text files, and there are no complex data types (such as objects or multivalued fields). Table 10-1 Summary of Oracle Database XE Import/Export Options, Data Load/Unload wizards in the Oracle Database XE graphical user interface, Loads/unloads from and to external text files (delimited fields) or XML files, Loads/unloads tables only, one table at a time, Command-line interface, invoked with sqlldr command, Bulk-loads data into the database from external files, Supports numerous input formats, including delimited, fixed record, variable record, and stream, Data Pump Export and Data Pump Import utilities, Command-line interface, invoked with expdp and impdp commands, Exports and imports from one Oracle database to another (proprietary binary format), Imports/exports entire database, entire schema, multiple schemas, multiple tablespaces, or multiple tables, Command-line interface, invoked with exp and imp commands, Does not support the FLOAT and DOUBLE data types, Capabilities similar to Data Pump; Data Pump is preferred unless you must import or export XMLType data. Selectively load data (you can load records based on the records' values). Select the Include Column Names check box. The records are fixed length, and field definitions depend on column positions. Suppose also that you want to create a tab-delimited text file, and you want to save the data in a file called regions.txt. On the Specify Data page, accept the defaults and click Next. (You can use any character as the delimiter.).
It should have the following content: On Linux: Start a terminal session and log in to the Oracle Database XE host computer with the oracle user account. Use the operating system's file system to access the datafiles. Right-click anywhere on the data to invoke the context menu. This is also true for users with administrator privileges. You can export and import metadata (database object definitions), data, or both metadata and data. In the Connections navigator, expand the Tables node under the NICK connection. A dump file set is made up of one or more disk files that contain table data, database object metadata, and control information. You could export the HR schema and then import it into a new HRDEV schema, where you could perform development work and conduct testing. For example, if you created menu items at the property level, selecting this option exports only the menu items created for that property (the export does not include menu items created at the Enterprise or revenue center). The load executes INSERT statements to insert the data from the datafile into the target table.
Check the load status by looking under the Succeeded and Failed columns for the regions.txt file. On the Database Home Page, click the Utilities icon, and then click the Data Load/Unload icon. Format: Select xls to have the data saved to a Microsoft Excel .xls file. You can create this file using a variety of methods, such as a spreadsheet application or by simply typing it into a text editor. The dump file is an Oracle binary-format dump file that can be read only by the Import utility. 
This method can sometimes be slower than other methods because extra overhead is added as SQL statements are generated, passed to Oracle, and executed. In this example, you will import everything except constraints, ref_constraints, and indexes. When you export a file, the application assigns SimphonyExport.csv as the file name. Oracle Database XE provides the following command-line utilities for exporting and importing data: The following sections provide an overview of each utility. The field containing the first name is enclosed in double quotation marks in cases where a variant of the official name is also providedthat is, where the first name field contains a comma. ", How to export data to various file formats using SQL Developer, How to export the object definitions in your schema, Access to the Sample User Account HR (See. For example, direct path load cannot be used on clustered tables or on tables for which there are transactions pending. You can load or unload only a single table at a time. The log file contains a detailed summary of the load, including a description of any errors that occurred during the load. (In the figure, this file is C:\temp\export.xls.). (In the figure, this file is C:\temp\export.sql.) The content of the log file looks similar to the following: You can now work with the dependents table, as you would any other table. On the Types to Export page, deselect Toggle All, then select only Tables (because you only want to export data for a table). Selected Hierarchy Select this option to export only the current level of hierarchy. Assume that you wanted to re-create the REGIONS table that you exported in "Example: Exporting Metadata and Data for a Table", but in a different schema. Check the spelling of your keyword search.
Click Browse, select the regions.txt file, and then click Open. 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.
Click Next. The text files are in an industry-standard format such as tab-delimited or comma-delimited (. 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. Create the SQL*Loader control file, dependents.ctl, in your current working directory. In a network export, the data from the source database instance is written to a dump file set on the connected database instance. For example, Format has other possible values besides the default insert , which causes SQL INSERT statements to be included to insert the data. In this example, you will import everything except constraints, ref_constraints, and indexes. Scripting on this page enhances content navigation, but does not change the content in any way. Description of "Figure 10-1 Export Wizard: Source/Destination", Description of "Figure 10-2 Export Wizard: Specify Objects", "Example: Exporting Metadata and Data for a Table", Description of "Figure 10-3 Export Wizard: Source/Destination Specifying Data Export Only", Description of "Figure 10-4 Export Wizard: Specify Objects for Exporting Data", "Example: Exporting Data to a Microsoft Excel File", Description of "Figure 10-5 Microsoft Excel File with Exported Data (Modified)", "Setting Environment Variables on the Linux Platform", "Exporting and Importing Data with the Export and Import Utilities", "Exporting and Importing with Data Pump Export and Data Pump Import". On the Specify Objects page, click Lookup, then double-click the REGIONS table on the left to move it to the right-hand column. Export the HR schema to a dump file named schema.dmp by issuing the following command at the system command prompt: where password is the password for the SYSTEM user. Save and close the Microsoft Excel .xls file. Suppose you want to unload the REGIONS table, which is part of the HR sample schema, so that it can be used in another application. The data contains XMLType data and contains no FLOAT or DOUBLE data types. The dump file set can be imported to the same database or it can be moved to another system and loaded into the Oracle database there. After completing this How-To, you should be able to understand: Oracle SQL Developer provides utilities to export both the definitions of the objects in a schema and the data stored in those objects. There is no XMLType data in any of the data. There is no XMLType data in any of the data. When you run the Export utility against an Oracle database, objects (such as tables) are extracted, followed by their related objects (such as indexes, comments, and grants), if any. We suggest you try the following to help find what youre looking for: Updated: Nov 2009 for SQL Developer 2.1 Because the HRDEV user account does not already exist, the import process automatically creates it. At the SQL prompt, enter the following commands to create a directory object named dmpdir for the tmp directory that you just created, and to grant read and write access to it for user HR. To do this, take the following steps: At the command prompt, issue the command appropriate to your operating system, to create the directory where the exported files will be placed: Start SQL Command Line (SQL*Plus) and connect as user SYSTEM by entering the following at the command prompt: where password is the password that you specified for the SYS and SYSTEM user accounts upon installation (Windows) or configuration (Linux) of Oracle Database XE. Data exported from any Oracle Database edition (Express Edition, Standard Edition, and Enterprise Edition), can be imported into any other edition. SQL Developer provides the ability to export some or all of your object definitions to script files. Table 10-2 provides a number of load/unload/import/export scenarios and suggests the appropriate option to use for each. In the File Character Set list, select Unicode UTF-8. 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 Oracle Database Administrator's Guide for more information on external tables. In this step, you can specify the objects to export. This is done by using Data Pump parameters to specify export and import modes, as well as various filtering criteria. In this exercise, you export all the object definitions and the data for the Departments table. The records are fixed length, and field definitions depend on column positions. Instead of querying all objects by immediately selecting, You can restrict the list of choices further by using the drop list on the right.. Generate unique sequential key values in specified columns. On Windows: Log in to the Oracle Database XE host computer as the user who installed Oracle Database XE, and then open a command window. For example, if you created menu items at the property level, selecting this option exports menu items created for the property and the revenue center levels. After you receive the Table created message, enter exit to exit the SQL Command Line. In a network import, a target database is loaded directly from a source database with no intervening dump files. Choose an appropriate directory and name for the file. Assume that you want to export the REGIONS table, which is part of the HR sample schema, so that it can be created, along with its data, in another schema (either in the same Oracle database or another Oracle database). The files are in a proprietary binary format. A direct path load does not compete with other users for database resources. It eliminates much of the Oracle database overhead by formatting Oracle data blocks and writing them directly to the database files, bypassing much of the data processing that normally takes place. Save As location: Enter or browse to a desired folder on your local hard drive, and specify the file name for the .xls file. Export the HR schema to a dump file named schema.dmp by issuing the following command at the system command prompt: where password is the password for the SYSTEM user. This section contains the following examples of unloading and loading data with wizards: Example: Unloading Data with the Unload Wizard, Example: Loading Data with the Load Wizard. The bad file contains records that were rejected, either by SQL*Loader or by the Oracle database. In the Data Import Wizard, accept all the defaults; click Next on each page until Summary, and click Finish there. Next to the Primary Key From label, select Create new column. Load data into multiple tables during the same load session. In a network import, a target database is loaded directly from a source database with no intervening dump files. It should have the following content: This file is a CSV (comma-separated values) file in which the commas act as delimiters between the fields. The Unload to Text page appears, showing the Schema wizard step. See "Creating Users" for instructions. This section includes the following topics: The Data Load/Unload wizards of the Oracle Database XE graphical user interface enable you to easily load and unload delimited text data to and from the database. Allrightsreserved. Figure 10-1 Export Wizard: Source/Destination. Table 10-2 Import/Export Scenarios and Recommended Options. (Given what you specified, this causes the export script to be created as C:\temp\export.sql.). Because the dump files are written by the database, rather than by the Data Pump client application, you must create directory objects for the directories to which files will be written. Manipulate the data before loading it, using SQL functions. (This example is trivial, and adding a row to the Excel file may not be typical, but it is presented merely to illustrate some capabilities.). Load arbitrarily complex object-relational data. Data copying is accomplished by exporting and importing data, and by unloading and loading data. For example, assume that you created a user named NICK following the instructions in "Example: Creating a User". From within your current working directory (where you created the control and data files), issue the following SQL*Loader command at the system prompt: The data in the dependents.dat file is loaded into the dependents table and the following message is displayed: Information about the load is written to the log file, dependents.log. Oracle Database XE provides the following command-line utilities for exporting and importing data: The following sections provide an overview of each utility. Load arbitrarily complex object-relational data. See Oracle Database Utilities for a complete discussion of situations in which direct path load should and should not be used. Create a new sequence called REGIONS_SEQ. The Data Pump Import utility imports an export dump file set into a target Oracle database. You can use these as a backup of the object definitions or run them in another schema. To do this, take the following steps: At the command prompt, issue the command appropriate to your operating system, to create the directory where the exported files will be placed: Start the SQL Command Line (SQL*Plus) and connect as user SYSTEM by entering the following at the command prompt: where password is the password that you specified for the SYS and SYSTEM user accounts upon installation (Windows) or configuration (Linux) of Oracle Database XE. e.g.
Copying data into the database from external text files that are in either a standard delimited format or in any of the formats that are supported by the Oracle SQL*Loader utility. Therefore, a direct path load can usually load data faster than conventional path. In the Separator field, replace the comma with a backslash and a lowercase T (\t) to indicate that the field delimiter is a tab character. You can use this first row to set column names when you load. See "Accessing the Database Home Page" for information on getting logged in.
Select, In this step, you can specify the data that gets exported. Table 10-1 provides a summary of these options. Copyright2005, 2006,Oracle. Load data with the Oracle SQL*Loader utility. 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. It eliminates much of the Oracle database overhead by formatting Oracle data blocks and writing them directly to the database files, bypassing much of the data processing that normally takes place. For example, if you created menu items at the property level, selecting this option exports menu items created for the property and the Enterprise levels. The field containing the first name is enclosed in double quotation marks in cases where a variant of the official name is also providedthat is, where the first name field contains a comma. On the Specify Objects page, click Lookup, then double-click the REGIONS table on the left to have it appear in a row in the bottom part of the page. A conventional path load is the default loading method. When loading into a new table, the primary key can be taken from the data or generated from a new or existing Oracle sequence. Suppose your application calls for a REGIONS table, where each row contains a region number and a region name. In SQL Developer, click Tools, then Database Export. These utilities provide support for XMLType data, whereas the Data Pump Export and Import utilities do not. It executes SQL INSERT statements to populate tables in an Oracle database. You can also perform exports and imports over a network. To review the file created, click the Open Folder icon. This will produce a file of insert statements. It should have the following content: This file is a CSV (comma-separated values) file in which the commas act as delimiters between the fields. This example requires a data file and a SQL*Loader control file, which you will create in the first two steps. Exporting and Importing with Data Pump Export and Data Pump Import, Exporting and Importing Data with the Export and Import Utilities. The Data Pump Import utility imports an export dump file set into a target Oracle database. If the SQL Developer export and import wizards are not satisfactory for your needs, you can use one of the command-line utilities available with Oracle Database XE. You can unload and load data in the following ways: Unload and load data with the Data Unload/Load wizards of the Oracle Database XE graphical user interface. It contains the following topics: About Importing, Exporting, Loading, and Unloading Data, Choosing the Right Import/Export/Load/Unload Option. You have text data to load, and you want to load only records that meet certain selection criteria (for example, only records for employees in department number 3001). The input for a typical SQL*Loader session is a control file, which controls the behavior of SQL*Loader, and some data, located either at the end of the control file itself, or in a separate datafile.