Chapter 12 loading and transporting data
This presentation is the property of its rightful owner.
Sponsored Links
1 / 24

Chapter 12 Loading and Transporting Data PowerPoint PPT Presentation


  • 61 Views
  • Uploaded on
  • Presentation posted in: General

Chapter 12 Loading and Transporting Data. Chapter Objectives. Identify the purpose of the Export, Import, and SQL*Loader utilities Perform an interactive export of a table Perform a scripted export of a table. Chapter Objectives (Cont.). Perform an interactive import of an Oracle9 i table

Download Presentation

Chapter 12 Loading and Transporting Data

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Chapter 12 loading and transporting data

Chapter 12Loading and Transporting Data

Oracle9i DBA II: Backup/Recovery and Network Administration


Chapter objectives

Chapter Objectives

  • Identify the purpose of the Export, Import, and SQL*Loader utilities

  • Perform an interactive export of a table

  • Perform a scripted export of a table

Oracle9i DBA II: Backup/Recovery and Network Administration


Chapter objectives cont

Chapter Objectives (Cont.)

  • Perform an interactive import of an Oracle9i table

  • Perform a scripted import of an Oracle9i table

  • Load external data into an Oracle9i database

Oracle9i DBA II: Backup/Recovery and Network Administration


Export utility

Export Utility

  • Use to:

    • Create logical backup

      Copy contents and structure of database, schema, or tablespace

    • Reorganize database

      Reduces fragmentation

  • Creates a binary dump file for importing

  • Compresses extents by default

Oracle9i DBA II: Backup/Recovery and Network Administration


Export types

Export Types

  • Conventional-path

    • Verifies DDL statements

    • Default

  • Direct-path

    • Places retrieved rows in dump files, followed by DDL statements

    • Specified with DIRECT=Y argument

    • Saves time for large amounts of data

Oracle9i DBA II: Backup/Recovery and Network Administration


Export utility help

Export Utility Help

Oracle9i DBA II: Backup/Recovery and Network Administration


Export utility interactive

Export Utility Interactive

Oracle9i DBA II: Backup/Recovery and Network Administration


Parameter file for scripted export

Parameter File for Scripted Export

Oracle9i DBA II: Backup/Recovery and Network Administration


Executing scripted export

Executing Scripted Export

Oracle9i DBA II: Backup/Recovery and Network Administration


Import utility

Import Utility

  • Reads (and executes) DDL statements in exported binary dump file

  • Recreates tables if necessary

  • Can be interactive or scripted

  • Access by typing impoperating system prompt

Oracle9i DBA II: Backup/Recovery and Network Administration


Interactive import

Interactive Import

Oracle9i DBA II: Backup/Recovery and Network Administration


Parameter file for scripted import

Parameter File for Scripted Import

Oracle9i DBA II: Backup/Recovery and Network Administration


Scripted import

Scripted Import

Oracle9i DBA II: Backup/Recovery and Network Administration


Backup and recovery of the recovery catalog

Backup and Recovery of the Recovery Catalog

  • Export (or import) entire RMAN schema

  • Logical backup supplements physical backup strategy

  • Example RMAN schema export command:

    exp [email protected]_name

    file=dumpfilename.dmp owner=rman

Oracle9i DBA II: Backup/Recovery and Network Administration


Sql loader utility

SQL*Loader Utility

  • Imports data generated by a different type of database (non-Oracle)

  • Uses three types of files:

    • Control file

    • Input file

    • Log files (bad, discard, general)

Oracle9i DBA II: Backup/Recovery and Network Administration


Control file

Control File

Specifies data to be loaded, format, and procedure

Oracle9i DBA II: Backup/Recovery and Network Administration


Input file example

Input File Example

Oracle9i DBA II: Backup/Recovery and Network Administration


General log file example

General Log File Example

Oracle9i DBA II: Backup/Recovery and Network Administration


Loading methods

Loading Methods

  • Conventional-path

    Uses INSERT statements, fires triggers

  • Direct-path

    Bypasses the buffer cache, writes directly to data file

  • Parallel direct-path

    Uses multiple load sessions; requires more space

Oracle9i DBA II: Backup/Recovery and Network Administration


Sql loader utility1

SQL*Loader Utility

Oracle9i DBA II: Backup/Recovery and Network Administration


Chapter summary

Chapter Summary

  • The Export utility is used to export data from an existing Oracle9i database table

  • The entire database, a user’s schema, or simple a database table can be exported using the Export utility.

  • Exported data is written to a binary file, known as a dump file.

Oracle9i DBA II: Backup/Recovery and Network Administration


Chapter summary cont

Chapter Summary (Cont.)

  • The Export utility can be operated in an interactive or scripted mode.

  • If a value is not specified, the Export utility assumes the default value for the parameter.

  • The Import utility is used to import data that was previously exported to a dump file.

Oracle9i DBA II: Backup/Recovery and Network Administration


Chapter summary cont1

Chapter Summary (Cont.)

  • The Export and Import utilities can be used along with a physical backup strategy to create a backup of the Recovery Catalog.

  • The SQL*Loader utility is used to load data from text files.

  • SQL*Loader utilizes three types of files: a control file, log files, and input file.

Oracle9i DBA II: Backup/Recovery and Network Administration


Chapter summary cont2

Chapter Summary (Cont.)

  • The control file specifies the loading parameters and the structure of the data.

  • The log files are used to provide information about the loading process and identify any bad or discarded records.

  • The input file contains records to be loaded into the database; otherwise the data must be contained in the control file.

Oracle9i DBA II: Backup/Recovery and Network Administration


  • Login