1)external table example01
1@@@@create a external table.
guide:
  External table are read-only. No DML operations are allowed.
  No create indexes on an external table. No backup the external using RMAN.

Create an Externall Table:
  ~The metadata,which describes how the data looks to Oracle, including the
   external table name, the column names, and Oracle data types. These are
   the names you will use in the SQL statements to access the external table.
   The metadata is stored in the Oracle data dictionary.
  ~The access parameters, which describe how the data is stored in the external
   file(i.e, where it's located, its format, and how to identify the fields and
   records). The access driver use access parameters.

@@@
@@@<1>create directory object for log and data.
@@@
SYS@ocp> !mkdir -p /home/oracle/datafiles         
SYS@ocp> !mkdir -p /home/oracle/logfiles
SYS@ocp> CREATE OR REPLACE DIRECTORY data_file_dir AS '/home/oracle/datafiles';
Directory created.
SYS@ocp> CREATE OR REPLACE DIRECTORY log_file_dir AS '/home/oracle/logfiles';
Directory created.
SYS@ocp> GRANT READ, WRITE ON DIRECTORY data_file_dir TO sh;
Grant succeeded.
SYS@ocp> GRANT READ, WRITE ON DIRECTORY log_file_dir TO sh;
Grant succeeded.


@@@
@@@<2>create sql script for creating a external table.
@@@
@@@create external table like this:
[oracle@station78 datafiles]$ cat external_table.sql
CREATE TABLE sh.external_table
(product_id VARCHAR2(8),
 product_name  VARCHAR2(30),
 category   VARCHAR2(4),
 cost_price  NUMBER(6,2),
 sell_price   NUMBER(6,2),
 weight    NUMBER(4,2),
 shipping_charge  VARCHAR2(20),
 manufacturer  VARCHAR2(10))
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
   DEFAULT DIRECTORY data_file_dir
   ACCESS PARAMETERS
     (RECORDS DELIMITED BY NEWLINE
      CHARACTERSET US7ASCII
      BADFILE log_file_dir:'external_table.bad'
      LOGFILE log_file_dir:'external_table.log'
      FIELDS TERMINATED BY ','
        OPTIONALLY ENCLOSED BY "'")
  LOCATION ('external_data.dat') 

REJECT LIMIT UNLIMITED PARALLEL;

@@@
@@@<3>create the data for external table. It is like sqlldr.
@@@
[oracle@station78 datafiles]$ cat external_data.dat
'SP1000', 'Digital Camera', 'ELEC', 45.67, 67.23, 15.00, 4.50, 'Ricoh', 'Ricoh'
'SP1001', 'APS Camera', 'ELEC', 24.67, 36.23, 5.00, 4.50, 'Ricoh', 'Ricoh'
'SP1010', 'Camera', 'ELEC', 35.67, 47.89, 5.00, 4.50, 'Agfa', 'Agfa'

@@@
@@@<4>create external table, then inspect the result.
@@@
[oracle@station78 ~]$ sqlplus sh/sh
SH@ocp> @datafiles/external_table.sql
Table created.

@@@
SH@ocp> select * from external_table;
PRODUCT_ PRODUCT_NAME       CATE COST_PRICE SELL_PRICE     WEIGHT SHIPPING_CHARGE       MANUFACTUR
-------- ------------------------- ---- ---------- ---------- ---------- --------------------
SP1000     Digital Camera     ELEC      45.67      67.23       15 4.50           Ricoh
SP1001     APS Camera        ELEC      24.67      36.23        5 4.50           Ricoh
SP1010     Camera         ELEC      35.67      47.89        5 4.50           Agfa

@@@
@@@check the metadata in data dictionary.
SYS@ocp> desc sh.external_table
 Name                       Null?    Type
 ----------------------------------------- -------- ----------------------------
 PRODUCT_ID                        VARCHAR2(8)
 PRODUCT_NAME                        VARCHAR2(30)
 CATEGORY                        VARCHAR2(4)
 COST_PRICE                        NUMBER(6,2)
 SELL_PRICE                        NUMBER(6,2)
 WEIGHT                         NUMBER(4,2)
 SHIPPING_CHARGE                    VARCHAR2(20)
 MANUFACTURER                        VARCHAR2(10)

@@@
@@@check the discription of external table.
SH@ocp> select * from user_external_tables where table_name='EXTERNAL_TABLE';

TABLE_NAME               TYP TYPE_NAME              DEF DEFAULT_DIRECTORY_NAME
------------------------------ --- ------------------------------ --- ------------------------------
REJECT_LIMIT                 ACCESS_
---------------------------------------- -------
ACCESS_PARAMETERS
----------------------------------------------------------------------------------------------------
PROPERTY
----------
EXTERNAL_TABLE               SYS ORACLE_LOADER          SYS DATA_FILE_DIR
UNLIMITED                 CLOB
RECORDS DELIMITED BY NEWLINE
      CHARACTERSET US7ASCII
      BADFILE log_file_dir:'external_table.bad'
      LOGFILE log_file_dir:'external_table.log'
      FIELDS TERMINATED BY ','
    OPTIONALLY ENCLOSED BY "'"
ALL


SH@ocp> select * from user_external_locations where table_name='EXTERNAL_TABLE';
TABLE_NAME
------------------------------
LOCATION
----------------------------------------------------------------------------------------------------
DIR DIRECTORY_NAME
--- ------------------------------
EXTERNAL_TABLE
external_data.dat
SYS DATA_FILE_DIR


@@@
@@@<4>copy the external table data to assigned table.
@@@
@@@This is the one method:
SYS@ocp> create table dw.product as select * from sh.external_table;
Table created.

@@@
@@@If you already had the table, you could do like this:
SYS@ocp> ed
  1  INSERT INTO dw.product
  2  (product_id, product_name, category,
  3   cost_price, sell_price, weight,
  4   shipping_charge, manufacturer)
  5  SELECT product_id, product_name, category,
  6        cost_price, sell_price, weight,
  7       (shipping_charge * 1.10),     @@@this method is more flexible.
  8        manufacturer
  9* FROM sh.external_table
SYS@ocp> /
3 rows created.
SYS@ocp> commit;
Commit complete.

@@@
@@@<5>check the explain plan for external table.
@@@
@@@only explain, do not execute.
SYS@ocp> !find /u01/app/oracle/product/ -name utlxplp.sql
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/utlxplp.sql

SYS@ocp> @/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/utlxplp.sql
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------
Plan hash value: 2113680126
-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name           | Rows  | Bytes | Cost (%CPU)| Time     |    TQ    |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT          |            |  8168 |   678K|     4     (0)| 00:00:01 |    |      |        |
|   1 |  PX COORDINATOR           |            |       |       |        |           |    |      |        |
|   2 |   PX SEND QC (RANDOM)          | :TQ10000       |  8168 |   678K|     4     (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR          |            |  8168 |   678K|     4     (0)| 00:00:01 |  Q1,00 | PCWC |        |
|   4 |     EXTERNAL TABLE ACCESS FULL| EXTERNAL_TABLE |  8168 |   678K|     4     (0)| 00:00:01 |  Q1,00 | PCWP |        |
-----------------------------------------------------------------------------------------------------------------------------


@@@summary: we could see PX, it is parallel