oracle loading and unloading

1.method of loading

 

     Conventional path: employ SQL inserts on our behalf to load data.

  

     Direct path: does not use SQL in this mode; it formats database blocks directly.

 

     The direct path load allows you to read data from a flat file and write it directly to format-
ted database blocks, bypassing the entire SQL engine, undo generation and, optionally, redo
generation at the same time.

 

      conventional:insert into dept values();

      direct: insert /*+ append */ into dept values();

 

2.tools:SQL*LOADER/exp-imp

 

   SQL*LOADER:

   sqlldr userid=hr/hr control=demo.ctl

   demo.ctl

   load data

   infile *

   into table dept

   FIELDS TERMINATED BY ','

   (deptno,dname,loc)

   begindata

   10,Sales,Virginia
   20,Accounting,Virginia

 

   1)Load Delimited Data

      一.FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

      二.FIELDS TERMINATED BY WHITESPACE

      三.FIELDS TERMINATED BY x'09'
         (DEPTNO, dummy1 filler, DNAME, dummy2 filler, LOC)

      10,Sales,"Virginia,USA"
      20,Accounting,"Va, ""USA"""

      10      Sales       Virginia

   2)Load Fixed Format Data

      一.( DEPTNO      position(1:2),
            DNAME       position(3:16),
            LOC         position(17:29),
            ENTIRE_LINE position(1:29)
           )

      二.( DEPTNO      position(1:2),
          DNAME       position(*:16),
          LOC         position(*:29),
          ENTIRE_LINE position(1:29)
          )

      三.( DEPTNO      position(1) char(2),
            DNAME       position(*) char(14),
            LOC         position(*) char(13),
            ENTIRE_LINE position(1) char(29)
          )

         10Accounting    Virginia,USA

   3）Load Dates

          LAST_UPDATED date 'dd/mm/yyyy'

         10,Sales,Virginia,1/5/2000

   4）Load Data Using Functions

          TRAILING NULLCOLS

          DNAME        "upper(:dname)"

          ENTIRE_LINE  ":deptno||:dname||:loc||:last_updated"

   5）Load Data with Embedded Newlines

        一.COMMENTS     "replace(:comments,'\\n',chr(10))"

        二.INFILE demo.dat "fix 80"

        10,Sales,Virginia,This is the Sales\nOffice in Virginia
        20,Accounting,Virginia,This is the Accounting\nOffice in Virginia

        三.INFILE demo.dat "var 3"  

        05510,Sales,Virginia,This is the Sales
        Office in Virginia
        06520,Accounting,Virginia,This is the Accounting
        Office in Virginia

        四.select utl_raw.cast_to_raw('|'||chr(10)) from dual;

        UTL_RAW.CAST_TO_RAW('|'||CHR(10))
         --------------------------------------------------------------------------------
          7C0A

       INFILE demo.dat "str X'7C0A'"

       10,Sales,Virginia,This is the Sales
       Office in Virginia|
       20,Accounting,Virginia,This is the Accounting
       Office in Virginia|