Oracle 数据引擎
- ORACLE_LOADER 用户只能从外部读取表数据，但是不能将数据库中的数据卸载到操作系统文件中.
- ORACLE_DATAPUMP 用户不仅可以从外部表中读取数据，同时可以将数据库中复杂的数据查询后卸载到操作系统文件中.
External Tables
External tables are read-only tables stored as files on the operating outside of the Oracle database.
External Table Benefits
- Data can be used directly from the external file or loaded into another database.
- External data can be queried and joined directly in parallel with tables residing in the databas ,without requiring it to be loaded first.
- The results of a complex query can be unloaded to an external file.
- You can combine generated files from different sources for loading purposes.
Defining an External Tables with ORACLE_LOADER
External Table Population with ORACLE_DATAPUMP
Using External Tables
- Querying and external table:
SQL>SELECT * FROM extab_employees;
- Querying and joining an external table with an internal table
SQL>SELECT e.employee_id,e.first_name,e.last_name,d.department_name FROM departments d,extab_employees e WHERE d.department_id = e.department_id;
- Appending data to an internal table from an external table
SQL>INSERT /*+ APPEND*/ INTO hr.employees SELECT * FROM extab_employees;
Summary
In this lesson,you should have learned how to:
- Describe ways to move data
- DATA PUMP
- SQL LOADER
- EXTERNAL TABLE
- Use SQL*Loader to load data from a non-Oracle database (or user files)
- Use external tables to move data via platform-independent files
- Explain the general architecture of Oracle Data Pump
- Use Data Pump Export and Import to move data between Oracle databases.