一、查看系统环境

[root@kylinser ???]# echo $DISPLAY
:1.0
[root@kylinser ???]# cat /proc/version
Linux version 4.19.90-24.4.v2101.ky10.x86_64 (KYLINSOFT@localhost.localdomain) (gcc version 7.3.0 (GCC)) #1 SMP Mon May 24 12:14:55 CST 2021

[root@kylinser ???]# ldd --version
ldd (GNU libc) 2.28
[root@kylinser ???]# rpm -aq|grep glibc
glibc-2.28-49.p02.ky10.x86_64
glibc-devel-2.28-49.p02.ky10.x86_64
glibc-common-2.28-49.p02.ky10.x86_64

[root@kylinser ???]# lscpu
??? x86_64
CPU ??? 32-bit, 64-bit

[root@kylinser ???]# free -m
total used free shared buff/cache available
Mem: 1944 843 27 8 1073 1028
Swap: 2047 8 2039
[root@kylinser ???]# gcc --version
gcc (GCC) 7.3.0
Copyright ? 2017 Free Software Foundation, Inc.

[root@kylinser ???]# df -h
devtmpfs 946M 0 946M 0% /dev
tmpfs 973M 4.0K 973M 1% /dev/shm
tmpfs 973M 9.4M 964M 1% /run
tmpfs 973M 0 973M 0% /sys/fs/cgroup
/dev/mapper/klas_kylinser-root 19G 8.2G 11G 43% /
tmpfs 2.0G 12K 2.0G 1% /tmp
/dev/sda1 1014M 211M 804M 21% /boot
tmpfs 195M 76K 195M 1% /run/user/0
/dev/sr0 4.1G 4.1G 0 100% /run/media/root/Kylin-Server-10

[root@kylinser ???]# mount -o remount,size=2G /tmp
[root@kylinser ???]#

关闭防火墙
[root@kylinser ???]#
[root@kylinser ???]# systemctl status firewalld
?? firewalld.service - firewalld - dynamic firewall daemon
Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled)
Active: inactive (dead)
Docs: man:firewalld(1)
[root@kylinser ???]# systemctl stop firewalld
[root@kylinser ???]# systemctl disable firewalld
[root@kylinser ???]#

二、安装

1、创建用户及目录
[root@kylinser ???]# groupadd dinstall
[root@kylinser ???]# useradd -g dinstall -m -d /home/dmdba -s /bin/bash dmdba

[root@kylinser ???]# passwd dmdba
??? dmdba ??? ??
??? ???
??? ??? //pwd:kylin@123
passwd???
[root@kylinser ???]# id dmdba
???id=1000(dmdba) ??id=1000(dinstall) ??=1000(dinstall)
[root@kylinser ???]#

[root@kylinser ???]# mkdir /dm8
[root@kylinser ???]# chown -R dmdba:dinstall /dm8
[root@kylinser ???]# ll -ld /dm8
drwxr-xr-x 2 dmdba dinstall 6 11?? 13 21:16 /dm8
[root@kylinser ???]# chmod -R 755 /dm8
[root@kylinser ???]# ll -ld /dm8
drwxr-xr-x 2 dmdba dinstall 6 11?? 13 21:16 /dm8
[root@kylinser ???]#

2、修改文件限制数

[root@kylinser ???]# vi /etc/security/limits.conf
dmdba soft nofile 102400
dmdba hard nofile 102400
root soft nofile 102400
root hard nofile 102400

[root@kylinser ???]# ulimit -a
core file size (blocks, -c) unlimited
data seg size (kbytes, -d) unlimited
scheduling priority (-e) 0
file size (blocks, -f) unlimited
pending signals (-i) 7567
max locked memory (kbytes, -l) 16384
max memory size (kbytes, -m) unlimited
open files (-n) 1024
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
real-time priority (-r) 0
stack size (kbytes, -s) 8192
cpu time (seconds, -t) unlimited
max user processes (-u) 7567
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited
[root@kylinser ???]#

3、挂载安装境像iso

[root@kylinser ???]# mkdir /mnt/dm
[root@kylinser ???]# cd /opt
[root@kylinser opt]# ls
dm8_20220525_x86_rh6_64.iso firefox patch_workspace

[root@kylinser opt]# mount -o loop /opt/dm8_20220525_x86_rh6_64.iso /mnt/dm
mount: /mnt/dm: WARNING: source write-protected, mounted read-only.
[root@kylinser opt]#
[root@kylinser opt]# ls
dm8_20220525_x86_rh6_64.iso firefox patch_workspace

[root@kylinser opt]# cd /mnt/dm
[root@kylinser dm]# ls
‘DM8 Install.pdf’ DMInstall.bin
[root@kylinser dm]# ls -al
??? 931827
dr-xr-xr-x 1 root root 2048 5?? 25 15:37 .
drwxr-xr-x 3 root root 16 11?? 13 21:32 …
-r-xr-xr-x 1 root root 2802237 5?? 25 15:31 ‘DM8 Install.pdf’
-r-xr-xr-x 1 root root 951385360 5?? 25 15:37 DMInstall.bin
[root@kylinser dm]#

4、设置安装图形显示

[root@kylinser dm]# xhost +
[root@kylinser dm]#
[root@kylinser dm]# echo $DISPLAY
:1.0
[root@kylinser dm]#
[root@kylinser dm]# su - dmdba
[dmdba@kylinser ~]$ echo $DISPLAY

[dmdba@kylinser ~]$ export DISPLAY=:1.0
[dmdba@kylinser ~]$ echo $DISPLAY
:1.0
[dmdba@kylinser ~]$ cd /mnt/dm
[dmdba@kylinser dm]$ ls -al
??? 931827
dr-xr-xr-x 1 root root 2048 5?? 25 15:37 .
drwxr-xr-x 3 root root 16 11?? 13 21:32 …
-r-xr-xr-x 1 root root 2802237 5?? 25 15:31 ‘DM8 Install.pdf’
-r-xr-xr-x 1 root root 951385360 5?? 25 15:37 DMInstall.bin

5、注意以dmdba用户执行安装

[dmdba@kylinser dm]$ ./DMInstall.bin

No protocol specified

[dmdba@kylinser dm]$ exit
[root@kylinser dm]# xhost +
access control disabled, clients can connect from any host

[root@kylinser dm]# su - dmdba
??? ?? 11?? 13 21:37:04 CST 2022 pts/1 ??
[dmdba@kylinser ~]$ echo $DISPLAY

[dmdba@kylinser ~]$ export DISPLAY=:1.0
[dmdba@kylinser ~]$ echo $DISPLAY
:1.0
[dmdba@kylinser ~]$ cd /mnt/dm
[dmdba@kylinser dm]$ ./DMInstall.bin
/usr/share/themes/kylin-black-theme/gtk-2.0/gtkrc:817: ???apps/caja.rc??

6、安装完后root执行脚本
[root@kylinser ???]# /dm8/script/root/root_installer.sh
修改 /dm8/bin/dm_svc.conf 到/etc目录
修改服务器权限
创建DmAPService服务
Created symlink /etc/systemd/system/multi-user.target.wants/DmAPService.service --> /usr/lib/systemd/system/DmAPService.service.
创建服务(DmAPService)完成
启动DmAPService服务
[root@kylinser ???]#

7、安装完成后，请配置LD_LIBRARY_PATH坏境变量，以确个客户端工具所有功能能正常使用

root@kylinser dmdba]# more .bash_profile

Source /root/.bashrc if user has one

[ -f ~/.bashrc ] && . ~/.bashrc

export LD_LIBRARY_PATH=“$LD_LIBRARY_PATH:/dm8/bin”
export DM_HOME=“/dm8”
[root@kylinser dmdba]#

[root@kylinser dmdba]# vi .bash_profile
export PATH=“$PATH:$DM_HOME/bin:$DM_HOME/tool”
[dmdba@kylinser bin]$ source /home/dmdba/.bash_profile
[dmdba@kylinser bin]$ manager

[root@kylinser ???]# ps -ef|grep Dm*
UID PID PPID C STIME TTY TIME CMD
root 1144 1 0 20:45 ? 00:00:00 /usr/sbin/gssproxy -D
root 1158 1 0 20:45 ? 00:00:00 sshd: /usr/sbin/sshd -D [listener] 0 of 10-100 startups
root 2435 1709 0 20:46 ? 00:00:00 /usr/bin/fcitx -D
root 3167 2708 0 20:47 ? 00:00:02 /usr/bin/fcitx -D
dmdba 6017 5959 0 21:45 pts/1 00:00:00 /bin/sh ./DMInstall.bin
dmdba 8171 6017 5 21:45 pts/1 00:00:40 /tmp/DMInstall/source/jdk/bin/java -Xms256m -Xmx2048m -cp /tmp/DMInstall/source/tool/plugins/:/tmp/DMInstall/source/tool/dropins/com.dameng/plugins/:/tmp/DMInstall/source/tool/dropins/com.dameng/plugins/com.dameng.third/:/tmp/DMInstall/install/lib/:/tmp/DMInstall/install/* -Djava.library.path=/tmp/DMInstall/source/bin -Dsource.dir=/tmp/DMInstall/source -Dinstall.dir=/tmp/DMInstall/install -Ddameng.log.file=/tmp/DMInstall/install/log4j.xml -Ddameng.java.home= com.dameng.install.ui.MainApplication
root 8586 8381 0 21:58 pts/2 00:00:00 grep Dm*
[root@kylinser ???]# ps -ef|grep DmAP*
root 8588 8381 0 21:59 pts/2 00:00:00 grep DmAP*
[root@kylinser ???]#

[dmdba@kylinser dm]$ cd /dm8/tool
[dmdba@kylinser tool]$ ls -al
??? 1456
drwxr-xr-x 9 dmdba dinstall 4096 11?? 13 21:50 .
drwxr-xr-x 16 dmdba dinstall 251 11?? 13 21:51 …
-rwxr-xr-x 1 dmdba dinstall 908 11?? 13 21:50 analyzer
-rwxr-xr-x 1 dmdba dinstall 282440 11?? 13 21:50 analyzer.bmp
-rwxr-xr-x 1 dmdba dinstall 698 11?? 13 21:50 backup.xml
drwxr-xr-x 3 dmdba dinstall 70 11?? 13 21:50 configuration
-rwxr-xr-x 1 dmdba dinstall 919 11?? 13 21:50 console
-rwxr-xr-x 1 dmdba dinstall 282440 11?? 13 21:50 console.bmp
-rwxr-xr-x 1 dmdba dinstall 749 11?? 13 21:51 dbca.sh
-rwxr-xr-x 1 dmdba dinstall 121 11?? 13 21:50 disql
drwxr-xr-x 6 dmdba dinstall 182 11?? 13 21:50 dmagent
-rwxr-xr-x 1 dmdba dinstall 730 11?? 13 21:51 dmservice.sh
drwxr-xr-x 3 dmdba dinstall 24 11?? 13 21:50 dropins
-rwxr-xr-x 1 dmdba dinstall 987 11?? 13 21:50 dts
-rwxr-xr-x 1 dmdba dinstall 282440 11?? 13 21:50 dts.bmp
-rwxr-xr-x 1 dmdba dinstall 1222 11?? 13 21:51 dts_cmd_run.sh
-rwxr-xr-x 1 dmdba dinstall 2063 11?? 13 21:51 log4j.xml
-rwxr-xr-x 1 dmdba dinstall 919 11?? 13 21:50 manager
-rwxr-xr-x 1 dmdba dinstall 282440 11?? 13 21:50 manager.bmp
-rwxr-xr-x 1 dmdba dinstall 903 11?? 13 21:50 monitor
-rwxr-xr-x 1 dmdba dinstall 282440 11?? 13 21:50 monitor.bmp
-rwxr-xr-x 1 dmdba dinstall 714 11?? 13 21:51 nca.sh
drwxr-xr-x 4 dmdba dinstall 78 11?? 13 21:50 p2
drwxr-xr-x 6 dmdba dinstall 8192 11?? 13 21:50 plugins
-rwxr-xr-x 1 dmdba dinstall 626 11?? 13 21:50 restore.xml
-rwxr-xr-x 1 dmdba dinstall 69 11?? 13 21:50 server_connection.xml
drwxr-xr-x 3 dmdba dinstall 32 11?? 13 21:50 templates
-rwxr-xr-x 1 dmdba dinstall 291 11?? 13 21:51 version.sh
drwxr-xr-x 3 dmdba dinstall 19 11?? 13 21:50 workspace
[dmdba@kylinser tool]$

直接修改 dm.ini 配置文件，不管修改什么类型参数都需要重启数据库才能生效。

DAMENG数据库连接密码设置为：dameng123

8、创建数据库实例

[dmdba@kylinser tool]$ ./dbca.sh
2022-11-13 22:12:09 [com.dameng.dbca.Startup]
[INFO] 启动DBCA

9、完成时以root运行脚本

[root@kylinser dmdba]# mv /dm8/bin/DmServiceDMSERVER.service /usr/lib/systemd/system/DmServiceDMSERVER.service
[root@kylinser dmdba]# systemctl enable DmServiceDMSERVER.service
Created symlink /etc/systemd/system/multi-user.target.wants/DmServiceDMSERVER.service ?? /usr/lib/systemd/system/DmServiceDMSERVER.service.
[root@kylinser dmdba]# systemctl start DmServiceDMSERVER.service
[root@kylinser dmdba]#

10、可系统启动实例服务

[root@kylinser dmdba]# systemctl restart DmServiceDMSERVER.service

[dmdba@kylinser ~]$ ps -ef|grep dmserver
dmdba 8971 1 0 22:20 ? 00:00:02 /dm8/bin/dmserver path=/dm8/data/DAMENG/dm.ini -noconsole
dmdba 9141 9078 0 22:31 pts/4 00:00:00 grep dmserver
[dmdba@kylinser ~]$ ps -T -P 8971
PID SPID PSR TTY STAT TIME COMMAND
8971 8971 0 ? Sl 0:00 /dm8/bin/dmserver path=/dm8/data/DAMENG/dm.ini -noconsole
8971 8974 1 ? Sl 0:00 /dm8/bin/dmserver path=/dm8/data/DAMENG/dm.ini -noconsole

[root@kylinser dmdba]# systemctl status DmServiceDMSERVER
?? DmServiceDMSERVER.service - DM Instance Service
Loaded: loaded (/usr/lib/systemd/system/DmServiceDMSERVER.service; enabled; vendor preset: disabled)
Active: active (running) since Sun 2022-11-13 22:20:38 CST; 13min ago
Process: 8948 ExecStart=/dm8/bin/DmServiceDMSERVER start (code=exited, status=0/SUCCESS)
Main PID: 8971 (dmserver)
Tasks: 65
Memory: 382.5M
CGroup: /system.slice/DmServiceDMSERVER.service
???8971 /dm8/bin/dmserver path=/dm8/data/DAMENG/dm.ini -noconsole

11?? 13 22:20:22 kylinser systemd[1]: DmServiceDMSERVER.service: Succeeded.
11?? 13 22:20:22 kylinser systemd[1]: Stopped DM Instance Service.
11?? 13 22:20:23 kylinser systemd[1]: Starting DM Instance Service…
11?? 13 22:20:38 kylinser DmServiceDMSERVER[8948]: [39B blob data]
11?? 13 22:20:38 kylinser systemd[1]: Started DM Instance Service.
[root@kylinser dmdba]#

[root@kylinser dmdba]# systemctl list-unit-files
UNIT FILE STATE
proc-sys-fs-binfmt_misc.automount static
dm-event.service static
DmAPService.service enabled
DmServiceDMSERVER.service enabled
[root@kylinser dmdba]# systemctl list-unit-files|grep DM*
DmAPService.service enabled
DmServiceDMSERVER.service enabled

[dmdba@kylinser bin]$ ll Dm*
-rwxr-xr-x 1 dmdba dinstall 13819 11?? 13 21:51 DmAPService
-rwxr-xr-x 1 dmdba dinstall 14483 11?? 13 21:51 DmAuditMonitorService
-rwxr-xr-x 1 dmdba dinstall 13647 11?? 13 21:51 DmInstanceMonitorService
-rwxr-xr-x 1 dmdba dinstall 14120 11?? 13 21:51 DmJobMonitorService
-rwxr-xr-x 1 dmdba dinstall 16449 11?? 13 22:17 DmServiceDMSERVER
[dmdba@kylinser bin]$

11、达梦服务本身启动管理

[dmdba@kylinser bin]$ ./DmServiceDMSERVER status
DmServiceDMSERVER (pid 8971) is running.
[dmdba@kylinser bin]$
[dmdba@kylinser bin]$ ./DmServiceDMSERVER stop
Stopping DmServiceDMSERVER: [ OK ]
[dmdba@kylinser bin]$ ./DmServiceDMSERVER start
Starting DmServiceDMSERVER: [ OK ]
[dmdba@kylinser bin]$

[dmdba@kylinser ~]$ DmServiceDMSERVER status
DmServiceDMSERVER (pid 1176) is running.

[dmdba@kylinser ~]$ top Hp 1176
top - 23:25:42 up 7 min, 3 users, load average: 0.09, 0.21, 0.15
Threads: 65 total, 0 running, 65 sleeping, 0 stopped, 0
%Cpu(s): 0.2 us, 5.6 sy, 0.0 ni, 93.2 id, 0.0 wa, 0.3 hi, 0.7 si, 0.0 st
MiB Mem : 2368.3 total, 227.0 free, 1412.6 used, 728.8 buff/cache
MiB Swap: 2048.0 total, 2048.0 free, 0.0 used. 710.6 avail Mem

PID USER      PR  NI    VIRT    RES    SHR S  %CPU  %MEM     TIME+ COMMAND                                                

1176 dmdba 20 0 3129568 627368 27296 S 0.0 25.9 0:03.78 dmserver
1276 dmdba 20 0 3129568 627368 27296 S 0.0 25.9 0:00.00 dm_sqllog_thd
1314 dmdba 20 0 3129568 627368 27296 S 0.0 25.9 0:00.00 dm_quit_thd
…

[root@kylinser ???]# su - dmdba
??? ?? 11?? 13 23:45:35 CST 2022 pts/1 ??

12、修改网络服务文件

[dmdba@kylinser ~]$ cat /etc/dm_svc.conf
TIME_ZONE=(420)
LANGUAGE=(cn)[dmdba@kylinser ~]$ vi /etc/dm_svc.conf

三、参数文件管理

[dmdba@kylinser ~]$ disql sysdba/dameng123:5236
???[LOCALHOST:5236]:???
??? : 5.523(ms)
disql V8
SQL> select distinct type from v$parameter;

??? TYPE

---

1 READ ONLY
2 SYS
3 IN FILE
4 SESSION

SQL>
SQL> select name,value,type from v$parameter t where t.name like ‘COMPATIBLE_MODE’;

??? NAME VALUE TYPE

---

1 COMPATIBLE_MODE 0 IN FILE

SQL> alter system set ‘COMPATIBLE_MODE’=2 both; //为静态参数 需spfile
alter system set ‘COMPATIBLE_MODE’=2 both;

SQL> alter system set ‘COMPATIBLE_MODE’=2 spfile;

SQL> select name,value,file_value,sys_value,type from v$parameter t where t.name like ‘COMPATIBLE_MODE’;
??? NAME VALUE FILE_VALUE SYS_VALUE TYPE

---

1 COMPATIBLE_MODE 0 2 0 IN FILE

SQL> alter system set ‘UNDO_RETENTION’=5;
DMSQL ???

SQL> select name,value,file_value,sys_value,type from v$parameter t where t.name=‘UNDO_RETENTION’;
??? NAME VALUE FILE_VALUE SYS_VALUE TYPE

---

1 UNDO_RETENTION 5.000000 90.000000 5.000000 SYS

SQL> ALTER SESSION SET ‘LIST_TABLE’=1;
DMSQL ???

SQL> select name,value,file_value,sys_value,type from v$parameter t where t.name like ‘LIST_TABLE%’;
??? NAME VALUE FILE_VALUE SYS_VALUE TYPE

---

1 LIST_TABLE 1 0 0 SESSION

SQL>

查看参数函数

SQL> select name,id from v$ifun t where t.name like ‘SP_SET_PARA%’;

??? NAME ID

---

1 SP_SET_PARA_DOUBLE_VALUE 586
2 SP_SET_PARA_STRING_VALUE 585
3 SP_SET_PARA_VALUE 584

SQL> select * from v$ifun_arg t where t.id=584;
??? ID NAME SEQ DATA_TYPE LEN PREC IO_TYPE COMMENT$

---

1 584 RVAL -1 UNKNOWN DATATYPE 0 0 RETURN NULL
2 584 SCOPE 0 INTEGER 4 0 IN NULL
3 584 PARANAME 1 VARCHAR 256 0 IN NULL
4 584 VALUE 2 BIGINT 8 0 IN NULL

SQL>

#SP_SET_PARA_VALUE参数
第一个参数：scope:0：memory；1：both；2：spfile；
第二个参数：参数名称
第三个参数：参数的值

SQL> sp_set_para_value(2,‘COMPATIBLE_MODE’,0);
DMSQL ???

SQL>

SQL>select * from v$bufferpool;

SQL> set linesize 800
SQL> select name,value,file_value,sys_value,type,description from v$parameter t where t.name like ‘CKPT%’;
??? NAME VALUE FILE_VALUE SYS_VALUE TYPE DESCRIPTION

SQL> select name,value,file_value,sys_value,type,description from v$parameter t where t.name=‘BUFFER’;
??? NAME VALUE FILE_VALUE SYS_VALUE TYPE DESCRIPTION

---

1 BUFFER 1000 1000 1000 IN FILE Initial System Buffer Size In Megabytes

SQL> alter system set ‘BUFFER’=1024 spfile;
DMSQL ???

SQL> select name,value,file_value,sys_value,type,description from v$parameter t where t.name like ‘RLOG_BUF_SIZE%’;
??? NAME VALUE FILE_VALUE SYS_VALUE TYPE DESCRIPTION

---

1 RLOG_BUF_SIZE 1024 1024 1024 IN FILE The Number Of Log Pages In One Log Buffer

四、表空间管理：

select * from v$tablespace;
select * from dba_tablespaces;
select * from dba_data_files;

1、普通表空间
SQL> create tablespace tbs datafile ‘/dm8/data/DAMENG/tbs01.dbf’ size 32;

SQL> select name from v$tablespace;

??? NAME

---

1 SYSTEM
2 ROLL
3 TEMP
4 MAIN
5 BOOKSHOP
6 DMHR
7 TBS

7 rows got

SQL> alter tablespace tbs datafile ‘/dm8/data/DAMENG/tbs01.dbf’ autoextend off;
SQL> alter tablespace tbs datafile ‘/dm8/data/DAMENG/tbs01.dbf’ autoextend on;
SQL> alter tablespace tbs add datafile ‘/dm8/data/DAMENG/tbs02.dbf’ size 32;
SQL> alter tablespace tbs resize datafile ‘/dm8/data/DAMENG/tbs01.dbf’ to 64;
SQL> alter tablespace tbs datafile ‘/dm8/data/DAMENG/tbs01.dbf’ autoextend on next 2 maxsize 1024;

SQL>

SQL> select name from v$tablespace;
??? NAME

---

1 SYSTEM
2 ROLL
3 TEMP
4 MAIN
5 BOOKSHOP
6 DMHR
7 TBS
7 rows got

SQL> alter tablespace tbs rename to dmtbs;

SQL> select name from v$tablespace;
??? NAME

---

1 SYSTEM
2 ROLL
3 TEMP
4 MAIN
5 BOOKSHOP
6 DMHR
7 DMTBS

7 rows got

SQL> drop tablespace dmtbs;

SQL> alter tablespace tbs_test offline

create tablespace “TBS_TEST” datafile ‘/dm8/data/DAMENG/TBS_TEST01.DBF’ size 32 autoextend on next 2 maxsize 1024, ‘/dm8/data/DAMENG/TBS_TEST02.DBF’ size 32 CACHE = NORMAL;
alter tablespace tbs_test rename datafile ‘TBS_TEST01.DBF’ to ‘/dm8/data/TBS/TBS_TEST01.dbf’;
alter tablespace TBS_TEST rename datafile ‘TBS_TEST02.DBF’ to ‘/dm8/data/TBS/TBS_TEST02.dbf’;

SQL> alter tablespace tbs_test rename datafile ‘TBS_TEST01.DBF’ to ‘/dm8/data/TBS/TBS_TEST01.dbf’;
SQL> alter tablespace TBS_TEST rename datafile ‘TBS_TEST02.DBF’ to ‘/dm8/data/TBS/TBS_TEST02.dbf’;

如果执行没反应 回车+/

SQL> alter tablespace tbs_test onine

2、临时表空间设置：

SQL> select name,value,type from v$parameter where name like ‘TEMP%’;

??? NAME VALUE TYPE

---

1 TEMP_PATH /dm8/data/DAMENG READ ONLY
2 TEMP_SIZE 10 IN FILE
3 TEMP_SPACE_LIMIT 0 SYS

SQL> alter system set ‘TEMP_SIZE’=64 spfile;

SQL> host cat /dm8/data/DAMENG/dm.ini |grep TEMP_SIZE
TEMP_SIZE = 64 #temporary file size in Megabytes

SQL> select name,value,file_value,sys_value,type from v$parameter where name like ‘TEMP%’;
??? NAME VALUE FILE_VALUE SYS_VALUE TYPE

---

1 TEMP_PATH /dm8/data/DAMENG /dm8/data/DAMENG /dm8/data/DAMENG READ ONLY
2 TEMP_SIZE 10 64 10 IN FILE
3 TEMP_SPACE_LIMIT 0 0 0 SYS

SQL>

3、日志文件管理

SQL>select * from v$rlogfile;
SQL>select * from v$rlog;

SQL> alter database add logfile ‘DAMENG03.log’ SIZE 128;

SQL> alter database resize logfile ‘/dm8/data/DAMENG/DAMENG03.log’ to 256

日志文件迁移要在mount下进行

SQL> alter database mount;
SQL> alter database rename logfile ‘DAMENG03.log’ to ‘/dm8/data/DAMENG/REDO/DAMENG03.log’
SQL> alter database open;

4、归档的配置

开归档

SQL> alter database mount;
SQL> alter database archivelog;
SQL> alter database add archivelog ‘type=local, dest=/dm8/arch,file_size=64,space_limit=10240’;
SQL> alter database open;

SQL> select arch_mode from v$database;
??? ARCH_MODE

---

1 Y

SQL> select * from v$dm_arch_ini;
SQL> alter database modify archivelog ‘type=local, dest=/dm8/arch,file_size=64,space_limit=20480’;
SQL> select * from SYS.“V$ARCH_FILE”;

归档置文件dmarch.ini

[root@kylinser DAMENG]# cat dmarch.ini
#DaMeng Database Archive Configuration file
#this is comments

ARCH_WAIT_APPLY      = 0        

[ARCHIVE_LOCAL1]
ARCH_TYPE = LOCAL
ARCH_DEST = /dm8/arch
ARCH_FILE_SIZE = 64
ARCH_SPACE_LIMIT = 20480
ARCH_FLUSH_BUF_SIZE = 0
ARCH_HANG_FLAG = 1

关闭归档

SQL> alter database mount;
SQL> alter database noarchivelog;
SQL> alter database delete archivelog ‘type=local,dest=/dm8/arch’;
SQL> alter database open;

SQL> select arch_mode from v$database;
??? ARCH_MODE

---

1 N

SQL> select * from v$dm_arch_ini;

dmdba@kylinser DAMENG]$ cat dmarch.ini
#DaMeng Database Archive Configuration file
#this is comments

ARCH_WAIT_APPLY      = 0        

[dmdba@kylinser DAMENG]$ cat dm.ini|grep ARCH_INI
ARCH_INI = 0 #dmarch.ini

#修改dm.ini参数ARCH_INI，修改后要重启

[dmdba@localhost DAMENG]$ vi dm.ini
ARCH_INI = 1
[dmdba@localhost DAMENG]$ vi dmarch.ini
[ARCHIVE_LOCAL1]
ARCH_TYPE = LOCAL
ARCH_DEST = /dm8/arch
ARCH_FILE_SIZE = 64
ARCH_SPACE_LIMIT = 10240

SQL> select * from v$ifun t where name like ‘SF_ARCHIVELOG_%’;
??? NAME ID ARG_NUM HAS_RT_FACT IS_READONLY IS_MPP_BRO IS_MPP_FORBIDEN IS_MOUNT_ONLY IS_INCLUDE_SQL EXTRA_ATTR CLASS$ COMMENT$ IS_LPQ_FORBIDDEN

---

1 SF_ARCHIVELOG_DELETE_BEFORE_LSN 1462 1 Y Y N N N N NULL ??? NULL N
2 SF_ARCHIVELOG_DELETE_BEFORE_TIME 1461 1 Y Y N N N N NULL ??? NULL N

SQL> select SF_ARCHIVELOG_DELETE_BEFORE_TIME(sysdate-10);
??? SF_ARCHIVELOG_DELETE_BEFORE_TIME(SYSDATE-10)

---

1 0

五、用户管理

SQL> select * from v$parameter where name = ‘PWD_POLICY’;
??? ID NAME TYPE VALUE SYS_VALUE FILE_VALUE DESCRIPTION DEFAULT_VALUE ISDEFAULT

---

1 463 PWD_POLICY SYS 2 2 2 Flag of password policy 2 1

SQL> create user dm identified by dameng123;

密码策略

SQL> alter system set ‘PWD_POLICY’=0 both;

SQL> alter user dm limit failed_login_attemps 5 password_lock_time 5;

SQL> alter user dm account lock;
SQL> alter user dm account unlock;

manager图形方式：
create user “DM1” identified by “DAMENG123” password_policy 0
limit FAILED_LOGIN_ATTEMPS 5 PASSWORD_LIFE_TIME 180 PASSWORD_LOCK_TIME 1 PASSWORD_GRACE_TIME 10;
grant “PUBLIC”,“SOI”,“VTI” to “DM1”;
grant CREATE TABLE,CREATE VIEW,CREATE INDEX to “DM1”;
grant SELECT on “DMHR”.“CITY” to “DM1”;

查询用户权限

select * from DBA_SYS_PRIVS where grantee = ‘DM’;
alter user “DM” default tablespace “TBS_TEST”;

select * from DBA_SYS_PRIVS where grantee = ‘DM’;
select * from dba_tab_privs where grantee = ‘DM’;
select * from dba_role_privs where grantee = ‘DM’;
select * from dba_col_privs where grantee = ‘DM’;

SQL> select * from dba_role_privs where grantee = ‘DM’;
??? GRANTEE GRANTED_ROLE ADMIN_OPTION DEFAULT_ROLE

---

1 DM PUBLIC N NULL
2 DM VTI N NULL
3 DM SOI N NULL

SQL> select * from dba_tab_privs where grantee = ‘DM’;

SQL> select * from DBA_SYS_PRIVS where grantee = ‘DM’;
??? GRANTEE PRIVILEGE ADMIN_OPTION

---

1 DM CREATE SESSION NO

SQL> create table t_test(id int);

SQL> grant create table to dm;

SQL> grant select on dmhr.employee to dm;

SQL> select * from dmhr.employee limit 5;

SQL> select * from session_privs;
??? PRIVILEGE

---

1 CREATE TABLE
2 CREATE SESSION
3 SELECT EMPLOYEE
4 PUBLIC
5 VTI
6 SOI

6 rows got

SQL> revoke select on dmhr.employee from dm;

SQL> revoke create table from dm;

SQL> grant select any table to dm;
???
???: 4.203(???). ???:514.

SQL> select * from v$parameter t where t.name like ‘ENABLE_DDL_ANY_PRIV%’;
??? ID NAME TYPE VALUE SYS_VALUE FILE_VALUE
495 ENABLE_DDL_ANY_PRIV SYS 0 0 0

SQL> alter system set ‘ENABLE_DDL_ANY_PRIV’=0 both;

SQL> grant create any table to dm;

SQL> alter system set ‘ENABLE_DDL_ANY_PRIV’=1 both;

SQL> grant create any table to dm;

SQL> select * from dba_roles;
??? ROLE PASSWORD_REQUIRED AUTHENTICATION_TYPE

---

1 DBA NULL NULL
2 DB_AUDIT_ADMIN NULL NULL
3 DB_AUDIT_OPER NULL NULL
4 DB_AUDIT_PUBLIC NULL NULL
5 DB_AUDIT_SOI NULL NULL
6 DB_AUDIT_SVI NULL NULL
7 DB_AUDIT_VTI NULL NULL
8 DB_POLICY_ADMIN NULL NULL
9 DB_POLICY_OPER NULL NULL
10 DB_POLICY_PUBLIC NULL NULL
11 DB_POLICY_SOI NULL NULL

??? ROLE PASSWORD_REQUIRED AUTHENTICATION_TYPE

---

12 DB_POLICY_SVI NULL NULL
13 DB_POLICY_VTI NULL NULL
14 PUBLIC NULL NULL
15 RESOURCE NULL NULL
16 SOI NULL NULL
17 SVI NULL NULL
18 SYS_ADMIN NULL NULL
19 VTI NULL NULL

19 rows got

角色管理

SQL> create role r1;

SQL> grant create table to r1;

SQL> grant select on dmhr.department to r1;

SQL> grant r1 to dm;

SQL> alter user dm limit session_per_user 3;

SQL> revoke r1 from dm;

SQL> revoke create table from dm;
SQL> revoke create table from r1;

SQL> drop role r1 cascade;

图形界面方式：

create role “r1”;
grant “PUBLIC”,“SOI”,“VTI” to “r1”;
grant CREATE TABLE to “r1”;
grant SELECT,INSERT,DELETE,UPDATE,REFERENCES,SELECT FOR DUMP,ALTER,INDEX on “DMHR”.“CITY” to “r1”;

六、模式对象管理

SQL> select * from SYSOBJECTS where “TYPE$” = ‘SCH’;

SQL> select a.id scheid, a.name schename, b.id userid, b.name username
from SYS.SYSOBJECTS a, SYS.SYSOBJECTS b
where a.“TYPE$” = ‘SCH’ and a.pid = b.id;
??? SCHEID SCHENAME USERID USERNAME

---

1 150994944 SYS 50331648 SYS
2 150994948 CTISYS 50331648 SYS
3 150994945 SYSDBA 50331649 SYSDBA
4 150995944 RESOURCES 50331649 SYSDBA
5 150995945 PERSON 50331649 SYSDBA
6 150995946 SALES 50331649 SYSDBA
7 150995947 PRODUCTION 50331649 SYSDBA
8 150995948 PURCHASING 50331649 SYSDBA
9 150995949 OTHER 50331649 SYSDBA
10 150995950 DMHR 50331649 SYSDBA
11 150994946 SYSAUDITOR 50331650 SYSAUDITOR
12 150994947 SYSSSO 50331651 SYSSSO
13 150995951 DM 50331748 DM

13 rows got

SQL> select sys_context(‘USERENV’,‘CURRENT_SCHEMA’);
??? SYS_CONTEXT(‘USERENV’,‘CURRENT_SCHEMA’)

---

1 SYSDBA

SQL> select * from dm.t_testpid;
SQL> create schema hrtest01 authorization dm; 要先建好用户
? /

SQL>

1、表管理

SQL> create table dm.t_testpid
(pid int,
pname varchar(20) not null ,
sex bit,
logdate date DEFAULT sysdate,
logtime time DEFAULT sysdate,
logdatetime datetime DEFAULT sysdate) tablespace TBS_TEST;

SQL>
SQL> insert into dm.t_testpid(pid,pname) values(1,‘a’);

SQL>

SQL> insert into dm.t_testpid(pid,pname) values(1,‘a’);

SQL> alter table dm.t_testpid add email varchar(50);

SQL> alter table dm.t_testpid modify pname varchar(50) not null;

SQL> alter table dm.t_testpid drop email;

SQL> create table dm.EMPLOYEE as select * from dmhr.employee where 1=2;

2、命令导入表数据

SQL> start /home/dmdba/emp.sql
SQL> INSERT INTO EMPLOYEE (EMPLOYEE_ID,EMPLOYEE_NAME,EMAIL,PHONE_NUM,HIRE_DATE,JOB_ID,SALARY,DEPARTMENT_ID) VALUES
(1001,‘???’,‘maxueming@dameng.com’,‘15312348552’,‘2008-05-30’,‘11’,30000,101)

SQL> create table dm.t_testchild as select * from dm.t_testpid;

3、约束

SQL> alter table dm.t_testchild add CONSTRAINT fk_testchild_pid FOREIGN key(PID) REFERENCES dm.t_testpid(PID);
SQL> alter table dm.t_testchild add salary number(10,2);
SQL> alter table dm.t_testchild add CONSTRAINT ck_testchild_salary CHECK (salary>=3000);