外部表

一，How Are External Tables Created?
二，举几个栗子
一，使用sql*loader生成创建外部表语句
二.ORACLE_LOADER驱动方式
三，使用 ORACLE_DATAPUMP 卸载/装载数据

一，How Are External Tables Created?

External tables are created using the SQL CREATE TABLE...ORGANIZATION EXTERNAL statement. When you create an external table, you specify the following attributes:

  • TYPE - specifies the type of external table. The two available types are the ORACLE_LOADER type and the ORACLE_DATAPUMP type. Each type of external table is supported by its own access driver.

    • The ORACLE_LOADER access driver is the default. It loads data from external tables to internal tables. The data must come from text data files. (The ORACLE_LOADER access driver cannot perform unloads; that is, it cannot move data from an internal table to an external table.)

    • The ORACLE_DATAPUMP access driver can perform both loads and unloads. The data must come from binary dump files. Loads to internal tables from external tables are done by fetching from the binary dump files. Unloads from internal tables to external tables are done by populating the binary dump files of the external table. The ORACLE_DATAPUMP access driver can write dump files only as part of creating an external table with the SQL CREATE TABLE AS SELECT statement. Once the dump file is created, it can be read any number of times, but it cannot be modified (that is, no DML operations can be performed).

  • DEFAULT DIRECTORY - specifies the default directory to use for all input and output files that do not explicitly name a directory object. The location is specified with a directory object, not a directory path. You must create the directory object before you create the external table; otherwise, an error is generated. See “Location of Data Files and Output Files” for more information.

  • ACCESS PARAMETERS - describe the external data source and implements the type of external table that was specified. Each type of external table has its own access driver that provides access parameters unique to that type of external table. Access parameters are optional. See “Access Parameters”.

  • LOCATION - specifies the data files for the external table. The files are named in the form directory:file. The directory portion is optional. If it is missing, then the default directory is used as the directory for the file.

The following example shows the use of each of these attributes (it assumes that the default directory def_dir1 already exists):

  1. SQL> CREATE TABLE emp_load
  2.   2    (employee_number      CHAR(5),
  3.   3     employee_dob         CHAR(20),
  4.   4     employee_last_name   CHAR(20),
  5.   5     employee_first_name  CHAR(15),
  6.   6     employee_middle_name CHAR(15),
  7.   7     employee_hire_date   DATE)
  8.   8  ORGANIZATION EXTERNAL
  9.   9    (TYPE ORACLE_LOADER
  10.  10     DEFAULT DIRECTORY def_dir1
  11.  11     ACCESS PARAMETERS
  12.  12       (RECORDS DELIMITED BY NEWLINE
  13.  13        FIELDS (employee_number      CHAR(2),
  14.  14                employee_dob         CHAR(20),
  15.  15                employee_last_name   CHAR(18),
  16.  16                employee_first_name  CHAR(11),
  17.  17                employee_middle_name CHAR(11),
  18.  18                employee_hire_date   CHAR(10) date_format DATE mask "mm/dd/yyyy"
  19.  19               )
  20.  20       )
  21.  21     LOCATION ('info.dat')
  22.  22    );
  24. Table created.

The information you provide through the access driver ensures that data from the data source is processed so that it matches the definition of the external table. The fields listed after CREATE TABLE emp_load are actually defining the metadata for the data in the info.dat source file.

二，举几个栗子

一，使用sql*loader生成创建外部表语句

1.数据源

t.dat

  1. P,James,31,
  2. P,Thomas,22,
  3. E,Pat,38,93645,1122,Engineering,
  4. P,Bill,19,
  5. P,Scott,55,
  6. S,Judy,45,27316,English,
  7. S,Karen,34,80356,History,
  8. E,Karen,61,90056,1323,"Manufa,cturing",
  9. S,Pat,29,98625,Spanish,
  10. S,Cody,22,99743,Math,
  11. P,Ted,43,
  12. E,Judy,44,87616,1544,Accounting,
  13. E,Bob,50,63421,1314,Shipping,
  14. S,Bob,32,67420,Psychology,
  15. E,Cody,33,25143,1002,"Hum,an Resources",

**
2.创建控制文件**

t.ctl

  1. load data
  2. infile '/home/oracle/t.dat'
  3. badfile '/home/oracle/t.bad'
  4. discardfile '/home/oracle/t.dec'
  5. append
  6. into table t
  7. fields terminated by ',' optionally enclosed by '"'
  8. trailing nullcols
  9. (
  10. x1,
  11. x2,
  12. x3,
  13. x4,
  14. x5,
  15. x6
  16. )

3.创建表T

  1. create table t(
  2.   x1 varchar2(20),
  3.   x2 varchar2(20),
  4.   x3 varchar2(20),
  5.   x4 varchar2(20),
  6.   x5 varchar2(20),
  7.   x6 varchar2(20)
  8.   );
  1. sqlldr test/test control=/tmp/t.ctl log=/home/oracle/t.log external_table=GENERATE_ONLY

4.修改生成的日志文件，并执行

删除刚才建的表T

  1. CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/tmp/';
  3. CREATE TABLE t     ---不加引号，如果加引号，查询的时候表名也必须加引号
  4. (
  5.   "X1" VARCHAR2(20),
  6.   "X2" VARCHAR2(20),
  7.   "X3" VARCHAR2(20),
  8.   "X4" VARCHAR2(20),
  9.   "X5" VARCHAR2(20),
  10.   "X6" VARCHAR2(20)
  11. )
  12. ORGANIZATION external 
  13. (
  14.   TYPE oracle_loader
  15.   DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
  16.   ACCESS PARAMETERS 
  17.   (
  18.     RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
  19.     BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'t.bad'
  20.     DISCARDFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'t.dec'
  21.     READSIZE 1048576
  22.     FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM 
  23.     MISSING FIELD VALUES ARE NULL 
  24.     REJECT ROWS WITH ALL NULL FIELDS 
  25.     (
  26.       "X1" CHAR(255)
  27.         TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
  28.       "X2" CHAR(255)
  29.         TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
  30.       "X3" CHAR(255)
  31.         TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
  32.       "X4" CHAR(255)
  33.         TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
  34.       "X5" CHAR(255)
  35.         TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
  36.       "X6" CHAR(255)
  37.         TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
  38.     )
  39.   )
  40.   location 
  41.   (
  42.     't.dat'
  43.   )
  44. )REJECT LIMIT UNLIMITED;
  1. SQL> set line 150 pages 999
  2. SQL> select * from t;
  4. X1                   X2                   X3                   X4                   X5                   X6
  5. -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
  6. P                    James                31
  7. P                    Thomas               22
  8. E                    Pat                  38                   93645                1122                 Engineering
  9. P                    Bill                 19
  10. P                    Scott                55
  11. S                    Judy                 45                   27316                English
  12. S                    Karen                34                   80356                History
  13. E                    Karen                61                   90056                1323                 Manufa,cturing
  14. S                    Pat                  29                   98625                Spanish
  15. S                    Cody                 22                   99743                Math
  16. P                    Ted                  43
  17. E                    Judy                 44                   87616                1544                 Accounting
  18. E                    Bob                  50                   63421                1314                 Shipping
  19. S                    Bob                  32                   67420                Psychology
  20. E                    Cody                 33                   25143                1002                 Hum,an Resources
  22. 15 rows selected.

二.ORACLE_LOADER驱动方式

通过sqlldr引擎方式加载，访问平面文件

  1. Assume your .dat file looks as follows:

    56november, 15, 1980  baker             mary       alice     09/01/2004
87december, 20, 1970  roper             lisa       marie     01/01/2002

  2. Execute the following SQL statements to set up a default directory (which contains the data source) and to grant access to it:

    CREATE DIRECTORY def_dir1 AS '/usr/apps/datafiles';
GRANT READ ON DIRECTORY ext_tab_dir TO SCOTT;

  3. Create a traditional table named emp:

    CREATE TABLE emp (emp_no CHAR(6), last_name CHAR(25), first_name CHAR(20), middle_initial CHAR(1), hire_date DATE, dob DATE);

  4. Create an external table named emp_load:

    SQL> CREATE TABLE emp_load
  2    (employee_number      CHAR(5),
  3     employee_dob         CHAR(20),
  4     employee_last_name   CHAR(20),
  5     employee_first_name  CHAR(15),
  6     employee_middle_name CHAR(15),
  7     employee_hire_date   DATE)
  8  ORGANIZATION EXTERNAL
  9    (TYPE ORACLE_LOADER
 10     DEFAULT DIRECTORY def_dir1
 11     ACCESS PARAMETERS
 12       (RECORDS DELIMITED BY NEWLINE
 13        FIELDS (employee_number      CHAR(2),
 14                employee_dob         CHAR(20),
 15                employee_last_name   CHAR(18),
 16                employee_first_name  CHAR(11),
 17                employee_middle_name CHAR(11),
 18                employee_hire_date   CHAR(10) date_format DATE mask "mm/dd/yyyy"
 19               )
 20       )
 21     LOCATION ('info.dat')
 22    );

Table created.

  5. Load the data from the external table emp_load into the table emp:

    SQL> INSERT INTO emp (emp_no,
  2                   first_name,
  3                   middle_initial,
  4                   last_name,
  5                   hire_date,
  6                   dob)
  7  (SELECT employee_number,
  8          employee_first_name,
  9          substr(employee_middle_name, 1, 1),
 10          employee_last_name,
 11          employee_hire_date,
 12          to_date(employee_dob,'month, dd, yyyy')
 13  FROM emp_load);

2 rows created.

  6. Perform the following select operation to verify that the information in the .dat file was loaded into the emp table:

    SQL> SELECT * FROM emp;

EMP_NO LAST_NAME                 FIRST_NAME           M HIRE_DATE DOB

    

56     baker                     mary                 a 01-SEP-04 15-NOV-80
87     roper                     lisa                 m 01-JAN-02 20-DEC-70

2 rows selected.

Notes about this example:

  • The employee_number field in the data file is converted to a character string for the employee_number field in the external table.

  • The data file contains an employee_dob field that is not loaded into any field in the table.

  • The substr function is used on the employee_middle_name column in the external table to generate the value for middle_initial in table emp.

  • The character string for employee_hire_date in info.dat is automatically converted into a DATE datatype at external table access time, using the format mask specified in the external table definiition.

  • Unlike employee_hire_date, the DATE datatype conversion for employee_dob is done at SELECT time and is not part of the external table definition.

三，使用 ORACLE_DATAPUMP 卸载/装载数据

The following steps use the sample schema, oe, to show an extended example of how you can use the ORACLE_DATAPUMP access driver to unload and load data. (The example assumes that the directory object def_dir1 already exists, and that user oe has read and write access to it.)

  1. An external table will populate a file with data only as part of creating the external table with the AS SELECT clause. The following example creates an external table named inventories_xt and populates the dump file for the external table with the data from table inventories in the oe schema.

    SQL> CREATE TABLE inventories_xt
  2  ORGANIZATION EXTERNAL
  3  (
  4    TYPE ORACLE_DATAPUMP
  5    DEFAULT DIRECTORY def_dir1
  6    LOCATION ('inv_xt.dmp')
  7  )
  8  AS SELECT * FROM inventories;

Table created.

  2. Describe both inventories and the new external table, as follows. They should both match.

    SQL> DESCRIBE inventories
 Name                                      Null?    Type

    

 PRODUCT_ID                                NOT NULL NUMBER(6)
 WAREHOUSE_ID                              NOT NULL NUMBER(3)
 QUANTITY_ON_HAND                          NOT NULL NUMBER(8)

SQL> DESCRIBE inventories_xt
 Name                                      Null?    Type

    

 PRODUCT_ID                                NOT NULL NUMBER(6)
 WAREHOUSE_ID                              NOT NULL NUMBER(3)
 QUANTITY_ON_HAND                          NOT NULL NUMBER(8)

  3. Now that the external table is created, it can be queried just like any other table. For example, select the count of records in the external table, as follows:

    SQL> SELECT COUNT(*) FROM inventories_xt;

COUNT(*)

    

1112

  4. Compare the data in the external table against the data in inventories. There should be no differences.

    SQL> SELECT * FROM inventories MINUS SELECT * FROM inventories_xt;

no rows selected

  5. After an external table has been created and the dump file populated by the CREATE TABLE AS SELECT statement, no rows may be added, updated, or deleted from the external table. Any attempt to modify the data in the external table will fail with an error.

    The following example shows an attempt to use data manipulation language (DML) on an existing external table. This will return an error, as shown.

    SQL> DELETE FROM inventories_xt WHERE warehouse_id = 5;
DELETE FROM inventories_xt WHERE warehouse_id = 5
            *
ERROR at line 1:
ORA-30657: operation not supported on external organized table

  6. The dump file created for the external table can now be moved and used as the dump file for another external table in the same database or different database. Note that when you create an external table that uses an existing file, there is no AS SELECT clause for the CREATE TABLE statement.

    SQL> CREATE TABLE inventories_xt2
  2  (
  3    product_id          NUMBER(6),
  4    warehouse_id        NUMBER(3),
  5    quantity_on_hand    NUMBER(8)
  6  )
  7  ORGANIZATION EXTERNAL
  8  (
  9    TYPE ORACLE_DATAPUMP
 10    DEFAULT DIRECTORY def_dir1
 11    LOCATION ('inv_xt.dmp')
 12  );

Table created.

  7. Compare the data for the new external table against the data in the inventories table. The product_id field will be converted to a compatible datatype before the comparison is done. There should be no differences.

    SQL> SELECT * FROM inventories MINUS SELECT * FROM inventories_xt2;

no rows selected

  8. Create an external table with three dump files and with a degree of parallelism of three.

    SQL> CREATE TABLE inventories_xt3
  2  ORGANIZATION EXTERNAL
  3  (
  4    TYPE ORACLE_DATAPUMP
  5    DEFAULT DIRECTORY def_dir1
  6    LOCATION ('inv_xt1.dmp', 'inv_xt2.dmp', 'inv_xt3.dmp')
  7  )
  8  PARALLEL 3
  9  AS SELECT * FROM inventories;

Table created.

  9. Compare the data unload against inventories. There should be no differences.

    SQL> SELECT * FROM inventories MINUS SELECT * FROM inventories_xt3;

no rows selected

  10. Create an external table containing some rows from table inventories.

    SQL> CREATE TABLE inv_part_xt
  2  ORGANIZATION EXTERNAL
  3  (
  4  TYPE ORACLE_DATAPUMP
  5  DEFAULT DIRECTORY def_dir1
  6  LOCATION ('inv_p1_xt.dmp')
  7  )
  8  AS SELECT * FROM inventories WHERE warehouse_id < 5;

Table created.

  11. Create another external table containing the rest of the rows from inventories.

    SQL> drop table inv_part_xt;

Table dropped.

SQL> 
SQL> CREATE TABLE inv_part_xt
  2  ORGANIZATION EXTERNAL
  3  (
  4  TYPE ORACLE_DATAPUMP
  5  DEFAULT DIRECTORY def_dir1
  6  LOCATION ('inv_p2_xt.dmp')
  7  )
  8  AS SELECT * FROM inventories WHERE warehouse_id >= 5;

Table created.

  12. Create an external table that uses the two dump files created in Steps 10 and 11.

    SQL> CREATE TABLE inv_part_all_xt
  2  (
  3  product_id NUMBER(6),
  4  warehouse_id NUMBER(3),
  5  quantity_on_hand NUMBER(8)
  6  )
  7  ORGANIZATION EXTERNAL
  8  (
  9  TYPE ORACLE_DATAPUMP
 10  DEFAULT DIRECTORY def_dir1
 11  LOCATION ('inv_p1_xt.dmp','inv_p2_xt.dmp')
 12  );

Table created.

  13. Compare the new external table to the inventories table. There should be no differences. This is because the two dump files used to create the external table have the same metadata (for example, the same table name inv_part_xt and the same column information).

    SQL> SELECT * FROM inventories MINUS SELECT * FROM inv_part_all_xt;

no rows selected




posted @ 2017-04-07 11:25  hao_xiaoyu  阅读(488)  评论(0)    收藏  举报