今天参加了DCP考试，笔试问题不大，但是机考感觉时间很紧，虽然有4小时听上去时间不短，但对于新手来说，各种命令不熟悉，打字错误浪费了很多时间，哎，希望借此文能拉回点考分，其实原理都已经明白了，忘老师手下留情。

A.分区表（仅列出重要考点）

1、要求：分区列是数字或是日期类型

create tablespace TEST datafile '/dm8/data/DAMENG/test_01.dbf' size 512;
create user TEST identified by dameng123 default tablespace test;
---创建范围分区
create table "TEST"."T1"
( "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)
)
storage(initial 1, next 1, minextents 1, fillfactor 0)
---创建默认分区
alter table test.t1 add partition pn values less than(maxvalue);

insert into test.t1 values(100,'a');
insert into test.t1 values(201,'b');
insert into test.t1 values(300,'c');

2.哈希分区

CREATE TABLE TEST.T4
(ID INT, NAME VARCHAR(20))
PARTITION BY HASH(ID)
( PARTITION "01", PARTITION "02", PARTITION "03", PARTITION "04"
) ;
create table test.t_hash(id int, name varchar(20))
partition by hash (id) partitions 10;

B.fastload 快速数据加载

1.编辑数据文件

cd /dm8/fldr/
vi fldrtest.txt
1,aaaaa,2022-04-21
2,bbbbb,
3,cccc,2021-11-11

2.编写控制文件

vi fldrtest.ctl
LOAD DATA
INFILE '/dm8/fldr/fldrtest.txt' INTO TABLE TEST.FLDRTEST
FIELDS ','

3.创建表

create table test.fldrtest(id int, name varchar(20), bir date);

4.快速加载数据

./dmfldr sysdba/Zd76215@ control='/dm8/fldr/fldrtest.ctl'

-----------大字段导入导出---------------------

1.创建大字段表

create table test.dtoutput(c1 int, c2 blob, c3 clob);
insert into test.dtoutput values(1,0XAB1211032DE,'THIS IS A dmfldr
test');
insert into test.dtoutput
values(2,0XAB1211032DE,'werwerwr3cdsRWR');
insert into test.dtoutput
values(3,0XAB1211032DE,'REWRWRWCVDHGH');
COMMIT;
select * from test.dtoutput;

2、创建控制文件

[dmdba@localhost fldr]$ cat dtfldr.ctl
LOAD DATA
INFILE '/dm8/fldr/dtfldr.txt' INTO TABLE TEST.DTOUTPUT
FIELDS '|' (C1, C2, C3)

3、大字段导出

[dmdba@localhost bin]$ ./dmfldr sysdba/zd761215 control='/dm8/fldr/dtfldr.ctl' lob_directory='/dm8/fldr' mode='out'

4、大字段导入--创建带大字段的表

create table test.dtinput(c1 int, c2 blob, c3 clob);

5、大字段导入--创建控制文件

[dmdba@localhost fldr]$ cat dtfldr.ctl
LOAD DATA
INFILE '/dm8/fldr/dtfldr.txt' INTO TABLE TEST.DTINPUT
FIELDS '|' (C1, C2, C3)

6、大字段导入

./dmfldr sysdba/zd761215 control='/dm8/fldr/dtfldrin.ctl' lob_directory='/dm8/fldr' mode='in'

7、错误文件写入

./dmfldr sysdba/zd761215 control='/dm8/fldr/dtfldrin.ctl' lob_directory='/dm8/fldr' badfile='/dm8/fldr/test.bad' mode='in'

C、索引

DBMS_STATS 包：收集统计信息，删除统计信息，查看统计信息。

1、GATHER_TABLE_STATS
2、COLUMN_STATS_SHOW

DBMS_STATS.GATHER_TABLE_STATS('TEST','T1','P1',100,FALSE,'FOR ALL COLUMNS SIZE AUTO')

索引类型：

唯一索引
函数索引
复合索引
位图索引

索引的维护
新建索引，重建索引，删除索引，收集统计信息，更新统计信息

查看索引的相关信息
DBA_INDEXES, USER_INDEXES;

分区索引

全文索引

create context index cti_name on test.t1(name) lexer
default_lexer;

全文索引更新：
alter context index cti_name on test.t1 rebuild;

select * from test.cti$cti_name$i;
select * from test.cti$cti_name$p;
select * from test.cti$cti_name$n;
select * from test.cti$cti_name$d;

D、外部表

1.编辑文本文件

vi a.txt
1,AAAAA
2,BBBBB
3,DD
4,CCCC
5,FF
6,WERWRWRW
7,iiiii
8,nnnnn
9,SER
10,EWDEWRWRWRWRW

2.控制文件

cat a.ctl
LOAD DATA
INFILE 'a.txt' INTO TABLE TEST.EXT
FIELDS ',

3.创建目录

create directory extdir as '/dm8/ext'

4.创建外部表

create external table test.ext(id int, name varchar(20)) from default directory extdir location('a.ctl')

E、序列

CREATE SEQUENCE "TEST"."S1"
INCREMENT BY 2
START WITH 1
MAXVALUE 20
MINVALUE 1
CYCLE
CACHE 2
NOORDER ;

F、物化视图

1.创建

create materialized view TEST.MV1 AS SELECT * FROM TEST.T1;

create materialized view test.mv2 as select * from test.t1 where id>200;

refresh materialized view test.mv1;

2.自动更新的物化视图

create materialized view log on test.t1;
create materialized view test.mv3 refresh complete on commit as
select * from test.t1;

3.创建物化视图日志

create materialized view log on test.t1;
创建完全更新物化视图
create materialized view test.mv3 refresh complete on commit as
select * from test.t1;
创建快速更新物化视图（必须要有物化视图日志）
create materialized view test.mv4 refresh fast on commit as select *
from test.t1;

G、审计

系统审计

语句审计 DDL粗粒度

对象审计 DML 细粒度

---审计开关：

使用sysauditor登录
DM8
开启系统审计SP_SET_ENABLE_AUDIT(PARAM INT);
关闭系统审计 Sp_set_enable_audit(0);

开启语句级审计

Sp_audit_stmt(type,username,whenever)
sp_audit_stmt('table','test','all');

关闭语句级审计

Sp_noaudit_stmt(type,username,whenever)
Sp_noaudit_stmt(‘TABLE’,’TEST’,’ALL’);

--对像审计打开

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

--对像审计关闭

Sp_noaudit_object
例：sp_audit_object('INSERT','TEST','TEST','T1','ID','ALL');

--查看审计记录

select * from v$auditrecords;

H、集群搭建（重头戏）

1.主机备份、备机恢复

主机操作
backup database '/dm8/data/DM01/dm.ini' full to backup1 backupset '/dm8/backup/BACKUP_FILE_01'
scp ./* 192.168.79.134:/dm8/backup/
备机操作
restore database '/dm8/data/DM01/dm.ini' from backupset '/dm8/backup'
recover database '/dm8/data/DM01/dm.ini' from backupset '/dm8/backup'
recover database '/dm8/data/DM01/dm.ini' update db_magic

2、主机配置（/dm8/DMDW）

------------------------1）配置dm.ini
INSTANCE_NAME = DM01SERVER
PORT_NUM = 5238
DW_INACTIVE_INTERVAL = 60
ALTER_MODE_STATUS = 0 ****
ENABLE_OFFLINE_TS = 2 ****
MAL_INI = 1 ****
ARCH_INI = 1 ****
RLOG_SEND_APPLY_MON = 64
-----------------------2）配置dmmal.ini
MAL_CHECK_INTERVAL = 5
MAL_CONN_FAIL_INTERVAL = 5
[MAL_INST1]
MAL_INST_NAME = DM01SERVER
MAL_HOST = 192.168.79.133
MAL_PORT = 55101
MAL_INST_HOST = 192.168.79.133
MAL_INST_PORT = 5238
MAL_DW_PORT = 65101
MAL_INST_DW_PORT = 45101
[MAL_INST2]
MAL_INST_NAME = DM02SERVER
MAL_HOST = 192.168.79.134
MAL_PORT = 55101
MAL_INST_HOST = 192.168.79.134
MAL_INST_PORT = 5238
MAL_DW_PORT = 65101
MAL_INST_DW_PORT=45101
-----------------------3）配置dmarch.ini
[ARCHIVE_REALTIME]
ARCH_TYPE = REALTIME
ARCH_DEST = DM02SERVER
[ARCHIVE_LOCAL1]
ARCH_TYPE = LOCAL
ARCH_DEST = /dm8/arch
ARCH_FILE_SIZE = 128
ARCH_SPACE_LIMIT = 0
----------------------4）配置dmwatcher.ini
[GRP1]
DW_TYPE = GLOBAL
DW_MODE = AUTO
DW_ERROR_TIME = 10
INST_RECOVER_TIME = 60
INST_ERROR_TIME = 10
INST_OGUID = 453331
INST_INI = /dm8/data/DM01/dm.ini
INST_AUTO_RESTART = 1
INST_STARTUP_CMD = /dm8/bin/dmserver
RLOG_SEND_THRESHOLD = 0
RLOG_APPLY_THRESHOLD = 0

3启动主库+修改参数

./dmserver /dm8/data/DM01/dm.ini mount
SP_SET_PARA_VALUE(1,'ALTER_MODE_STATUS', 1);
设置OGUID: SQL> SP_SET_OGUID(453331);
ALTER DATABASE PRIMARY;
SP_SET_PARA_VALUE(1,'ALTER_MODE_STATUS', 0);
使主库进入配置状态

4.备机配置信息

备机配置
--------------------1）配置dm.ini
INSTANCE_NAME = DM02SERVER
PORT_NUM = 5238
DW_INACTIVE_INTERVAL = 60
ALTER_MODE_STATUS = 0 ************
ENABLE_OFFLINE_TS = 2 ************
MAL_INI = 1 ************
ARCH_INI = 1 ************
RLOG_SEND_APPLY_MON = 64
--------------------2）配置dmmal.ini
MAL_CHECK_INTERVAL = 5
MAL_CONN_FAIL_INTERVAL = 5
[MAL_INST1]
MAL_INST_NAME = DM01SERVER
MAL_HOST = 192.168.79.133
MAL_PORT = 55101
MAL_INST_HOST = 192.168.79.133
MAL_INST_PORT = 5238
MAL_DW_PORT = 65101
MAL_INST_DW_PORT = 45101
[MAL_INST2]
MAL_INST_NAME = DM02SERVER
MAL_HOST = 192.168.79.134
MAL_PORT = 55101
MAL_INST_HOST = 192.168.79.134
MAL_INST_PORT = 5238
MAL_DW_PORT = 65101
MAL_INST_DW_PORT = 45101
----------------3）配置dmarch.ini
[ARCHIVE_REALTIME]
ARCH_TYPE = REALTIME
ARCH_DEST = DM01SERVER
[ARCHIVE_LOCAL1]
ARCH_TYPE = LOCAL
ARCH_DEST = /dm8/arch
ARCH_FILE_SIZE = 128
ARCH_SPACE_LIMIT = 0
-----------------4）配置dmwatcher.ini
[GRP1]
DW_TYPE = GLOBAL
DW_MODE = AUTO
DW_ERROR_TIME = 10
INST_RECOVER_TIME = 60
INST_ERROR_TIME = 10
INST_OGUID = 453331
INST_INI = /dm8/data/DM01/dm.ini
INST_AUTO_RESTART = 1
INST_STARTUP_CMD = /dm8/bin/dmserver
RLOG_APPLY_THRESHOLD = 0

5启动备库+修改参数

./dmserver /dm8/data/DM01/dm.ini mount
SQL> SP_SET_PARA_VALUE(1,'ALTER_MODE_STATUS', 1);
设置OGUID: SQL> SP_SET_OGUID(453331);
设置备库模式：SQL> ALTER DATABASE STANDBY;
SQL> SP_SET_PARA_VALUE(1,'ALTER_MODE_STATUS', 0);

6监视器配置

vi /dm8/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 = 453331
MON_DW_IP = 192.168.79.133:65101
MON_DW_IP = 192.168.79.134:65101

7注册服务

分别在主库和备库注册数据守护服务：
cd /dm8/script/root
[root@localhost root]#./dm_service_installer.sh –t dmwatcher –p DMWATCHER –watcher_ini /dm8/data/DM01/dmwatcher.ini
备库注册数据守护服务：
cd /dm8/script/root
[root@localhost root]#./dm_service_installer.sh –t dmwatcher –p DMWATCHER –watcher_ini /dm8/data/DM01/dmwatcher.ini

在监视服务器上注册服务
cd /dm8/script/root
./dm_service_installer.sh –t dmmonitor –p DMMONITOR –monitor_ini /dm8/dmmonitor.ini

上述就是考试中经常会考到的部分，请大家熟练练习，考试时好像没办法复制黏贴，需要手动生成配置文件，请务必熟练。

达梦DCP备考准备