2013年4月27日 星期六
10:53
|
1. sql loader ：将外部数据（比如文本型）数据导入oracle database。（用于数据导入、不同类型数据库数据迁移） |
|
2、sqlloader 导入数据原理：在段（segment 表）insert 记录 |
|
1）normal ：将记录插入到segment的HWM(高水位线）以下的块，要首先访问bitmap ，来确定那些block 有free space 2）direct： 将记录插入到segment的HWM(高水位线）以上的从未使用过的块，加快插入速度 |
|
3、sql loader 帮助 |
|
C:Documents and Settingsliqx>sqlldr 【要装有oracle才行】
SQL*Loader: Release 10.2.0.1.0 - Production on 星期四 8月 11 13:57:10 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
用法: SQLLDR keyword=value [,keyword=value,...]
有效的关键字: userid -- ORACLE 用户名/口令 control -- 控制文件名 log -- 日志文件名 bad -- 错误文件名 data -- 数据文件名 discard -- 废弃文件名 discardmax -- 允许废弃的文件的数目 (全部默认) skip -- 要跳过的逻辑记录的数目 (默认 0) load -- 要加载的逻辑记录的数目 (全部默认) errors -- 允许的错误的数目 (默认 50) rows -- 常规路径绑定数组中或直接路径保存数据间的行数(默认: 常规路径 64, 所有直接路径) bindsize -- 常规路径绑定数组的大小 (以字节计) (默认 256000) silent -- 运行过程中隐藏消息 (标题,反馈,错误,废弃,分区) direct -- 使用直接路径 (默认 FALSE) parfile -- 参数文件: 包含参数说明的文件的名称 parallel -- 执行并行加载 默认 FALSE) file -- 要从以下对象中分配区的文件 skip_unusable_indexes -- 不允许/允许使用无用的索引或索引分区 (默认 FALSE) skip_index_maintenance -- 没有维护索引, 将受到影响的索引标记为无用 (默认 FALSE) commit_discontinued -- 提交加载中断时已加载的行 (默认 FALSE) readsize -- 读取缓冲区的大小 (默认 1048576) external_table -- 使用外部表进行加载; NOT_USED, GENERATE_ONLY, EXECUTE (默认 NOT_USED) columnarrayrows -- 直接路径列数组的行数 (默认 5000) streamsize -- 直接路径流缓冲区的大小 (以字节计) (默认 256000) multithreading -- 在直接路径中使用多线程 resumable -- 启用或禁用当前的可恢复会话 (默认 FALSE) resumable_name -- 有助于标识可恢复语句的文本字符串 resumable_timeout -- RESUMABLE 的等待时间 (以秒计) (默认 7200) date_cache -- 日期转换高速缓存的大小 (以条目计) (默认 1000)
PLEASE NOTE: 命令行参数可以由位置或关键字指定。 前者的例子是 'sqlloadscott/tiger foo'; 后一种情况的一个示例是 'sqlldr control=foo userid=scott/tiger'.位置指定参数的时间必须早于但不可迟于由关键字指定的参数。 例如,允许 'sqlldr scott/tiger control=foo logfile=log', 但是不允许 'sqlldr scott/tiger control=foo log', 即使参数 'log' 的位置正确。 |
|
4、sqlldr 应用 |
|
1）模拟生成数据源 ——建立脚本emp.sql【这里很多格式都会变，执行完以后记得执行另一个脚本恢复原样on.sql】 [oracle@work sqlldr]$ more emp.sql set heading off set feedback off set time off set linesize 120 set pagesize 0 set echo off set trimspool off spool /export/home/oracle/sqlldr/emp.dat select empno||','||ename||','||job||','||mgr||','||hiredate||','||sal||','||comm||','||deptno from scott.emp; spool off; [oracle@solaris10 sqlplus]$ vi on.sql
set heading on set feedback on set time on set linesize 250 set pagesize 24 set echo on set trimspool on
SQL> @/export/home/oracle/sqlldr/emp.sql
7369,SMITH,CLERK,7902,1980-12-17 00:00:00,800,,20 7499,ALLEN,SALESMAN,7698,1981-02-20 00:00:00,1600,300,30 7521,WARD,SALESMAN,7698,1981-02-22 00:00:00,1250,500,30 7566,JONES,MANAGER,7839,1981-04-02 00:00:00,2975,,20 7654,MARTIN,SALESMAN,7698,1981-09-28 00:00:00,1250,1400,30 7698,BLAKE,MANAGER,7839,1981-05-01 00:00:00,2850,,30 7782,CLARK,MANAGER,7839,1981-06-09 00:00:00,2450,,10 7788,SCOTT,ANALYST,7566,1987-04-19 00:00:00,3000,,20 7839,KING,PRESIDENT,,1981-11-17 00:00:00,5000,,10 7844,TURNER,SALESMAN,7698,1981-09-08 00:00:00,1500,0,30 7876,ADAMS,CLERK,7788,1987-05-23 00:00:00,1100,,20 7900,JAMES,CLERK,7698,1981-12-03 00:00:00,950,,30 7902,FORD,ANALYST,7566,1981-12-03 00:00:00,3000,,20 7934,MILLER,CLERK,7782,1982-01-23 00:00:00,1300,,10 ——查看数据源 [oracle@work sqlldr]$ more emp.dat
7369,SMITH,CLERK,7902,1980-12-17 00:00:00,800,,20 7499,ALLEN,SALESMAN,7698,1981-02-20 00:00:00,1600,300,30 7521,WARD,SALESMAN,7698,1981-02-22 00:00:00,1250,500,30 7566,JONES,MANAGER,7839,1981-04-02 00:00:00,2975,,20 7654,MARTIN,SALESMAN,7698,1981-09-28 00:00:00,1250,1400,30 7698,BLAKE,MANAGER,7839,1981-05-01 00:00:00,2850,,30 7782,CLARK,MANAGER,7839,1981-06-09 00:00:00,2450,,10 7788,SCOTT,ANALYST,7566,1987-04-19 00:00:00,3000,,20 7839,KING,PRESIDENT,,1981-11-17 00:00:00,5000,,10 7844,TURNER,SALESMAN,7698,1981-09-08 00:00:00,1500,0,30 7876,ADAMS,CLERK,7788,1987-05-23 00:00:00,1100,,20 7900,JAMES,CLERK,7698,1981-12-03 00:00:00,950,,30 7902,FORD,ANALYST,7566,1981-12-03 00:00:00,3000,,20 7934,MILLER,CLERK,7782,1982-01-23 00:00:00,1300,,10 2)导入数据到表emp1 SYS @ prod > create table emp1 as select * from emp where 1=2;——【创建一个只有表结构的空表】 Table created. SYS @ prod > exec dbms_stats.gather_table_stats(user,’emp2’)
SYS @ prod > analyze table emp1 compute statistics;——先分析表，以观察普通模式导入和直接导入的区别 Table analyzed.
SYS @ prod > col table_name for a15 SYS @ prod > select table_name,num_rows,blocks,empty_blocks from user_tables where table_name='EMP1';
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS --------------- ---------- ---------- ------------ EMP1 0 0 128 3）普通模式导入 SCOTT @ prod > insert into /*+ append */ into emp1 select *from emp where rownum <3;
【/*+ append */ 添加提示 优先按照提示进行查询 append 追加数据 使用高水位以上的块 insert 插入数据 调优时常用使用高水位以下的块】
[oracle@work sqlldr]$ sqlldr ——建立控制文件
（控制文件有两种方式，一种是指明数据存放的路径，一种是将数据文件和控制文件放到一起，数据量大时采用前者）
[oracle@work sqlldr]$ vi emp.ctl
load data infile '/home/oracle/data /emp.dat' insert ——insert 插入表必须是空表，非空表用append into table emp1 fields terminated by ',' 指定字段以 , 分隔 terminated 终止的意思 optionally enclosed by '"' 可选 可不选的 外面是单引号 里边是双引号 不要晕了 ( empno, 注意字段和 原表字段 一一对应 ename, job, mgr, hiredate, comm, sal, deptno)
——执行导入（normal） [oracle@solaris10 sqlldr]$ sqlldr scott/tiger control=emp.ctl log=emp.log ——执行导入的语句，指明用户/口令、控制文件的位置和导入生成日志的位置
SQL*Loader: Release 10.2.0.2.0 - Production on Fri Mar 16 14:30:47 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Commit point reached - logical record count 14
——查看日志 [oracle@work sqlldr]$ more emp.log
[oracle@solaris10 sqlldr]$sqlldr scott/tiger control=emp.ctl log=emp.log SQL*Loader: Release 10.2.0.2.0 - Production on Fri Mar 16 14:30:47 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Commit point reached - logical record count 14 [oracle@solaris10 sqlldr]$ more emp.log
SQL*Loader: Release 10.2.0.2.0 - Production on Fri Mar 16 14:30:47 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Control File: emp.ctl Data File: /export/home/oracle/sqlldr/emp.dat Bad File: emp.bad Discard File: none specified
(Allow all discards)
Number to load: ALL Number to skip: 0 Errors allowed: 50 Bind array: 64 rows, maximum of 256000 bytes Continuation: none specified Path used: Conventional
Table EMP1, loaded from every logical record. Insert option in effect for this table: INSERT
Column Name Position Len Term Encl Datatype ------------------------------ ---------- ----- ---- ---- --------------------- EMPNO FIRST * , O(") CHARACTER ENAME NEXT * , O(") CHARACTER JOB NEXT * , O(") CHARACTER MGR NEXT * , O(") CHARACTER HIREDATE NEXT * , O(") CHARACTER COMM NEXT * , O(") CHARACTER SAL NEXT * , O(") CHARACTER DEPTNO NEXT * , O(") CHARACTER
Table EMP1: 14 Rows successfully loaded. 0 Rows not loaded due to data errors. 0 Rows not loaded because all WHEN clauses were failed. 0 Rows not loaded because all fields were null.
Space allocated for bind array: 132096 bytes(64 rows) Read buffer bytes: 1048576
Total logical records skipped: 0 Total logical records read: 14 Total logical records rejected: 0 Total logical records discarded: 0
Run began on Fri Mar 16 14:30:47 2012 Run ended on Fri Mar 16 14:30:47 2012
Elapsed time was: 00:00:00.50 CPU time was: 00:00:00.02
——验证
SCOTT @ prod > select * from scott.emp1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 8000 20 7499 ALLEN SALESMAN 7698 20-FEB-81 300 1600 30 7521 WARD SALESMAN 7698 22-FEB-81 500 1250 30 7566 JONES MANAGER 7839 02-APR-81 8000 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1400 1250 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2000 10 7788 SCOTT ANALYST 7566 19-APR-87 100 2000 10 7839 KING PRESIDENT 17-NOV-81 2000 10 7844 TURNER SALESMAN 7698 08-SEP-81 0 1500 30 7876 ADAMS CLERK 7788 23-MAY-87 8000 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 8000 20 7934 MILLER CLERK 7782 23-JAN-82 2000 10 14 rows selected. SYS @ prod > analyze table emp1 compute statistics; Table analyzed.
SYS @ prod > select table_name,num_rows,blocks,empty_blocks from user_tables where table_name='EMP1';
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS -------------------- ---------- ---------- ------------ EMP1 14 60 68
——数据源和控制文件在一起 [oracle@work sqlldr]$ vi emp.ctl
load data infile * ——【不需要指明数据源了即 数据文件 和控制文件在一起】 append into table emp1 fields terminated by ',' optionally enclosed by '"' ( empno, ename, job, mgr, hiredate, comm, sal, deptno) begindata ——要以该关键字开头 7369,SMITH,CLERK,7902,1980-12-17 00:00:00,800,,20 7499,ALLEN,SALESMAN,7698,1981-02-20 00:00:00,1600,300,30 7521,WARD,SALESMAN,7698,1981-02-22 00:00:00,1250,500,30 7566,JONES,MANAGER,7839,1981-04-02 00:00:00,2975,,20 7654,MARTIN,SALESMAN,7698,1981-09-28 00:00:00,1250,1400,30 7698,BLAKE,MANAGER,7839,1981-05-01 00:00:00,2850,,30 7782,CLARK,MANAGER,7839,1981-06-09 00:00:00,2450,,10 7788,SCOTT,ANALYST,7566,1987-04-19 00:00:00,3000,,20 7839,KING,PRESIDENT,,1981-11-17 00:00:00,5000,,10 7844,TURNER,SALESMAN,7698,1981-09-08 00:00:00,1500,0,30 7876,ADAMS,CLERK,7788,1987-05-23 00:00:00,1100,,20 7900,JAMES,CLERK,7698,1981-12-03 00:00:00,950,,30
[oracle@solaris10 sqlldr]$sqlldr scott/tiger control=emp.ctl log=emp.log
SQL*Loader: Release 10.2.0.2.0 - Production on Fri Mar 16 14:46:04 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Commit point reached - logical record count 14
SCOTT @ prod > select * from scott.emp1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 8000 20 7499 ALLEN SALESMAN 7698 20-FEB-81 300 1600 30 7521 WARD SALESMAN 7698 22-FEB-81 500 1250 30 7566 JONES MANAGER 7839 02-APR-81 8000 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1400 1250 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2000 10 7788 SCOTT ANALYST 7566 19-APR-87 100 2000 10 7839 KING PRESIDENT 17-NOV-81 2000 10 7844 TURNER SALESMAN 7698 08-SEP-81 0 1500 30 7876 ADAMS CLERK 7788 23-MAY-87 8000 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 8000 20 7934 MILLER CLERK 7782 23-JAN-82 2000 10 7369 SMITH CLERK 7902 17-DEC-80 8000 20 7499 ALLEN SALESMAN 7698 20-FEB-81 300 1600 30 7521 WARD SALESMAN 7698 22-FEB-81 500 1250 30 7566 JONES MANAGER 7839 02-APR-81 8000 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1400 1250 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2000 10 7788 SCOTT ANALYST 7566 19-APR-87 100 2000 10 7839 KING PRESIDENT 17-NOV-81 2000 10 7844 TURNER SALESMAN 7698 08-SEP-81 0 1500 30 7876 ADAMS CLERK 7788 23-MAY-87 8000 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 8000 20 7934 MILLER CLERK 7782 23-JAN-82 2000 10
28 rows selected.
SCOTT @ prod > analyze table emp1 compute statistics; Table analyzed.
SCOTT @ prod > select table_name,num_rows,blocks,empty_blocks from user_tables where table_name='EMP1';
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS -------------------- ---------- ---------- ------------ EMP1 28 60 68——普通导入是将数据导入到高水位上的块
~4）直接导入（direct） SCOTT @ prod > analyze table emp1 compute statistics;
Table analyzed.
SCOTT @ prod > select table_name,num_rows,blocks,empty_blockS from user_tables where table_name='EMP1';
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS ------------------------------ ---------- ---------- ------------ EMP1 268 8 0
[oracle@work sqlldr]$ vi emp.ctl
load data infile * append into table emp1 fields terminated by ',' optionally enclosed by '"' ( empno, ename, job, mgr, hiredate, comm, sal, deptno) //字段的类型和记录的类型和顺序必须一致。 begindata 7369,SMITH,CLERK,7902,17-DEC-80,8000,,20
[oracle@work sqlldr]$ sqlldr scott/tiger control=emp.ctl log=emp.log direct=y ——标明是采取直接导入的方式
SQL*Loader: Release 10.2.0.1.0 - Production on Thu Aug 11 12:28:55 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Load completed - logical record count 1. SCOTT @ prod >analyze table emp1 compute statistics; Table analyzed.
SCOTT @ prod > select table_name,num_rows,blocks,empty_blocks from user_tables where table_name='EMP1';
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS -------------------- ---------- ---------- ------------ EMP1 29 65 6
---虽然只导入了一条记录，但oracle又重新给我们分配了一个新的extent（8 个blocks） |