2021-05-21

川流不息，永不止步 - DCP学习笔记

生命，本就是对五彩斑斓人生追逐的一场旅途，而不间断的学习，正是点亮这场旅途的星光，为我们指引着方向。

历时四天的DCP课程学习，让我受益匪浅。在学习过程中，有疑惑也有收获，相信经过一系列的整理、练习，我会对达梦数据库有更深的了解。

由于涉及到数据守护集群的搭建，因此以下除了最后一块内容<数据守护集群搭建>之外，其余操作均在主机执行。

1、主机创建数据库实例、表空间、用户

1） 创建数据库实例（IP:192.168.0.1； 实例名：DMSERVER01）

[dmdba@dcp-<主机名称> ~]$ export DISPLAY=localhost:10.0

[dmdba@dcp-<主机名称> ~]$ echo $DISPLAY

[dmdba@dcp-<主机名称> ~]$ cd /dm8/tool/

[dmdba@dcp-<主机名称> tool]$ ./dbca.sh

界面安装完毕，关闭之前，使用root用户执行脚本：

[root@dcp-<主机名称> tool]# mv /dm8/bin/DmServiceGRP1_RT_01.service

[root@dcp-<主机名称> tool]# systemctl enable DmServiceGRP1_RT_01.service

[root@dcp-<主机名称> tool]# systemctl start DmServiceGRP1_RT_01.service

显示“创建数据库完成”界面，说明主机实例创建成功。

 

2） 创建表空间TEST01

[dmdba@dcp-<主机名称> tool]$ ./manager

注册连接到SYSDBA用户

CREATE TABLESPACE TEST01 DATAFILE '/dm8/data/DAMENG01/TEST01.DBF' SIZE 32 AUTOEXTEND ON NEXT 1 MAXSIZE 1024 CACHE=NORMAL;

 

3） 创建用户TEST

在已经使用manager连接到的SYSDBA用户中：

对应的DDL：

create user "TEST" identified by "dameng123"

limit failed_login_attemps 3, password_lock_time 1, password_grace_time 10

default tablespace "TEST01"

default index tablespace "TEST01";

grant "DBA","PUBLIC","VTI" to "TEST";

grant SELECT,INSERT,DELETE,UPDATE,REFERENCES,SELECT FOR DUMP on "DMHR"."EMPLOYEE" to "TEST";

2、创建分区表

2.1 范围分区

要求：分区列是数字或是日期类型（比如不能是字符类型）

下面来创建范围分区表：

1） 创建范围分区表（T_R1，分区使用相同的表空间）

 

CREATE TABLE TEST.T_R1(ID INT,NAME VARCHAR(20))

 PARTITION BY RANGE(ID)

  (PARTITION P1 VALUES LESS THAN(100),

   PARTITION P2 VALUES LESS THAN(200),

   PARTITION P3 VALUES LESS THAN(300));

注：执行后的警告 ” 警告:范围分区未包含MAXVALUE,可能无法定位到分区” 可以先不用管。

 

2） 查看范围分区表（T_R1）

SELECT T.TABLE_NAME,T.PARTITIONED FROM DBA_TABLES T

  WHERE T.TABLE_NAME='T_R1' AND T.OWNER='TEST';

注：PARTITIONED 为YES表明是分区表

    可在前台查看到范围分区表

 

3） 测试数据插入情况（向T_R1插入200笔数据）

1. 录入批量数据

BEGIN

   FOR I IN 1..200 LOOP

       INSERT INTO TEST.T_R1 VALUES(I,'AAAA'||I);

      END LOOP;

      COMMIT;

 END;

 

1. 插入完毕之后，可以在前台浏览数据插入情况：

注：T_R1_P1中只有99笔数据，这是因为在创建分区表的时候，P1是less than，因此小于100的数据会被插入，即P1中只有99笔数据。

 

1. 插入完毕之后，可以从数据库查看数据插入情况：

SELECT COUNT(*) FROM TEST.T_R1_P1;

SELECT COUNT(*) FROM TEST.T_R1;

 

 

4） 测试数据插入情况（向T_R1插入300）

INSERT INTO TEST.T_R1 VALUES(300,'AAA300');

注：由于分区表T_R1中没有合适的分区，插入300会报错

 

5） 创建范围分区表（T_R2，分区使用不同的表空间）

1. 建不同表空间

表空间分别为 TBS1、TBS2、TBS3（在这里创建表空间时除了名字其他参数默认）

CREATE TABLESPACE TBS1 DATAFILE '/dm8/data/DAMENG01/TBS1_01.DBF' SIZE 32 CACHE=NORMAL;

 CREATE TABLESPACE TBS2 DATAFILE '/dm8/data/DAMENG01/TBS2_01.DBF' SIZE 32 CACHE=NORMAL;

 CREATE TABLESPACE TBS3 DATAFILE '/dm8/data/DAMENG01/TBS3_01.DBF' SIZE 32 CACHE=NORMAL;

 

1. 创建分区表T_R2（指定不同的表空间）

CREATE TABLE TEST.T_R2(SID INT,ID INT,NAME VARCHAR(20))

  PARTITION BY RANGE(SID)

    (PARTITION P1 VALUES LESS THAN(100) TABLESPACE TBS1,

     PARTITION P2 VALUES LESS THAN(200) TABLESPACE TBS2,

     PARTITION P3 VALUES LESS THAN(300) TABLESPACE TBS3);

 

注：执行后的警告 ” 警告:范围分区未包含MAXVALUE,可能无法定位到分区” 可以先不用管。

 

6） 测试数据插入情况（向T_R2插入10000条数据）

BEGIN

 FOR I IN 1..10000 LOOP

   INSERT INTO TEST.T_R2 VALUES(I,I,'AAA'||I);

   END LOOP;

   COMMIT;

END;

 

注：提示没有找到合适的分区，因此需要修改分区表T_R2，添加上MAXVALUE,即警告中提示的信息，即只要大于P3的数据都会放入MAXVALUE的分区中。

--添加MAXVALUE的分区

ALTER TABLE TEST.T_R2 ADD PARTITION PN VALUES LESS THAN(MAXVALUE);

 

--此时再去执行插入脚本，即执行以下脚本后成功。

BEGIN

 FOR I IN 1..10000 LOOP

   INSERT INTO TEST.T_R2 VALUES(I,I,'AAA'||I);

   END LOOP;

   COMMIT;

END;

 

以上都是索引组织表的分区表.

 

7） 创建范围分区表（堆表: T_R3）

CREATE TABLE TEST.T_R3(SID INT,ID INT,NAME VARCHAR(20))

 PARTITION BY RANGE(SID)

  (PARTITION P1 VALUES LESS THAN(100),

   PARTITION P2 VALUES LESS THAN(200),

   PARTITION P3 VALUES LESS THAN(300))

  STORAGE(NOBRANCH);

 

注意：堆表会有一些限制，水平分区堆表主表及其各子表必须位于同一个表空间，如果子表位于不同的表空间就会报错：

2.2 列表分区

要求：分区列适合字符串类型

1） 创建列表分区（T_SALES）

CREATE TABLE TEST.T_SALES(SALES_ID INT,SALEMAN VARCHAR(20),SALEDATE DATETIME,CITY CHAR(10))

 PARTITION BY LIST(CITY)

 (PARTITION P1 VALUES('北京','天津','河北'),

  PARTITION P2 VALUES('上海','南京','杭州'),

  PARTITION P3 VALUES('武汉','长沙'),

  PARTITION P4 VALUES('广州','深圳'));

 

2） 录入测试数据（T_SALES）

 

1. 插入已经存在的分区数据

INSERT INTO TEST.T_SALES VALUES(1,'AAA','2021-05-18','天津');

COMMIT;

注：执行成功，插入到P1分区表中

1. 插入不存在的分区数据

INSERT INTO TEST.T_SALES VALUES(1,'DDD','2021-05-18','合肥');

注：由于没有合适的分区，无法插入，需要修改T_SALES表，增加分区。

1. 修改分区表，增加PN分区（default）

ALTER TABLE TEST.T_SALES ADD PARTITION PN VALUES(DEFAULT);

注：此时再去执行插入脚本（city=’合肥’的插入语句）成功，插入到PN子表

2.3 哈希分区

存数据非常快，取数据慢，（不会使用什么规则，没有规律，没有顺序）数据平均分配各个子分区。如果hash 分区不指定分区表名，那就通过指定的哈希的分区数来创建，分区表名统一使用DMHASHPART+分区号（从0 开始）作为分区名。

 

1） 创建哈希分区（T_ HASH）

CREATE TABLE TEST.T_HASH(ID INT,NAME VARCHAR(20))

PARTITION BY HASH(ID) PARTITIONS 10;

注：哈希分区表各子表名，表名从DMHASHPART0开始

 

2） 录入测试数据（T_HASH）

BEGIN

 FOR I IN 1..100000 LOOP

 INSERT INTO TEST.T_HASH VALUES(I,'AAA'||I);

 END LOOP;

 COMMIT;

END;

 

3） 查看数据插入情况

SELECT COUNT(*) FROM TEST.T_HASH;

SELECT COUNT(*) FROM TEST.T_HASH_DMHASHPART0;

 

注：可以看到，每个分区表中的数据并不是按照大小顺序插入的，它按照的的是hash的一种算法插入的（比如：0子表分入的是id值个位都是0的数据）。

2.4 组合分区

range--list 、range-hash 、range-range、 list—list、hash-hash 、list-range

DM 支持最多八层分区。

 

1） 创建组合分区（列表分区-范围分区 <List—range> ）

CREATE TABLE TEST.SALES_SUM

(SALE_ID INT,SALE_NAME VARCHAR(20),SALE_DATE DATE,CITY CHAR(10)) --表定义

  PARTITION BY LIST(CITY) --主分区

    SUBPARTITION BY RANGE(SALE_DATE) --子分区

    SUBPARTITION TEMPLATE(  --子分区模版

     SUBPARTITION P11 VALUES LESS THAN('2012-04-01'),

     SUBPARTITION P12 VALUES LESS THAN('2013-04-01'),

     SUBPARTITION P13 VALUES LESS THAN(MAXVALUE))

   (PARTITION P1 VALUES('北京','天津','河北')

     (SUBPARTITION P1_1 VALUES LESS THAN('2012-08-01'),  --创建组合分区

      SUBPARTITION P1_2 VALUES LESS THAN('2013-04-01'),

      SUBPARTITION P1_N VALUES LESS THAN(MAXVALUE)),  --自定义子分区

    PARTITION P2 VALUES('上海','南京','杭州'),--使用子分区默认的模版

    PARTITION P3 VALUES('武汉','长沙'),

    PARTITION P4 VALUES('广州','深圳'));

 

注：可以看到，第一层子表P1、P2的第二层子表名称是不相同的。

P1的第二层子表名称按照P1中设置的子分区模版规则，命名为P1_P1_1、P1_P1_2 …

P2的第二层子表名称按照分区表SALES_SUM表设置的子表模版规则，命名为P2_P11、P2_P12 …

 

2） 录入测试数据（SALES_SUM）

 

INSERT INTO TEST.SALES_SUM VALUES(1,'aaa','2012-05-01','武汉');

COMMIT;

INSERT INTO TEST.SALES_SUM VALUES(1,'aaa','2012-05-01','北京');

COMMIT;

 

注：第一条语句插入到P3区的P12， 即插入到表 TEST.SALES_SUM_P3_P12；

    第二条语句插入到P1区的P1_1，即插入到表 TEST.SALES_SUM_P1_P1_1

由此可以看出来，如果子表有自定义的第二层子模版，则按照自定义的第二层子模版插入；如果没有自定义的第二层子模版，则按照分区表的子模版插入。

 

3） 创建组合分区案例：创建的分区表有主键

 

CREATE TABLE TEST.T_R6

(SID INT,ID INT,NAME VARCHAR(20),CONSTRAINT T_R6_PRI PRIMARY KEY(SID))

 PARTITION BY RANGE(ID)

 (PARTITION P1 VALUES LESS THAN(100),

  PARTITION P2 VALUES LESS THAN(200),

  PARTITION P3 VALUES LESS THAN(MAXVALUE));

建分区表的时候，如果分区表有主键，那么主键的列必须要包含在分区列中。

解决方案1:将主键SID作为分区列

CREATE TABLE TEST.T_R6

(SID INT,ID INT,NAME VARCHAR(20),CONSTRAINT T_R6_PRI PRIMARY KEY(SID))

 PARTITION BY RANGE(SID)

 (PARTITION P1 VALUES LESS THAN(100),

  PARTITION P2 VALUES LESS THAN(200),

  PARTITION P3 VALUES LESS THAN(MAXVALUE));

 

解决方案2：如果必须要使用ID作为分区列，那么或者去掉主键约束或者将主键设置为ID

CREATE TABLE TEST.T_R6

(SID INT,ID INT,NAME VARCHAR(20),CONSTRAINT T_R6_PRI PRIMARY KEY(ID))

 PARTITION BY RANGE(ID)

 (PARTITION P1 VALUES LESS THAN(100),

  PARTITION P2 VALUES LESS THAN(200),

  PARTITION P3 VALUES LESS THAN(MAXVALUE));

2.5 间隔分区

Numtoyminterval(n,interval_unit)  实际上是范围分区的一个扩展

n:数据类型;  interval_unit:year,month

注：此函数中的ym分别指的是年和月，函数的意思是把年和月做了一个计算

 

1） 函数案例一：年度间隔

SELECT SYSDATE+NUMTOYMINTERVAL(1,'YEAR') FROM DUAL;

注：在当前的时间上加了一年

 

2） 函数案例二：月份间隔

SELECT SYSDATE+NUMTOYMINTERVAL(1,'MONTH') FROM DUAL;

注：在当前的时间上加了一月

 

3） 利用此函数创建一个间隔分区表（T_R8_INTERVAL_YEAR）

CREATE TABLE TEST.T_R8_INTERVAL_YEAR

(EMPLOYEE_ID NUMBER,

 EMPLOYEE_NAME VARCHAR(20),

 BIRTHDAY DATE)

 PARTITION BY RANGE(BIRTHDAY) INTERVAL(NUMTOYMINTERVAL(1,'YEAR'))

 (PARTITION P1990 VALUES LESS THAN(TO_DATE('1991-01-01','YYYY-MM-DD')),

  PARTITION P1991 VALUES LESS THAN(TO_DATE('1992-01-01','YYYY-MM-DD')));

 

4） 向间隔分区表插入数据

INSERT INTO TEST.T_R8_INTERVAL_YEAR VALUES(1,'AAAA','1990-03-01');

COMMIT;

INSERT INTO TEST.T_R8_INTERVAL_YEAR VALUES(1,'BBBB','1993-03-01');

COMMIT;

注：语句2的数据由于没有合适的分区，插入到自动创建的一个分区中。

 

INSERT INTO TEST.T_R8_INTERVAL_YEAR VALUES(1,'CCCC','1994-03-01');

COMMIT;

注：语句3的数据由于没有合适的分区，数据库会再创建另外一个新的分区，数据插入到新的分区中。

 

因此，用了时间间隔函数做分区,在数据插入的时候,如果没有适合的分区,数据库会自动给你创建一个新分区。

注意：由于分区表函数使用了INTERVAL(NUMTOYMINTERVAL(1,'YEAR'))，那么就会 1 年创建一个分区，如果分区表函数使用了INTERVAL(NUMTOYMINTERVAL(2,'YEAR'))，就会 2 年创建一个分区。

2.6 分区表维护

1） 分区表所在数据字典

SELECT * FROM DBA_TAB_PARTITIONS;

 

2） 增加分区

ALTER TABLE TEST.T_R1 ADD PARTITION PN VALUES LESS THAN(MAXVALUE);

 

3） 删除分区

ALTER TABLE TEST.T_R1 DROP PARTITION PN;

注：删除分区表之后，会将分区表中的数据一起删除。删除分区前请确认好，并做好备份。

 

4） 合并分区

场景：T_R1中第二个分区P2有100笔数据，第三个分区P3有1笔数据，需要将这两个分区的数据合并起来。

ALTER TABLE TEST.T_R1 MERGE PARTITIONS P2,P3 INTO PARTITION P2_3;

 

5） 拆分分区

 

将4）中合并的分区P2_3再做拆分，拆分到两个分区表P2,P3，以200为界（及less than 200放入P2，其他放入P3）

ALTER TABLE TEST.T_R1 SPLIT PARTITION P2_3 AT(200) INTO (PARTITION P2,PARTITION P3);

 

6） 交换分区

 

T2是一张索引组织表，即一张普通表，将分区表T_R1的P2分区表数据和T2表数据做交换。

1. T2表数据

CREATE TABLE TEST.T2(ID INT,NAME VARCHAR(20));

BEGIN

 FOR I IN 300..500 LOOP

  INSERT INTO TEST.T2 VALUES(I,'AAA'||I);

  END LOOP;

 COMMIT;

END;

 

1. 两张表数据做交换

ALTER TABLE TEST.T_R1 EXCHANGE PARTITION P2 WITH TABLE TEST.T2;

 

注意：交换时，会把普通表的数据全部交换过来，包括不属于这个分区的数据也会一起交换过来。所以我们在交换分的时候，普通表的数据需要先整理好。

 

7） 如何把非分区表转换成分区表

 

A) 把非分区的数据导出来，dexp parallel（并行） ,remap_schema；

B) 建一个结构一样的分区表；

C) 将数据导入到分区表中（dimp)。

 

1. 创建非分区表T10

CREATE TABLE TEST.T10(ID INT);

 

1. 录入数据

BEGIN

 FOR I IN 1..10000 LOOP

  INSERT INTO TEST.T10 VALUES(I);

  END LOOP;

 COMMIT;

END;

 

1. 非分区表T10导出数据

 

[dmdba@dcp-<主机名称> bin]$ ./dexp SYSDBA/dameng123@localhost:5231 file=/dm8/dmdmp/test10.dmp tables=TEST.T10

 

1. 删除普通T10（非分区表）

DROP TABLE TEST.T10;

 

1. 创建同名的分区表T10

CREATE TABLE TEST.T10(ID INT)

 PARTITION BY RANGE(ID)

  (PARTITION P1 VALUES LESS THAN(100),

   PARTITION P2 VALUES LESS THAN(200),

   PARTITION P3 VALUES LESS THAN(300),

   PARTITION P4 VALUES LESS THAN(400),

   PARTITION P5 VALUES LESS THAN(500),

   PARTITION P6 VALUES LESS THAN(600),

   PARTITION P7 VALUES LESS THAN(700),

   PARTITION PN VALUES LESS THAN(MAXVALUE));

 

1. 向分区表T10导入数据（原非分区表的T10数据）

[dmdba@dcp-<主机名称> bin]$ ./dimp SYSDBA/dameng123@localhost:5231 file=/dm8/dmdmp/test10.dmp tables=TEST.T10 ignore=y

 

1. 查看分区表T10数据

SELECT * FROM TEST.T10_P1 LIMIT 5;

3、创建HUGE表

Huge有自已特有的表空间（huge 空间）。V$huge_tablespace.最多可以创建32767 个huge 表空间。如果不指定huge 表空间，默认放在hmain 表空间中。Huge 表空间的大小由创建huge 表空间时指定的文件大小来定。单个文件的大小为：16M ~ 1024*1024M。文件的大小必须2 的n 次方。如果不是则向上看齐，默认为64M.

 

1） 创建huge表（THUGE1，存放在新建的THUGE表空间）

 

1. 创建huge表空间

语法： CREATE HUGE TABLESPACE<表空间名> path <表空间文件路径>

create huge tablespace "THUGE" path '/dm8/data/DAMENG01/THUGE01.DBF';

或者

create huge tablespace THUGE path '/dm8/data/DAMENG01/THUGE01.DBF';

 

1. 创建huge表

create huge table "TEST"."THUGE1" ("A" INT,"B" INT)

storage(section(65536), filesize(64), on "THUGE")

 

注意： 在创建huge表的时候有点问题：如果huge表是在界面创建，那么默认都会有without delta,（目前尚未找到可以在前台去除的方法），如果有此参数，那么在创建huge表的时候会报错“不支持创建非事务型HUGE表”。此时，可以将DDL语句复制出来去掉without delta,后再执行。

 

2） 创建huge表（THUGE2，存放在默认的HMAIN表空间）

create huge table "TEST"."THUGE2"("A" INT,"B" INT);

 

3） huge表的增删改查

 

1. 向huge表（列存表）插入数据

BEGIN

  FOR I IN 1..100000 LOOP

   INSERT INTO TEST.THUGE1 VALUES(I,I);

   END LOOP;

  COMMIT;

END;

注：向列存表插入数据是比较慢的（用时13秒）。

 

1. 查询huge表

和查询普通表一样。

SELECT COUNT(*) FROM TEST.THUGE1;

 

1. 查看huge表的表结构（达梦特有的SP_TABLEDEF）

SP_TABLEDEF('TEST','THUGE1');

注：WITH DELTA（如果不添加这个参数，默认应该就是使用此参数 WITH DELTA）表明huge标THUGE1是事务型的。

 

1. 查看huge表的表结构（和ORACLE兼容的包DBMS_METADATA）

SELECT DBMS_METADATA.GET_DDL('TABLE','THUGE1','TEST');

 

4） huge表的课后练习

 

1. 创建HUGE表THUGE10

create huge table "TEST"."THUGE10"("ID" INT,  "NAME" VARCHAR(20))

storage(section(65536), filesize(64), on "THUGE");

 

1. 将T2的数据导入到HUGE表THUGE10

INSERT INTO TEST.THUGE10 SELECT * FROM TEST.T2;

COMMIT;

 

1. 查询HUGE表THUGE10中数据情况

SELECT COUNT(*) FROM TEST.THUGE10;

SELECT * FROM TEST.THUGE10 LIMIT 5;

 

 

4、创建外部表

外部表的数据存储在操作系统中，是操作系统文件。建立外部表的时候，不会产生段，页，簇等存储结构。只有与表相关的定义放在数据字典中，不能对外部表的内容进行修改，不能对外部表建立索引。不需要将外部表的数据装载到数据库中来，通过Sql 解码器来访问外部表。

 

1） 创建外部表语法

Create external table<表名><表结构定义>from <控制文件路径>｜<数据文件路径><参数>；

 

2） 创建外部表案例1（使用数据文件和控制文件创建外部表）

1. A)编译一个数据文件 a.txt

[dmdba@dcp-<主机名称> bin]$ cd /dm8

[dmdba@dcp-<主机名称> dm8]$ vi a.txt

a.txt内容：

1,aaaaa

2,bbbbb

3,cc

4,ddd

5,fff

6,uuu

 

1. B)建立一个控制文件 a.ctl

[dmdba@dcp-<主机名称> dm8]$ vi a.ctl

a.ctl内容：

LOAD DATA

INFILE '/dm8/a.txt'

INTO TABLE EXT

FIELDS ','

 

1. C)创建外部表

CREATE EXTERNAL TABLE TEST.EXT(ID INT,NAME VARCHAR(20)) FROM '/dm8/a.ctl';

 

1. D)查询外部表

SELECT * FROM TEST.EXT;

 

1. E)重新编译一下数据文件 a.txt

[dmdba@dcp-<主机名称> dm8]$ vi a.txt

a.txt内容：

1,aaaaa

2,bbbbb

3,cc

4,ddd

5,fff

6,uuu

7,ooo

8,pppp

 

1. F)再次查询外部表

SQL> SELECT * FROM TEST.EXT;

注：可以看到，只要是数据文件a.txt的内容发生了变化，外部表结构不变的情况下，那么查询外部表就会相应发生变化。

 

3） 创建外部表案例2（只使用数据文件创建外部表）

1. A)编译一个数据文件 b.txt

[dmdba@dcp-<主机名称> dm8]$ vi b.txt

b.txt 内容：

10|9|7

4|3|2|5

1|3|4|5

6|7

 

1. B)创建外部表

CREATE EXTERNAL TABLE TEST.EXT2(C1 INT,C2 INT,C3 INT)

FROM DATAFILE '/dm8/b.txt' PARMS(FIELDS DELIMITED BY '|');

 

1. C)查询外部表

SELECT * FROM TEST.EXT2;

注：可以看到，由于外部表EXT2只定义了3列，因此：

第1行数据完全存放；

第2行数据的“5”丢失；

第4行数据由于只有两个值，在第3列C3上补值“NULL”。

5、DM8索引管理

5.1 收集查看统计信息

达梦统计信息分为三类：表统计信息，列统计信息，索引索信息。

 

统计信息在生成的过程中分为三个步骤：

1、确定采样的数据（对象，分析数据，表，列，索引）；

2、确定采样率：采样率和数据量成反比；

3、生成直方图（频率直方图和等高直方图）。

 

1） 查看统计信息（列的统计信息）

DBMS_STATS.COLUMN_STATS_SHOW(OWNNAME='TEST',TABNAME='T_R1',COLNAME='ID');

注：统计信息查询出来之后是空的，说明T_R1的ID列还未收集统计信息。

 

2） 收集统计信息（列的统计信息）

DBMS_STATS.GATHER_TABLE_STATS(OWNNAME='TEST',TABNAME='T_R1',METHOD_OPT='FOR ALL COLUMNS SIZE AUTO');

注：收集方法METHOD_OPT 默认就是'FOR ALL COLUMNS SIZE AUTO'，也就是说这个参数就算不写的话，也是'FOR ALL COLUMNS SIZE AUTO'。

 

3）收集统计信息之后再次查看（列的统计信息）

 

DBMS_STATS.COLUMN_STATS_SHOW(OWNNAME='TEST',TABNAME='T_R1',COLNAME='ID');

注：FREQUENCY 是频率直方图，只有在等高直方图的时候，字段 ENDPOINT_KEYHEIGHT 和

ENDPOINT_DISTINCT才有用。

5.2 统计信息定时收集（作业）

统计信息一般情况下都是手动收集的，如果想要自动或者定时收集，需要使用到作业。

作业步骤 – 步骤类型：选择 更新统计信息

作业调度：比如选择每周日凌晨两点自动执行

 

1） 添加作业对应DDL

call SP_CREATE_JOB('TEST',1,0,'',0,0,'',0,'');

call SP_JOB_CONFIG_START('TEST');

call SP_ADD_JOB_STEP('TEST', 'S1', 3, '', 1, 2, 0, 0, NULL, 0);

call SP_ADD_JOB_SCHEDULE('TEST', 'S1', 1, 2, 1, 1, 0, '02:00:00', NULL, '2021-05-12 10:57:04', NULL, '');

call SP_JOB_CONFIG_COMMIT('TEST');

需要注意的是：收集统计信息要在业务低谷时间去做。

5.3 执行计划

执行计划是一条SQL 语句在DM 数据库中执行过程或访问路径的描述。可以通过EXPLAIN 命令查看。

一个执行计划由若干个计划节点组成，每个计划节点中包含操作符(CSCN2)和它的代价([0, 1711, 396])等信息。代价由一个三元组组成 [代价,记录行数,字节数]，代价数是ms。

 

解读一下第三个计划节点：

操作符是CSCN2 即全表扫描；

代价估算是0ms，扫描的记录行数是1711 行，输出字节数是396 个。

 

1） 执行计划案例1（列的统计信息）

 

1 未创建索引

1. 构建一张表T16（数量：43404）

CREATE TABLE TEST.T16 AS SELECT * FROM SYSOBJECTS;

 

1. 查看执行计划

EXPLAIN SELECT ID FROM TEST.T16 WHERE ID<20;

可以看到，此时走的是全表扫描。

注意：索引也是需要占存储空间的，在正式环境中，一般单独建立一个索引表空间。

 

2创建了索引

1. 单独创建一个索引表空间

CREATE TABLESPACE IND1 DATAFILE '/dm8/data/DAMENG01/IND1_01.DBF' SIZE 32;

 

1. 创建索引

CREATE INDEX TEST.T16_IND ON TEST.T16(ID) TABLESPACE IND1;

 

1. 创建索引之后，查看执行计划（还未收集统计信息）

EXPLAIN SELECT ID,NAME FROM TEST.T16 WHERE ID<20;

注：和未创建索引时一样走的全表扫描

 

1. 创建索引之后，查看执行计划（收集统计信息之后）

DBMS_STATS.GATHER_TABLE_STATS(OWNNAME='TEST',TABNAME='T16',METHOD_OPT='FOR ALL COLUMNS SIZE AUTO');

EXPLAIN SELECT ID FROM TEST.T16 WHERE ID<20;

注：收集统计信息后，走的索引.

 

2） 执行计划案例2（表的统计信息）

 

DBMS_STATS.TABLE_STATS_SHOW(OWNNAME='TEST',TABNAME='T16');

 

表的统计信息字段说明：NUN_ROWS 表的总行数；Leaf_blocks 总页数；Leaf_use_block 已经使用的页数

 

3） 执行计划解读

 

比如上面查询出来的执行计划：

SSEK2: [0, 5, 60] 中

0  代表的是 代价；

5  代表的是 返回结果集的记录条数

60 代表的是 每一条记录的字符数，是长度标识

 

5.4 创建索引

5.4.1 唯一索引

创建表的时候，有主键和唯一约束，数据库会自动创建唯一索引。

 

1） 创建唯一索引

 

1. 创建表，并插入数据

CREATE TABLE TEST.T15(ID INT,NAME VARCHAR(20));

INSERT INTO TEST.T15 VALUES(1,'AAAA');

INSERT INTO TEST.T15 VALUES(2,'AAAA');

INSERT INTO TEST.T15 VALUES(3,'AAAA');

INSERT INTO TEST.T15 VALUES(4,'AAAA');

INSERT INTO TEST.T15 VALUES(5,'AAAA');

INSERT INTO TEST.T15 VALUES(6,'AAAA');

INSERT INTO TEST.T15 VALUES(7,'AAAA');

INSERT INTO TEST.T15 VALUES(8,'AAAA');

COMMIT;

 

1. 创建唯一索引

CREATE UNIQUE INDEX TEST.T15_IND ON TEST.T15(ID);

 

2） 在创建的唯一索引上测试数据

 

1. 向唯一索引 TEST.T15_IND 再插入数据，检验是否违反

INSERT INTO TEST.T15 VALUES(5,'AAAA');

 

1. 向唯一索引 TEST.T15_IND 插入NAME列NULL，检验是否违反

INSERT INTO TEST.T15 VALUES(NULL,'AAAA');

 

1. 向唯一索引 TEST.T15_IND 再次插入NAME列NULL，检验是否违反

INSERT INTO TEST.T15 VALUES(NULL,'AAAA');

注：可以看到的是，如果插入之前已经插入过的 (5,'AAAA')，由于存在唯一性索引，插入时报错“违反唯一性约束“；如果第一次插入(null,'AAAA')显示插入成功，插入第二次(null,'AAAA')依然插入成功。

 

3）查看表中最终插入的数据

SELECT * FROM TEST.T15;

因此：唯一约束可以插入多个空值！

5.4.2 函数索引（使用 系统自有函数&自定义函数）

函数可以使用 系统自有函数 或者是 自定义函数。

 

1） 主机上创建函数索引

 

1. 创建表

CREATE TABLE TEST.EMP AS SELECT * FROM DMHR.EMPLOYEE;

 

1. 创建索引

CREATE INDEX TEST.IND_EMP ON TEST.EMP(UPPER(EMAIL)) TABLESPACE IND1;

 

2） 主机上收集统计信息

 

DBMS_STATS.GATHER_TABLE_STATS(OWNNAME='TEST',TABNAME='EMP',METHOD_OPT='FOR ALL COLUMNS SIZE AUTO');

 

3） 主机上查看执行计划

 

EXPLAIN SELECT * FROM TEST.EMP WHERE UPPER(EMAIL)=UPPER('maxueming@dameng.com');

 

5.4.3 复合索引

1） 创建复合索引（在表emp上）

CREATE INDEX TEST.EMP_IND ON TEST.EMP(EMPLOYEE_ID,EMPLOYEE_NAME);

 

2）更新统计信息

DBMS_STATS.GATHER_TABLE_STATS(OWNNAME='TEST',TABNAME='EMP',METHOD_OPT='FOR ALL COLUMNS SIZE AUTO');

 

3）查询数据，查看执行计划

1. 使用前导列，走索引

EXPLAIN SELECT * FROM TEST.EMP WHERE EMPLOYEE_ID='1001';

注：可以看到，使用EMPLOYEE_ID查询的时候，走的是索引。

 

1. 使用非前导列，不走索引，走全表扫描

EXPLAIN SELECT * FROM TEST.EMP WHERE EMPLOYEE_NAME='马学铭';

注：可以看到，使用EMPLOYEE_NAME查询的时候，走的是全表扫描。

 

1. 使用复合列，前导列在前

EXPLAIN SELECT * FROM TEST.EMP WHERE EMPLOYEE_ID='1001' AND EMPLOYEE_NAME='马学铭';

 

1. 使用复合列，前导列在后

EXPLAIN SELECT * FROM TEST.EMP WHERE EMPLOYEE_NAME='马学铭' AND EMPLOYEE_ID='1001';

注：可以看到，使用EMPLOYEE_ID 和EMPLOYEE_NAME查询的时候，走的是索引。

 

注：可以看到，使用EMPLOYEE_NAME 和EMPLOYEE_ID查询的时候，走的是索引，和使用的where条件的顺序没有太大关系。

 

因此，在复合索引中：

单独使用前导列（第一列）优化器会走二级索引；

单独使用非前导列，优化器不会走二级索引，会走全表扫；

同时使用复合索引的字段，优化器会选择走二级索引，并且执行效率和where字句中前导列在前或者在后没有太大关系；

5.4.4 位图索引

适合列上的数值类型少的情况，比如说性别。

 

1） 创建位图索引（在表T17上）

 

1. 创建表

CREATE TABLE TEST.T17(ID INT,SEX CHAR(1),NAME VARCHAR(20));

 

1. 插入数据

INSERT INTO TEST.T17 VALUES(1,1,'AAAAAA');

INSERT INTO TEST.T17 VALUES(2,1,'AAAAAA');

INSERT INTO TEST.T17 VALUES(3,0,'AAAAAA');

INSERT INTO TEST.T17 VALUES(4,1,'AAAAAA');

INSERT INTO TEST.T17 VALUES(5,0,'AAAAAA');

INSERT INTO TEST.T17 VALUES(6,1,'AAAAAA');

INSERT INTO TEST.T17 VALUES(7,0,'AAAAAA');

INSERT INTO TEST.T17 VALUES(8,1,'AAAAAA');

COMMIT;

 

1. 创建位图索引

CREATE BITMAP INDEX TEST.T17_IND ON TEST.T17(SEX);

 

2） 更新统计信息

DBMS_STATS.GATHER_TABLE_STATS(OWNNAME='TEST',TABNAME='T17',METHOD_OPT='FOR ALL COLUMNS SIZE AUTO');

 

3） 查看执行计划

EXPLAIN SELECT * FROM TEST.T17 WHERE SEX=1;

可以看到，此查询走的全表扫描，并没有走创建位图索引，原因可能是 数据量太小。

 

4） 重建位图索引（位图索引创建后不支持修改）

ALTER INDEX TEST.T17_IND REBUILD;

5.4.5 索引维护

对数据库做DML 操作的时候，会产生碎片，需要对索引进行更新和重建。

Alter index test.t17_ind rebuild;

Alter index test.t17_ind rebuild online;

 

删除索引

DROP INDEX TEST.T17_IND;

 

5.4.6 分区索引

如果表是分区表，在该表上创建的索引就是分区索引。

非堆表每个分区一个索引 （即 局部索引）；堆表可以创建全局索引，也可以创建局部索引。

在创建索引的时候指定 global 关键字，那建立的索引就是全局索引。

 

1） 创建分区索引

CREATE INDEX TEST.T_R1_IND ON TEST.T_R1(ID);

 

5.4.7 全文索引

DM 中，全文索引段必在基表定义，而不能在系统表、视图、临时表，列存表，外部表上定义，同一个列只能创建一个全文索引。在创建全文索引的时候，用户可以为分词器定义分词参数，即控制分词器分词的数量。

DM的分词种类：（常用的有四个）

CHINESE_LEXER --中文最少分词

CHINESE_VGRAM_LEXER --机械双字分词。

CHINESE_FP_LEXER --中文分词最多

ENGLISH_LEXER --英文分词

DEFAULT_LEXER --默认分词，中文最少分词

分词举例：玩具和服装

生成：    玩具和服装（分词最少）

玩 玩具 和 和服 服 服装 装 （分词最多）

 

1）创建全文索引

CREATE CONTEXT INDEX CTI_ADDRESS ON TEST.EMP(EMPLOYEE_NAME) LEXER DEFAULT_LEXER;

 

2）创建全文索引之后, 索引的存储位置

SELECT * FROM CTISYS.SYSCONTEXTINDEXES;

 

可以看到，全文索引执行成功后,索引信息会保存到CTISYS模式下的SYSCONTEXTINDEXES

系统表中。4 个自动生成的动态表CTI$ CTI_ADDRESS $D、CTI$ CTI_ADDRESS $I、CTI$ CTI_ADDRESS $N 和 CTI$ CTI_ADDRESS $P 等信息保存在SYSOBJECTS 系统表中.

 

3） 查询全文索引分词情况

SELECT * FROM TEST.CTI$CTI_ADDRESS$I;

SELECT * FROM TEST.CTI$CTI_ADDRESS$D;

这些分词信息在前台无法查看到。

可以看到分词索引的分词情况都是空的，这是因为还没有做初始化。

 

注意：如果是在PERSON表上建立的全文索引，那么CTI$CTI_ADDRESS$I 或者 TEST.CTI$CTI_ADDRESS$D 前面的模式名就是 PERSON

 

4） 初始化全文索引（即 更新全文索引，使其生效）

ALTER CONTEXT INDEX CTI_ADDRESS ON TEST.EMP REBUILD;

此时再去查询分词信息，分词信息已经存在：

SELECT * FROM TEST.CTI$CTI_ADDRESS$I;

 

5）分词信息所在的表

SELECT * FROM TEST.CTI$CTI_ADDRESS$I;

SELECT * FROM TEST.CTI$CTI_ADDRESS$D;

SELECT * FROM TEST.CTI$CTI_ADDRESS$P;

SELECT * FROM TEST.CTI$CTI_ADDRESS$N;

I 表： CTI$INDEX_NAME$I 用于保存分词结果

P 表：CTI$INDEX_NAME$P: 用于保存基表发生的增量数据变化

N 表：CTI$INDEX_NAME$N:用于保存原表记录rowid 和新词条记录的docid 的映射关系

D 表：CTI$INDEX_NAME$P: 保存了所有的将被删除的DOCID

 

6） 应用全文索引

1. 比如想要查询EMPLYEE_NAME包含 马学铭 的数据：

<普通查询>

SELECT * FROM TEST.EMP WHERE EMPLOYEE_NAME LIKE'%马学铭%';

<使用全文索引>

SELECT * FROM TEST.EMP WHERE CONTAINS(EMPLOYEE_NAME,'马学铭');

 

1. 比如想要查询EMPLYEE_NAME包含 马，并且包含 学铭 的数据：

<普通查询>

SELECT * FROM TEST.EMP WHERE EMPLOYEE_NAME LIKE'%马%' AND EMPLOYEE_NAME LIKE'%学铭%';

<使用全文索引>

SELECT * FROM TEST.EMP WHERE CONTAINS(EMPLOYEE_NAME,'马' AND '学铭');

 

1. 比如想要查询EMPLYEE_NAME包含 马，并且包含 学, 并且包含 铭 的数据：

<普通查询>

SELECT * FROM TEST.EMP WHERE EMPLOYEE_NAME LIKE'%马%' AND EMPLOYEE_NAME LIKE'%学%' AND EMPLOYEE_NAME LIKE'%铭%';

<使用全文索引>

SELECT * FROM TEST.EMP WHERE CONTAINS(EMPLOYEE_NAME,'马' AND '学' AND '铭');

 

<普通和全文索引联合查询>

SELECT * FROM TEST.EMP WHERE CONTAINS(EMPLOYEE_NAME,'马' AND '学') AND EMPLOYEE_NAME LIKE'%铭%';

 

7） 主机上更新增加全文索引信息

ALTER CONTEXT INDEX CTI_ADDRESS ON TEST.EMP INCREMENT;

 

8） 主机上删除全文索引信息

DROP CONTEXT INDEX CTI_ADDRESS ON TEST.EMP;

 

9） 主机上查询全文索引信息

SELECT * FROM CTISYS.SYSCONTEXTINDEXES;

6、做审计分析

达梦的审计有三种：系统审计、语句审计、对象审计。

审计开关

DM8: SP_SET_ENABLE_AUDIT(PARAM int)

过程执行完成后，立即生效，PARAM 有三种取值：

0：关闭审计；1：打开普通审计；2：打开普通审计和实时审计；缺省值为0。

 

需要注意的是：使用SYSAUDITOR用户登录进去才可以打开或者关闭审计，如果使用SYSDBA登录进去操作会报错“没有执行权限“。

 

1） 打开和关闭审计操作（系统级）

1. 打开审计（系统级）

[dmdba@dcp-<主机名称> bin]$ ./disql SYSAUDITOR/SYSAUDITOR

SQL> SP_SET_ENABLE_AUDIT(1);

 

1. 关闭审计（系统级）

[dmdba@fwsyb-230 bin]$ ./disql SYSAUDITOR/SYSAUDITOR

SQL> SP_SET_ENABLE_AUDIT(0);

 

1. 使用SYSDB登录操作会报错

[dmdba@fwsyb-230 bin]$ ./disql SYSDBA/SYSDBA1026

SQL> SP_SET_ENABLE_AUDIT(1);

 

2） 打开和查询审计操作（语句级）

语句级别的审计是 粗粒度 的。

需要注意的是：使用SYSAUDITOR用户登录进去才可以打开或者关闭审计或者查询审计。

 

1. 审计语法（语句级）

sp_audit_stmt(type，username, whenever)

Type:审计选项

Username:用户，null 表不限制

Whenever:审计的时机

All:所有的时候

Successful:操作成功的时候

Fail:操作失败的时候。

 

1. 打开审计（语句级）

SQL> SP_SET_ENABLE_AUDIT(1);

SQL> SP_AUDIT_STMT('TABLE','TEST','ALL');

注意：在打开语句审计的时候，审计开关必须打开，否则会报错

 

1. 审计案例（语句级）

1) 场景：创建两张表T30和T31，然后DROP 掉T30

创建场景的时候，必须使用TEST用户登录去操作，否则无法查看到审计（因为：语句级函数设置的是审计 TEST用户）

 

[dmdba@fwsyb-230 bin]$ ./disql TEST/dameng123

SQL> CREATE TABLE T30(ID INT);

SQL> CREATE TABLE T31 AS SELECT * FROM SYSOBJECTS;

SQL> DROP TABLE T30;

 

2) 查询审计操作

此操作必须使用审计账户去查询

[dmdba@fwsyb-230 bin]$ ./disql SYSAUDITOR/SYSAUDITOR

SQL> SELECT USERNAME,OPERATION,SQL_TEXT FROM V$AUDITRECORDS;

 

3） 打开和查询审计操作（对象级）

对象级别的审计是 细粒度 的。

1. 对象审计语法

Sp_audit_object(type,username,schema_name,object_name,colmun_name,whenever)

1. 对象审计案例1（对象级 - insert）

1）打开审计（对象级 - insert）

此操作必须使用审计账户去查询

[dmdba@fwsyb-230 bin]$ ./disql SYSAUDITOR/SYSAUDITOR

SQL> SP_AUDIT_OBJECT('INSERT','TEST','TEST','T_R1','NAME','ALL');

 

2) 场景：审计TEST下的T_R1表：删除id=1的记录，插入id=1的记录，只可以审计到插入的记录。

[dmdba@fwsyb-230 bin]$ ./disql TEST/Dameng123

SQL> DELETE T_R1 WHERE ID=1;

SQL> INSERT INTO T_R1 VALUES(1,'DDDD');

SQL> COMMIT;

 

3）查询审计（对象级）

[dmdba@fwsyb-230 bin]$ ./disql SYSAUDITOR/SYSAUDITOR

SQL> SELECT USERNAME,OPERATION,SQL_TEXT FROM V$AUDITRECORDS;

 

1. 审计案例2（对象级 - delete）

 

1）打开审计（对象级 - delete）

此操作必须使用审计账户去查询

[dmdba@fwsyb-230 bin]$ ./disql SYSAUDITOR/SYSAUDITOR

SQL> SP_AUDIT_OBJECT('DELETE','TEST','TEST','T_R1','NAME','ALL');

 

2) 场景：审计TEST下的T_R1表：删除id=4的记录。

[dmdba@fwsyb-230 bin]$ ./disql TEST/Dameng123

SQL> DELETE T_R1 WHERE ID=4;

SQL> COMMIT;

 

3）查询审计（对象级）

[dmdba@fwsyb-230 bin]$ ./disql SYSAUDITOR/SYSAUDITOR

SQL> SELECT USERNAME,OPERATION,SQL_TEXT FROM V$AUDITRECORDS;

 

4） 关闭审计命令总结

1. 关闭系统级审计：

SP_SET_ENABLE_AUDIT(0);

 

1. 关闭语句级别的审计（粗粒度）

SP_noaudit_stmt(type，username,whenever);

SP_noaudit_stmt(‘TABLE’,’TEST’,’ALL’);

需要注意的是：开审计和关审计的参数必须要保持一致

 

1. 关闭对象级别的审计（细粒度）

SP_noaudit_object(‘INSERT’,’TEST’,’TEST’,’T_R1’,’NAME’,’ALL’);

SP_noaudit_object(‘UPDATE’,’TEST’,’TEST’,’T_R1’,’NAME’,’ALL’);

SP_noaudit_object(‘DELETE’,’TEST’,’TEST’,’T_R1’,’NAME’,’ALL’);

需要注意的是：开审计和关审计的参数必须要保持一致

 

7、快速数据加载

dmfld（r DM Fast Loader）是 DM提供的快速数据装载命令行工具。

用户通过使用dmfldr 工具能够把按照一定格式排序的文本数据以简单、快速、高效的方式载入到DM 数据库中，或把DM 数据库中的数据按照一定格式写入文本文件。

注：和外部数据的区别。外部数据不会装载到DM数据库中，快速装载要载入DM数据库中。

 

1） 数据快速装载案例1（使用数据文件和控制文件常规导出）

 

1） 编辑数据文件

[dmdba@dcp-<主机名称> ~]$ cd /dm8

[dmdba@dcp-<主机名称> dm8]$ vi fldrtest.txt

fldrtest.txt的内容：

 

1,AAAAA,2021-03-24

2,BBBBB

3,CCCCC,2020-03-24

 

2） 编辑控制文件

[dmdba@dcp-<主机名称> ~]$ cd /dm8

[dmdba@dcp-<主机名称> dm8]$ vi fldrtest.ctl

fldrtest.ctl的内容：（可以看到，和外部表的控制文件是比较相似的）

 

LOAD DATA

INFILE '/dm8/fldrtest.txt'

INTO TABLE TEST.FLDRTEST

FIELDS ','

 

3） 创建表

CREATE TABLE TEST.FLDRTEST(ID INT,NAME VARCHAR(20),BIR DATE);

 

4） 快速加载数据

1. 使用./dmfldr help 查看快速装载的帮助

[dmdba@dcp-<主机名称> bin]$ ./dmfldr help

 

1. 使用快速装载

[dmdba@dcp-<主机名称> bin]$ ./dmfldr SYSDBA/dameng123@localhost:5231 CONTROL=\'/dm8/fldrtest.ctl\'

 

5）快速数据加载及其结果说明

 

说明1：之所以会丢弃一行数据，是因为fldrtest.txt 中第二行数据缺失了日期值

fldrtest.txt的内容：

 

1,AAAAA,2021-03-24

2,BBBBB

3,CCCCC,2020-03-24

 

在外部表中，如果字段有缺失确实，会自动当成空值处理，但是在快速加载中，如果字段有缺失会直接丢弃。

 

说明2：如果移除数据文件，被快速加载的数据依然存在

如果移除了数据文件，查询数据库中表数据是依然存在的，说明快速加载的数据是载入了数据库中。

 

[dmdba@dcp-<主机名称> bin]$ cd /home/dm8

[dmdba@dcp-<主机名称> dm8]$ mv fldrtest.txt fldrtest.txt.bak

SELECT * FROM TEST.FLDRTEST;

 

说明3：可以看到在快速加载语句中有转义符 \

 

如果不添加转义符 \，会有报错信息“控制文件输入参数出错，请确认以符号'作为首尾字符”，出现这个错误的原因就是我们需要对单引号 ‘ 做转义。

[dmdba@fwsyb-230 bin]$ ./dmfldr SYSDBA/SYSDBA1026 CONTROL='/home/dm8/fldrtest.ctl';

 

2） 数据快速装载案例2（大字段导入&导出）

 

1） 创建带有大字段的表,并插入数据（导出表DTOUTPUT）

CREATE TABLE TEST.DTOUTPUT(C1 INT,C2 BLOB,C3 CLOB);

INSERT INTO TEST.DTOUTPUT VALUES(1,0x0AB1211031DE,'STRING1233');

INSERT INTO TEST.DTOUTPUT VALUES(2,0x0AB1211031DE,'DFET12345ERER');

INSERT INTO TEST.DTOUTPUT VALUES(3,0x0AB1211031DE,' 执行耗时2 毫秒. 执行号:2231');

COMMIT;

 

2） 创建控制文件

[dmdba@dcp-<主机名称> dm8]$ vi fldrdt.ctl

 

fldrdt.ctl的内容：

 

LOAD DATA

INFILE '/dm8/fldrdt.txt'

INTO TABLE TEST.DTOUTPUT

FIELDS '|'

(

C1,

C2,

C3

)

 

3） 大字段导出

1. 大字段按照控制文件fldrdt.ctl 导出

[dmdba@dcp-<主机名称> bin]$ ./dmfldr SYSDBA/dameng123@localhost:5231  CONTROL=\'/dm8/fldrdt.ctl\' LOB_DIRECTORY=\'/dm8\' MODE=\'out\'

dmfldr V8

3 rows is load out

总共导出 3 行数据

用时:331.601(ms)

 

1. 查看导出文件

查看导出的数据文件fldrdt.txt，并且在此路径下自动生成了一个大数据文件dmfldr.lob（系统默认为dmfldr.lob）

路径：/dm8

 

4）创建带有大字段的表（导入表DTINPUT）

CREATE TABLE TEST.DTINPUT(C1 INT,C2 BLOB,C3 CLOB);

 

5）修改控制文件（fldrdt.ctl）

[dmdba@dcp-<主机名称> ~]$ cd /dm8

[dmdba@dcp-<主机名称> dm8]$ vi fldrdt.ctl

 

修改后的fldrdt.ctl的内容：

 

LOAD DATA

INFILE '/dm8/fldrdt.txt'

INTO TABLE TEST.DTINPUT

FIELDS '|'

(

C1,

C2,

C3

)

 

6）大字段导入

1. 使用dmfldr导入到DTINPUT表

[dmdba@dcp-<主机名称> bin]$ ./dmfldr SYSDBA/dameng123@localhost:5231 CONTROL=\'/dm8/fldrdt.ctl\' LOB_DIRECTORY=\'/dm8\'

 

1. 查看数据

SELECT * FROM TEST.DTOUTPUT;

SELECT * FROM TEST.DTINPUT;

 

3） 主机上数据快速装载案例3（数据文件存在表头，需要跳过）

 

1）创建数据文件（含有表头）

 

[dmdba@dcp-<主机名称> ~]$ cd /dm8

[dmdba@dcp-<主机名称> dm8]$ vi test2.txt

test2.txt的内容：

 

ID,NAME

1,AAAA

2,BBB

3,CCC

4,DDD

 

2）创建表

CREATE TABLE TEST.T5(ID INT,NAME VARCHAR(20));

 

3）创建控制文件（控制文件中要写明跳过表头）

 

[dmdba@dcp-<主机名称> dm8]$ vi test2.ctl

test2.ctl的内容：

 

options(

  skip=1

)

LOAD DATA

INFILE '/dm8/test2.txt'

INTO TABLE TEST.T5

FIELDS ','

 

4）快速加载数据

1. 使用dmfldr快速加载

[dmdba@dcp-<主机名称> bin]$ ./dmfldr SYSDBA/dameng123@localhost:5231 CONTROL=\'/dm8/test2.ctl\'

 

1. 查看表T5中数据

SELECT * FROM TEST.T5;

 

4） 数据快速装载使用限制

1） 不支持临时表，外部表；

2） 不支持系统表；

3） 不支持有位图索引，函数索引，全文索引；

4） 不支持垂直分区子表。

8、序列

序列：在内存预先申请的一段地址空间，类似于取号排队。

1） 创建序列

CREATE SEQUENCE "TEST"."S1" INCREMENT BY 1 START WITH 1 MAXVALUE 8 MINVALUE 1;

CREATE SEQUENCE "TEST"."S1"

INCREMENT BY 1 ----递增多少

START WITH 1 ---起始值

MAXVALUE 8 ---最大值

MINVALUE 1 ---最小值

NOCYCLE --不循环

NOCACHE --不缓存

NOORDER ----不排序

 

2） 使用序列（第一次使用，需要做初始化）

序列创建完第一次使用，需要先进行初始化,即执行：

SELECT TEST.S1.NEXTVAL;

 

此时再去使用，可以查询序列的当前值：

SELECT TEST.S1.CURRVAL;

 

注：如果序列创建完成之后，第一次使用之前没有初始化，那么在查询当前值的时候会报错：

3） 主机上序列在表中的使用

 

1. 创建表T8

CREATE TABLE TEST.T8(ID INT);

 

1. 通过序列向表中插入数据

INSERT INTO TEST.T8 VALUES(TEST.S1.CURRVAL); --第一次插入

INSERT INTO TEST.T8 VALUES(TEST.S1.NEXTVAL); --第二次插入

 

一直执行INSERT INTO TEST.T8 VALUES(TEST.S1.NEXTVAL);

直到序列溢出（序列设置的最大值为8）：

 

4） 主机上修改序列

ALTER SEQUENCE TEST.S1 MAXVALUE 12;

 

5） 主机上删除序列

DROP SEQUENCE TEST.S1;

9、同义词

同义词：表或视图，序列，函数，存储过程的别名，为安全考虑。

同义词分为：公共同义词，普通同义词（即私有同义词）。

 

1）创建同义词

 

1. 创建普通同义词

CREATE SYNONYM "TEST"."SY1" FOR "TEST"."EXT2";

 

1. 创建公共同义词

一般情况下使用语句创建 公共同义词：

CREATE PUBLIC SYNONYM "SY2" FOR "DMHR"."EMPLOYEE";

 

查看公共同义词数据情况：

SELECT * FROM SY2;

 

2）查询同义词的相关信息

SELECT T.TABLE_NAME,T.SYNONYM_NAME

FROM DBA_SYNONYMS T WHERE T.SYNONYM_NAME IN ('SY1','SY2');

 

3）删除同义词

 

1. 删除普通同义词

DROP SYNONYM TEST.SY1;

 

1. 删除公共同义词

DROP PUBLIC SYNONYM SY2;

10、物化视图

物化视图数据要单独存储，占用磁盘空间，规划表空间。

物化视图的数据来自于基表，基表发生变化，物化视图可以根据更新方式来进行数据更新。

物化视图更新方式：手动更新（默认的方式）：demand； 自动更新：commit

物化视图如何更新：Fast-----快速更新；Complete ---完全更新；Force ---选择性更新；Never ---不更新

 

1）主机上创建物化视图

1. 创建手动更新的物化视图（案例1）

1) 创建物化视图

CREATE MATERIALIZED VIEW TEST.MV1 AS SELECT * FROM TEST.T_R1;

 

2) 修改基表

UPDATE TEST.T_R1 SET NAME='T_R1AAA_1' WHERE ID=1;

COMMIT;

 

3) 手动更新物化视图

REFRESH MATERIALIZED VIEW TEST.MV1;

 

1. 创建自动更新的物化视图（案例2）

创建一个自动更新的物化视图,需要有物化视图日志。

 

1) 创建物化视图日志

CREATE MATERIALIZED VIEW LOG ON TEST.T_R1;

CREATE MATERIALIZED VIEW LOG ON TEST.T_R2;

注：如果想要创建物化视图日志，所在的表必须有主键，否则报错“表XXX不存在主键”

 

2) 创建物化视图（自动更新）

 

--快速更新

CREATE MATERIALIZED VIEW TEST.MV2 REFRESH FAST ON COMMIT AS SELECT * FROM TEST.T_R1;

 

--完全更新

CREATE MATERIALIZED VIEW TEST.MV3 REFRESH COMPLETE ON COMMIT AS SELECT * FROM TEST.T_R2;

 

在这里有一个需要注意的点，如果是在基表上创建的物化视图，基表发生更新后，对应的物化视图可以实现自动刷新；如果是在分区表上的创建的物化视图，目前基表发生变化后，物化视图数据并没有自动更新，但是可以实现手动更新，这一块内容需要再验证！！

 

2）主机上查询物化视图

SELECT * FROM SYS.USER_MVIEWS WHERE MVIEW_NAME='MV1';

 

物化视图支持查询重写，会有很多查询重写的规则，拖慢速度（不建议生产使用）

目前达梦支持查询重写的语法，实际查询重写不完善。

 

--执行以下语句后会让物化视图支持查询重写

ALTER MATERIALIZED VIEW TEST.MV2 ENABLE QUERY REWRITE;

 

3）主机上删除物化视图

DROP MATERIALIZED VIEW TEST.MV2;

 

4）主机上修改物化视图

ALTER MATERIALIZED VIEW TEST.MV3 REFRESH FAST;

11、数据守护集群搭建

服务器类型	IP	数据库名	实例名	PORT	MAL_ INST_DW_PORT	MAL_ HOST	MAL_ PORT	MAL_ DW_PORT
主机	192.168.0.1	DAMENG01	DMSERVER01	5231	45201	192.168.0.1	55201	65201
备机	192.168.0.2	DAMENG01	DMSERVER03	5231	45221	192.168.0.2	55221	65221
监视器	192.168.0.3

 

1） 主机创建数据库实例（IP:192.168.0.1； 实例名：DMSERVER01）

已经在1 中创建完毕！

 

2） 备机创建数据库实例（IP:192.168.0.2； 实例名：DMSERVER03）

[dmdba@dcp-<备机名称> ~]$ cd /dm8/tool/

[dmdba@dcp-<备机名称> tool]$ ./dbca.sh

界面安装完毕，关闭之前，使用root用户执行脚本：

[root@dcp-<备机名称> tool]# mv /dm8/bin/DmServiceGRP1_RT_03.service

[root@dcp-<备机名称> tool]# systemctl enable DmServiceGRP1_RT_03.service

[root@dcp-<备机名称> tool]# systemctl start DmServiceGRP1_RT_03.service

 

注意：以下3） 和 4） 步骤没有先后顺序！！

3） 备机数据库服务关闭（IP:192.168.0.2； 实例名：DMSERVER03）

由于备机要做备份还原，因此要使用root用户关闭备机的数据库服务

[root@dcp-<备机名称> ~]# systemctl status DmServiceDMSERVER03.service

[root@dcp-<备机名称> ~]# systemctl stop DmServiceDMSERVER03.service

 

4） 主机数据库服务关闭（IP:192.168.0.2； 实例名：DMSERVER01）

由于主机要做冷备，因此要使用root用户关闭备机的数据库服务

[root@dcp-<主机名称> ~]# systemctl status DmServiceDMSERVER01.service

[root@dcp-<备机名称> ~]# systemctl stop DmServiceDMSERVER01.service

 

5） 主机数据库做备份（IP:192.168.0.1； 实例名：DMSERVER01）

 

--创建backup路径，用于存放主机备份的文件（注意：一定要用dmdba用户创建，否则备份报错！！）

[dmdba@dcp-<主机名称> ~]$ mkdir /dm8/backup

 

--使用dmrman做备份

[dmdba@dcp-<主机名称> bin]$ ./dmrman

dmrman V8

 

RMAN> BACKUP DATABASE '/dm8/data/DAMENG01/dm.ini' FULL TO BACKUP_FILE2 BACKUPSET '/dm8/backup/BACKUP_FILE_02'

 

6） 备机数据库建backup目录（IP:192.168.0.2； 实例名：DMSERVER03）

创建backup路径，用于存放从主机拷贝过来的备份文件BACKUP_FILE_01（注意：一定要用dmdba用户创建）

[dmdba@dcp-<备机名称> ~]$ mkdir /dm8/backup

 

7）使用SCP命令拷贝主机的备份文件BACKUP_FILE_01到备机

注意：这是在主机（IP:192.168.0.1； 实例名：DAMENG01）上操作的，需要进入主机的备份文件目录BACKUP_FILE_02后再执行。

[dmdba@dcp-<主机名称> BACKUP_FILE_02]$ scp *.* 192.168.0.2:/dm8/backup

 

可以查看到在备机中已经成功传过去了主机的备份文件（两个文件）

 

8）备机数据库做dmrman恢复还原（IP:192.168.0.2； 实例名：DMSERVER03）

 

RMAN> RESTORE DATABASE '/dm8/data/DAMENG01/dm.ini' FROM BACKUPSET '/dm8/backup'

RMAN> RECOVER DATABASE '/dm8/data/DAMENG01/dm.ini' FROM BACKUPSET '/dm8/backup'

RMAN> RECOVER DATABASE '/dm8/data/DAMENG01/dm.ini' UPDATE DB_MAGIC

 

9） 配置主机数据库 primary（IP:192.168.0.1； 实例名：DMSERVER01）

 

--配置dm.ini文件（参数文件）

[dmdba@dcp-<主机名称> ~]$ cd /dm8/data/DAMENG01/

[dmdba@dcp-<主机名称> DAMENG01]$ vi dm.ini

dm.ini中涉及的参数

--------------------------------------------------

INSTANCE_NAME = DMSERVER01

PORT_NUM = 5231

DW_INACTIVE_INTERVAL = 60

ALTER_MODE_STATUS = 0

ENABLE_OFFLINE_TS = 2

MAL_INI = 1

ARCH_INI = 1

RLOG_SEND_APPLY_MON = 64

--------------------------------------------------

 

--配置dmmal.ini文件（dmmal.ini是新文件）

 

[dmdba@dcp-<主机名称> ~]$ cd /dm8/data/ DAMENG01/

[dmdba@dcp-<主机名称> DAMENG01]$ vi dmmal.ini

 

dmmal.ini内容

--------------------------------------------------

MAL_CHECK_INTERVAL = 5

MAL_CONN_FAIL_INTERVAL = 5

[MAL_INST1]

MAL_INST_NAME = DMSERVER01

MAL_HOST = 192.168.0.1

MAL_PORT = 55201

MAL_INST_HOST = 192.168.0.1

MAL_INST_PORT = 5231

MAL_DW_PORT = 65201

MAL_INST_DW_PORT = 45201

[MAL_INST2]

MAL_INST_NAME = DMSERVER03

MAL_HOST = 192.168.0.2

MAL_PORT = 55221

MAL_INST_HOST = 192.168.0.2

MAL_INST_PORT = 5231

MAL_DW_PORT = 65221

MAL_INST_DW_PORT = 45221

--------------------------------------------------

 

由于在归档文件dmarch.ini需要用到归档路径，因此首先需要建立一个归档路径！

[dmdba@dcp-<主机名称> ~]$ mkdir /dm8/arch

 

[dmdba@dcp-<主机名称> ~]$ cd /dm8/data/ DAMENG01/

[dmdba@dcp-<主机名称> DAMENG01]$ vi dmarch.ini

 

dmarch.ini内容

--------------------------------------------------

[ARCHIVE_REALTIME]

ARCH_TYPE = REALTIME

ARCH_DEST = DMSERVER03

[ARCHIVE_LOCAL1]

ARCH_TYPE = LOCAL

ARCH_DEST = /dm8/arch

ARCH_FILE_SIZE = 128

ARCH_SPACE_LIMIT = 0

--------------------------------------------------

 

 

--配置dmwatcher.ini文件（数据守护文件 dmwatcher.ini是新文件）

 

[dmdba@dcp-<主机名称> ~]$ cd /dm8/data/ DAMENG01/

[dmdba@dcp-<主机名称> DAMENG01]$ vi dmwatcher.ini

 

dmwatcher.ini内容

--------------------------------------------------

[GRP1]

DW_TYPE = GLOBAL

DW_MODE = AUTO

DW_ERROR_TIME = 10

INST_RECOVER_TIME = 60

INST_ERROR_TIME = 10

INST_OGUID = 453332

INST_INI = /dm8/data/DAMENG01/dm.ini 

INST_AUTO_RESTART = 1

INST_STARTUP_CMD = /dm8/bin/dmserver

RLOG_SEND_THRESHOLD = 0 

RLOG_APPLY_THRESHOLD = 0

--------------------------------------------------

 

10） 启动主机数据库（IP:192.168.0.1； 实例名：DMSERVER01）

 

[dmdba@dcp-<主机名称> dm8]$ cd /dm8/bin

[dmdba@dcp-<主机名称> bin]$ ./dmserver /dm8/data/DAMENG01/dm.ini mount

注意：这里要把主机先启动到mount状态，即主机的配置状态

 

在这里，启动好之后会出现“SYSTEM IS READY.”，此时不要再动这个窗口，打开另外一个主机的窗口再去做其他操作！！

 

--登录到主机数据库SYSDBA，修改参数（打开另外一个窗口操作）

 

[dmdba@dcp-<主机名称> DAMENG01]$ cd /dm8/bin

[dmdba@dcp-<主机名称> bin]$ ./disql SYSDBA/dameng123@localhost:5231

服务器[LOCALHOST:5231]:处于普通配置状态

登录使用时间 : 5.553(ms)

disql V8

SQL>

SQL> SP_SET_PARA_VALUE(1,'ALTER_MODE_STATUS',1);

SQL> SP_SET_OGUID(453332);

SQL> SP_SET_PARA_VALUE(1,'ALTER_MODE_STATUS',0);

SQL> ALTER DATABASE PRIMARY;

 

其中：alter database primary 是将数据库的模式修改为主库primary模式

 

重新登录到数据库，发现数据库已经处于了主库的配置状态：

 

11）配置备机数据库standby（IP:192.168.0.2； 实例名：DMSERVER03）

 

--配置dm.ini文件（参数文件）

[dmdba@dcp-<主机名称> ~]$ cd /dm8/data/DAMENG01/

[dmdba@dcp-<主机名称> DAMENG01]$ vi dm.ini

dm.ini中涉及的参数

--------------------------------------------------

INSTANCE_NAME = DMSERVER01

PORT_NUM = 5231

DW_INACTIVE_INTERVAL = 60

ALTER_MODE_STATUS = 0

ENABLE_OFFLINE_TS = 2

MAL_INI = 1

ARCH_INI = 1

RLOG_SEND_APPLY_MON = 64

--------------------------------------------------

 

--配置dmmal.ini文件（dmmal.ini是新文件）

 

[dmdba@dcp-<备机名称> ~]$ cd /dm8/data/ DAMENG01/

[dmdba@dcp-<备机名称> DAMENG01]$ vi dmmal.ini

 

dmmal.ini内容

--------------------------------------------------

MAL_CHECK_INTERVAL = 5

MAL_CONN_FAIL_INTERVAL = 5

[MAL_INST1]

MAL_INST_NAME = DMSERVER01

MAL_HOST = 192.168.0.1

MAL_PORT = 55201

MAL_INST_HOST = 192.168.0.1

MAL_INST_PORT = 5231

MAL_DW_PORT = 65201

MAL_INST_DW_PORT = 45201

[MAL_INST2]

MAL_INST_NAME = DMSERVER03

MAL_HOST = 192.168.0.2

MAL_PORT = 55221

MAL_INST_HOST = 192.168.0.2

MAL_INST_PORT = 5231

MAL_DW_PORT = 65221

MAL_INST_DW_PORT = 45221

--------------------------------------------------

 

由于在归档文件dmarch.ini需要用到归档路径，因此首先需要建立一个归档路径！

[dmdba@dcp-<备机名称> ~]$ mkdir /dm8/arch

 

[dmdba@dcp-<备机名称> ~]$ cd /dm8/data/ DAMENG01/

[dmdba@dcp-<备机名称> DAMENG01]$ vi dmarch.ini

 

dmarch.ini内容

--------------------------------------------------

[ARCHIVE_REALTIME]

ARCH_TYPE = REALTIME

ARCH_DEST = DMSERVER01

[ARCHIVE_LOCAL1]

ARCH_TYPE = LOCAL

ARCH_DEST = /dm8/arch

ARCH_FILE_SIZE = 128

ARCH_SPACE_LIMIT = 0

--------------------------------------------------

 

--配置dmwatcher.ini文件（数据守护文件 dmwatcher.ini是新文件）

 

[dmdba@dcp-<备机名称> ~]$ cd /dm8/data/ DAMENG01/

[dmdba@dcp-<备机名称> DAMENG01]$ vi dmwatcher.ini

 

dmwatcher.ini内容

--------------------------------------------------

[GRP1]

DW_TYPE = GLOBAL

DW_MODE = AUTO

DW_ERROR_TIME = 10

INST_RECOVER_TIME = 60

INST_ERROR_TIME = 10

INST_OGUID = 453332

INST_INI = /dm8/data/DAMENG01/dm.ini 

INST_AUTO_RESTART = 1

INST_STARTUP_CMD = /dm8/bin/dmserver

RLOG_SEND_THRESHOLD = 0 

RLOG_APPLY_THRESHOLD = 0

--------------------------------------------------

 

12） 启动备机数据库（IP:192.168.0.2； 实例名：DMSERVER03）

 

--使用dmserver启动备机到mount状态

[dmdba@dcp-<备机名称> DAMENG01]$ cd /dm8/bin

[dmdba@dcp-<备机名称> bin]$ ./dmserver /dm8/data/DAMENG01/dm.ini mount

注意：这里要把备机先启动到mount状态，即备机的配置状态

 

在这里，启动好之后会出现“SYSTEM IS READY.”，此时不要再动这个窗口，打开另外一个备机的窗口再去做其他操作！！

 

 

--登录到备机数据库SYSDBA，修改参数（打开另外一个窗口操作）

[dmdba@dcp-<备机名称> ~]$ cd /dm8/bin

[dmdba@dcp-<备机名称> bin]$ ./disql SYSDBA/dameng123@localhost:5231

 

服务器[localhost:5231]:处于普通配置状态

登录使用时间 : 5.121(ms)

disql V8

 

SQL> SP_SET_PARA_VALUE(1,'ALTER_MODE_STATUS',1);

SQL> SP_SET_OGUID(453332);

SQL> ALTER DATABASE STANDBY;

SQL> SP_SET_PARA_VALUE(1,'ALTER_MODE_STATUS',0);

 

其中：alter database standby是将数据库的模式修改为备库standby模式

重新登录到数据库，发现数据库已经处于了备库的配置状态：

 

13）配置监视器数据库（IP:192.168.0.3）

 

--配置dmmonitor.ini文件（dmmonitor.ini是新文件）

一定要注意：以下操作必须必须要切换到 dmdba下操作，否则主机、备机打开数据守护后会一直处于配置状态！！

dmmonitor.ini文件可以直接配置到 /dm8 目录下

首先需要创建 /dm8/data/log 目录，在配置dmmonitor.ini中会使用到。

[dmdba@dcp-<监视器名称> ~]$ mkdir /dm8/data

[dmdba@dcp-<监视器名称> ~]$ mkdir /dm8/data/log

 

[dmdba@dcp-<监视器名称> data]$ cd /dm8

[dmdba@dcp-<监视器名称> dm8]$ vi dmmonitor.ini

 

dmmonitor.ini中涉及的参数

--------------------------------------------------

MON_DW_CONFIRM = 1

MON_LOG_PATH = /dm8/data/log 

MON_LOG_INTERVAL = 60

MON_LOG_FILE_SIZE = 32

MON_LOG_SPACE_LIMIT = 0

[GRP1]

MON_INST_OGUID = 453332

MON_DW_IP = 192.168.0.1:65201

MON_DW_IP = 192.168.0.2:65221

--------------------------------------------------

 

 

14）启动数据库的数据守护

 

--首先启动主库的数据守护（IP:192.168.0.1； 实例名：DMSERVER01）

[dmdba@dcp-<主机名称> ~]$ cd /dm8/bin

[dmdba@dcp-<主机名称> bin]$ ./dmwatcher /dm8/data/DAMENG01/dmwatcher.ini

 

需要注意的是，此时主库的第一个窗口还在起着dmserver的mount状态服务！！

 

--再启动备库的数据守护（IP:192.168.0.2； 实例名：DMSERVER 03）

 

[dmdba@dcp-<备机名称> ~]$ cd /dm8/bin

[dmdba@dcp-<备机名称> bin]$ ./dmwatcher /dm8/data/DAMENG01/dmwatcher.ini

 

需要注意的是，此时备库的第一个窗口还在起着dmserver的mount状态服务！！

15）检测搭建的数据守护集群是否正常

 

--进入主机的数据库SYSDBA，查看备机数据库状态

[dmdba@dcp-<主机名称> bin]$ ./disql SYSDBA/dameng123:5231

 

--进入备机的数据库SYSDBA，查看备机数据库状态

[dmdba@dcp-<备机名称> bin]$ ./disql SYSDBA/dameng123:5231

 

发现主机的状态已经变为：

“服务器[LOCALHOST:5246]:处于主库打开状态”；备机的状态已经变为：“服务器[LOCALHOST:5246]:处于备库打开状态”. 在这里，表示数据守护集群已经搭建成功，如果想进一步检测集群的搭建情况，可以检查监视器状态！

 

--进入监视器，查看监视器状态

[dmdba@dcp-<监视器名称> dm8]$ cd /dm8/bin

[dmdba@dcp-<监视器名称> bin]$ ./dmmonitor /dm8/dmmonitor.ini

 

16）数据守护主备集群重启顺序

主备集群重启有顺序要求：

1、关闭监视器

2、关闭主库守护进程

3、关闭备库守护进程

4、关闭主库实例

5、关闭备库实例

6、启动主库实例

7、启动备库实例

8、启动主库守护进程

9、启动备库守护进程

10、启动监视器