1 / 11

Loading the Oracle Warehouse

Loading the Oracle Warehouse. Data Warehousing Lab. 박유림. SQL *Loader. SQL *Loader Move data from operating system files into oracle database tables Features of SQL*Loader Load data from disk or tape Support a wide range of data type including date and binary data

jelani-sharp
Download Presentation

Loading the Oracle Warehouse

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Loading the Oracle Warehouse Data Warehousing Lab. 박유림

  2. SQL *Loader • SQL *Loader • Move data from operating system files into oracle database tables • Features of SQL*Loader • Load data from disk or tape • Support a wide range of data type including date and binary data • Selectively load data into one or more tables based upon one or more filtering criteria • Load fixed or variable length records • Generate unique keys as Oracle tables are populated • Support a number of high performance mechanisms crucial to large data load • Produce sophisticated error reports to assist further any processing required of bad or incomplete data • Preprocess data before it is moved into Oracle

  3. Inputs to and Ouputs from SQL*loader • Features of SQL*Loader Log file : accload.log, this file contains a wealth of information about the load. Bad file : accload.bad, it is unable to insert them into a table Discard file : ~.dsc, if one or more when keyword are used and a record fails all the tests, it is written to the discard file Control file : accload.ctl

  4. Control file • Oracle9i 데이터베이스가 생성될 때 작성된다. 인스턴스당 하나씩만 필요하지만 항상 최소한 두개의 제어 파일을 작성하는 것이 바람직하다. • 데이터베이스의 구성 정보를 저장 하는 바이너리 파일. • 다음과 같은 정보가 저장 된다. • 데이터베이스명 • 데이터파일과 REDO 로그 파일의 전체 경로 및 이름 • 데이터베이스가 생성된 시간과 날짜 • 현재 로그 시퀀스 번호 ( Oracle9i 는 새로운 로그 파일을 시작할 때 이 시퀀스 번호를 할당하며 온라인 리두 로그를 순환 방식으로 이용한다. • 체크포인트 정보 • 제어파일이 없어지면 이런 정보들이 날라가기 때문에 데이터베이스를 기동할 수 없어 복구하기 곤란해진다. • 제어파일은 초기화 파라미터 파일 안에 CONTROL_FILES를 기초로 작성된다. 복수로 지정하여 하나가 파손 되어도 영향이 없도록 한다

  5. Control file • The control file usually begins with the text • Although the keyword • Input file is specified next starting with the keyword • Assume the input datafile Accounts.dat • Processing options to override the bad and discard file defaults Load data options infile Infile = ‘ACCOUNTS’ Load data Infile ‘mytown.dat’ discardfile mtdisc.rec Badfile mtbad.rec

  6. Fixed Length records 1. Load data Infile ‘account.dat’ Into table account append (account_nbr position (1 :10) char, Account_type position (11:12) char, Owner position(13: 42) char, Last_activity position(43 : 48) date‘RRMMDD’, Status position(49:50) char) Specification value Input file account.dat Control file account.ctl Bad file account.bad Log file account.out Table to load account Table status before load preserve existing data 18-JAN-03 030118

  7. Variable length record 123, “OK”,”Michael abbey System International Inc. “,1826,9383 “R”,908 456,”GT”,”Data Visions Inc”,342,2211,”Q”,82 789,”OK”, “The Beatles”, 77,2128,”R”,112 Look at the control file to accomplish this load Comma-delimited Load data Infile ‘customer.dat’ Into table customer append (crust_id char terminated by ‘,’, Status char enclosed by ‘”’, Cname char enclosed by ‘”’, Acc_num char terminated by ‘,’, Tax_class char terminated by ‘,’, Dsc_class char enclosed by ‘”’ , Source char terminated by whitespace) • Note • Some software permits using the single and double quotes interchangeably, but Oracle does not. • Comma-separated field definitions with the whole list bound in parentheses • The trailing information item is not terminated by the comma; hence, the keyword whitespace is used in its place

  8. Fixed & variable length record

  9. Export • Export and Import Utility 2. Export 사용법 (exp scott/tiger file=/backup/orabackup.dmp owner=scott)$ exp scott/tiger tables=(dept,emp) \> file=emp.dmp log=exp.log compress=n \> direct=y recordlength=32768[syntax]$ exp [keyword=]{value | (value, value ...)}[ [ [,] keyword=]{value | (value, value ...)} ] ...

  10. Export Parameter Description USERID Username/password of schema objects to export FILE Name of output file ROWS Include table rows in export file: (Y)ex/(N)o FULL Export entire database : (Y)es/(N)o OWNER Users to export : Username TABLES Tables to export : List of tables INDEXES Indexes to export : (Y)es/(N)o DIRECT Specify direct mode export : (Y)es/(N)o -- 좀더 빨리처리한다. INCTYPE Type of export level PARFILE Name of file in which parameters are specified. HELP Display export parameters in interactive mode (Y) LOG Name of file for informational and error messages

  11. Export

More Related