--关闭防火墙
[root@~]# systemctl stop firewalld
--关闭开机自启
[root@~]# systemctl disable firewalld

---查看当前状态命令
[root@localhost ~]# getenforce
Permissive

---永久关闭SELinux需修改配置文件/etc/selinux/config，修改 SELINUX=disabled
[root@localhost ~]# cat /etc/selinux/config

This file controls the state of SELinux on the system.

SELINUX= can take one of these three values:

enforcing - SELinux security policy is enforced.

permissive - SELinux prints warnings instead of enforcing.

disabled - No SELinux policy is loaded.

SELINUX=disabled #修改为disabled

SELINUXTYPE= can take one of three two values:

targeted - Targeted processes are protected,

minimum - Modification of targeted policy. Only selected processes are protected.

mls - Multi Level Security protection.

SELINUXTYPE=targeted

su - dmdba
vi .bash_profile
export DM_HOME="/dm8"
export LD_LIBRARY_PATH="$LD_LIBRARY_PATH:/dm8/bin"
export PATH=$PATH:$DM_HOME/bin:$DM_HOME/tool

调整 dmdba 用户下的环境变量文件.bash_profile。

LD_LIBRARY_PATH 主要用于指定查找共享库（动态链接库）时除了默认路径之外的其他路径；
PATH 环境变量定义了用于进行命令和程序查找的目录。 PATH 中的目录使用冒号分隔；
DM_HOME 定义达梦数据库的软件目录。

create user TEST IDENTIFIED by Dameng123 DEFAULT tablespace DMDATA DEFAULT index tablespace dmidx;
create tablespace dmidx
DATAFILE 'DMIDX.DBF' size 100 AUTOEXTEND on NEXT 10 MAXSIZE 5120;
grant create table, create index, create VIEW to test;
grant soi, vti, PUBLIC to test;

select * from USER_TAB_PARTITIONS;
select * from USER_SEGMENTS;

select * from v$ifun t where name like '%SPACE%';

create table t_partrangetab
( logtime datetime,
userid int,
username varchar(20),
areaid int,
optype int
) PARTITION BY RANGE(logtime)
( PARTITION p202502 VALUES LESS THAN ('2025-01-01') TABLESPACE dmdata,
PARTITION p202504 VALUES LESS THAN ('2025-03-01') TABLESPACE dmdata,
PARTITION p202506 VALUES LESS THAN ('2025-05-01') TABLESPACE dmdata,
PARTITION p202508 VALUES LESS THAN ('2025-07-01') TABLESPACE dmdata,
PARTITION p202510 VALUES LESS THAN ('2025-09-01') TABLESPACE dmdata
);

insert into t_partrangetab(logtime, userid, username, areaid, optype)
select sysdate - numtodsinterval(level,'minute'),
DBMS_RANDOM.STRING('A',20),
mod(level,5),
mod(level,10)
from dual
connect by rownum <=100000;

commit;

explain select * from t_partrangetab t where t.logtime < sysdate -10;

-- 5505024
SELECT TABLE_USED_SPACE ('TEST','T_PARTRANGETAB_P202506')*page;

SELECT COUNT(*) FROM t_partrangetab PARTITION (p202506);

alter table t_partrangetab add PARTITION p202510 VALUES LESS THAN ('2025-11-01') TABLESPACE dmdata;
alter table t_partrangetab MERGE PARTITIONS p202301, p202302 INTO PARTITION p202302;
alter table t_partrangetab SPLIT PARTITION p202302 AT ('2023-02-01') INTO
(PARTITION p202301, PARTITION p202302);
alter table T_PARTRANGETAB SPLIT PARTITION p202302 AT ('2023-02-15') INTO
(PARTITION p20230215, PARTITION p202302);

alter table T_PARTRANGETAB truncate PARTITION p202506;
alter table T_PARTRANGETAB drop PARTITION p2024;

CREATE TABLE T_PARTINTERVALRANGETAB
(logtime datetime,
userid int,
username varchar(20),
areaid int,
optype int)
PARTITION BY RANGE("LOGTIME")
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
(PARTITION "P1" VALUES LESS THAN(DATETIME'2023-01-01 00:00:00')
) tablespace tbs ;
insert into t_partintervalrangetab(logtime, userid, username, areaid, optype)
select sysdate + numtodsinterval(level,'hour'), level, DBMS_RANDOM.STRING('A',20),
mod(level,5), mod(level,10)
from dual
connect by rownum <=10000;
commit;

在计算机领域中，{CR}{LF} 代表回车（Carriage Return，CR）和换行（Line Feed，LF） ，它们对应的十六进制值分别是 0x0d 和 0x0a。

所以，{CR}{LF} 组合起来 ，从十六进制角度看就是 0x0d0a， 它常用于表示文本文件中的换行符，在Windows系统中，文本文件默认使用 \r\n（即 0x0d0a）作为换行标识。在这张DM管理工具的文本格式设置界面里，{CR}{LF} 就是设置的行分隔符，与 0x0d0a 本质上是对应的 。

create DIRECTORY dir_ext as '/dm8/directory/ext';
create DIRECTORY dir_ext as '/dm8/directory/ext';
grant read, write on dir_ext to test;

create EXTERNAL table t_ext_part
(
"LOGTIME" DATETIME(6),
"USERID" INT,
"USERNAME" VARCHAR(20),
"AREAID" INT,
"OPTYPE" INT
)
from DATAFILE DEFAULT DIRECTORY dir_ext LOCATION ('T_PARTRANGETAB.txt')
parms (fields DELIMITED by '|', records 0x0d0a, skip 1);

select count(*) from t_ext_part;

options
(skip=1
)
load data
infile 'T_PARTRANGETAB.txt' str x '0d0a'
into table t_extpart02
fields '|'

create EXTERNAL table t_extpart02
(
"LOGTIME" DATETIME(6),
"USERID" INT,
"USERNAME" VARCHAR(20),
"AREAID" INT,
"OPTYPE" INT
)
from DEFAULT DIRECTORY dir_ext LOCATION ('T_PARTRANGETAB.ctl');

create table t_test
as select * from TEST.T_PARTRANGETAB;

insert into t_test(logtime, userid, username, areaid, optype)
select sysdate - numtodsinterval(level,'minute'), level, DBMS_RANDOM,
mod(level,5), mod(level,10)
from dual
connect by rownum <=1000000;

dbms_stats.gather_schema_stats('TEST');
dbms_stats.gather_table_stats('TEST', 'T_TEST', ESTIMATE_PERCENT=>100,
METHOD_OPT=>'for columns SIZE AUTO userid, optype');
dbms_stats.gather_index_stats('TEST', upper('ix_test_userid'), ESTIMATE_PERCENT=>100);

select count() from t_test; --1020741
select count(distinct optype) from t_test;
select optype, count() from t_test group by optype;
dbms_stats.table_stats_show('TEST','T_TEST');

dbms_stats.index_stats_show('TEST',upper('ix_test_userid')); --HEIGHT BALANCED
dbms_stats.column_stats_show('TEST','T_TEST','USERID'); --HEIGHT BALANCED
dbms_stats.column_stats_show('TEST','T_TEST','OPTYPE'); --FREQUENCY

select count(userid) from t_test where userid > 103442 and userid < 107177; --3734
select count(distinct userid) from t_test where userid > 103442 and userid < 107177; --3734

select * from t_test where userid >=999999; --SSEK2: IX_TEST_USERID(T_TEST)
select * from t_test where userid >=1; --CSCN2: INDEX33555591(T_TEST)

dbms_stats.delete_table_stats('TEST','T_TEST');

create index ix_test_userid on t_test(userid);

SQL> spool '/dm8/backup/stat.txt'
SQL> dbms_stats.column_stats_show('TEST','T2', 'OPTYPE');
SQL> more
SQL> spool off

dbms_stats.CREATE_STAT_TABLE('TEST','T_STATTABLE','DMDATA');

dbms_stats.export_TABLE_stats('TEST','T_TEST');
dbms_stats.delete_TABLE_stats('TEST','T_TEST');
select * from SYSSTATS;
dbms_stats.export_TABLE_stats('TEST','T_TEST', STATTAB=>'T_STATTABLE', statid=>'TEST01', statown=>'TEST');
dbms_stats.import_TABLE_stats('TEST','T_TEST', STATTAB=>'T_STATTABLE', statid=>'TEST01', statown=>'TEST');

-- 建索引
create user TEST IDENTIFIED by Dameng123 DEFAULT tablespace DMDATA DEFAULT index tablespace dmidx;
create tablespace dmidx
DATAFILE 'DMIDX.DBF' size 100 AUTOEXTEND on NEXT 10 MAXSIZE 5120;

grant create table, create index, create VIEW to test;
grant soi, vti, PUBLIC to test;

select count(*) from T_SYSOBJECT02; --21309252
select * from T_SYSOBJECT02 where id = 1001; --SSEK2+BLKUP2

insert into SYSDBA.T_SYSOBJECT02
select * from test.T_SYSOBJECT_PART;

dbms_stats.gather_index_stats('SYSDBA','IX_SYSOBJECT02_ID');

--DROP INDEX IX_SYSOBJECT02_ID;
CREATE cluster INDEX IX_SYSOBJECT02_ID ON SYSDBA.T_SYSOBJECT02(id);

create CLUSTER index ix_test_idname on test.t_test(userid, username);

grant select on t_sysobject02 to test;

create DIRECTORY dir_ext as '/dm8/directory/ext';

select to_char(sysdate,' dd ') || 'T';

select * from t_test where USERNAME = upper('ABC') and userid=101;
select count(*) from t_test;
drop index IX_TEST_IDNAME;

select * from i;
create index IX_TEST_NAME on t_test(username);
create index IX_TEST_funcNAME on t_test(upper(username));
create index ix_test_areaoptype on t_test(areaid, optype);

select /+index(t, ix_test_areaoptype)/ *
from t_test t where optype=5;

SQL> show parameter pk_with
行号 PARA_NAME PARA_VALUE

1 PK_WITH_CLUSTER 0
SQL> alter session set 'PK_WITH_CLUSTER'=1;
DMSQL 过程已成功完成
已用时间: 13.773(毫秒). 执行号:2702.
SQL>
SQL> create table t2(id int primary key, name varchar(20));
操作已执行
已用时间: 53.514(毫秒). 执行号:2703.

select * from TEST.T_PARTRANGETAB t
where t.optype=5
and t.logtime >= to_date('2025-07-14','YYYY-MM-DD')
and t.logtime < to_date('2025-07-15','YYYY-MM-DD');

--分区索引
select * from USER_IND_PARTITIONS;
select * from USER_INDEXES t where t.PARTITIONED = 'NO' and t.INDEX_TYPE = 'NORMAL';

select * from USER_INDEXES t where t.INDEX_TYPE = 'CLUSTER';
user_segments;

select * from USER_IND_PARTITIONS;
select * from USER_INDEXES t where t.PARTITIONED = 'NO' and t.INDEX_TYPE = 'NORMAL';
select * from user_segments where t.SEGMENT_NAME in
('IX_PARTTAB_OPTYPE', 'IX_PARTRANGETAB_USERID');

--全文索引
grant CREATE CONTEXT index to test;
alter table t_test add descinfo varchar(4000);

create CONTEXT index cti_TEST_descinfo on t_test(descinfo);

select * from CTIS$CTI_TEST_DESCINFO$I;
select * from CTIS$CTI_TEST_DESCINFO$P;
select * from CTIS$CTI_TEST_DESCINFO$D;
select * from CTIS$CTI_TEST_DESCINFO$N;

alter CONTEXT index cti_TEST_descinfo ON t_test REBUILD;

alter CONTEXT index cti_TEST_descinfo ON t_test INCREMENT;
select * from t_test where CONTAINS(descinfo, '同义词' or 'SYSOBJECTS');

create CONTEXT index cti_TEST_descinfo on t_test(descinfo) sync TRANSACTION; --基于事务

--索引
grant create SEQUENCE to test;

create SEQUENCE seq_test;
select * from USER_SEQUENCES;
select seq_test.nextval;
select seq_test.currval;
create SEQUENCE seq_s1 START WITH 10 INCREMENT by 10 minvalue 20 maxvalue 200 cycle cache 10;

select seq_s1.nextval;
select seq_s1.currval;
select * from SYS.V$CACHESQL;
select * from SYS.V$CACHEPLN;

-- 同义词
grant create SYNONYM to test;
create SYNONYM PART for TEST.T_SYSOBJECT_PART;
select * from PART

select * from user_SYNONYMS;
select * from dba_SYNONYMS;

create table t_dept as select * from dmhr.DEPARTMENT;
物化视图日志的创建，可以使用with primary key 或者with rowid。默认是with primary key，
with primary key要求基础表要有主键。
47
48
create MATERIALIZED VIEW LOG on t_dept WITH rowid(DEPARTMENT_ID,
DEPARTMENT_NAME);
drop MATERIALIZED view v_deptinfo;
create MATERIALIZED view v_deptinfo REFRESH FAST on COMMIT WITH ROWID
as
select b.DEPARTMENT_ID, b.DEPARTMENT_NAME, b.rowid dept_rowid
from t_dept b;
insert into t_dept(DEPARTMENT_ID, DEPARTMENT_NAME)
values(888888, 'TEST');
select * from "MLOG$_T_DEPT";
commit;
select * from v_deptinfo;
select * from t_dept;
select * from v_deptinfo t where T.DEPARTMENT_ID = 888888;
alter MATERIALIZED VIEW v_deptinfo REFRESH FAST on DEMAND;
delete from t_dept where DEPARTMENT_ID < 1003;
commit;
select * from "MLOG$_T_DEPT";
update t_dept set DEPARTMENT_ID =0 where DEPARTMENT_ID=2;
手动刷新
refresh MATERIALIZED VIEW v_deptinfo fast;
物化视图和物化视图日志的删除：
drop MATERIALIZED VIEW LOG on t_dept;
drop MATERIALIZED VIEW v_deptinfo;

restore database to '/dm/data/DAMENG' overwrite from backupset '/dm/backup/FULLDB/'

recover database '/dm/data/DAMENG/dm.ini' update db_magic