oracle_外部表底简单种实现情势oracle_loader[datapump]

 

       TYPE ORACLE_LOADER 

CREATE OR REPLACE DIRECTORY admin_bad_dir 

 13       ORGANIZATION EXTERNAL 

         fields terminated by ‘,’ 

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 JOB_ID
    M

362,Brenda,Starr,AD_ASST,200,17-MAY-2001,5500,0,10,bstarr

        363 Alex                 Alda                      AC_MGR      

     PARALLEL 

 

        361 Mark                 Jasper                    SA_REP    

 10                      department_id   ,

 

 

 

conn sun/sun

-rw-r–r– 1 ora11 oinstall 3110 Sep  6 21:19 empxt001_2765.log

                    hire_date         DATE,

 

— create the external table

—-自定义五只格式文件内容如下:

GRANT WRITE ON DIRECTORY admin_bad_dir TO sun;


         ( employee_id, first_name, last_name, job_id, manager_id, 

 17         LOCATION (’emp2.dmp’)) ;

  8                      salary            NUMBER(8,2),

     

                    manager_id        NUMBER(4),

-rw-r–r– 1 ora11 oinstall 2826 Sep  6 21:19 empxt000_2756.log

—-通过loader情势创设外部表,直接通过申明来查询操作

—-查询文件内容,大体是爆发XML格式的公文

Table created.

     ORGANIZATION EXTERNAL 

402,Abby,Applegate,IT_PROG,103,17-MAY-2001,9000,.2,60,aapplega

-rw-r–r– 1 ora11 oinstall 3110 Sep  6 21:19 empxt000_2763.log

 14       ( 

 

        402 Abby                 Applegate                 IT_PROG   

 

 15         TYPE ORACLE_DATAPUMP 

——oracle_datapump


    AS ‘/u01/backup’; 

 

—-通过loader情势创造外部表,直接通过注解来查询操作

                    commission_pct    NUMBER(2,2),

        403 Carol                Cousins                   AD_VP     

           salary, commission_pct, department_id, email 

外部表可以兑现,通过数据库对象直接看目录文件里的格式数据,加载模式分为二种植oracle_loader和oracle_datapump,oracle_loader模式通过sqlldr引擎形式加载,访问flat格式文件;oracle_datapump通过datapump接口来加载,访问通过oracle_datapump格局卸载的dmp文件;

  6                      manager_id        NUMBER(4),

SQL> CREATE TABLE admin_ext_employees

 13       ORGANIZATION EXTERNAL 

 

[ora11@prim backup]$ ls -l

  3                      first_name        VARCHAR2(20),

 

       ACCESS PARAMETERS 

                    salary            NUMBER(8,2),

         logfile admin_log_dir:’empxt%a_%p.log’ 

           hire_date char date_format date mask “dd-mon-yyyy”, 

—-更多是落实数据迁移的效应,通过datapump情势卸载并搬迁至其他系统;

—-创造datapump模式的表表,并拿数据卸载到目文件中;

no rows selected

 18        AS SELECT  * FROM emp;

CREATE OR REPLACE DIRECTORY admin_log_dir 

        361 Mark                 Jasper                    SA_REP      

361,Mark,Jasper,SA_REP,145,17-MAY-2001,8000,.1,80,mjasper

        402 Abby                 Applegate                 IT_PROG     

        403 Carol                Cousins                   AD_VP       

— Set up directories and grant access to hr 

 

    AS ‘/u01/backup’; 

 

  6                      manager_id      ,

SQL> SELECT * FROM admin_ext_employees2 ;

 17         LOCATION (’emp2.dmp’))

  9                      commission_pct  ,

Table created.

 

         missing field values are null 

 11                      email             VARCHAR2(25) 

  2                     (employee_id       NUMBER(4), 

x86_64/Linux 2.4.xx

 16         DEFAULT DIRECTORY admin_dat_dir 

                    job_id            VARCHAR2(10),

====b.dat====

     ) 

                    last_name         VARCHAR2(25), 

GRANT WRITE ON DIRECTORY admin_log_dir TO sun; 

 

 11                      email           

SQL> SELECT * FROM user_external_tables ;

CREATE TABLE admin_ext_employees

-rw-r—– 1 ora11 oinstall 12288 Sep  6 21:33 emp2.dmp

 

        360 Jane                 Janus                     ST_CLERK    

“SUN”.”U”

360,Jane,Janus,ST_CLERK,121,17-MAY-2001,3000,0,50,jjanus

     REJECT LIMIT UNLIMITED; 

                    first_name        VARCHAR2(20),

       ) 

SQL> 

-rw-r–r– 1 ora11 oinstall  252 Sep  6 21:17 b.dat

SQL> SELECT * FROM emp;

  9                      commission_pct    NUMBER(2,2),

     ( 

 

        363 Alex                 Alda                      AC_MGR    

GRANT READ ON DIRECTORY admin_dat_dir TO sun; 

create table emp as select * from admin_ext_employees;     

                   (employee_id       NUMBER(4), 

  7                      hire_date       ,

====a.dat====

 

  7                      hire_date         DATE,

  3                      first_name      ,

                    department_id     NUMBER(4),

       ( 

404,John,Richardson,AC_ACCOUNT,205,17-MAY-2001,5000,0,110,jrichard

SQL> CREATE TABLE admin_ext_employees2

         records delimited by newline 

    AS ‘/u01/backup’; 

       LOCATION (‘a.dat’, ‘b.dat’) 

        362 Brenda               Starr                     AD_ASST     

 12                     ) 

        401 Jesse                Cromwell                  HR_REP    

-rw-r–r– 1 ora11 oinstall  228 Sep  6 21:17 a.dat

 15         TYPE ORACLE_DATAPUMP 

  2                     (employee_id     ,

        362 Brenda               Starr                     AD_ASST   

——oracle_loader

SQL> 

                    email             VARCHAR2(25) 

 

 16         DEFAULT DIRECTORY admin_dat_dir 

CONNECT  /  AS SYSDBA;

  4                      last_name         VARCHAR2(25), 

        401 Jesse                Cromwell                  HR_REP      

         ) 

  4                      last_name       , 

403,Carol,Cousins,AD_VP,100,17-MAY-2001,27000,.3,90,ccousins

  8                      salary          ,

 

         badfile admin_bad_dir:’empxt%a_%p.bad’ 

—-创设需要之目

363,Alex,Alda,AC_MGR,145,17-MAY-2001,9000,.15,80,aalda

 10                      department_id     NUMBER(4),

  5                      job_id            VARCHAR2(10),

[ora11@prim backup]$ strings emp2.dmp 

CREATE OR REPLACE DIRECTORY admin_dat_dir

                   ) 

 14       ( 

 12                     ) 

  5                      job_id          ,

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 JOB_ID
   

 

       DEFAULT DIRECTORY admin_dat_dir 

total 20

401,Jesse,Cromwell,HR_REP,203,17-MAY-2001,7000,0,40,jcromwel

        404 John                 Richardson                AC_ACCOUNT  

        404 John                 Richardson                AC_ACCOUNT

        360 Jane                 Janus                     ST_CLERK  

 

 

相关文章