One of the common tasks of a dba is to load data from external sources normally the data is supplied by flat files which then have to be transformed before being loaded into the database.
Most warehouse data will go through a series of steps before loading the data into the database.
- extraction - identification and extraction of raw data, possible from several sources i.e. flat files, databases
- transformation - applying application rules to data before loading, this is the most time-consuming of the three processes.
- loading - loading the transformed data into the database tables, which includes maintaining indexes and constraints.
Oracle provides a host of tools to cut time when loading data, which include
- external tables - eliminate cumbersome and intermediate staging tables during data loading
- multitable inserts - insert data into multiple tables based on certain criteria
- merge - insert and update data using the merge statement
- table functions - produce a set of rows as output
- transportable tablespaces - speedy way of moving data from one database to another
Sql*Loader
see SQL*Loader for more information
External Tables
see External Tables for more information
Datapump Utilities
see Data Pump for more information
Transportable Tablespaces
see Transportable Tables for more information