14
Managing and Moving Data

This chapter describes how to use Oracle Data Manager wizards to transfer data to and from an Oracle database. It also includes a discussion of the Data Manager tasks that can be submitted with the Job Scheduling system. The topics in this chapter are:

• Starting Data Manager
• Exporting Data to a File
• Importing Data from a File
• Loading Data from an External File
• Data Manager Tasks

This chapter assumes that you have read Chapter 7, "Overview of the Database Tools", and that you are familiar with the interface elements of the database tools.

For more information on the tasks Data Manager performs, see the sections on the Export, Import and SQL*Loader utilities in Oracle Server Utilities. For information about standard elements of your operating system, see your operating system-specific documentation.

Starting Data Manager

To start Data Manager, select a database object from the Navigator tree list. Then click on the Data Manager icon in the Applications Launch Palette, or choose Applications and then Oracle Data Manager from the Tools menu.

• To use Data Manager's remote import capability, the Oracle Enterprise Manager Console must be running before starting up Data Manager. Data Manager uses the Console job system to perform remote import operations.
• If you have already connected to a database, the Oracle Data Manager main window displays.
• If a valid database connection has not been made, the Connect dialog box displays. This dialog box also appears when you launch Data Manager in standalone mode. See Connecting to an Instance on page 1-23.

Note: You can change the database connection with the Change Database Connection option in the File menu. For information, see Application Menus on page 7-10.

Data Manager Window

After Data Manager has successfully connected to a database, the Users container displays in a tree list on the left side of the Data Manager window. The Users container is located in the database that Data Manager is connected to. An individual user container can be expanded to show the tables that the user owns.

The display on the right side of the window is determined by the object selected on the left side of the screen. The right side may contain a multi-column list, property sheet, or other information.

• If the User container is selected, a multi-column list of all users displays.
• If a specific user is selected, the user property sheet displays.
• If a specific table is selected, the table property sheet displays.
• If a table partition is selected, the table property sheet displays.

For general information, see:

• Application Objects on page 7-3.
• Multi-Column Lists on page 7-14
• Dialog Boxes on page 7-15.
• Property Sheets on page 7-17.

An example of a Data Manager window is shown in Figure 14-1, "Oracle Data Manager".

Figure 14-1 Oracle Data Manager

 

Data Manager Menus

Oracle Data Manager includes the standard menus, File, View, Log, and Help, plus the Data menu. For information on the standard menus, see Application Menus on page 7-10.

The Data menu includes the Export, Import, and Load wizard options for:

• Exporting Data to a File which transfers data from an Oracle database to an Oracle binary operating system file.
• Importing Data from a File which transfers exported data in a file to an Oracle database.
• Loading Data from an External File which loads operating system files to an Oracle database.

Context-sensitive menus may also be active when you press the right mouse button to select a specific object from the navigator or the multi-column list. This feature provides quick access to a subset of the menu options provided in the menu bars.

Data Manager Icons

The objects in the tree list are identified by various icons. In the listing:

• A folder icon identifies an object type container.
• A single person icon depicts a user.
• A grid icon signifies a table.
• A broken grid signifies a table partition.

Exporting Data to a File

Data Manager Export is used to the transfer data from an Oracle database to an operating system file in Oracle-binary format. Files in this proprietary format can only be read by using the Import component of Data Manager. Export files can be used to transfer data between databases or used as backups.

To export data from a database with the Data Manager Export wizard:

• Select the objects you want to export in the tree displayed on the left side of the Data Manager window. You can also add or remove objects while using the Export wizard.
• From the Data menu, select the Export option to start the Export wizard.
• Complete the pages of the Export wizard to define how the data is exported. In the pages of the wizard, you can specify options such as:
• The associated objects you want to export
• Buffer and record length sizes
• Increment and statistics types

See the Data Manager Export online help for information on the individual pages of the Export wizard.

Remote File Option

This option specifies that the file is on the remote machine that contains the connected database. This option uses the Job Scheduling system and is only available when the Console is running.

BUFFER and RECORDLENGTH Defaults

When running the Export wizard, note that the default values for certain parameters are operating-system specific. Specifically, the BUFFER parameter defaults to a value of 4K under Windows NT and the RECORDLENGTH parameter defaults to 2K.

Importing Data from a File

Data Manager Import is used to transfer data from an export file to an Oracle database. Import can only read data that has been created with the Export component of Data Manager.

To import data to a database with the Data Manager Import wizard:

• From the Data menu, select the Import option to start the Import wizard.
• Complete the pages of the Import wizard to define how the data is imported. In the pages of the wizard, you can specify options such as:
• The associated objects you want to import

For user-level imports, expand the available user tree list of the Importable Objects and drag the object to the Selected Objects/Available Objects tree list.

• Buffer and record length sizes
• Increment type

See the Data Manager Import online help for information on the individual pages of the Import wizard.

Remote File Option

This option specifies that the file is on the remote machine that contains the connected database. This option uses the Job Scheduling system and is only available when the Console is running.

Note:

When the .DMP file resides on a remote machine, Data Manager uses the Console job and event system to retrieve the file before displaying the data in through the Import Wizard. The Remote Import page of the Import Wizard provides a status line at the top of the page indicating the progress of the data retrieval job. There are three conditions that can be displayed: Job Submitted, Job Started, and Job Completed. Important: the job must complete successfully befor proceeding with the import operation.

BUFFER and RECORDLENGTH Defaults

When running the Data Manager, note that the default values for certain parameters are operating-system specific. Specifically, the BUFFER parameter defaults to a value of 4K under Windows NT and the RECORDLENGTH parameter defaults to 2K.

Loading Data from an External File

Data Manager Load is used to transfer data to an Oracle database from files that have a different format from export files. Often these are delimited text files. Using a control file, you specify how the data is stored externally and how it is to be loaded into the database.

To load data into a database with the Data Manager Load wizard:

• From the Data menu, select the Load option to start the Load wizard.
• Complete the pages of the Load wizard to define how the data is loaded. In the pages of the wizard, you can specify options such as:
• The control, data, and log, files. Important: You must specify complete paths when entering filenames.
• The records to load, records to skip, and rows per commit

See the Data Manager Load online help for information on the individual pages of the Load wizard.

Note:

You must supply a control filename to load data. For information on control files, see the Oracle Server Utilities Guide.

Remote File Option

This option specifies that the file is on the remote machine that contains the connected database. This option uses the Job Scheduling system and is only available when the Console is running.

Data Manager Tasks

The Export, Import, and Load tasks are set up and submitted with the Oracle Enterprise Manager Job Scheduling system and executed by Oracle Enterprise Manager Intelligent Agents residing on managed hosts.

The Data Manager tasks are selected in the Task page of the Create Job property sheet. The parameters are defined in the Parameters page of the Create Job property sheet. These parameters are similar to the options available when using the Data Manager wizards. For information on the specific parameters for the Export, Import, and Load job tasks, see the online help for the Data Manager tasks.

For information on using the Job Scheduling system, see Chapter 4, "Job Scheduling". For information on specific job tasks, see the online help for Predefined Job Tasks.

---