oracle 19c容器数据库数据加载和传输-----SQL*Loader（一）

[oracle@19c ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jan 4 12:03:28 2024
Version 19.3.0.0.0

Copyright (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.0

SQL> startup
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> show pdbs

? ? CON_ID CON_NAME?? ??? ??? ? ?OPEN MODE ?RESTRICTED
---------- ------------------------------ ---------- ----------
?? ? 2 PDB$SEED?? ??? ??? ? ?READ ONLY ?NO
?? ? 3 PDB1 ?? ??? ??? ? ?MOUNTED
SQL> alter pluggable database pdb1 open;

Pluggable database altered.


SQL> alter session set container=pdb1;

Session altered.

SQL> show pdbs;

? ? CON_ID CON_NAME?? ??? ??? ? ?OPEN MODE ?RESTRICTED
---------- ------------------------------ ---------- ----------
?? ? 3 PDB1 ?? ??? ??? ? ?READ WRITE NO
SQL> show user;
USER is "SYS"


SQL> create user nice identified by oracle
? 2 ?quota 100m on users;--------在pdb创建一个用户并且给用户分配空间

User created.

SQL> grant create table ,connect ,resource to nice;------给用户分配权限

Grant succeeded.



[oracle@19c testtest]$ sqlplus nice/oracle@pdb1;------验证是否能连接上pdb

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jan 4 21:47:26 2024
Version 19.3.0.0.0

Copyright (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.0

SQL> show user;
USER is "NICE"


SQL> 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 selected

SQL> desc nice.test1;
?Name?? ??? ??? ??? ??? ? ? Null? ? ?Type
?----------------------------------------- -------- ----------------------------
?X1?? ??? ??? ??? ??? ??? ? ? ?CHAR(1)
?X2?? ??? ??? ??? ??? ??? ? ? ?VARCHAR2(10)
?X3?? ??? ??? ??? ??? ??? ? ? ?NUMBER
?X4?? ??? ??? ??? ??? ??? ? ? ?NUMBER
?X5?? ??? ??? ??? ??? ??? ? ? ?VARCHAR2(20)
?X6?? ??? ??? ??? ??? ??? ? ? ?VARCHAR2(20)
?X7?? ??? ??? ??? ??? ??? ? ? ?DATE

2.创建文本文件和控制文件

[oracle@19c oracle]$ cd testtest
[oracle@19c 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//和文本格式不符合的信息文件


[oracle@19c 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')//指定日期格式

[oracle@19c 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

[oracle@19c testtest]$ sqlldr nice/oracle@pdb1 control=test1.ctl//需要进入到文本文件所在的目录下执行文件，不然就需要加绝对路径,不然会遇到如下报错
SQL*Loader: Release 19.0.0.0.0 - Production on Thu Jan 4 21:57:36 2024
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates. ?All rights reserved.

Path used: ? ? ?Conventional
Commit point reached - logical record count 5

Table 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文件

[oracle@19c testtest]$ cat test1.log

SQL*Loader: Release 19.0.0.0.0 - Production on Thu Jan 4 21:57:36 2024
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates. ?All rights reserved.

Control File: ? test1.ctl
Data File: ? ? ?test1.dat
? Bad File: ? ? test1.bad
? Discard 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: ? ? ?Conventional

Table NICE.TEST1, loaded from every logical record.
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect

? ?Column 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: 1048576

Total logical records skipped: ? ? ? ? ?0
Total logical records read: ? ? ? ? ? ? 5
Total logical records rejected: ? ? ? ? 0
Total logical records discarded: ? ? ? ?1

Run began on Thu Jan 04 21:57:36 2024
Run ended on Thu Jan 04 21:57:42 2024

Elapsed time was: ? ? 00:00:05.69
CPU time was: ? ? ? ? 00:00:00.04

在导入的过程中还遇到了一个报错：

SQL*Loader-941：Error 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-14

B BB		      2 	22 BBB			BBBB
09-SEP-14

C CC		      3 	33 CCC			CCCC
09-SEP-14


X X2		     X3 	X4 X5			X6
- ---------- ---------- ---------- -------------------- --------------------
X7
---------
D DD		      4 	44 DDD			DDDD
09-SEP-14


SQL> truncate table test1;-------先清空这张表为重新导入做准备

Table truncated.

SQL> select * from test1;

no rows selected

[oracle@19c testtest]$ sqlldr system/oracle@pdb1 table=nice.test1//system用户执行

SQL*Loader: Release 19.0.0.0.0 - Production on Fri Jan 5 13:14:24 2024
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates. ?All rights reserved.

Express Mode Load, Table: NICE.TEST1
Path used: ? ? ?External Table, DEGREE_OF_PARALLELISM=AUTO

Table NICE.TEST1:
? 1 Row successfully loaded.

Check the log files:
? nice.log
? nice_%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文件

以下可看出这钟方式导入时先创建了一个外部表，加载完之后又把外部表删除

[oracle@19c testtest]$ cat nice.log

SQL*Loader: Release 19.0.0.0.0 - Production on Fri Jan 5 13:14:24 2024
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates. ?All rights reserved.

Express Mode Load, Table: NICE.TEST1
Data File: ? ? ?test1.dat
? Bad File: ? ? test1.bad
? Discard File: ?none specified
?
?(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation: ? ?none specified
Path used: ? ? ?External Table

Table NICE.TEST1, loaded from every logical record.
Insert option in effect for this table: APPEND

? ?Column 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_TABLE=EXECUTE, TRIM=LRTRIM)
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/testtest

enable parallel DML: ALTER SESSION ENABLE PARALLEL DML

creating external table "SYS_SQLLDR_X_EXT_TEST1"

CREATE 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_loader
? DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
? ACCESS PARAMETERS?
? (
? ? RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
? ? BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'test1.bad'
? ? LOGFILE 'nice_%p.log_xt'
? ? READSIZE 1048576
? ? FIELDS 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 UNLIMITED

executing INSERT statement to load database table NICE.TEST1

INSERT /*+ 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_TEST1"

dropping external table "SYS_SQLLDR_X_EXT_TEST1"

Table 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 2024

Elapsed time was: ? ? 00:00:02.00
CPU time was: ? ? ? ? 00:00:00.03

外部表

外部表的数据不会被加载到数据库里面。

ORACLE_LOADER 针对的是文本文件，ORACLE_DATADUMP针对的是二进制文件，不管是哪种引擎，外部表的数据源都是放在操作系统上的，不占用数据库的空间。

以SQL*Loader为引擎创建外部表

[oracle@19c ~]$ sqlplus sys/oracle@pdb1 as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jan 5 13:51:09 2024
Version 19.3.0.0.0

Copyright (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.0

SQL> 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
[oracle@19c ~]$ sqlplus nice/oracle@pdb1--------nice用户连接到数据库

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jan 5 13:58:42 2024
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle. ?All rights reserved.

Last Successful login time: Fri Jan 05 2024 12:41:44 +08:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0


SQL> CREATE TABLE test1_ext ----表的名字叫test1_ext
? 2 ?(
? 3 ? "X1" CHAR(1),
? 4 ? "X2" VARCHAR2(10),
? 5 ? "X3" NUMBER,
? 6 ? "X4" NUMBER,
? 7 ? "X5" VARCHAR2(20),
? 8 ? "X6" VARCHAR2(20),
? 9 ? "X7" DATE
?10 ?)
?11 ?ORGANIZATION external -----指明这是一个外部表
?12 ?(
?13 ? TYPE oracle_loader-------用的是oracle_loader引擎
?14 ? DEFAULT DIRECTORY ldr_dir-----目录是 ldr_dir
?15 ? ACCESS PARAMETERS?-----以下是访问的参数
?16 ? (
?17 ? RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII-----指定字符集
?18 ?BADFILE ldr_dir:'test1.bad'
?19 ? DISCARDFILE ldr_dir:'test1.dsc'
?20 ? LOGFILE ldr_dir:'test1.log'
?21 ? READSIZE 1048576
?22 ? 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-dd'
?41 ? )
?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?? ??? ??? ?CIH

7 rows selected.

文章来源:https://blog.csdn.net/m0_70247753/article/details/135381646
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若内容造成侵权/违法违规/事实不符，请联系我的编程经验分享网邮箱：chenni525@qq.com进行投诉反馈，一经查实，立即删除！