DCP培训总结

一、分区表

1、范围分区
create table “TEST”.“T1”
(
“ID” INT,
“NAME” VARCHAR(20)
) PA
RTITION BY RANGE (“ID”)
( PA
RTITION “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)
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

二、索引管理

1、分区索引
分区索引建立和主表索引建立一致；
reate index “IND_SALE_SUM” on “TEST”.“SALES_SUM”(“SALE_ID”)storage(initial 1,next 1,minextents 1,on “IND”);
验证方法：
explain select * from 表名；
2、全文索引
全文索引出现位置不在模式里，和模式平级
create context index cti_address on person .address(address1) lexer default_lexer;
验证方法：
通过explain执行计划进行查看

三、外部表

1、创建外部数据文件，具备标准格式的数据文件
2、创建外部表；
create external table test.ext2(c1 int, c2 int, c3 int) from datafile
‘/dm8/ext/b.txt’ parms(fields delimited by ‘|’);
3、外部表和表同级

四、审计

审计需在审计用户下执行
1、打开审计
SP_SET_ENABLE_AUDIT（2）；
2、设置审计
语句级别审计
Sp_audit_stmt(‘UPDATE TABLE’,’TEST’,’ALL’)；
对象级别审计
Sp_audit_object(type, username, schema_name, object_name,
column_name, whenever)
3、查询执行结果
select username, operation, sql_text from v$auditrecords;

五、物化视图

1、创建物化视图
create materialized view test.mv2 as select * from test.t1 where id>200;
2、手动更新
修改 test.t1,手动更新 MV1,MV2;
refresh materialized view test.mv1;

六、序列

通过图形界面进行设置操作

七、数据守护

1、主机配置
（1）dm.ini
INSTANCE_NAME = GRP1_RT_01
PORT_NUM = 5246
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 = GRP1_RT_01
MAL_HOST = 192.168.1.30
MAL_PORT = 55101
MAL_INST_HOST = 192.168.1.30
MAL_INST_PORT = 5246
MAL_DW_PORT = 65101
MAL_INST_DW_PORT = 45101
[MAL_INST2]
MAL_INST_NAME = GRP1_RT_03
MAL_HOST = 192.168.1.20
MAL_PORT = 55121
MAL_INST_HOST = 192.168.1.20
MAL_INST_PORT = 5246
MAL_DW_PORT = 65121
MAL_INST_DW_PORT = 45121

（3）dmarch.ini
[ARCHIVE_REALTIME]
ARCH_TYPE = REALTIME
ARCH_DEST = GRP1_RT_03
[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/DMDW/dm.ini
INST_AUTO_RESTART = 1
INST_STARTUP_CMD = /dm8/bin/dmserver
RLOG_SEND_THRESHOLD = 0
RLOG_APPLY_THRESHOLD = 0

（5）启动主库
./dmserver /dm8/data/DMDW/dm.ini mount

SQL>SP_SET_PARA_VALUE(1, ‘ALTER_MODE_STATUS’, 1);
SQL>sp_set_oguid(453331);
SQL>alter database primary;
SQL>SP_SET_PARA_VALUE(1, ‘ALTER_MODE_STATUS’, 0);
2、备机配置
dm.ini

INSTANCE_NAME = GRP1_RT_03
PORT_NUM = 5246
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
MAL_CHECK_INTERVAL = 5
MAL_CONN_FAIL_INTERVAL = 5
[MAL_INST1]
MAL_INST_NAME = GRP1_RT_01
MAL_HOST = 192.168.1.30
MAL_PORT = 55101
MAL_INST_HOST = 192.168.1.30
MAL_INST_PORT = 5246
MAL_DW_PORT = 65101
MAL_INST_DW_PORT = 45101
[MAL_INST2]
MAL_INST_NAME = GRP1_RT_03
MAL_HOST = 192.168.1.20
MAL_PORT = 55121
MAL_INST_HOST = 192.168.1.20
MAL_INST_PORT = 5246
MAL_DW_PORT = 65121
MAL_INST_DW_PORT = 45121

dmarch.ini
[ARCHIVE_REALTIME]
ARCH_TYPE = REALTIME
ARCH_DEST = GRP1_RT_01
[ARCHIVE_LOCAL1]
ARCH_TYPE = LOCAL
ARCH_DEST = /dm8/arch
ARCH_FILE_SIZE = 128
ARCH_SPACE_LIMIT = 0

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/DMDW/dm.ini
INST_AUTO_RESTART = 1
INST_STARTUP_CMD = /dm8/bin/dmserver
RLOG_APPLY_THRESHOLD = 0

以 Mount 方式启动备库
./dmserver /dm8/data/DMDW/dm.ini mount
SQL>SP_SET_PARA_VALUE(1, ‘ALTER_MODE_STATUS’, 1);
SQL>sp_set_oguid(453331);
SQL>alter database standby;
SQL>SP_SET_PARA_VALUE(1, ‘ALTER_MODE_STATUS’, 0);
3、监视机配置
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.1.30:65101
MON_DW_IP = 192.168.1.20:65121