1 / 24

Chapter 12 Loading and Transporting Data

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

emmanuel-hansen
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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 12Loading and Transporting Data Oracle9i DBA II: Backup/Recovery and Network Administration

  2. 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

  3. 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

  4. 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

  5. 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

  6. Export Utility Help Oracle9i DBA II: Backup/Recovery and Network Administration

  7. Export Utility Interactive Oracle9i DBA II: Backup/Recovery and Network Administration

  8. Parameter File for Scripted Export Oracle9i DBA II: Backup/Recovery and Network Administration

  9. Executing Scripted Export Oracle9i DBA II: Backup/Recovery and Network Administration

  10. 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

  11. Interactive Import Oracle9i DBA II: Backup/Recovery and Network Administration

  12. Parameter File for Scripted Import Oracle9i DBA II: Backup/Recovery and Network Administration

  13. Scripted Import Oracle9i DBA II: Backup/Recovery and Network Administration

  14. 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 rman/rman@database_name file=dumpfilename.dmp owner=rman Oracle9i DBA II: Backup/Recovery and Network Administration

  15. 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

  16. Control File Specifies data to be loaded, format, and procedure Oracle9i DBA II: Backup/Recovery and Network Administration

  17. Input File Example Oracle9i DBA II: Backup/Recovery and Network Administration

  18. General Log File Example Oracle9i DBA II: Backup/Recovery and Network Administration

  19. 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

  20. SQL*Loader Utility Oracle9i DBA II: Backup/Recovery and Network Administration

  21. 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

  22. 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

  23. 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

  24. 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

More Related