达梦数据共享集群(DSC)允许达梦数据库实现在一组集群服务器上运行任何程序包，而客户应用不必作任何更改。
这种体系提供了最高级别的可用性和最灵活的可伸缩性。如果出现集群其中一个服务器失败，达梦会继续运行在集群中其它服务器上。如果您需要更多的处理能力，您可以很方便地在线增加另外的服务器而不必使用户脱机。为了保持低成本，可以将高端的系统构建在标准化的、低成本的产品上。
达梦数据共享集群是达梦企业网格计算体系的基础。达梦DSC技术可为低成本硬件平台提供支持，使其提供优质的服务，并达到或超出昂贵的大型SMP计算机所能提供的可用性和可伸缩性等级。通过显著降低管理成本和提供出色的管理灵活性，达梦为企业网格环境提供了强有力的支持。
本白皮书作为达梦数据共享集群技术概述,强调达梦 Dsc能给企业应用提供最高可用性和可伸缩性的特性和功能。
通过 DM 管理工具日常维护管理数据库，包括：表空间管理、用户管理、模式对象管理、配置归档、备份、作业管理。

一、分区表
---1范围分区

create tablespace TEST datafile '/dm8/data/DAMENG/TEST01.DBF' size 512;
create user TEST IDENTIFIED BY aiNI1995 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)
;

begin
FOR i IN 1..299 LOOP
insert INTO TEST.T1 VALUES(i,'eeee'||i);
COMMIT;
END LOOP;
end;

insert INTO TEST.T1 VALUES(300,'aaa');

alter TABLE TEST.T1 ADD PARTITION pn VALUES LESS THAN (MAXVALUE);

create TABLESPACE tbs1 DATAFILE '/dm8/data/DAMENG/tbs01.DBF' size 64;
create TABLESPACE tbs2 DATAFILE '/dm8/data/DAMENG/tbs02.DBF' size 64;
create TABLESPACE tbs3 DATAFILE '/dm8/data/DAMENG/tbs03.DBF' size 64;
create TABLESPACE tbs4 DATAFILE '/dm8/data/DAMENG/tbs04.DBF' size 64;

create table test.t2
(
"ID" int,
"NAME" varchar(20)
)
partition by RANGE ("ID")
(
PARTITION "P1" VALUES LESS THAN(100) STORAGE (INITIAL 1,NEXT 1,MINEXTENTS 1,FILLFACTOR 0,on "TBS1"),
PARTITION "P2" VALUES LESS THAN(200) STORAGE (INITIAL 1,NEXT 1,MINEXTENTS 1,FILLFACTOR 0,on "TBS2"),
PARTITION "P3" VALUES LESS THAN(300) STORAGE (INITIAL 1,NEXT 1,MINEXTENTS 1,FILLFACTOR 0,on "TBS3"),
PARTITION "pn" VALUES LESS THAN(MAXVALUE) STORAGE (INITIAL 1,NEXT 1,MINEXTENTS 1,FILLFACTOR 0,on "TBS4")
)
storage (INITIAL 1,NEXT 1,MINEXTENTS 1,fillfactor 0)
;

create table test.t3
(
"ID" int,
"NAME" varchar(20)
)
partition by RANGE ("ID")
(
PARTITION "P1" VALUES LESS THAN(100) STORAGE (INITIAL 1,NEXT 1,MINEXTENTS 1,FILLFACTOR 0,on "TBS1"),
PARTITION "P2" VALUES LESS THAN(200) STORAGE (INITIAL 1,NEXT 1,MINEXTENTS 1,FILLFACTOR 0,on "TBS2"),
PARTITION "P3" VALUES LESS THAN(300) STORAGE (INITIAL 1,NEXT 1,MINEXTENTS 1,FILLFACTOR 0,on "TBS3"),
PARTITION "pn" VALUES LESS THAN(MAXVALUE) STORAGE (INITIAL 1,NEXT 1,MINEXTENTS 1,FILLFACTOR 0,on "TBS4")
)
storage (INITIAL 1,NEXT 1,MINEXTENTS 1,fillfactor 0 ,NOBRANCH)
;

---2列表分区

---'北京','天津', '哈尔滨','青岛'
---'上海','南京','杭州'
---'武汉','长沙','合肥'
---'广州','深圳','福建'
---'西安'

create table TEST.t_sales(sales_id INT,saleman VARCHAR,saledata DATE,city CHAR(10))
partition by LIST(city)
(partition P1 values ('北京','天津', '哈尔滨','青岛'),
PARTITION P2 VALUES ('上海','南京','杭州'),
PARTITION P3 VALUES ('武汉','长沙','合肥'),
PARTITION P4 VALUES ('广州','深圳','福建'));

insert INTO TEST.T_SALES values (1,'aaaa','2022-04-18','北京');
commit;

insert INTO TEST.T_SALES values (2,'ccccc','2022-03-18','西安');

alter table TEST.T_SALES ADD PARTITION PN VALUES(DEFAULT);

---3哈希分区
create TABLE TEST.t4
(ID int,
NAME varchar(20)
)
partition by HASH(ID)
(
PARTITION "P1",
PARTITION "P2",
PARTITION "P3",
PARTITION "P4"
);

create TABLE TEST.HASH1
(ID int,
NAME varchar(20)
)
partition by HASH(ID) PARTITIONS 10;

---4组合分区
create TABLE TEST.sales_sum(id int,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-08-01'),
SUBPARTITION P1_3 values less than (maxvalue)),
PARTITION P2 VALUES ('上海','南京','杭州'),
PARTITION P3 VALUES ('武汉','长沙','合肥'),
PARTITION P4 VALUES ('广州','深圳','福建')
);

insert into TEST.SALES_SUM VALUES(1,'aaaaa','2012-05-01','北京');
insert into TEST.SALES_SUM VALUES(2,'bbbbbb','2012-06-01','武汉');
commit;

---5间隔分区

create TABLE TEST.R10_INTERVAL_YEAR
(ID int,NAME VARCHAR(20),BIR DATE)
partition by RANGE(BIR)
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')),
partition P1992 VALUES LESS THAN (to_date('1993-01-01','YYYY-MM-DD'))
);

INSERT INTO TEST.R10_INTERVAL_YEAR VALUES(3,'EEE','1989-03-02');
INSERT INTO TEST.R10_INTERVAL_YEAR VALUES(4,'ffffff','1991-03-02');
INSERT INTO TEST.R10_INTERVAL_YEAR VALUES(7,'UIOULIU','1994-07-02');
commit;

----6分区表维护

---相关的数据字典： dba_tab_partition

select * from SYS.DBA_TAB_PARTITIONS where DBA_TAB_PARTITIONS.TABLE_OWNER='test';

alter TABLE TEST.t1 ADD PARTITION pn VALUES LESS THAN (MAXVALUE);

alter TABLE test.T1 DROP PARTITION pn;

alter TABLE TEST.R10_INTERVAL_YEAR drop PARTITION SYS_p1147_1151;

alter table TEST.T1 MERGE PARTITIONS p2,p3 INTO PARTITION p2_3;

alter TABLE test.T1 SPLIT PARTITION p2_3 AT(200) INTO (PARTITION p2,PARTITION p3);

create TABLE TEST.t10 (ID int,NAME VARCHAR(20));

alter TABLE TEST.T1 EXCHANGE PARTITION P1 WITH TABLE TEST.T10;

select * from test.t10;

create table test.t20(id int);
begin
for i in 1..10000 loop
insert into test.t20 values(i);
end loop;
commit;
end;
drop TABLE TEST.t20;

create TABLE TEST.t20 (id int)
partition by RANGE(id)
(
PARTITION p1 values LESS THAN (1000),
PARTITION p2 values LESS THAN (2000),
PARTITION p3 values LESS THAN (3000),
PARTITION p4 values LESS THAN (4000),
PARTITION p5 values LESS THAN (5000),
PARTITION p6 values LESS THAN (6000),
PARTITION p7 values LESS THAN (7000),
PARTITION pn values LESS THAN (maxvalue)
);

select * from TEST.t20;

二、外部表
create EXTERNAL TABLE TEST.ext (id int,name VARCHAR(20)) from '/dm8/a.ctl';
select * from TEST.EXT;

create EXTERNAL TABLE TEST.ext2 (c1 int,c2 int,c3 int) from DATAFILE '/dm8/b.txt' PARMS(fields delimited by '|');

三、临时表
---1、事务级别： on commit detele rows
create GLOBAL TEMPORARY TABLE TEST.tmp_01(id int) ON COMMIT DELETE ROWS;
insert into test.tmp_01 values(1);
insert into test.tmp_01 values(2);
insert into test.tmp_01 values(3);
commit;
select * from TEST.TMP_01;

---2、会话级别的临时表 on commit preserve rows
create GLOBAL TEMPORARY TABLE TEST.tmp_02(id int) on COMMIT PRESERVE ROWS;

select para_name,para_value from v$dm_ini WHERE "V$DM_INI".PARA_NAME LIKE '%TEMP%';

SP_SET_PARA_VALUE(2,'TEMP_SIZE',20);

sp_trunc_ts_file(3,0,10);
select TABLESPACE_NAME, FILE_NAME, BYTES/1024/1024 from
dba_data_files where tablespace_name='TEMP';

四、序列
---序列：在内存预先申请的一段地址空间，类似于取号排
CREATE SEQUENCE "TEST"."s1" INCREMENT BY 2 START WITH 1 MAXVALUE 10 MINVALUE 1;

五、同义词
create SYNONYM TEST.SY1 FOR TEST.T1;
create PUBLIC SYNONYM SY2 FOR TEST.EXT;

六、视图
CREATE VIEW TEST.V1 AS SELECT employee_id,employee_name FROM DMHR.EMPLOYEE;
CREATE VIEW TEST.V2 AS
SELECT d.department_name, a.av1
FROM (select department_id,AVG( salary) as av1
FROM dmhr.EMPLOYEE e GROUP BY DEPARTMENT_ID having
avg(salary)>10000) a
join DMHR.DEPARTMENT d ON a.DEPARTMENT_ID=d.DEPARTMENT_ID;

---物化视图
create MATERIALIZED VIEW TEST.MV1 AS SELECT * FROM TEST.T1;
create MATERIALIZED VIEW TEST.MV2 AS SELECT * FROM TEST.T1 WHERE id >200;

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.MV3 REFRESH FAST ON COMMIT AS SELECT * FROM TEST.T1;---自动更新

七、索引
---唯一索引
create UNIQUE INDEX ind_t2 ON TEST.t2(ID) TABLESPACE ind;

---函数索引
create INDEX ind_tmp ON TEST.TMP(UPPER(email)) TABLESPACE ind;

---复合索引
create INDEX IND_TMP1 on TEST.TMP (EMPLOYEE_ID,employee_name);

---位图索引
create BITMAP INDEX ind_t2 ON TEST.T2(SEX);

---索引维护
alter INDEX TEST.IND_TMP REBUILD;
alter INDEX TEST.IND_TMP1 REBUILD ONLINE;
drop INDEX TEST.IND_TMP1;

---分区索引
create INDEX ind_sale2 ON TEST.SALE2(SALE2.ID);
create INDEX ind_sale3 ON TEST.SALE2(SALE2.NAME) GLOBAL STORAGE(INITIAL 1,NEXT 1,MINEXTENTS 1,on ind);

---全文索引
create CONTEXT INDEX cti_ad on PERSON.address(ADDRESS1) LEXER default_lexer;

---执行计划
explain select * from TEST.T1;

八、审计
---审计---使用审计用户
sp_set_enable_audit(2);---0,1,2
sp_set_enable_audit(0);---0,1,2

sp_audit_stmt('table','test','all');---语句级别-表，用户，all，successful，fail
sp_noaudit_stmt('table','test','all');

sp_audit_objecit('UPDATE','test','test','emp','salary','all');---对象级别
sp_noaudit_objecit('UPDATE','test','test','emp','salary','all');

---select username,operation,sql_text form v$auditrecords;

九、AWR报告
---AWR报告
select sf_check_awr_sys;
sp_init_awr_sys(1);
call SYS.DBMS_WORKLOAD_REPOSITORY.AWR_SET_INTERVAL(10);
call SYS.DBMS_WORKLOAD_REPOSITORY.create_snapshot();
select * from sys.wrm$_snapshot;
call SYS.AWR_REPORT_HTML(1,2,'/dm8','awr1.html');

十、快速加载
---快速加载dmfldr
create TABLE TEST.fldr1 (id int,name VARCHAR,bir DATE);
---导入命令：./dmfldr sysdba/sysdba control='/dm8/data/fldr1.ctl'
select * from TEST.FLDR1;

create TABLE TEST.da(c1 int,c2 BLOB,c3 CLOB);
insert into TEST.DA VALUES(1,0XAB12354323567,'jasdfkjasldkfjaslkdfjalsasdklfajsdlfkasjdf');
insert into TEST.DA VALUES(2,0XAB12354224567,'jasdfkjasldkfjaslkdfjalsasdklfajsdlfkasjdf');
insert into TEST.DA VALUES(3,0XAB12353225367,'jasdfkjasldkfjaslkdfjalsasdklfajsdlfkasjdf');
commit;

---控制文件dafldr.ctl
---options(skip=1) 跳过第一行
LOAD DATA
INFILE '/dm8/fldr/dafldr.txt'
INTO TABLE TEST.DA
FILEDS '|'
(C1,
C2,
C3)

---导入命令：./dmfldr sysdba/SYSDBA control='/dm8/fldr/dafldr.ctl' lob_directory='/dm8/fldr' mode='in'
---./dmfldr sysdba/SYSDBA control='/dm8/fldr/dafldr.ctl' lob_directory='/dm8/fldr' badfile='/dm8/fldr/test.bad'