课程第50/51次：2016-07-17星期日上下午

botang · 发表于 2016-7-17 22:28:24

本帖最后由 botang 于 2016-7-17 23:02 编辑

课程第50/51次：2016-07-17星期日上下午
【上完1Z0-052的第17章】数据仓库技术
【上完1Z0-051】：共12章（0 1 2 3 4 5 6 7 8 9 10 11）
【1Z0-052】：共17章（0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18）
【1Z0-053】：共15章（0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20）
表示已经上过的，表示还没上的。

9isqlldr.tar.gz (933.43 KB, 下载次数: 761)

2016-7-17 22:42 上传

点击文件名下载附件

Sqlldr：

create table t_bank_invoice (
account_number number ,
sub_number number ,
transac_date date ,
transac_address varchar2(600),
transac_comment varchar2(600),
currency varchar2(100),
remit varchar2(100),
in_amount number(20,2) ,
out_amount number(20,2),
balance number(20,2),
opposite_number number ,
opposite_username varchar2(100),
all_comment varchar2(2000)) ;

复制代码

ccb.ctl:

LOAD DATA
characterset ZHS16GBK
INFILE 'ccb.dat'
DISCARDFILE 'ccb.dis'
append
INTO table t_bank_invoice
TRAILING NULLCOLS
(
account_number "4367421823250099999",
sub_number "00000",
transac_date position(1:8) DATE(8) "YYYYMMDD" TERMINATED BY ',',
transac_address position(10) char TERMINATED BY ',',
out_amount decimal external TERMINATED BY ',' nullif out_amount=blanks,
in_amount decimal external TERMINATED BY ',' nullif in_amount=blanks ,
balance decimal external TERMINATED BY ',' nullif balance=blanks,
opposite_number char TERMINATED BY ',',
opposite_username char TERMINATED BY ',',
currency char TERMINATED BY ',',
transac_comment char TERMINATED BY whitespace
)

复制代码

ccb.dat：
账　　号：4367421823250099999
开户机构：福建省
币种：人民币
发生日期,交易地点,支出,收入,账户余额,对方账号,对方户名,币种,摘要
20100209,008350183980040,150.00,,6756.67,,,人民币,消费
20100210,,,2765.90,9522.57,,,人民币,工资/奖金
20100216,,3.00,,9519.57,,,人民币,自定义
20100217,,4859.10,,4660.47,,,人民币,自定义

icbc.ctl：

LOAD DATA
characterset ZHS16GBK
INFILE 'icbc.dat'
DISCARDFILE 'icbc.dis'
append
INTO table t_bank_invoice
when (5) ='^'
FIELDS TERMINATED BY '^'
TRAILING NULLCOLS
(
account_number "1402029101000999999",
sub_number "00000",
transac_date position(6:15) DATE(10) "YYYY-MM-DD" TERMINATED BY WHITESPACE,
transac_address TERMINATED BY WHITESPACE
"substr(:transac_address,2)",
transac_comment TERMINATED BY WHITESPACE
"substr(:transac_comment,2)",
currency TERMINATED BY WHITESPACE
"substr(:currency,2)",
remit TERMINATED BY WHITESPACE
"substr(:remit,2)",
in_amount TERMINATED BY WHITESPACE
"TO_NUMBER(substr(:in_amount,2),'999,999,999,999.00')",
out_amount TERMINATED BY WHITESPACE
"TO_NUMBER(substr(:out_amount,2),'999,999,999,999.00')",
balance TERMINATED BY WHITESPACE
"TO_NUMBER(substr(:balance,2),'999,999,999,999.00')",
all_comment TERMINATED BY WHITESPACE
"substr(:all_comment,2)"
)

复制代码

icbc.dat：
明细查询文件下载

卡号: 1402029101000999999 卡别名:

子账户序号:    子账户类别:    子账户别名:

  ^交易日期    ^交易场所                ^业务摘要  ^币种    ^钞/汇    ^收入金额 ^支出金额 ^余额
^2010-05-10    ^                      ^工资    ^人民币 ^钞       ^564.94    ^          ^6,748.42
^2010-05-14    ^                      ^工资    ^人民币 ^钞       ^5,989.54 ^          ^12,737.96
^2010-05-26    ^金山支行营业部          ^支取    ^人民币 ^钞       ^          ^12,700.00 ^37.96
^2010-06-12    ^                      ^工资    ^人民币 ^钞       ^5,811.04 ^          ^5,849.00
^2010-06-21    ^                      ^息       ^人民币 ^钞       ^5.03       ^          ^5,854.03
^2010-07-01    ^金山支行营业部          ^支取    ^人民币 ^钞       ^          ^5,854.00 ^0.03

#人民币合计                                                                ^163,952.85 ^170,134.00

sqlloder外部表：
控制文件：

LOAD DATA
infile '/home/oracle/prod_master.dat'
APPEND
INTO TABLE SH.SALES2
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
(
PROD_ID INTEGER EXTERNAL(3),
CUST_ID INTEGER EXTERNAL,
TIME_ID DATE(11) "DD-MON-YYYY" ,
CHANNEL_ID char,
PROMO_ID INTEGER EXTERNAL(3),
QUANTITY_SOLD INTEGER EXTERNAL,
AMOUNT_SOLD decimal EXTERNAL
)

复制代码

sqlldr sh/sh control=prod.ctl external_table=GENERATE_ONLY log=prod.sql变换出来的sql语句:

CREATE TABLE SALES2
(
"PROD_ID" NUMBER,
"CUST_ID" NUMBER,
"TIME_ID" DATE,
"CHANNEL_ID" CHAR(2),
"PROMO_ID" NUMBER,
"QUANTITY_SOLD" NUMBER(10,2),
"AMOUNT_SOLD" NUMBER(10,2)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY DIRLOG
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET AL32UTF8
BADFILE 'prod_master.bad'
LOGFILE 'prod_master.log'
READSIZE 1048576
FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"' LDRTRIM
missing field values are null
REJECT ROWS WITH ALL NULL FIELDS
(
"PROD_ID" CHAR(3)
TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
"CUST_ID" CHAR(255)
TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
"TIME_ID" CHAR(11)
TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"'
DATE_FORMAT DATE MASK "DD-MON-YYYY",
"CHANNEL_ID" CHAR(255)
TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
"PROMO_ID" CHAR(3)
TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
"QUANTITY_SOLD" CHAR(255)
TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
"AMOUNT_SOLD" CHAR(255)
TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"'
)
)
location
(
dir1:'a.dat',dir2:'b.dat'
)
)REJECT LIMIT UNLIMITED;

复制代码

Datapump：

两个parfile：

directory=dir1
dumpfile=user1.dmp
schemas=user1
job_name=user1_job
exclude=table:"in ('T05217_USER1')", procedure:"in ('PROC05217_USER1')"
query='USER1.T05208_A:where a>1'
flashback_time='2016-07-17:16:02:04'

复制代码

directory=dirwin
network_link=dblink_user1
schemas=user1
job_name=user1_network_job
exclude=table:"in ('T05217_USER1')", procedure:"in ('PROC05217_USER1')"
query='USER1.T05208_A:where a>1'
flashback_time='2016-07-17:16:02:04'
remap_schema=user1:user2

复制代码

随堂语句：

select * from dba_datapump_jobs;
select * from system.full_job;
select * from dba_tables t where t.owner='HR' and t.table_name='TCNV';
select * from dba_users u where u.username='USER1';
select * from dba_objects o where o.owner='USER1';
select * from dba_tablespaces;
select * from dba_tables t where t.table_name='TBS05207_A';
create table sys.tsys( a number ) tablespace tbs05207_a;
insert into sys.tsys values (1) ;
commit;
create table hr.thr ( a number ) tablespace tbs05207_a;
insert into hr.thr values (1) ;
commit;
create table sh.tsh ( a number ) tablespace tbs05207_a;
insert into sh.tsh values (1) ;
commit;
grant all on directory dirlog to hr;
grant all on directory dir1 to hr;
grant all on directory dir2 to hr;
-----
CREATE TABLE ext_emp_query_results
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY dirlog
LOCATION (dir1:'emp1.exp',dir2:'emp2.exp')
)
PARALLEL 2
AS
SELECT e.first_name,e.last_name,d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id AND
d.department_name in
('Marketing', 'Purchasing');
select * from ext_emp_query_results;

复制代码