达梦数据库日常运维指令

# 常用数据库运维指令

- 使用disql登陆数据库

```
# ./disql <username>[/<password>][@<host:port>][<option>] [<os_auth>]
./disql SYSDBA/SYSDBA@localhost:5236
```
更多详情: https://eco.dameng.com/document/dm/zh-cn/pm/getting-started-disql.html

- 为特定用户创建多个模式

```sql
create schema 新模式名；
# 或
create schema 新模式名 AUTHORIZATION 指定用户;
```

# 常用数据库业务指令

- 创建表空间test01，存储文件到tablespace_test01.dbf，size最小为128MB

```sql
create tablespace test01 datafile \
'/data/dm8server/databases/material/tablespace_test01.dbf' size 128 autoextend on;
```

- 创建表空间索引，存储文件到tablespace_test01_idx01.dbf，size最小为128MB

```sql
create tablespace test01_idx datafile \
'/data/dm8server/databases/material/tablespace_test01_idx01.dbf' size 128 autoextend on;
```

- 创建用户，设置密码，指定默认表空间和默认表索引

```sql
create user "test01" identified by "test123456" \
default tablespace test01 default index tablespace test01_idx;
```

- （可选）修改用户已配置的默认表空间及索引

```sql
alter user test01 default tablespace NewTS default index tablespace NewTS_idx;
```

- 授权dba角色给用户

```sql
grant dba to test01;
```

- 查看文件存储路径

```sql
select PATH from v$datafile;
```

- 切换用户

```sql
conn test01/test123456@localhost:3306;
```

- 创建表

```sql
create table table_test01 (id int,name varchar(50),create_date date);
```

# 数据库会话监控查询语句
```sql
SELECT
    *
FROM
(
    SELECT
        (
            SELECT
                ROUND(SUM(TOTAL_SIZE / 1024 / 1024), 2)
            FROM
                V$MEM_POOL C
            WHERE
                C.name = 'VIRTUAL MACHINE'
                AND C.CREATOR = A.THRD_ID
            GROUP BY
                CREATOR
        ) AS VM_TOTAL_SIZE_BY_M,
        A.trx_id,
        A.thrd_id,
        A.clnt_ip,
        'sp_close_session(' || A.sess_id || ');' AS curr_sch,
        B.IO_WAIT_TIME / 1000 AS IO_WAIT_TIME,
        sf_get_session_sql(A.sess_id),
        DATEDIFF(ss, A.last_recv_time, SYSDATE) AS ss,
        B.sql_text,
        C.ins_cnt,
        C.upd_cnt,
        C.del_cnt
    FROM
        V$sessions A,
        V$SQL_STAT B,
        V$trx C
    WHERE
        A.state = 'ACTIVE'
        AND A.SESS_ID = B.SESSID
        AND A.trx_id = C.id
)
ORDER BY
    ss DESC;
```