GBase 8s 学习笔记 010 —— GBase 8s 数据迁移

数据迁移分类

• 数据格式
• 数据范围
• 数据用途

数据格式

• 文本格式
• 二进制格式

数据范围

• 整库迁移
• 单表迁移

数据用途

• 产品自己使用
• 提供给第三方数据

数据迁移问题

• 行分隔符
• 列分隔符
• 日期时间格式
• 汉字乱码

数据包含行分隔符

• 默认的行分隔符为\n（Linux）或\r\n（Windows）。对于从Windows系统生成的数据需要在Linux系统导入数据时，可以使用dos2unix工具删除\r。
• 对于需要导入数据的系统，如果导入程序只支持逐行解析数据并导入，则需要在生成数据时，对数据中的换行符进行转义或删除。对字符进行转义会使数据产生膨胀，在导入数据时，可根据实际情况对目标字段进行加长处理。
• 对于一些数据库系统，通过定义一些规则来解决数据中包含换行符问题，如GBase 8s使用在换行符前增加一个\表示这行数据未完成，下一行仍然是本行数据的延续，来解决数据中包含换行符的问题。基于特定规则的换行符处理方式，只能在本系统内使用，无法提供数据给第三方系统。
• 部分厂商采用自定义换行符方法，解决数据中包含\n问题。用户可以指定不可见的二进制字符作为数据的换行符。

数据包含列分隔符

• 用户常指定一个字符，做为列数据的分隔符，如逗号或管道符。对于数据中全部为数值字段的数据，这种方式非常有效和高效。
• 对于迁移的数据包含文本数据时，数据中可能包含任意的单个可见字符，指定单个字符作为列分隔符时，容易出现部分数据无法导入现象。通常迁移工具需要支持转义，将数据中的列分隔符进行转义，以区别数据中的列分隔符和实际的列分隔符。
• 一些工具支持不可见字符或多个字符一起作为列分隔符。这种方案通常不需要对数据中的字符进行转义处理，适用于不同数据库系统间的数据迁移。

数据中的日期时间格式不匹配

格式化符	说明
%a	星期的短格式表示
%A	星期的完整格式表示
%b	月份的短格式表示
%B	月份的完整格式表示
%C	世纪的表示，通常是年值除以100后的整数部分
%d	天(01-31)
%D	相当于格式：%m/%d/%y
%e	天(1-31)
%h	和%b相同
%H	小时(00-24)
%iy	两位年(00-99)
%iY	四位年(0000-9999)
%m	月(01-12)
%M	分钟(00-59)
%n	换行符
%S	秒(00-59)
%t	制表符
%w	星期的数字表示(0-6)
%y	两位年(00-99)
%Y	四位年(0000-9999)

汉字乱码

• 不同的汉字编码格式，是产生乱码的主要原因。
• 数据截断可能导致部分数据产生乱码。

数据准备

> create database mydb in datadbs1 with log;

Database created.

> create table t_dept_00(f_deptid int, f_deptname varchar(50));

Table created.

> create table t_dept_01(f_deptid int, f_deptname varchar(50));

Table created.

> create table t_employee_00(f_employeeid int, f_deptid int, f_employeename varchar(50), f_birthdate date);

Table created.

> create table t_employee_01(f_employeeid int, f_deptid int, f_employeename varchar(50), f_birthdate date);

Table created.

> create table t_employee_02(f_employeeid int, f_deptid int, f_employeename varchar(50), f_birthdate date);

Table created.

> create table t_employee_03(f_employeeid int, f_deptid int, f_employeename varchar(50), f_birthdate date);

Table created.

> create table t_employee_04(f_employeeid int, f_deptid int, f_employeename varchar(50), f_birthdate date);

Table created.

> create table t_employee_05(f_employeeid int, f_deptid int, f_employeename varchar(50), f_birthdate date);

Table created.

> create table t_employee_06(f_employeeid int, f_deptid int, f_employeename varchar(50), f_birthdate date);

Table created.

> create table t_employee_07(f_employeeid int, f_deptid int, f_employeename varchar(50), f_birthdate date);

Table created.

> create table t_employee_08(f_employeeid int, f_deptid int, f_employeename varchar(50), f_birthdate date);

Table created.

> create table t_employee_09(f_employeeid int, f_deptid int, f_employeename varchar(50), f_birthdate date);

Table created.

[gbasedbt@localhost ~]$ export GL_DATE="%iY-%m-%d"
[gbasedbt@localhost ~]$ dbaccess - -
Your evaluation license will expire on 2024-02-14 00:00:00
> database mydb;

Database selected.

> create table t_dept(f_deptid int, f_deptname varchar(50));

Table created.

> insert into t_dept values(1,'Dev');

1 row(s) inserted.

> insert into t_dept values(2,'Test');

1 row(s) inserted.

> insert into t_dept values(3,'Market');

1 row(s) inserted.

> create table t_employee(f_employeeid int, f_deptid int, f_employeename varchar(50), f_birthdate date);

Table created.

> insert into t_employee values(1,1,'Bill','1983-06-01');

1 row(s) inserted.

> insert into t_employee values(2,1,'John','1985-12-25');

1 row(s) inserted.

> insert into t_employee values(3,2,'Mary','1987-10-10');

1 row(s) inserted.

> insert into t_employee values(4,3,'Kate','1989-11-11');

1 row(s) inserted.

> insert into t_employee values(5,1,'Will
Smith','1981-02-28');>

1 row(s) inserted.

数据迁移方法

数据迁移工具

• unload/load
• dbload
• dbexport/dbimport
• onunload/onload
• external table

数据迁移工具对比

数据迁移方法	运行方式	数据格式	数据范围	数据用途
unload/load	SQL	文本数据	单表	自用/第三方
dbexport/dbimport	命令行	文本数据	库	自用
dbload	命令行	文本数据	多表	自用
onunload/onload	命令行	二进制数据	库/表	自用
external table	SQL	文本数据	单表	自用/第三方

unload/load

unload语法

unload to 'file_name' [delimiter 'delimiter_string'] 
select <* | columns> from <table_name | synonym_name | view_name>;

file_name:保存数据的文件名，可以为全路径或者相对路径;
delimiter:定义数据列分隔符，为一个或多个字符，默认为'|';
columns:查询的字段列表;

导出单表数据，列分隔符使用【,】

[gbasedbt@localhost ~]$ ls
gdca  profile.gbaseserver  tmp  train
[gbasedbt@localhost ~]$ cd train/
[gbasedbt@localhost train]$ ls
data  dbload  export  external  import  load  onload  onunload  unload
[gbasedbt@localhost train]$ cd unload/
[gbasedbt@localhost unload]$ ll
总用量 4
-rw-r--r--. 1 root root 70 2月  15 11:43 unload_01.sql
[gbasedbt@localhost unload]$ cat unload_01.sql
unload to '../data/unload_01.unl' delimiter ','
select * from t_dept;[gbasedbt@localhost unload]$
[gbasedbt@localhost unload]$
[gbasedbt@localhost unload]$ dbaccess mydb unload_01.sql
Your evaluation license will expire on 2024-02-14 00:00:00

Database selected.


3 row(s) unloaded.


Database closed.

[gbasedbt@localhost unload]$

[gbasedbt@localhost ~]$ ls
gdca  profile.gbaseserver  tmp  train
[gbasedbt@localhost ~]$ cd train/
[gbasedbt@localhost train]$ ls
data  dbload  export  external  import  load  onload  onunload  unload
[gbasedbt@localhost train]$ cd data/
[gbasedbt@localhost data]$ ll
总用量 0
[gbasedbt@localhost data]$ ll
总用量 4
-rw-rw-r--. 1 gbasedbt gbasedbt 25 2月  15 11:57 unload_01.unl
[gbasedbt@localhost data]$ cat unload_01.unl
1,Dev,
2,Test,
3,Market,
[gbasedbt@localhost data]$

导出多表关联数据，列分隔符使用【|】

[gbasedbt@localhost unload]$ ll
总用量 8
-rw-r--r--. 1 root root  70 2月  15 11:43 unload_01.sql
-rw-r--r--. 1 root root 179 2月  15 13:34 unload_02.sql
[gbasedbt@localhost unload]$ cat unload_02.sql
unload to '../data/unload_02.unl'
select a.f_employeeid, a.f_employeename, a.f_deptid, b.f_deptname, a.f_birthdate
from t_employee a, t_dept b
where a.f_deptid = b.f_deptid;
[gbasedbt@localhost unload]$ dbaccess mydb unload_02.sql
Your evaluation license will expire on 2024-02-14 00:00:00

Database selected.


5 row(s) unloaded.


Database closed.

[gbasedbt@localhost unload]$

[gbasedbt@localhost data]$ ll
总用量 4
-rw-rw-r--. 1 gbasedbt gbasedbt 25 2月  15 11:57 unload_01.unl
[gbasedbt@localhost data]$ cat unload_02.unl
1|Bill|1|Dev|1983 06月 01日|
2|John|1|Dev|1985 12月 25日|
3|Mary|2|Test|1987 10月 10日|
4|Kate|3|Market|1989 11月 11日|
5|Will\
Smith|1|Dev|1981 02月 28日|
[gbasedbt@localhost data]$ ll
总用量 8
-rw-rw-r--. 1 gbasedbt gbasedbt  25 2月  15 11:57 unload_01.unl
-rw-rw-r--. 1 gbasedbt gbasedbt 166 2月  15 13:37 unload_02.unl
[gbasedbt@localhost data]$

[gbasedbt@localhost unload]$ export CLIENT_LOCALE=en_US.utf8
[gbasedbt@localhost unload]$ dbaccess mydb unload_02.sql
Your evaluation license will expire on 2024-02-14 00:00:00

Database selected.


5 row(s) unloaded.


Database closed.

[gbasedbt@localhost unload]$

[gbasedbt@localhost data]$ cat unload_02.unl
1|Bill|1|Dev|06/01/1983|
2|John|1|Dev|12/25/1985|
3|Mary|2|Test|10/10/1987|
4|Kate|3|Market|11/11/1989|
5|Will\
Smith|1|Dev|02/28/1981|
[gbasedbt@localhost data]$

导出单表数据，日期类型数据使用类似18.06.2021的格式，列分隔符使用英文的句号【.】

[gbasedbt@localhost unload]$ ll
总用量 12
-rw-r--r--. 1 root root  70 2月  15 11:43 unload_01.sql
-rw-r--r--. 1 root root 179 2月  15 13:34 unload_02.sql
-rw-r--r--. 1 root root 145 2月  15 13:45 unload_03.sql
[gbasedbt@localhost unload]$ export CLIENT_LOCALE=en_US.UTF8
[gbasedbt@localhost unload]$ export GL_DATE="%d.%m.%iY"
[gbasedbt@localhost unload]$ dbaccess mydb unload_03.sql
Your evaluation license will expire on 2024-02-14 00:00:00

Database selected.


5 row(s) unloaded.


Database closed.

[gbasedbt@localhost unload]$

[gbasedbt@localhost data]$ ll
总用量 8
-rw-rw-r--. 1 gbasedbt gbasedbt  25 2月  15 11:57 unload_01.unl
-rw-rw-r--. 1 gbasedbt gbasedbt 136 2月  15 13:47 unload_02.unl
[gbasedbt@localhost data]$ ll
总用量 12
-rw-rw-r--. 1 gbasedbt gbasedbt  25 2月  15 11:57 unload_01.unl
-rw-rw-r--. 1 gbasedbt gbasedbt 136 2月  15 13:47 unload_02.unl
-rw-rw-r--. 1 gbasedbt gbasedbt 122 2月  15 13:48 unload_03.unl
[gbasedbt@localhost data]$ cat unload_03.unl
1.1.Bill.01\.06\.1983.
2.1.John.25\.12\.1985.
3.2.Mary.10\.10\.1987.
4.3.Kate.11\.11\.1989.
5.1.Will\
Smith.28\.02\.1981.
[gbasedbt@localhost data]$

导出单表数据，日期类型数据使用类似18.06.2021的格式，列分隔符使用【…】

[gbasedbt@localhost unload]$ ll
总用量 16
-rw-r--r--. 1 root root  70 2月  15 11:43 unload_01.sql
-rw-r--r--. 1 root root 179 2月  15 13:34 unload_02.sql
-rw-r--r--. 1 root root 145 2月  15 13:45 unload_03.sql
-rw-r--r--. 1 root root 148 2月  15 13:51 unload_04.sql
[gbasedbt@localhost unload]$
[gbasedbt@localhost unload]$ cat unload_04.sql
-- export CLIENT_LOCALE=en_US.UTF8
-- export GL_DATE="%d.%m.%iY"

unload to '../data/unload_04.unl' delimiter '....'
select * from t_employee;
[gbasedbt@localhost unload]$
[gbasedbt@localhost unload]$ export CLIENT_LOCALE=en_US.UTF8
[gbasedbt@localhost unload]$ export GL_DATE="%d.%m.%iY"
[gbasedbt@localhost unload]$
[gbasedbt@localhost unload]$ dbaccess mydb unload_04.sql
Your evaluation license will expire on 2024-02-14 00:00:00

Database selected.


5 row(s) unloaded.


Database closed.

[gbasedbt@localhost unload]$

[gbasedbt@localhost data]$ ll
总用量 12
-rw-rw-r--. 1 gbasedbt gbasedbt  25 2月  15 11:57 unload_01.unl
-rw-rw-r--. 1 gbasedbt gbasedbt 136 2月  15 13:47 unload_02.unl
-rw-rw-r--. 1 gbasedbt gbasedbt 122 2月  15 13:48 unload_03.unl
[gbasedbt@localhost data]$
[gbasedbt@localhost data]$ ll
总用量 16
-rw-rw-r--. 1 gbasedbt gbasedbt  25 2月  15 11:57 unload_01.unl
-rw-rw-r--. 1 gbasedbt gbasedbt 136 2月  15 13:47 unload_02.unl
-rw-rw-r--. 1 gbasedbt gbasedbt 122 2月  15 13:48 unload_03.unl
-rw-rw-r--. 1 gbasedbt gbasedbt 182 2月  15 13:53 unload_04.unl
[gbasedbt@localhost data]$
[gbasedbt@localhost data]$ cat unload_04.unl
1....1....Bill....01\.06\.1983....
2....1....John....25\.12\.1985....
3....2....Mary....10\.10\.1987....
4....3....Kate....11\.11\.1989....
5....1....Will\
Smith....28\.02\.1981....
[gbasedbt@localhost data]$

导出单表数据，日期类型数据使用类似18.06.2021的格式，列分隔符使用【#…】

[gbasedbt@localhost unload]$ ll
总用量 20
-rw-r--r--. 1 root root  70 2月  15 11:43 unload_01.sql
-rw-r--r--. 1 root root 179 2月  15 13:34 unload_02.sql
-rw-r--r--. 1 root root 145 2月  15 13:45 unload_03.sql
-rw-r--r--. 1 root root 148 2月  15 13:51 unload_04.sql
-rw-r--r--. 1 root root 148 2月  15 13:56 unload_05.sql
[gbasedbt@localhost unload]$
[gbasedbt@localhost unload]$ cat unload_05.sql
-- export CLIENT_LOCALE=en_US.UTF8
-- export GL_DATE="%d.%m.%iY"

unload to '../data/unload_05.unl' delimiter '#...'
select * from t_employee;
[gbasedbt@localhost unload]$
[gbasedbt@localhost unload]$ dbaccess mydb unload_05.sql
Your evaluation license will expire on 2024-02-14 00:00:00

Database selected.


5 row(s) unloaded.


Database closed.

[gbasedbt@localhost unload]$

[gbasedbt@localhost data]$ ll
总用量 16
-rw-rw-r--. 1 gbasedbt gbasedbt  25 2月  15 11:57 unload_01.unl
-rw-rw-r--. 1 gbasedbt gbasedbt 136 2月  15 13:47 unload_02.unl
-rw-rw-r--. 1 gbasedbt gbasedbt 122 2月  15 13:48 unload_03.unl
-rw-rw-r--. 1 gbasedbt gbasedbt 182 2月  15 13:53 unload_04.unl
[gbasedbt@localhost data]$
[gbasedbt@localhost data]$ ll
总用量 20
-rw-rw-r--. 1 gbasedbt gbasedbt  25 2月  15 11:57 unload_01.unl
-rw-rw-r--. 1 gbasedbt gbasedbt 136 2月  15 13:47 unload_02.unl
-rw-rw-r--. 1 gbasedbt gbasedbt 122 2月  15 13:48 unload_03.unl
-rw-rw-r--. 1 gbasedbt gbasedbt 182 2月  15 13:53 unload_04.unl
-rw-rw-r--. 1 gbasedbt gbasedbt 172 2月  15 13:58 unload_05.unl
[gbasedbt@localhost data]$
[gbasedbt@localhost data]$ cat unload_05.unl
1#...1#...Bill#...01.06.1983#...
2#...1#...John#...25.12.1985#...
3#...2#...Mary#...10.10.1987#...
4#...3#...Kate#...11.11.1989#...
5#...1#...Will\
Smith#...28.02.1981#...
[gbasedbt@localhost data]$

导出单表数据，日期类型数据使用类似18.06.2021的格式，列分隔符使用【…#】

[gbasedbt@localhost unload]$ ll
总用量 24
-rw-r--r--. 1 root root  70 2月  15 11:43 unload_01.sql
-rw-r--r--. 1 root root 179 2月  15 13:34 unload_02.sql
-rw-r--r--. 1 root root 145 2月  15 13:45 unload_03.sql
-rw-r--r--. 1 root root 148 2月  15 13:51 unload_04.sql
-rw-r--r--. 1 root root 148 2月  15 13:56 unload_05.sql
-rw-r--r--. 1 root root 148 2月  15 14:00 unload_06.sql
[gbasedbt@localhost unload]$
[gbasedbt@localhost unload]$ cat unload_06.sql
-- export CLIENT_LOCALE=en_US.UTF8
-- export GL_DATE="%d.%m.%iY"

unload to '../data/unload_06.unl' delimiter '...#'
select * from t_employee;
[gbasedbt@localhost unload]$
[gbasedbt@localhost unload]$ export CLIENT_LOCALE=en_US.UTF8
[gbasedbt@localhost unload]$ export GL_DATE="%d.%m.%iY"
[gbasedbt@localhost unload]$
[gbasedbt@localhost unload]$ dbaccess mydb unload_06.sql
Your evaluation license will expire on 2024-02-14 00:00:00

Database selected.


5 row(s) unloaded.


Database closed.

[gbasedbt@localhost unload]$

[gbasedbt@localhost data]$ ll
总用量 20
-rw-rw-r--. 1 gbasedbt gbasedbt  25 2月  15 11:57 unload_01.unl
-rw-rw-r--. 1 gbasedbt gbasedbt 136 2月  15 13:47 unload_02.unl
-rw-rw-r--. 1 gbasedbt gbasedbt 122 2月  15 13:48 unload_03.unl
-rw-rw-r--. 1 gbasedbt gbasedbt 182 2月  15 13:53 unload_04.unl
-rw-rw-r--. 1 gbasedbt gbasedbt 172 2月  15 13:58 unload_05.unl
[gbasedbt@localhost data]$
[gbasedbt@localhost data]$ ll
总用量 24
-rw-rw-r--. 1 gbasedbt gbasedbt  25 2月  15 11:57 unload_01.unl
-rw-rw-r--. 1 gbasedbt gbasedbt 136 2月  15 13:47 unload_02.unl
-rw-rw-r--. 1 gbasedbt gbasedbt 122 2月  15 13:48 unload_03.unl
-rw-rw-r--. 1 gbasedbt gbasedbt 182 2月  15 13:53 unload_04.unl
-rw-rw-r--. 1 gbasedbt gbasedbt 172 2月  15 13:58 unload_05.unl
-rw-rw-r--. 1 gbasedbt gbasedbt 182 2月  15 14:03 unload_06.unl
[gbasedbt@localhost data]$
[gbasedbt@localhost data]$ cat unload_06.unl
1...#1...#Bill...#01\.06\.1983...#
2...#1...#John...#25\.12\.1985...#
3...#2...#Mary...#10\.10\.1987...#
4...#3...#Kate...#11\.11\.1989...#
5...#1...#Will\
Smith...#28\.02\.1981...#
[gbasedbt@localhost data]$

导出单表数据，日期类型数据使用类似 Wed Dec 25 1985 的格式，列分隔符使用【.*.】

[gbasedbt@localhost unload]$ ll
总用量 28
-rw-r--r--. 1 root root  70 2月  15 11:43 unload_01.sql
-rw-r--r--. 1 root root 179 2月  15 13:34 unload_02.sql
-rw-r--r--. 1 root root 145 2月  15 13:45 unload_03.sql
-rw-r--r--. 1 root root 148 2月  15 13:51 unload_04.sql
-rw-r--r--. 1 root root 148 2月  15 13:56 unload_05.sql
-rw-r--r--. 1 root root 148 2月  15 14:00 unload_06.sql
-rw-r--r--. 1 root root 150 2月  15 14:06 unload_07.sql
[gbasedbt@localhost unload]$
[gbasedbt@localhost unload]$ cat unload_07.sql
-- export CLIENT_LOCALE=en_US.UTF8
-- export GL_DATE="%a %b %e %iY"

unload to '../data/unload_07.unl' delimiter '.*.'
select * from t_employee;
[gbasedbt@localhost unload]$
[gbasedbt@localhost unload]$ export CLIENT_LOCALE=en_US.UTF8
[gbasedbt@localhost unload]$ export GL_DATE="%a %b %e %iY"
[gbasedbt@localhost unload]$
[gbasedbt@localhost unload]$ dbaccess mydb unload_07.sql
Your evaluation license will expire on 2024-02-14 00:00:00

Database selected.


5 row(s) unloaded.


Database closed.

[gbasedbt@localhost unload]$

[gbasedbt@localhost data]$ ll
总用量 24
-rw-rw-r--. 1 gbasedbt gbasedbt  25 2月  15 11:57 unload_01.unl
-rw-rw-r--. 1 gbasedbt gbasedbt 136 2月  15 13:47 unload_02.unl
-rw-rw-r--. 1 gbasedbt gbasedbt 122 2月  15 13:48 unload_03.unl
-rw-rw-r--. 1 gbasedbt gbasedbt 182 2月  15 13:53 unload_04.unl
-rw-rw-r--. 1 gbasedbt gbasedbt 172 2月  15 13:58 unload_05.unl
-rw-rw-r--. 1 gbasedbt gbasedbt 182 2月  15 14:03 unload_06.unl
[gbasedbt@localhost data]$
[gbasedbt@localhost data]$ ll
总用量 28
-rw-rw-r--. 1 gbasedbt gbasedbt  25 2月  15 11:57 unload_01.unl
-rw-rw-r--. 1 gbasedbt gbasedbt 136 2月  15 13:47 unload_02.unl
-rw-rw-r--. 1 gbasedbt gbasedbt 122 2月  15 13:48 unload_03.unl
-rw-rw-r--. 1 gbasedbt gbasedbt 182 2月  15 13:53 unload_04.unl
-rw-rw-r--. 1 gbasedbt gbasedbt 172 2月  15 13:58 unload_05.unl
-rw-rw-r--. 1 gbasedbt gbasedbt 182 2月  15 14:03 unload_06.unl
-rw-rw-r--. 1 gbasedbt gbasedbt 177 2月  15 14:08 unload_07.unl
[gbasedbt@localhost data]$
[gbasedbt@localhost data]$ cat unload_07.unl
1.*.1.*.Bill.*.Wed Jun  1 1983.*.
2.*.1.*.John.*.Wed Dec 25 1985.*.
3.*.2.*.Mary.*.Sat Oct 10 1987.*.
4.*.3.*.Kate.*.Sat Nov 11 1989.*.
5.*.1.*.Will\
Smith.*.Sat Feb 28 1981.*.
[gbasedbt@localhost data]$

导出单表数据，日期类型数据使用类似 星期三 十二月 25 1985 的格式，列分隔符使用【…】

[gbasedbt@localhost unload]$ ll
总用量 36
-rw-r--r--. 1 root root  70 2月  15 11:43 unload_01.sql
-rw-r--r--. 1 root root 179 2月  15 13:34 unload_02.sql
-rw-r--r--. 1 root root 145 2月  15 13:45 unload_03.sql
-rw-r--r--. 1 root root 148 2月  15 13:51 unload_04.sql
-rw-r--r--. 1 root root 148 2月  15 13:56 unload_05.sql
-rw-r--r--. 1 root root 148 2月  15 14:00 unload_06.sql
-rw-r--r--. 1 root root 150 2月  15 14:06 unload_07.sql
-rw-r--r--. 1 root root 150 2月  15 14:11 unload_08.sql
-rw-r--r--. 1 root root  80 2月  15 14:11 unload_09.sql
[gbasedbt@localhost unload]$
[gbasedbt@localhost unload]$ cat unload_08.sql
-- export CLIENT_LOCALE=zh_CN.UTF8
-- export GL_DATE="%A %B %e %iY"

unload to '../data/unload_08.unl' delimiter '...'
select * from t_employee;
[gbasedbt@localhost unload]$
[gbasedbt@localhost unload]$ export CLIENT_LOCALE=zh_CN.UTF8
[gbasedbt@localhost unload]$ export GL_DATE="%A %B %e %iY"
[gbasedbt@localhost unload]$
[gbasedbt@localhost unload]$ dbaccess mydb unload_08.sql
Your evaluation license will expire on 2024-02-14 00:00:00

Database selected.


5 row(s) unloaded.


Database closed.

[gbasedbt@localhost unload]$

[gbasedbt@localhost data]$ ll
总用量 28
-rw-rw-r--. 1 gbasedbt gbasedbt  25 2月  15 11:57 unload_01.unl
-rw-rw-r--. 1 gbasedbt gbasedbt 136 2月  15 13:47 unload_02.unl
-rw-rw-r--. 1 gbasedbt gbasedbt 122 2月  15 13:48 unload_03.unl
-rw-rw-r--. 1 gbasedbt gbasedbt 182 2月  15 13:53 unload_04.unl
-rw-rw-r--. 1 gbasedbt gbasedbt 172 2月  15 13:58 unload_05.unl
-rw-rw-r--. 1 gbasedbt gbasedbt 182 2月  15 14:03 unload_06.unl
-rw-rw-r--. 1 gbasedbt gbasedbt 177 2月  15 14:08 unload_07.unl
[gbasedbt@localhost data]$
[gbasedbt@localhost data]$ ll
总用量 32
-rw-rw-r--. 1 gbasedbt gbasedbt  25 2月  15 11:57 unload_01.unl
-rw-rw-r--. 1 gbasedbt gbasedbt 136 2月  15 13:47 unload_02.unl
-rw-rw-r--. 1 gbasedbt gbasedbt 122 2月  15 13:48 unload_03.unl
-rw-rw-r--. 1 gbasedbt gbasedbt 182 2月  15 13:53 unload_04.unl
-rw-rw-r--. 1 gbasedbt gbasedbt 172 2月  15 13:58 unload_05.unl
-rw-rw-r--. 1 gbasedbt gbasedbt 182 2月  15 14:03 unload_06.unl
-rw-rw-r--. 1 gbasedbt gbasedbt 177 2月  15 14:08 unload_07.unl
-rw-rw-r--. 1 gbasedbt gbasedbt 228 2月  15 14:12 unload_08.unl
[gbasedbt@localhost data]$
[gbasedbt@localhost data]$ cat unload_08.unl
1...1...Bill...星期三 六月  1 1983...
2...1...John...星期三 十二月 25 1985...
3...2...Mary...星期六 十月 10 1987...
4...3...Kate...星期六 十一月 11 1989...
5...1...Will\
Smith...星期六 二月 28 1981...
[gbasedbt@localhost data]$

GBase 8s最多支持4个字符做为列分隔符，超过4个时分报错

[gbasedbt@localhost unload]$ ll
总用量 36
-rw-r--r--. 1 root root  70 2月  15 11:43 unload_01.sql
-rw-r--r--. 1 root root 179 2月  15 13:34 unload_02.sql
-rw-r--r--. 1 root root 145 2月  15 13:45 unload_03.sql
-rw-r--r--. 1 root root 148 2月  15 13:51 unload_04.sql
-rw-r--r--. 1 root root 148 2月  15 13:56 unload_05.sql
-rw-r--r--. 1 root root 148 2月  15 14:00 unload_06.sql
-rw-r--r--. 1 root root 150 2月  15 14:06 unload_07.sql
-rw-r--r--. 1 root root 150 2月  15 14:11 unload_08.sql
-rw-r--r--. 1 root root  80 2月  15 14:11 unload_09.sql
[gbasedbt@localhost unload]$
[gbasedbt@localhost unload]$ cat unload_09.sql
unload to '../data/unload_09.unl' delimiter '#...#'
select * from t_employee;
[gbasedbt@localhost unload]$
[gbasedbt@localhost unload]$ dbaccess mydb unload_09.sql
Your evaluation license will expire on 2024-02-14 00:00:00

Database selected.


32404: Invalid delimiter. Do not use '\\', hex digits or more than 4 characters.
Error in line 2
Near character position 0

Database closed.

[gbasedbt@localhost unload]$

load语法

load from 'file_name' [delimiter 'delimiter_string']
insert into table_name | synonym_name| view_name [(columns)];

file_name:导入的数据文件，可以为全路径或者相对路径文件名;
delimiter:定义数据列分隔符，为一个或多个字符，默认为'|';
columns:导入表的字段列表，默认为数据库表的字段的顺序;

加载单表数据，列分隔符为【,】

[gbasedbt@localhost unload]$ cd ..
[gbasedbt@localhost train]$ ls
data  dbload  export  external  import  load  onload  onunload  unload
[gbasedbt@localhost train]$ cd load/
[gbasedbt@localhost load]$ ll
总用量 32
-rw-r--r--. 1 root root 257 2月  15 14:23 load_01.sql
-rw-r--r--. 1 root root 405 2月  15 14:23 load_02.sql
-rw-r--r--. 1 root root 334 2月  15 14:23 load_03.sql
-rw-r--r--. 1 root root 337 2月  15 14:23 load_04.sql
-rw-r--r--. 1 root root 337 2月  15 14:23 load_05.sql
-rw-r--r--. 1 root root 337 2月  15 14:23 load_06.sql
-rw-r--r--. 1 root root 339 2月  15 14:23 load_07.sql
-rw-r--r--. 1 root root 339 2月  15 14:23 load_08.sql
[gbasedbt@localhost load]$ cat load_01.sql
load from '../data/unload_01.unl' delimiter ','
insert into t_dept_01;

!echo "******************************************"
!echo "# Query the table to validate load result."
!echo "******************************************"
select * from t_dept_01;
[gbasedbt@localhost load]$
[gbasedbt@localhost load]$ dbaccess mydb load_01.sql
Your evaluation license will expire on 2024-02-14 00:00:00

Database selected.


3 row(s) loaded.

******************************************
# Query the table to validate load result.
******************************************


   f_deptid f_deptname

          1 Dev
          2 Test
          3 Market

3 row(s) retrieved.


Database closed.

[gbasedbt@localhost load]$

尝试加载通过多表关联导出的数据。

加载单表数据，观察列分隔符与数据中字符冲突时的转义处理。

[gbasedbt@localhost load]$ cat load_03.sql
-- export CLIENT_LOCALE=en_US.UTF8
-- export GL_DATE="%d.%m.%iY"

load from '../data/unload_03.unl' delimiter '.'
insert into t_employee_03;

!echo "******************************************"
!echo "# Query the table to validate load result."
!echo "******************************************"
select * from t_employee_03;
[gbasedbt@localhost load]$
[gbasedbt@localhost load]$ export CLIENT_LOCALE=en_US.UTF8
[gbasedbt@localhost load]$ export GL_DATE="%d.%m.%iY"
[gbasedbt@localhost load]$
[gbasedbt@localhost load]$ dbaccess mydb load_03.sql
Your evaluation license will expire on 2024-02-14 00:00:00

Database selected.


5 row(s) loaded.

******************************************
# Query the table to validate load result.
******************************************



f_employeeid    1
f_deptid        1
f_employeename  Bill
f_birthdate     01.06.1983

f_employeeid    2
f_deptid        1
f_employeename  John
f_birthdate     25.12.1985

f_employeeid    3
f_deptid        2
f_employeename  Mary
f_birthdate     10.10.1987

f_employeeid    4
f_deptid        3
f_employeename  Kate
f_birthdate     11.11.1989

f_employeeid    5
f_deptid        1
f_employeename  Will
Smith
f_birthdate     28.02.1981

5 row(s) retrieved.


Database closed.

[gbasedbt@localhost load]$

加载单表数据，观察列分隔符与数据中字符冲突时的转义处理。

[gbasedbt@localhost load]$ cat load_04.sql
-- export CLIENT_LOCALE=en_US.UTF8
-- export GL_DATE="%d.%m.%iY"

load from '../data/unload_04.unl' delimiter '....'
insert into t_employee_04;

!echo "******************************************"
!echo "# Query the table to validate load result."
!echo "******************************************"
select * from t_employee_04;
[gbasedbt@localhost load]$
[gbasedbt@localhost load]$ export CLIENT_LOCALE=en_US.UTF8
[gbasedbt@localhost load]$ export GL_DATE="%d.%m.%iY"
[gbasedbt@localhost load]$
[gbasedbt@localhost load]$ dbaccess mydb load_04.sql
Your evaluation license will expire on 2024-02-14 00:00:00

Database selected.


5 row(s) loaded.

******************************************
# Query the table to validate load result.
******************************************



f_employeeid    1
f_deptid        1
f_employeename  Bill
f_birthdate     01.06.1983

f_employeeid    2
f_deptid        1
f_employeename  John
f_birthdate     25.12.1985

f_employeeid    3
f_deptid        2
f_employeename  Mary
f_birthdate     10.10.1987

f_employeeid    4
f_deptid        3
f_employeename  Kate
f_birthdate     11.11.1989

f_employeeid    5
f_deptid        1
f_employeename  Will
Smith
f_birthdate     28.02.1981

5 row(s) retrieved.


Database closed.

[gbasedbt@localhost load]$

加载单表数据，观察列分隔符与数据中字符冲突时的转义处理。

[gbasedbt@localhost load]$ cat load_05.sql
-- export CLIENT_LOCALE=en_US.UTF8
-- export GL_DATE="%d.%m.%iY"

load from '../data/unload_05.unl' delimiter '#...'
insert into t_employee_05;

!echo "******************************************"
!echo "# Query the table to validate load result."
!echo "******************************************"
select * from t_employee_05;
[gbasedbt@localhost load]$
[gbasedbt@localhost load]$ export CLIENT_LOCALE=en_US.UTF8
[gbasedbt@localhost load]$ export GL_DATE="%d.%m.%iY"
[gbasedbt@localhost load]$
[gbasedbt@localhost load]$ dbaccess mydb load_05.sql
Your evaluation license will expire on 2024-02-14 00:00:00

Database selected.


5 row(s) loaded.

******************************************
# Query the table to validate load result.
******************************************



f_employeeid    1
f_deptid        1
f_employeename  Bill
f_birthdate     01.06.1983

f_employeeid    2
f_deptid        1
f_employeename  John
f_birthdate     25.12.1985

f_employeeid    3
f_deptid        2
f_employeename  Mary
f_birthdate     10.10.1987

f_employeeid    4
f_deptid        3
f_employeename  Kate
f_birthdate     11.11.1989

f_employeeid    5
f_deptid        1
f_employeename  Will
Smith
f_birthdate     28.02.1981

5 row(s) retrieved.


Database closed.

[gbasedbt@localhost load]$

加载单表数据，观察列分隔符与数据中字符冲突时的转义处理。

[gbasedbt@localhost load]$ cat load_06.sql
-- export CLIENT_LOCALE=en_US.UTF8
-- export GL_DATE="%d.%m.%iY"

load from '../data/unload_06.unl' delimiter '...#'
insert into t_employee_06;

!echo "******************************************"
!echo "# Query the table to validate load result."
!echo "******************************************"
select * from t_employee_06;
[gbasedbt@localhost load]$
[gbasedbt@localhost load]$ export CLIENT_LOCALE=en_US.UTF8
[gbasedbt@localhost load]$ export GL_DATE="%d.%m.%iY"
[gbasedbt@localhost load]$
[gbasedbt@localhost load]$ dbaccess mydb load_06.sql
Your evaluation license will expire on 2024-02-14 00:00:00

Database selected.


5 row(s) loaded.

******************************************
# Query the table to validate load result.
******************************************



f_employeeid    1
f_deptid        1
f_employeename  Bill
f_birthdate     01.06.1983

f_employeeid    2
f_deptid        1
f_employeename  John
f_birthdate     25.12.1985

f_employeeid    3
f_deptid        2
f_employeename  Mary
f_birthdate     10.10.1987

f_employeeid    4
f_deptid        3
f_employeename  Kate
f_birthdate     11.11.1989

f_employeeid    5
f_deptid        1
f_employeename  Will
Smith
f_birthdate     28.02.1981

5 row(s) retrieved.


Database closed.

[gbasedbt@localhost load]$

加载单表数据，熟悉日期字段数据的格式控制。

[gbasedbt@localhost load]$ cat load_07.sql
-- export CLIENT_LOCALE=en_US.UTF8
-- export GL_DATE="%a %b %e %iY"

load from '../data/unload_07.unl' delimiter '.*.'
insert into t_employee_07;

!echo "******************************************"
!echo "# Query the table to validate load result."
!echo "******************************************"
select * from t_employee_07;
[gbasedbt@localhost load]$
[gbasedbt@localhost load]$ export CLIENT_LOCALE=en_US.UTF8
[gbasedbt@localhost load]$ export GL_DATE="%a %b %e %iY"
[gbasedbt@localhost load]$
[gbasedbt@localhost load]$ dbaccess mydb load_07.sql
Your evaluation license will expire on 2024-02-14 00:00:00

Database selected.


5 row(s) loaded.

******************************************
# Query the table to validate load result.
******************************************



f_employeeid    1
f_deptid        1
f_employeename  Bill
f_birthdate     Wed Jun  1 1983

f_employeeid    2
f_deptid        1
f_employeename  John
f_birthdate     Wed Dec 25 1985

f_employeeid    3
f_deptid        2
f_employeename  Mary
f_birthdate     Sat Oct 10 1987

f_employeeid    4
f_deptid        3
f_employeename  Kate
f_birthdate     Sat Nov 11 1989

f_employeeid    5
f_deptid        1
f_employeename  Will
Smith
f_birthdate     Sat Feb 28 1981

5 row(s) retrieved.


Database closed.

[gbasedbt@localhost load]$

加载单表数据，熟悉日期字段数据的格式控制。

[gbasedbt@localhost load]$ cat load_08.sql
-- export CLIENT_LOCALE=zh_CN.UTF8
-- export GL_DATE="%A %B %e %iY"

load from '../data/unload_08.unl' delimiter '...'
insert into t_employee_08;

!echo "******************************************"
!echo "# Query the table to validate load result."
!echo "******************************************"
select * from t_employee_08;
[gbasedbt@localhost load]$
[gbasedbt@localhost load]$ export CLIENT_LOCALE=zh_CN.UTF8
[gbasedbt@localhost load]$ export GL_DATE="%A %B %e %iY"
[gbasedbt@localhost load]$
[gbasedbt@localhost load]$ dbaccess mydb load_08.sql
Your evaluation license will expire on 2024-02-14 00:00:00

Database selected.


5 row(s) loaded.

******************************************
# Query the table to validate load result.
******************************************



f_employeeid    1
f_deptid        1
f_employeename  Bill
f_birthdate     星期三 六月  1 1983

f_employeeid    2
f_deptid        1
f_employeename  John
f_birthdate     星期三 十二月 25 1985

f_employeeid    3
f_deptid        2
f_employeename  Mary
f_birthdate     星期六 十月 10 1987

f_employeeid    4
f_deptid        3
f_employeename  Kate
f_birthdate     星期六 十一月 11 1989

f_employeeid    5
f_deptid        1
f_employeename  Will
Smith
f_birthdate     星期六 二月 28 1981

5 row(s) retrieved.


Database closed.

[gbasedbt@localhost load]$

dbload

dbload语法

dbload [-d db_name] [-c cmd_file] [-l log_file] [-e errors] [-n num_rows]
	[-i i_skip] [-s] [-p] [-r | -k] [-X]

db_name:指定要加载数据的数据库名称
cmd_file:指定包含加载命令的文件路径
log_file:指定日志文件，其中记录不能正确加载的数据
errors:指定最多可以有多少行数据错误，超过指定的行数后，加载任务失败
num_rows:指定多少条记录执行一次提交
s:指定只做语法检查，不进行实际的数据加载
i_skip:指定跳过多少行数据，再进行加载，可用于跳过数据中的标题部分
r:加载数据时，不对表加锁
k:加载数据时，对表加排它锁

command file语法

file 'file_name' delimiter 'delimiter_string' nfields
insert into table_name [(col1,col2,...) values (f01,f02,…)]

file_name:指定要导入的文件名称，可以为绝对路径和相对路径。
delimiter_string:数据文件使用的列分隔符。
nfields:数据文件中，每行的列数。
table_name:要导入的表的名称。
col1,col2...:表的列名称，多个列用逗号分隔。
f01,f02...:数据文件中的列数据编号，从01开始编号，多个编号用逗号分隔。

使用dbload导入数据文件与表定义格式不一致的数据

[gbasedbt@localhost data]$ cat unload_02.unl
1|Bill|1|Dev|01.06.1983|
2|John|1|Dev|25.12.1985|
3|Mary|2|Test|10.10.1987|
4|Kate|3|Market|11.11.1989|
5|Will\
Smith|1|Dev|28.02.1981|
[gbasedbt@localhost data]$

[gbasedbt@localhost dbload]$ ll
总用量 8
-rw-r--r--. 1 root root 182 2月  15 15:42 dept_employee.ctl
-rw-r--r--. 1 root root 171 2月  15 15:38 employee.ctl
[gbasedbt@localhost dbload]$ cat employee.ctl
file '/home/gbasedbt/train/data/unload_02.unl' delimiter '|' 5;
insert into t_employee_02(f_employeeid, f_employeename, f_deptid, f_birthdate) values(f01, f02, f03, f05);
[gbasedbt@localhost dbload]$
[gbasedbt@localhost dbload]$ export CLIENT_LOCALE=en_US.UTF8
[gbasedbt@localhost dbload]$ export GL_DATE="%d.%m.%iY"
[gbasedbt@localhost dbload]$
[gbasedbt@localhost dbload]$ dbload -d mydb -c employee.ctl -l dbload.log -e 10 -n 100
Your evaluation license will expire on 2024-02-14 00:00:00

DBLOAD Load Utility           GBASE-SQL Version 12.10.FC4G1TL

Table t_employee_02 had 5 row(s) loaded into it.
[gbasedbt@localhost dbload]$

[gbasedbt@localhost data]$ dbaccess - -
Your evaluation license will expire on 2024-02-14 00:00:00
> database mydb;

Database selected.

> select * from t_employee_02;



f_employeeid    1
f_deptid        1
f_employeename  Bill
f_birthdate     01.06.1983

f_employeeid    2
f_deptid        1
f_employeename  John
f_birthdate     25.12.1985

f_employeeid    3
f_deptid        2
f_employeename  Mary
f_birthdate     10.10.1987

f_employeeid    4
f_deptid        3
f_employeename  Kate
f_birthdate     11.11.1989

f_employeeid    5
f_deptid        1
f_employeename  Will
Smith
f_birthdate     28.02.1981

5 row(s) retrieved.

>

使用dbload一次导入多个表数据的方法。

[gbasedbt@localhost dbload]$ ll
总用量 8
-rw-rw-r--. 1 gbasedbt gbasedbt   0 2月  15 15:45 dbload.log
-rw-r--r--. 1 root     root     182 2月  15 15:42 dept_employee.ctl
-rw-r--r--. 1 root     root     171 2月  15 15:38 employee.ctl
[gbasedbt@localhost dbload]$ cat dept_employee.ctl
file '/home/gbasedbt/train/data/unload_01.unl' delimiter ',' 2;
insert into t_dept_00;

file '/home/gbasedbt/train/data/unload_03.unl' delimiter '.' 4;
insert into t_employee_00;[gbasedbt@localhost dbload]$
[gbasedbt@localhost dbload]$ export CLIENT_LOCALE=en_US.UTF8
[gbasedbt@localhost dbload]$
[gbasedbt@localhost dbload]$ export GL_DATE="%d.%m.%iY"
[gbasedbt@localhost dbload]$
[gbasedbt@localhost dbload]$ dbload -d mydb -c dept_employee.ctl -l dbload.log -e 10 -n 100
Your evaluation license will expire on 2024-02-14 00:00:00

DBLOAD Load Utility           GBASE-SQL Version 12.10.FC4G1TL

Table t_dept_00 had 3 row(s) loaded into it.

Table t_employee_00 had 5 row(s) loaded into it.
[gbasedbt@localhost dbload]$
[gbasedbt@localhost dbload]$ echo "select * from t_dept_00" | dbaccess mydb
Your evaluation license will expire on 2024-02-14 00:00:00

Database selected.



   f_deptid f_deptname

          1 Dev
          2 Test
          3 Market

3 row(s) retrieved.



Database closed.

[gbasedbt@localhost dbload]$ echo "select * from t_employee_00" | dbaccess mydb
Your evaluation license will expire on 2024-02-14 00:00:00

Database selected.




f_employeeid    1
f_deptid        1
f_employeename  Bill
f_birthdate     01.06.1983

f_employeeid    2
f_deptid        1
f_employeename  John
f_birthdate     25.12.1985

f_employeeid    3
f_deptid        2
f_employeename  Mary
f_birthdate     10.10.1987

f_employeeid    4
f_deptid        3
f_employeename  Kate
f_birthdate     11.11.1989

f_employeeid    5
f_deptid        1
f_employeename  Will
Smith
f_birthdate     28.02.1981

5 row(s) retrieved.



Database closed.

[gbasedbt@localhost dbload]$

dbexport/dbimport

dbexport语法

dbexport <database> [-X] [-c] [-q] [-d] [-ss [-si]] [-ext]
  [{ -o <dir> | -t <tapedev> -b <blksz> -s <tapesz> [-f <sql-command-file>] }] [-nw]
  [-no-data-tables[=table name{,table name}]]
  [-no-data-tables-accessmethods[=access method name{,access method name}]]

database:指定要导出全部表数据的数据库名称
no-data-tables:指定哪些表不导出数据
o:指定导出的数据保存在哪个操作系统的目录中
ss:测试是否对分片表有特殊作用

导出数据库

[gbasedbt@localhost export]$ pwd
/home/gbasedbt/train/export
[gbasedbt@localhost export]$ ll
总用量 0
[gbasedbt@localhost export]$ dbexport mydb
Your evaluation license will expire on 2024-02-14 00:00:00
{ DATABASE mydb  delimiter | }

grant dba to "gbasedbt";







drop cast (varchar2 as lvarchar(2048));
create implicit cast (varchar2 as lvarchar(2048));

drop cast (lvarchar(2048) as varchar2);
create implicit cast (lvarchar(2048) as varchar2);

drop cast (xmltype as lvarchar(2048));
create implicit cast (xmltype as lvarchar(2048));

drop cast (lvarchar(2048) as xmltype);
create implicit cast (lvarchar(2048) as xmltype);

drop cast (dbmsoutput_linesarray as lvarchar(2048));
create implicit cast (dbmsoutput_linesarray as lvarchar(2048));

drop cast (lvarchar(2048) as dbmsoutput_linesarray);
create implicit cast (lvarchar(2048) as dbmsoutput_linesarray);






{ TABLE "gbasedbt".t_dept_00 row size = 56 number of columns = 2 index size = 0 ccolnum = 0
 commcol = f_deptid,f_deptname }

{ unload file name = t_dep00100.unl number of rows = 3 }


create table "gbasedbt".t_dept_00
  (
    f_deptid integer,
    f_deptname varchar(50)
  );

revoke all on "gbasedbt".t_dept_00 from "public" as "gbasedbt";

{ TABLE "gbasedbt".t_dept_01 row size = 56 number of columns = 2 index size = 0 ccolnum = 0
 commcol = f_deptid,f_deptname }

{ unload file name = t_dep00101.unl number of rows = 3 }


create table "gbasedbt".t_dept_01
  (
    f_deptid integer,
    f_deptname varchar(50)
  );

revoke all on "gbasedbt".t_dept_01 from "public" as "gbasedbt";

{ TABLE "gbasedbt".t_employee_00 row size = 64 number of columns = 4 index size = 0 ccolnum = 0
 commcol = f_employeeid,f_deptid,f_employeename,f_birthdate }

{ unload file name = t_emp00102.unl number of rows = 5 }


create table "gbasedbt".t_employee_00
  (
    f_employeeid integer,
    f_deptid integer,
    f_employeename varchar(50),
    f_birthdate date
  );

revoke all on "gbasedbt".t_employee_00 from "public" as "gbasedbt";

{ TABLE "gbasedbt".t_employee_01 row size = 64 number of columns = 4 index size = 0 ccolnum = 0
 commcol = f_employeeid,f_deptid,f_employeename,f_birthdate }

{ unload file name = t_emp00103.unl number of rows = 0 }


create table "gbasedbt".t_employee_01
  (
    f_employeeid integer,
    f_deptid integer,
    f_employeename varchar(50),
    f_birthdate date
  );

revoke all on "gbasedbt".t_employee_01 from "public" as "gbasedbt";

{ TABLE "gbasedbt".t_employee_03 row size = 64 number of columns = 4 index size = 0 ccolnum = 0
 commcol = f_employeeid,f_deptid,f_employeename,f_birthdate }

{ unload file name = t_emp00105.unl number of rows = 5 }


create table "gbasedbt".t_employee_03
  (
    f_employeeid integer,
    f_deptid integer,
    f_employeename varchar(50),
    f_birthdate date
  );

revoke all on "gbasedbt".t_employee_03 from "public" as "gbasedbt";

{ TABLE "gbasedbt".t_employee_04 row size = 64 number of columns = 4 index size = 0 ccolnum = 0
 commcol = f_employeeid,f_deptid,f_employeename,f_birthdate }

{ unload file name = t_emp00106.unl number of rows = 5 }


create table "gbasedbt".t_employee_04
  (
    f_employeeid integer,
    f_deptid integer,
    f_employeename varchar(50),
    f_birthdate date
  );

revoke all on "gbasedbt".t_employee_04 from "public" as "gbasedbt";

{ TABLE "gbasedbt".t_employee_05 row size = 64 number of columns = 4 index size = 0 ccolnum = 0
 commcol = f_employeeid,f_deptid,f_employeename,f_birthdate }

{ unload file name = t_emp00107.unl number of rows = 5 }


create table "gbasedbt".t_employee_05
  (
    f_employeeid integer,
    f_deptid integer,
    f_employeename varchar(50),
    f_birthdate date
  );

revoke all on "gbasedbt".t_employee_05 from "public" as "gbasedbt";

{ TABLE "gbasedbt".t_employee_06 row size = 64 number of columns = 4 index size = 0 ccolnum = 0
 commcol = f_employeeid,f_deptid,f_employeename,f_birthdate }

{ unload file name = t_emp00108.unl number of rows = 5 }


create table "gbasedbt".t_employee_06
  (
    f_employeeid integer,
    f_deptid integer,
    f_employeename varchar(50),
    f_birthdate date
  );

revoke all on "gbasedbt".t_employee_06 from "public" as "gbasedbt";

{ TABLE "gbasedbt".t_employee_07 row size = 64 number of columns = 4 index size = 0 ccolnum = 0
 commcol = f_employeeid,f_deptid,f_employeename,f_birthdate }

{ unload file name = t_emp00109.unl number of rows = 5 }


create table "gbasedbt".t_employee_07
  (
    f_employeeid integer,
    f_deptid integer,
    f_employeename varchar(50),
    f_birthdate date
  );

revoke all on "gbasedbt".t_employee_07 from "public" as "gbasedbt";

{ TABLE "gbasedbt".t_employee_08 row size = 64 number of columns = 4 index size = 0 ccolnum = 0
 commcol = f_employeeid,f_deptid,f_employeename,f_birthdate }

{ unload file name = t_emp00110.unl number of rows = 5 }


create table "gbasedbt".t_employee_08
  (
    f_employeeid integer,
    f_deptid integer,
    f_employeename varchar(50),
    f_birthdate date
  );

revoke all on "gbasedbt".t_employee_08 from "public" as "gbasedbt";

{ TABLE "gbasedbt".t_employee_09 row size = 64 number of columns = 4 index size = 0 ccolnum = 0
 commcol = f_employeeid,f_deptid,f_employeename,f_birthdate }

{ unload file name = t_emp00111.unl number of rows = 0 }


create table "gbasedbt".t_employee_09
  (
    f_employeeid integer,
    f_deptid integer,
    f_employeename varchar(50),
    f_birthdate date
  );

revoke all on "gbasedbt".t_employee_09 from "public" as "gbasedbt";

{ TABLE "gbasedbt".t_dept row size = 56 number of columns = 2 index size = 0 ccolnum = 0
 commcol = f_deptid,f_deptname }

{ unload file name = t_dep00115.unl number of rows = 3 }


create table "gbasedbt".t_dept
  (
    f_deptid integer,
    f_deptname varchar(50)
  );

revoke all on "gbasedbt".t_dept from "public" as "gbasedbt";

{ TABLE "gbasedbt".t_employee row size = 64 number of columns = 4 index size = 0 ccolnum = 0
 commcol = f_employeeid,f_deptid,f_employeename,f_birthdate }

{ unload file name = t_emp00116.unl number of rows = 5 }


create table "gbasedbt".t_employee
  (
    f_employeeid integer,
    f_deptid integer,
    f_employeename varchar(50),
    f_birthdate date
  );

revoke all on "gbasedbt".t_employee from "public" as "gbasedbt";

{ TABLE "gbasedbt".t_employee_02 row size = 64 number of columns = 4 index size = 0 ccolnum = 0
 commcol = f_employeeid,f_deptid,f_employeename,f_birthdate }

{ unload file name = t_emp00117.unl number of rows = 5 }


create table "gbasedbt".t_employee_02
  (
    f_employeeid integer,
    f_deptid integer,
    f_employeename varchar(50),
    f_birthdate date
  );

revoke all on "gbasedbt".t_employee_02 from "public" as "gbasedbt";




grant select on "gbasedbt".t_dept_00 to "public" as "gbasedbt";
grant update on "gbasedbt".t_dept_00 to "public" as "gbasedbt";
grant insert on "gbasedbt".t_dept_00 to "public" as "gbasedbt";
grant delete on "gbasedbt".t_dept_00 to "public" as "gbasedbt";
grant index on "gbasedbt".t_dept_00 to "public" as "gbasedbt";
grant select on "gbasedbt".t_dept_01 to "public" as "gbasedbt";
grant update on "gbasedbt".t_dept_01 to "public" as "gbasedbt";
grant insert on "gbasedbt".t_dept_01 to "public" as "gbasedbt";
grant delete on "gbasedbt".t_dept_01 to "public" as "gbasedbt";
grant index on "gbasedbt".t_dept_01 to "public" as "gbasedbt";
grant select on "gbasedbt".t_employee_00 to "public" as "gbasedbt";
grant update on "gbasedbt".t_employee_00 to "public" as "gbasedbt";
grant insert on "gbasedbt".t_employee_00 to "public" as "gbasedbt";
grant delete on "gbasedbt".t_employee_00 to "public" as "gbasedbt";
grant index on "gbasedbt".t_employee_00 to "public" as "gbasedbt";
grant select on "gbasedbt".t_employee_01 to "public" as "gbasedbt";
grant update on "gbasedbt".t_employee_01 to "public" as "gbasedbt";
grant insert on "gbasedbt".t_employee_01 to "public" as "gbasedbt";
grant delete on "gbasedbt".t_employee_01 to "public" as "gbasedbt";
grant index on "gbasedbt".t_employee_01 to "public" as "gbasedbt";
grant select on "gbasedbt".t_employee_03 to "public" as "gbasedbt";
grant update on "gbasedbt".t_employee_03 to "public" as "gbasedbt";
grant insert on "gbasedbt".t_employee_03 to "public" as "gbasedbt";
grant delete on "gbasedbt".t_employee_03 to "public" as "gbasedbt";
grant index on "gbasedbt".t_employee_03 to "public" as "gbasedbt";
grant select on "gbasedbt".t_employee_04 to "public" as "gbasedbt";
grant update on "gbasedbt".t_employee_04 to "public" as "gbasedbt";
grant insert on "gbasedbt".t_employee_04 to "public" as "gbasedbt";
grant delete on "gbasedbt".t_employee_04 to "public" as "gbasedbt";
grant index on "gbasedbt".t_employee_04 to "public" as "gbasedbt";
grant select on "gbasedbt".t_employee_05 to "public" as "gbasedbt";
grant update on "gbasedbt".t_employee_05 to "public" as "gbasedbt";
grant insert on "gbasedbt".t_employee_05 to "public" as "gbasedbt";
grant delete on "gbasedbt".t_employee_05 to "public" as "gbasedbt";
grant index on "gbasedbt".t_employee_05 to "public" as "gbasedbt";
grant select on "gbasedbt".t_employee_06 to "public" as "gbasedbt";
grant update on "gbasedbt".t_employee_06 to "public" as "gbasedbt";
grant insert on "gbasedbt".t_employee_06 to "public" as "gbasedbt";
grant delete on "gbasedbt".t_employee_06 to "public" as "gbasedbt";
grant index on "gbasedbt".t_employee_06 to "public" as "gbasedbt";
grant select on "gbasedbt".t_employee_07 to "public" as "gbasedbt";
grant update on "gbasedbt".t_employee_07 to "public" as "gbasedbt";
grant insert on "gbasedbt".t_employee_07 to "public" as "gbasedbt";
grant delete on "gbasedbt".t_employee_07 to "public" as "gbasedbt";
grant index on "gbasedbt".t_employee_07 to "public" as "gbasedbt";
grant select on "gbasedbt".t_employee_08 to "public" as "gbasedbt";
grant update on "gbasedbt".t_employee_08 to "public" as "gbasedbt";
grant insert on "gbasedbt".t_employee_08 to "public" as "gbasedbt";
grant delete on "gbasedbt".t_employee_08 to "public" as "gbasedbt";
grant index on "gbasedbt".t_employee_08 to "public" as "gbasedbt";
grant select on "gbasedbt".t_employee_09 to "public" as "gbasedbt";
grant update on "gbasedbt".t_employee_09 to "public" as "gbasedbt";
grant insert on "gbasedbt".t_employee_09 to "public" as "gbasedbt";
grant delete on "gbasedbt".t_employee_09 to "public" as "gbasedbt";
grant index on "gbasedbt".t_employee_09 to "public" as "gbasedbt";
grant select on "gbasedbt".t_dept to "public" as "gbasedbt";
grant update on "gbasedbt".t_dept to "public" as "gbasedbt";
grant insert on "gbasedbt".t_dept to "public" as "gbasedbt";
grant delete on "gbasedbt".t_dept to "public" as "gbasedbt";
grant index on "gbasedbt".t_dept to "public" as "gbasedbt";
grant select on "gbasedbt".t_employee to "public" as "gbasedbt";
grant update on "gbasedbt".t_employee to "public" as "gbasedbt";
grant insert on "gbasedbt".t_employee to "public" as "gbasedbt";
grant delete on "gbasedbt".t_employee to "public" as "gbasedbt";
grant index on "gbasedbt".t_employee to "public" as "gbasedbt";
grant select on "gbasedbt".t_employee_02 to "public" as "gbasedbt";
grant update on "gbasedbt".t_employee_02 to "public" as "gbasedbt";
grant insert on "gbasedbt".t_employee_02 to "public" as "gbasedbt";
grant delete on "gbasedbt".t_employee_02 to "public" as "gbasedbt";
grant index on "gbasedbt".t_employee_02 to "public" as "gbasedbt";















set environment sqlmode 'oracle';
set environment sqlmode 'gbase';

revoke usage on language SPL from public ;

grant usage on language SPL to public ;


















dbexport completed

[gbasedbt@localhost export]$ ll
总用量 16
-rw-rw-r--. 1 gbasedbt gbasedbt 11355 2月  15 16:04 dbexport.out
drwxr-xr-x. 2 gbasedbt gbasedbt  4096 2月  15 16:04 mydb.exp
[gbasedbt@localhost export]$ cd mydb.exp/
[gbasedbt@localhost mydb.exp]$ ll
总用量 64
-rw-rw-r--. 1 gbasedbt gbasedbt    67 2月  15 16:04 mydb_ora.sql
-rw-rw-r--. 1 gbasedbt gbasedbt 11268 2月  15 16:04 mydb.sql
-rw-rw-r--. 1 gbasedbt gbasedbt    25 2月  15 16:04 t_dep00100.unl
-rw-rw-r--. 1 gbasedbt gbasedbt    25 2月  15 16:04 t_dep00101.unl
-rw-rw-r--. 1 gbasedbt gbasedbt    25 2月  15 16:04 t_dep00115.unl
-rw-rw-r--. 1 gbasedbt gbasedbt   112 2月  15 16:04 t_emp00102.unl
-rw-rw-r--. 1 gbasedbt gbasedbt     0 2月  15 16:04 t_emp00103.unl
-rw-rw-r--. 1 gbasedbt gbasedbt   112 2月  15 16:04 t_emp00105.unl
-rw-rw-r--. 1 gbasedbt gbasedbt   112 2月  15 16:04 t_emp00106.unl
-rw-rw-r--. 1 gbasedbt gbasedbt   112 2月  15 16:04 t_emp00107.unl
-rw-rw-r--. 1 gbasedbt gbasedbt   112 2月  15 16:04 t_emp00108.unl
-rw-rw-r--. 1 gbasedbt gbasedbt   112 2月  15 16:04 t_emp00109.unl
-rw-rw-r--. 1 gbasedbt gbasedbt   112 2月  15 16:04 t_emp00110.unl
-rw-rw-r--. 1 gbasedbt gbasedbt     0 2月  15 16:04 t_emp00111.unl
-rw-rw-r--. 1 gbasedbt gbasedbt   112 2月  15 16:04 t_emp00116.unl
-rw-rw-r--. 1 gbasedbt gbasedbt   112 2月  15 16:04 t_emp00117.unl
[gbasedbt@localhost mydb.exp]$

dbimport语法

dbimport <database> [-X] [-c] [-q] [-ext] [-d <dbspace>]
  [-l [{ buffered }] [-ansi]] [-ci] [-nv] [-D]
  [{ -i <dir> | -t <tapedev> [ -b <blksz> -s <tapesz> ] [-f <script-file>] }]

database:指定要导入全部表数据的数据库名称
d:指定数据库导入到哪个数据库空间中
nv:不对引用约束进行校验
i:指定要导入数据所在的目录

导入数据库

[gbasedbt@localhost export]$ dbaccess - -
Your evaluation license will expire on 2024-02-14 00:00:00
> rename database mydb to mydb2;

Database renamed.

>

[gbasedbt@localhost export]$ ll
总用量 16
-rw-rw-r--. 1 gbasedbt gbasedbt 11355 2月  15 16:04 dbexport.out
drwxr-xr-x. 2 gbasedbt gbasedbt  4096 2月  15 16:14 mydb.exp
[gbasedbt@localhost export]$ dbimport mydb -d datadbs2
Your evaluation license will expire on 2024-02-14 00:00:00
{ DATABASE mydb  delimiter | }

grant dba to "gbasedbt";







drop cast (varchar2 as lvarchar(2048));
create implicit cast (varchar2 as lvarchar(2048));

drop cast (lvarchar(2048) as varchar2);
create implicit cast (lvarchar(2048) as varchar2);

drop cast (xmltype as lvarchar(2048));
create implicit cast (xmltype as lvarchar(2048));

drop cast (lvarchar(2048) as xmltype);
create implicit cast (lvarchar(2048) as xmltype);

drop cast (dbmsoutput_linesarray as lvarchar(2048));
create implicit cast (dbmsoutput_linesarray as lvarchar(2048));

drop cast (lvarchar(2048) as dbmsoutput_linesarray);
create implicit cast (lvarchar(2048) as dbmsoutput_linesarray);






{ TABLE "gbasedbt".t_dept_00 row size = 56 number of columns = 2 index size = 0 ccolnum = 0
 commcol = f_deptid,f_deptname }

{ unload file name = t_dep00100.unl number of rows = 3 }


create table "gbasedbt".t_dept_00
  (
    f_deptid integer,
    f_deptname varchar(50)
  );

revoke all on "gbasedbt".t_dept_00 from "public" as "gbasedbt";

{ TABLE "gbasedbt".t_dept_01 row size = 56 number of columns = 2 index size = 0 ccolnum = 0
 commcol = f_deptid,f_deptname }

{ unload file name = t_dep00101.unl number of rows = 3 }


create table "gbasedbt".t_dept_01
  (
    f_deptid integer,
    f_deptname varchar(50)
  );

revoke all on "gbasedbt".t_dept_01 from "public" as "gbasedbt";

{ TABLE "gbasedbt".t_employee_00 row size = 64 number of columns = 4 index size = 0 ccolnum = 0
 commcol = f_employeeid,f_deptid,f_employeename,f_birthdate }

{ unload file name = t_emp00102.unl number of rows = 5 }


create table "gbasedbt".t_employee_00
  (
    f_employeeid integer,
    f_deptid integer,
    f_employeename varchar(50),
    f_birthdate date
  );

revoke all on "gbasedbt".t_employee_00 from "public" as "gbasedbt";

{ TABLE "gbasedbt".t_employee_01 row size = 64 number of columns = 4 index size = 0 ccolnum = 0
 commcol = f_employeeid,f_deptid,f_employeename,f_birthdate }

{ unload file name = t_emp00103.unl number of rows = 0 }


create table "gbasedbt".t_employee_01
  (
    f_employeeid integer,
    f_deptid integer,
    f_employeename varchar(50),
    f_birthdate date
  );

revoke all on "gbasedbt".t_employee_01 from "public" as "gbasedbt";

{ TABLE "gbasedbt".t_employee_03 row size = 64 number of columns = 4 index size = 0 ccolnum = 0
 commcol = f_employeeid,f_deptid,f_employeename,f_birthdate }

{ unload file name = t_emp00105.unl number of rows = 5 }


create table "gbasedbt".t_employee_03
  (
    f_employeeid integer,
    f_deptid integer,
    f_employeename varchar(50),
    f_birthdate date
  );

revoke all on "gbasedbt".t_employee_03 from "public" as "gbasedbt";

{ TABLE "gbasedbt".t_employee_04 row size = 64 number of columns = 4 index size = 0 ccolnum = 0
 commcol = f_employeeid,f_deptid,f_employeename,f_birthdate }

{ unload file name = t_emp00106.unl number of rows = 5 }


create table "gbasedbt".t_employee_04
  (
    f_employeeid integer,
    f_deptid integer,
    f_employeename varchar(50),
    f_birthdate date
  );

revoke all on "gbasedbt".t_employee_04 from "public" as "gbasedbt";

{ TABLE "gbasedbt".t_employee_05 row size = 64 number of columns = 4 index size = 0 ccolnum = 0
 commcol = f_employeeid,f_deptid,f_employeename,f_birthdate }

{ unload file name = t_emp00107.unl number of rows = 5 }


create table "gbasedbt".t_employee_05
  (
    f_employeeid integer,
    f_deptid integer,
    f_employeename varchar(50),
    f_birthdate date
  );

revoke all on "gbasedbt".t_employee_05 from "public" as "gbasedbt";

{ TABLE "gbasedbt".t_employee_06 row size = 64 number of columns = 4 index size = 0 ccolnum = 0
 commcol = f_employeeid,f_deptid,f_employeename,f_birthdate }

{ unload file name = t_emp00108.unl number of rows = 5 }


create table "gbasedbt".t_employee_06
  (
    f_employeeid integer,
    f_deptid integer,
    f_employeename varchar(50),
    f_birthdate date
  );

revoke all on "gbasedbt".t_employee_06 from "public" as "gbasedbt";

{ TABLE "gbasedbt".t_employee_07 row size = 64 number of columns = 4 index size = 0 ccolnum = 0
 commcol = f_employeeid,f_deptid,f_employeename,f_birthdate }

{ unload file name = t_emp00109.unl number of rows = 5 }


create table "gbasedbt".t_employee_07
  (
    f_employeeid integer,
    f_deptid integer,
    f_employeename varchar(50),
    f_birthdate date
  );

revoke all on "gbasedbt".t_employee_07 from "public" as "gbasedbt";

{ TABLE "gbasedbt".t_employee_08 row size = 64 number of columns = 4 index size = 0 ccolnum = 0
 commcol = f_employeeid,f_deptid,f_employeename,f_birthdate }

{ unload file name = t_emp00110.unl number of rows = 5 }


create table "gbasedbt".t_employee_08
  (
    f_employeeid integer,
    f_deptid integer,
    f_employeename varchar(50),
    f_birthdate date
  );

revoke all on "gbasedbt".t_employee_08 from "public" as "gbasedbt";

{ TABLE "gbasedbt".t_employee_09 row size = 64 number of columns = 4 index size = 0 ccolnum = 0
 commcol = f_employeeid,f_deptid,f_employeename,f_birthdate }

{ unload file name = t_emp00111.unl number of rows = 0 }


create table "gbasedbt".t_employee_09
  (
    f_employeeid integer,
    f_deptid integer,
    f_employeename varchar(50),
    f_birthdate date
  );

revoke all on "gbasedbt".t_employee_09 from "public" as "gbasedbt";

{ TABLE "gbasedbt".t_dept row size = 56 number of columns = 2 index size = 0 ccolnum = 0
 commcol = f_deptid,f_deptname }

{ unload file name = t_dep00115.unl number of rows = 3 }


create table "gbasedbt".t_dept
  (
    f_deptid integer,
    f_deptname varchar(50)
  );

revoke all on "gbasedbt".t_dept from "public" as "gbasedbt";

{ TABLE "gbasedbt".t_employee row size = 64 number of columns = 4 index size = 0 ccolnum = 0
 commcol = f_employeeid,f_deptid,f_employeename,f_birthdate }

{ unload file name = t_emp00116.unl number of rows = 5 }


create table "gbasedbt".t_employee
  (
    f_employeeid integer,
    f_deptid integer,
    f_employeename varchar(50),
    f_birthdate date
  );

revoke all on "gbasedbt".t_employee from "public" as "gbasedbt";

{ TABLE "gbasedbt".t_employee_02 row size = 64 number of columns = 4 index size = 0 ccolnum = 0
 commcol = f_employeeid,f_deptid,f_employeename,f_birthdate }

{ unload file name = t_emp00117.unl number of rows = 5 }


create table "gbasedbt".t_employee_02
  (
    f_employeeid integer,
    f_deptid integer,
    f_employeename varchar(50),
    f_birthdate date
  );

revoke all on "gbasedbt".t_employee_02 from "public" as "gbasedbt";




grant select on "gbasedbt".t_dept_00 to "public" as "gbasedbt";
grant update on "gbasedbt".t_dept_00 to "public" as "gbasedbt";
grant insert on "gbasedbt".t_dept_00 to "public" as "gbasedbt";
grant delete on "gbasedbt".t_dept_00 to "public" as "gbasedbt";
grant index on "gbasedbt".t_dept_00 to "public" as "gbasedbt";
grant select on "gbasedbt".t_dept_01 to "public" as "gbasedbt";
grant update on "gbasedbt".t_dept_01 to "public" as "gbasedbt";
grant insert on "gbasedbt".t_dept_01 to "public" as "gbasedbt";
grant delete on "gbasedbt".t_dept_01 to "public" as "gbasedbt";
grant index on "gbasedbt".t_dept_01 to "public" as "gbasedbt";
grant select on "gbasedbt".t_employee_00 to "public" as "gbasedbt";
grant update on "gbasedbt".t_employee_00 to "public" as "gbasedbt";
grant insert on "gbasedbt".t_employee_00 to "public" as "gbasedbt";
grant delete on "gbasedbt".t_employee_00 to "public" as "gbasedbt";
grant index on "gbasedbt".t_employee_00 to "public" as "gbasedbt";
grant select on "gbasedbt".t_employee_01 to "public" as "gbasedbt";
grant update on "gbasedbt".t_employee_01 to "public" as "gbasedbt";
grant insert on "gbasedbt".t_employee_01 to "public" as "gbasedbt";
grant delete on "gbasedbt".t_employee_01 to "public" as "gbasedbt";
grant index on "gbasedbt".t_employee_01 to "public" as "gbasedbt";
grant select on "gbasedbt".t_employee_03 to "public" as "gbasedbt";
grant update on "gbasedbt".t_employee_03 to "public" as "gbasedbt";
grant insert on "gbasedbt".t_employee_03 to "public" as "gbasedbt";
grant delete on "gbasedbt".t_employee_03 to "public" as "gbasedbt";
grant index on "gbasedbt".t_employee_03 to "public" as "gbasedbt";
grant select on "gbasedbt".t_employee_04 to "public" as "gbasedbt";
grant update on "gbasedbt".t_employee_04 to "public" as "gbasedbt";
grant insert on "gbasedbt".t_employee_04 to "public" as "gbasedbt";
grant delete on "gbasedbt".t_employee_04 to "public" as "gbasedbt";
grant index on "gbasedbt".t_employee_04 to "public" as "gbasedbt";
grant select on "gbasedbt".t_employee_05 to "public" as "gbasedbt";
grant update on "gbasedbt".t_employee_05 to "public" as "gbasedbt";
grant insert on "gbasedbt".t_employee_05 to "public" as "gbasedbt";
grant delete on "gbasedbt".t_employee_05 to "public" as "gbasedbt";
grant index on "gbasedbt".t_employee_05 to "public" as "gbasedbt";
grant select on "gbasedbt".t_employee_06 to "public" as "gbasedbt";
grant update on "gbasedbt".t_employee_06 to "public" as "gbasedbt";
grant insert on "gbasedbt".t_employee_06 to "public" as "gbasedbt";
grant delete on "gbasedbt".t_employee_06 to "public" as "gbasedbt";
grant index on "gbasedbt".t_employee_06 to "public" as "gbasedbt";
grant select on "gbasedbt".t_employee_07 to "public" as "gbasedbt";
grant update on "gbasedbt".t_employee_07 to "public" as "gbasedbt";
grant insert on "gbasedbt".t_employee_07 to "public" as "gbasedbt";
grant delete on "gbasedbt".t_employee_07 to "public" as "gbasedbt";
grant index on "gbasedbt".t_employee_07 to "public" as "gbasedbt";
grant select on "gbasedbt".t_employee_08 to "public" as "gbasedbt";
grant update on "gbasedbt".t_employee_08 to "public" as "gbasedbt";
grant insert on "gbasedbt".t_employee_08 to "public" as "gbasedbt";
grant delete on "gbasedbt".t_employee_08 to "public" as "gbasedbt";
grant index on "gbasedbt".t_employee_08 to "public" as "gbasedbt";
grant select on "gbasedbt".t_employee_09 to "public" as "gbasedbt";
grant update on "gbasedbt".t_employee_09 to "public" as "gbasedbt";
grant insert on "gbasedbt".t_employee_09 to "public" as "gbasedbt";
grant delete on "gbasedbt".t_employee_09 to "public" as "gbasedbt";
grant index on "gbasedbt".t_employee_09 to "public" as "gbasedbt";
grant select on "gbasedbt".t_dept to "public" as "gbasedbt";
grant update on "gbasedbt".t_dept to "public" as "gbasedbt";
grant insert on "gbasedbt".t_dept to "public" as "gbasedbt";
grant delete on "gbasedbt".t_dept to "public" as "gbasedbt";
grant index on "gbasedbt".t_dept to "public" as "gbasedbt";
grant select on "gbasedbt".t_employee to "public" as "gbasedbt";
grant update on "gbasedbt".t_employee to "public" as "gbasedbt";
grant insert on "gbasedbt".t_employee to "public" as "gbasedbt";
grant delete on "gbasedbt".t_employee to "public" as "gbasedbt";
grant index on "gbasedbt".t_employee to "public" as "gbasedbt";
grant select on "gbasedbt".t_employee_02 to "public" as "gbasedbt";
grant update on "gbasedbt".t_employee_02 to "public" as "gbasedbt";
grant insert on "gbasedbt".t_employee_02 to "public" as "gbasedbt";
grant delete on "gbasedbt".t_employee_02 to "public" as "gbasedbt";
grant index on "gbasedbt".t_employee_02 to "public" as "gbasedbt";
















revoke usage on language SPL from public ;

grant usage on language SPL to public ;


















dbimport completed

set environment sqlmode 'oracle';
set environment sqlmode 'gbase';
[gbasedbt@localhost export]$

> database mydb;

Database selected.

> info tables;


Table name

t_dept             t_dept_00          t_dept_01          t_employee
t_employee_00      t_employee_01      t_employee_02      t_employee_03
t_employee_04      t_employee_05      t_employee_06      t_employee_07
t_employee_08      t_employee_09

>

onunload/onload

onunload语法

onunload [-l] [-t <tape_device>] [-b <block size>] [-s <tape size>]
		<db_name>[:[<owner>.]<table_name>]

tape_device:保存数据的磁带设备。当使用磁盘时，需创建一个空文件并指定。
db_name:要导出数据的数据库名称。如果不指定表名称，则导出全库数据。
table_name:当需要导出表数据时，指定表名称。

说明：
参数b，参数s和参数l主要用于使用磁带设备，当使用磁盘设备时，可以忽略。
导出数据为二进制格式，只能用于兼容硬件上的兼容数据库版本的数据导入。
该方式使用限制较严格，真实场景中使用较少。

[gbasedbt@localhost train]$ ll
总用量 0
drwxrwxr-x. 2 gbasedbt gbasedbt 174 2月  15 14:12 data
drwxrwxr-x. 2 gbasedbt gbasedbt  69 2月  15 15:45 dbload
drwxrwxr-x. 3 gbasedbt gbasedbt  62 2月  15 16:17 export
drwxrwxr-x. 2 gbasedbt gbasedbt   6 2月  15 11:37 external
drwxrwxr-x. 2 gbasedbt gbasedbt   6 2月  15 11:37 import
drwxrwxr-x. 2 gbasedbt gbasedbt 158 2月  15 14:24 load
drwxrwxr-x. 2 gbasedbt gbasedbt   6 2月  15 11:37 onload
drwxrwxr-x. 2 gbasedbt gbasedbt   6 2月  15 11:37 onunload
drwxrwxr-x. 2 gbasedbt gbasedbt 195 2月  15 14:11 unload
[gbasedbt@localhost train]$ cd onunload/
[gbasedbt@localhost onunload]$ ll
总用量 0
[gbasedbt@localhost onunload]$ touch mydb.bak
[gbasedbt@localhost onunload]$ onunload -t mydb.bak mydb
Your evaluation license will expire on 2024-02-14 00:00:00
Please mount tape and press Return to continue ...
Please label this as tape number 1 in the tape sequence.
[gbasedbt@localhost onunload]$ ll -h
总用量 7.2M
-rw-rw-r--. 1 gbasedbt gbasedbt 7.2M 2月  15 16:28 mydb.bak
[gbasedbt@localhost onunload]$

[gbasedbt@localhost onunload]$ touch mytable.bak
[gbasedbt@localhost onunload]$ onunload -t mytable.bak mydb:t_dept
Your evaluation license will expire on 2024-02-14 00:00:00
Please mount tape and press Return to continue ...
Please label this as tape number 1 in the tape sequence.
[gbasedbt@localhost onunload]$ ll -h
总用量 7.2M
-rw-rw-r--. 1 gbasedbt gbasedbt 7.2M 2月  15 16:28 mydb.bak
-rw-rw-r--. 1 gbasedbt gbasedbt  64K 2月  15 16:30 mytable.bak
[gbasedbt@localhost onunload]$

onload语法

onload [-l] [-t <tape_device>] [-b <block size>] [-s <tape size>]
  [-d <data_dbs>] <db_name>[:[<owner>.]<table_name>]
  [{-i <old index name> <new index name>}]
  [{-fd old-DBspace-name new-DBspace-name}]
  [{-fi index-name old-DBspace-name new-DBspace-name}]
  [{-c <old constraint name> <new constraint name>}]

tape_device:保存数据的磁带设备或操作系统文件目录。
data_dbs:指定数据导入时，使用的数据库空间。
db_name:指定导入的数据库名称。
table_name:指定导入的表名称。

[gbasedbt@localhost export]$ dbaccess - -
Your evaluation license will expire on 2024-02-14 00:00:00
> drop database mydb;

Database dropped.

>

[gbasedbt@localhost onunload]$ onload -t mydb.bak mydb -d datadbs3
Your evaluation license will expire on 2024-02-14 00:00:00
Please mount tape and press Return to continue ...
The load has successfully completed.
[gbasedbt@localhost onunload]$

> database mydb;

Database selected.

> info tables;


Table name

t_dept             t_dept_00          t_dept_01          t_employee
t_employee_00      t_employee_01      t_employee_02      t_employee_03
t_employee_04      t_employee_05      t_employee_06      t_employee_07
t_employee_08      t_employee_09

>

external table

external table语法

create external table table_name([column definition | sameas table_template])
using (
datafiles ('disk:/textfile'),
format 'delimited',
delimiter 'delimiter_string'
);

table_name:要创建的外部表名称。
table_template:可以指定一个模板，以该表的列定义创建一个外部表。
datafiles:指定外部表使用的数据文件。
format:
delimiter:

[gbasedbt@localhost external]$ pwd
/home/gbasedbt/train/external
[gbasedbt@localhost external]$ cat create.sql
-- export GL_DATE="%d.%m.%iY"

create external table t_ext_data_01(f_employeeid int, f_deptid int, f_employeename varchar(50), f_birthdate date)
using(
datafiles ('disk:/home/gbasedbt/train/data/ext_data.unl'),
format 'delimited',
delimiter '.'
);

select * from t_ext_data_01;
[gbasedbt@localhost external]$ dbaccess mydb create.sql
Your evaluation license will expire on 2024-02-14 00:00:00

Database selected.


  310: Table (gbasedbt.t_ext_data_01) already exists in database.
Error in line 7
Near character position 14



f_employeeid    1
f_deptid        1
f_employeename  Bill
f_birthdate     01.06.1983

f_employeeid    2
f_deptid        1
f_employeename  John
f_birthdate     25.12.1985

f_employeeid    3
f_deptid        2
f_employeename  Mary
f_birthdate     10.10.1987

f_employeeid    4
f_deptid        3
f_employeename  Kate
f_birthdate     11.11.1989

f_employeeid    5
f_deptid        1
f_employeename  Will
Smith
f_birthdate     28.02.1981

5 row(s) retrieved.


Database closed.

[gbasedbt@localhost external]$

#删除两行后验证 
[gbasedbt@localhost data]$ vi ext_data.unl

[gbasedbt@localhost data]$ echo "select * from t_ext_data_01" | dbaccess mydb
Your evaluation license will expire on 2024-02-14 00:00:00

Database selected.




f_employeeid    1
f_deptid        1
f_employeename  Bill
f_birthdate     01.06.1983

f_employeeid    3
f_deptid        2
f_employeename  Mary
f_birthdate     10.10.1987

f_employeeid    5
f_deptid        1
f_employeename  Will
Smith
f_birthdate     28.02.1981

3 row(s) retrieved.



Database closed.

[gbasedbt@localhost data]$