网站分几种,iis建立的网站打不开,网站快速设计,珠海网站建设那家好目录
数据加载
#xff08;一#xff09;控制文件加载
1.创建用户执行sqlldr
2.创建文本文件和控制文件
3.查看表数据
4.查看log文件 #xff08;二#xff09;快捷方式加载
1.system用户执行
2.查看表数据
3.查看log文件
外部表 数据加载和传输的工具#xff1…目录
数据加载
一控制文件加载
1.创建用户执行sqlldr
2.创建文本文件和控制文件
3.查看表数据
4.查看log文件 二快捷方式加载
1.system用户执行
2.查看表数据
3.查看log文件
外部表 数据加载和传输的工具SQL*Loder , datadump以及其他的工具
1.SQL*Loader可以加载传输数据加载文本文件到数据库里面
2.用于外部表
数据加载
一控制文件加载
文件
control file定义文本数据的格式指定元数据的位置指定导入到指定的表
log file自动产生如果产生不了SQL*Loader的操作会终止记录了整个加载的过程
bad file可选数据中有约束如果加载的时候有一些行违反了约束就会放在这里面
discard file可选如果文本文件的格式和控制文件中的格式不符合会显示在这里
1.创建用户执行sqlldr
[oracle19c ~]$ sqlplus / as sysdbaSQL*Plus: Release 19.0.0.0.0 - Production on Thu Jan 4 12:03:28 2024
Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle. All rights reserved.Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0SQL startup
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL show pdbsCON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------2 PDB$SEED READ ONLY NO3 PDB1 MOUNTED
SQL alter pluggable database pdb1 open;Pluggable database altered.SQL alter session set containerpdb1;Session altered.SQL show pdbs;CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------3 PDB1 READ WRITE NO
SQL show user;
USER is SYSSQL create user nice identified by oracle2 quota 100m on users;--------在pdb创建一个用户并且给用户分配空间User created.SQL grant create table ,connect ,resource to nice;------给用户分配权限Grant succeeded.[oracle19c testtest]$ sqlplus nice/oraclepdb1;------验证是否能连接上pdbSQL*Plus: Release 19.0.0.0.0 - Production on Thu Jan 4 21:47:26 2024
Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle. All rights reserved.Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0SQL show user;
USER is NICESQL CREATE TABLE TEST1 (X1 CHAR(1),2 X2 VARCHAR2(10),3 X3 NUMBER,4 X4 NUMBER,5 X5 VARCHAR2(20),6 X6 VARCHAR2(20),7 X7 DATE);Table created.SQL select * from nice.test1;no rows selectedSQL desc nice.test1;Name Null? Type----------------------------------------- -------- ----------------------------X1 CHAR(1)X2 VARCHAR2(10)X3 NUMBERX4 NUMBERX5 VARCHAR2(20)X6 VARCHAR2(20)X7 DATE 2.创建文本文件和控制文件
[oracle19c oracle]$ cd testtest
[oracle19c testtest]$ ll -ls
total 12
0 -rwxrwxrwx 1 oracle oinstall 0 Jan 4 13:46 test1.bad//bad file
4 -rwxrwxrwx 1 oracle oinstall 220 Jan 4 21:34 test1.ctl//控制文件
4 -rwxrwxrwx 1 oracle oinstall 121 Jan 4 21:38 test1.dat//需要导入的文件
0 -rwxrwxrwx 1 oracle oinstall 0 Jan 4 13:46 test1.dsc//和文本格式不符合的信息文件[oracle19c testtest]$ cat test1.ctl
LOAD DATA
INFILE test1.dat
BADFILE test1.bad
DISCARDFILE test1.dsc
APPEND//累加到表中如果是replace就是不累加
INTO TABLE nice.test1
FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY //用逗号进行列之间的分割双引号是代表组合
TRAILING NULLCOLS//如果导入的文本文件列数比实际表的列数小就会被当成空值导入
(X1, X2, X3, X4, X5, X6, X7 date yyyy-mm-dd)//指定日期格式[oracle19c testtest]$ cat test1.dat
A,AA,1,11,AAA,AAAA,2014-09-09
B,BB,2,22,BBB,BBBB,2014-09-09
C,CC,3,33,CCC,CCCC,2014-09-09
D,DD,4,44,DDD,DDDD,2014-09-09[oracle19c testtest]$ sqlldr nice/oraclepdb1 controltest1.ctl//需要进入到文本文件所在的目录下执行文件不然就需要加绝对路径,不然会遇到如下报错
SQL*Loader: Release 19.0.0.0.0 - Production on Thu Jan 4 21:57:36 2024
Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.Path used: Conventional
Commit point reached - logical record count 5Table NICE.TEST1:4 Rows successfully loaded.Check the log file:test1.log
for more information about the load.
SQL*Loader-500: Unable to open file (test1.ctl)
SQL*Loader-553: file not found
SQL*Loader-509: System error: No such file or directory 3.查看表数据
SQL select * from nice.test1;SQL set linesize 200;
SQL /X X2 X3 X4 X5 X6 X7
- ---------- ---------- ---------- -------------------- -------------------- ---------
A AA 1 11 AAA AAAA 09-SEP-14
B BB 2 22 BBB BBBB 09-SEP-14
C CC 3 33 CCC CCCC 09-SEP-14
D DD 4 44 DDD DDDD 09-SEP-14 4.查看log文件
[oracle19c testtest]$ cat test1.logSQL*Loader: Release 19.0.0.0.0 - Production on Thu Jan 4 21:57:36 2024
Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.Control File: test1.ctl
Data File: test1.datBad File: test1.badDiscard File: test1.dsc (Allow all discards)Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 250 rows, maximum of 1048576 bytes
Continuation: none specified
Path used: ConventionalTable NICE.TEST1, loaded from every logical record.
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effectColumn Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
X1 FIRST * , O() CHARACTER
X2 NEXT * , O() CHARACTER
X3 NEXT * , O() CHARACTER
X4 NEXT * , O() CHARACTER
X5 NEXT * , O() CHARACTER
X6 NEXT * , O() CHARACTER
X7 NEXT * , O() DATE yyyy-mm-dd Record 5: Discarded - all columns null.Table NICE.TEST1:4 Rows successfully loaded.//4行数据加载成功0行失败0 Rows not loaded due to data errors.0 Rows not loaded because all WHEN clauses were failed.1 Row not loaded because all fields were null.Space allocated for bind array: 451500 bytes(250 rows)
Read buffer bytes: 1048576Total logical records skipped: 0
Total logical records read: 5
Total logical records rejected: 0
Total logical records discarded: 1Run began on Thu Jan 04 21:57:36 2024
Run ended on Thu Jan 04 21:57:42 2024Elapsed time was: 00:00:05.69
CPU time was: 00:00:00.04 在导入的过程中还遇到了一个报错
SQL*Loader-941Error during describe of table C##U1.TEST1
ORA-04043: OBJECT C##U1.TEST1 does not exist
遇到这个问题后很久都没有解决后来直接在pdb1创建一个普通用户nice就可以传输数据了有可能是通用用户的问题。 二快捷方式加载
这种方式要求table的名字和导入文件的名字要一样这样的话oracle会自己到路径下去找到这个文件进行导入。这种方式没有第一种只能不能转换格式。日期的格式要和数据库默认的格式一致不能识别的值不会被导入。 1.system用户执行
SQL select * from test1;X X2 X3 X4 X5 X6
- ---------- ---------- ---------- -------------------- --------------------
X7
---------
A AA 1 11 AAA AAAA
09-SEP-14B BB 2 22 BBB BBBB
09-SEP-14C CC 3 33 CCC CCCC
09-SEP-14X X2 X3 X4 X5 X6
- ---------- ---------- ---------- -------------------- --------------------
X7
---------
D DD 4 44 DDD DDDD
09-SEP-14SQL truncate table test1;-------先清空这张表为重新导入做准备Table truncated.SQL select * from test1;no rows selected
[oracle19c testtest]$ sqlldr system/oraclepdb1 tablenice.test1//system用户执行SQL*Loader: Release 19.0.0.0.0 - Production on Fri Jan 5 13:14:24 2024
Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.Express Mode Load, Table: NICE.TEST1
Path used: External Table, DEGREE_OF_PARALLELISMAUTOTable NICE.TEST1:1 Row successfully loaded.Check the log files:nice.lognice_%p.log_xt
for more information about the load. 2.查看表数据
SQL select * from test1;
SQL set linesize 300
SQL /X X2 X3 X4 X5 X6 X7
- ---------- ---------- ---------- -------------------- -------------------- ---------
A AA 1 11 AAA AAAA 09-SEP-14 3.查看log文件
以下可看出这钟方式导入时先创建了一个外部表加载完之后又把外部表删除
[oracle19c testtest]$ cat nice.logSQL*Loader: Release 19.0.0.0.0 - Production on Fri Jan 5 13:14:24 2024
Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.Express Mode Load, Table: NICE.TEST1
Data File: test1.datBad File: test1.badDiscard File: none specified(Allow all discards)Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation: none specified
Path used: External TableTable NICE.TEST1, loaded from every logical record.
Insert option in effect for this table: APPENDColumn Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
X1 FIRST * , CHARACTER
X2 NEXT * , CHARACTER
X3 NEXT * , CHARACTER
X4 NEXT * , CHARACTER
X5 NEXT * , CHARACTER
X6 NEXT * , CHARACTER
X7 NEXT * , DATE DD-MON-RR Generated control file for possible reuse:
OPTIONS(EXTERNAL_TABLEEXECUTE, TRIMLRTRIM)
LOAD DATA
INFILE (null)
APPEND
INTO TABLE NICE.TEST1
FIELDS TERMINATED BY ,
(X1,X2,X3,X4,X5,X6,X7 DATE
)
End of generated control file for possible reuse.created temporary directory object SYS_SQLLDR_XT_TMPDIR_00000 for path /u01/app/oracle/testtestenable parallel DML: ALTER SESSION ENABLE PARALLEL DMLcreating external table SYS_SQLLDR_X_EXT_TEST1CREATE TABLE SYS_SQLLDR_X_EXT_TEST1
(X1 CHAR(1),X2 VARCHAR2(10),X3 NUMBER,X4 NUMBER,X5 VARCHAR2(20),X6 VARCHAR2(20),X7 DATE
)
ORGANIZATION external
(TYPE oracle_loaderDEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000ACCESS PARAMETERS (RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCIIBADFILE SYS_SQLLDR_XT_TMPDIR_00000:test1.badLOGFILE nice_%p.log_xtREADSIZE 1048576FIELDS TERMINATED BY , LRTRIM REJECT ROWS WITH ALL NULL FIELDS (X1 CHAR(255),X2 CHAR(255),X3 CHAR(255),X4 CHAR(255),X5 CHAR(255),X6 CHAR(255),X7 CHAR(255)DATE_FORMAT DATE MASK DD-MON-RR))location (test1.dat)
)REJECT LIMIT UNLIMITEDexecuting INSERT statement to load database table NICE.TEST1INSERT /* append parallel(auto) */ INTO NICE.TEST1
(X1,X2,X3,X4,X5,X6,X7
)
SELECT X1,X2,X3,X4,X5,X6,X7
FROM SYS_SQLLDR_X_EXT_TEST1dropping external table SYS_SQLLDR_X_EXT_TEST1Table NICE.TEST1:1 Row successfully loaded.Run began on Fri Jan 05 13:14:24 2024
Run ended on Fri Jan 05 13:14:26 2024Elapsed time was: 00:00:02.00
CPU time was: 00:00:00.03 外部表
外部表的数据不会被加载到数据库里面。
ORACLE_LOADER 针对的是文本文件ORACLE_DATADUMP针对的是二进制文件不管是哪种引擎外部表的数据源都是放在操作系统上的不占用数据库的空间。
以SQL*Loader为引擎创建外部表
[oracle19c ~]$ sqlplus sys/oraclepdb1 as sysdbaSQL*Plus: Release 19.0.0.0.0 - Production on Fri Jan 5 13:51:09 2024
Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle. All rights reserved.Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0SQL show user
USER is SYS
SQL create directory ldr_dir as /u01/app/oracle/testtest;------用sys用户创建一个目录并且给nice赋权Directory created.SQL grant read,write,execute on directory ldr_dir to nice;Grant succeeded.SQL exit
[oracle19c ~]$ sqlplus nice/oraclepdb1--------nice用户连接到数据库SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jan 5 13:58:42 2024
Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle. All rights reserved.Last Successful login time: Fri Jan 05 2024 12:41:44 08:00Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0SQL CREATE TABLE test1_ext ----表的名字叫test1_ext2 (3 X1 CHAR(1),4 X2 VARCHAR2(10),5 X3 NUMBER,6 X4 NUMBER,7 X5 VARCHAR2(20),8 X6 VARCHAR2(20),9 X7 DATE10 )11 ORGANIZATION external -----指明这是一个外部表12 (13 TYPE oracle_loader-------用的是oracle_loader引擎14 DEFAULT DIRECTORY ldr_dir-----目录是 ldr_dir15 ACCESS PARAMETERS -----以下是访问的参数16 (17 RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII-----指定字符集18 BADFILE ldr_dir:test1.bad19 DISCARDFILE ldr_dir:test1.dsc20 LOGFILE ldr_dir:test1.log21 READSIZE 104857622 FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LDRTRIM ----逗号是分隔双引号代表组合23 MISSING FIELD VALUES ARE NULL ----列如果没有的话用空值来对待24 REJECT ROWS WITH ALL NULL FIELDS 25 (26 X1 CHAR(255)27 TERMINATED BY , OPTIONALLY ENCLOSED BY ,28 X2 CHAR(255)29 TERMINATED BY , OPTIONALLY ENCLOSED BY ,30 X3 CHAR(255)31 TERMINATED BY , OPTIONALLY ENCLOSED BY ,32 X4 CHAR(255)33 TERMINATED BY , OPTIONALLY ENCLOSED BY ,34 X5 CHAR(255)35 TERMINATED BY , OPTIONALLY ENCLOSED BY ,36 X6 CHAR(255)37 TERMINATED BY , OPTIONALLY ENCLOSED BY ,38 X7 CHAR(255)39 TERMINATED BY , OPTIONALLY ENCLOSED BY 40 DATE_FORMAT DATE MASK yyyy-mm-dd41 )42 )43 location 44 (45 test1.dat----指定需要导入的文本46 )47 )REJECT LIMIT UNLIMITED;Table created.SQL select * from test1_ext;SQL set linesize 300;
SQL /X X2 X3 X4 X5 X6 X7
- ---------- ---------- ---------- -------------------- -------------------- ---------
A AA 1 11 AAA AAAA 09-SEP-14
B BB 2 22 BBB BBBB 09-SEP-14
C CC 3 33 CCC CCCC 09-SEP-14
D DD 4 44 DDD DDDD 09-SEP-14
E EE 5 55 EEE EEEE 09-SEP-14
F FF 6 66 FFF FF,FF 09-SEP-14
G GG 7 77 ggg CIH7 rows selected.
