签到成功

知道了

CNDBA社区CNDBA社区

DM7 达梦数据库 常用的运维SQL语句

2019-09-04 15:58 3476 0 原创 DM 达梦
作者: dave

1、查看数据库版本号

SQL> select * from v$version;

LINEID     BANNER                                                       
---------- -------------------------------------------------------------
1          DM Database Server x64 V7.6.0.95-Build(2018.09.13-97108)ENT  
2          DB Version: 0x7000a

used time: 4.300(ms). Execute id is 11.
SQL>

2、查看表空间的名称及大小

SQL> select name tablepace_name, total_size*sf_get_page_size()/1024/1024||'m' ts_size from v$tablespace;

LINEID     TABLEPACE_NAME TS_SIZE
---------- -------------- -------
1          SYSTEM         24m
2          ROLL           128m
3          TEMP           10m
4          MAIN           128m
5          SYSAUX         128m

used time: 1.839(ms). Execute id is 12.
SQL>

3、 查看表空间物理文件的名称及大小

SQL> select t.name tablespace_name,t.id file_id,d.path file_name,d.total_size*sf_get_page_size()/1024/1024||'m' total_space from v$tablespace t, v$datafile d where t.id=d.group_id;

LINEID     TABLESPACE_NAME FILE_ID     FILE_NAME                        TOTAL_SPACE
---------- --------------- ----------- -------------------------------- -----------
1          SYSTEM          0           /dm/dmdbms/data/cndba/SYSTEM.DBF 24m
2          SYSAUX          5           /dm/dmdbms/data/cndba/SYSAWR.DBF 128m
3          MAIN            4           /dm/dmdbms/data/cndba/MAIN.DBF   128m
4          TEMP            3           /dm/dmdbms/data/cndba/TEMP.DBF   10m
5          ROLL            1           /dm/dmdbms/data/cndba/ROLL.DBF   128m

used time: 8.915(ms). Execute id is 13.
SQL>

4、 查看控制文件

SQL> select para_value name from v$dm_ini where para_name='CTL_PATH';

LINEID     NAME                        
---------- ----------------------------
1          /dm/dmdbms/data/cndba/dm.ctl

used time: 9.032(ms). Execute id is 14.
SQL>

5、 查看日志文件

SQL> select PATH,RLOG_SIZE/1024/1024||'M' from v$rlogfile;

LINEID     PATH                              RLOG_SIZE/1024/1024||'M'
---------- --------------------------------- ------------------------
1          /dm/dmdbms/data/cndba/cndba01.log 256M
2          /dm/dmdbms/data/cndba/cndba02.log 256M

used time: 0.627(ms). Execute id is 24.
SQL>

6、 查看表空间的使用情况

SQL> select
        t1.NAME tablespace_name,
        t2.FREE_SIZE*SF_GET_PAGE_SIZE()/1024/1024
        ||'M' free_space,
        t2.TOTAL_SIZE*SF_GET_PAGE_SIZE()/1024/1024
        ||'M' total_space,
        t2.FREE_SIZE*100/t2.total_size "% FREE"
from
        V$TABLESPACE t1,
        V$DATAFILE t2
where
        t1.ID=t2.GROUP_ID;

LINEID     TABLESPACE_NAME FREE_SPACE TOTAL_SPACE % FREE              
---------- --------------- ---------- ----------- --------------------
1          SYSTEM          18M        24M         75
2          SYSAUX          127M       128M        99
3          MAIN            123M       128M        96
4          TEMP            9M         10M         99
5          ROLL            93M        128M        73

used time: 3.373(ms). Execute id is 27.
SQL>

7、查看数据库库对象

SQL> select t2.name owner,t1.subtype$ object_type,t1.valid status,count(1) count# from sysobjects t1,sysobjects t2 where t1.schid=t2.id and t1.schid!=0 group by t2.name,t1.subtype$,t1.valid;

LINEID     OWNER  OBJECT_TYPE STATUS COUNT#              
---------- ------ ----------- ------ --------------------
1          SYS    INDEX       Y      292
2          SYSDBA PROC        Y      17
3          SYSDBA UTAB        Y      77
4          SYSDBA CONS        Y      60
5          SYSDBA INDEX       Y      105
6          SYS    UTAB        Y      29
7          SYS    VIEW        Y      123
8          SYS    PROC        Y      25
9          SYS    TRIG        Y      1
10         SYS    PKG         Y      33
11         SYS    SEQ         Y      1
...

8、 查看数据库的创建日期和归档方式

SQL> select create_time start_time,case when arch_mode = 'n' then '非归档模式' else '归档模式' end from v$database;
[70004]:String truncate.

LINEID     START_TIME                  CASEWHENARCH_MODE='n'THEN'非归档模式'ELSE'归档模式'END
---------- --------------------------- ---------------------------------------------------------------
1          2029-03-06 18:11:29         归档模

used time: 15.532(ms). Execute id is 29.
SQL>

9、可以通过V$LOCK和V$TRX查看还没提交的事务

SQL> select t2.name from v$lock t1,sysobjects t2 where t1.table_id=t2.id and subtype$='UTAB';
no rows

used time: 6.554(ms). Execute id is 30.
SQL>

10、查看等待(wait)情况

SQL> select class_name,total_waits count from v$wait_class;
no rows

used time: 2.109(ms). Execute id is 31.
SQL>

11、查看object分类数量

SQL> select subtype$ object_type, count(1) quantity from sysobjects where subtype$ <>'' group by subtype$ union select 'column',count(1) from syscolumns;

LINEID     OBJECT_TYPE QUANTITY            
---------- ----------- --------------------
1          UTAB        160
2          VIEW        123
3          PROC        42
4          TRIG        1
5          PKG         33
6          SEQ         3
7          TYPE        1
8          STAB        267
9          CONTEXT     1
10         CLASS       2
11         SYNOM       1
12         CONS        160
13         INDEX       498
14         USER        9
15         ROLE        10
16         column      5219

16 rows got

used time: 2.758(ms). Execute id is 33.
SQL>

12、按用户查看object种类(可查每个模式下的各对象数目)

select
        syssch.NAME "schema"                         ,
        sum(decode(t.SUBTYPE$, 'VIEW', 1, 0)) views  ,
        sum(decode(t.SUBTYPE$, 'PROC', 1, 0)) procs  ,
        sum(decode(t.SUBTYPE$, 'STAB', 1, 0)) stab   ,
        sum(decode(t.SUBTYPE$, 'UTAB', 1, 0)) utabs  ,
        sum(decode(t.SUBTYPE$, 'SYNOM', 1, 0)) synoms,
        sum(decode(t.SUBTYPE$, 'CONS', 1, 0)) conses ,
        sum(decode(t.SUBTYPE$, 'INDEX', 1, 0)) indexes
FROM
        SYSOBJECTS t,
        (
                select ID, NAME from SYSOBJECTS where TYPE$='SCH'
        )
        syssch
WHERE
        syssch.ID=t.SCHID
group by
        syssch.NAME;

13、查看数据库实例信息:

SQL> select name inst_name from v$instance;

LINEID     INST_NAME
---------- ---------
1          DAVE

used time: 1.363(ms). Execute id is 44.
SQL>

14、查看及修改最大会话数

SQL> select para_value from v$dm_ini where para_name='MAX_SESSIONS';

LINEID     PARA_VALUE
---------- ----------
1          100

used time: 11.276(ms). Execute id is 45.
SQL> 

SQL> sp_set_para_value (2,'MAX_SESSIONS',1000);
DMSQL executed successfully
used time: 10.593(ms). Execute id is 47.
SQL> 
然后重启生效。

DM7 达梦数据库 参数 修改方法
https://www.cndba.cn/dave/article/3578https://www.cndba.cn/dave/article/3626

https://www.cndba.cn/dave/article/3626

15、 确定高负载的 SQL

查询动态视图 V$LONG_EXEC_SQLS 或 V$SYSTEM_LONG_EXEC_SQLS 来确定高负载的 SQL 语句。

V$LONG_EXEC_SQLS显示最近 1000 条执行时间较长的 SQL 语句
SQL> select * from v$long_exec_sqls;
no rows

used time: 1.839(ms). Execute id is 59.
SQL> 

V$SYSTEM_LONG_EXEC_SQLS显示服务器启动以来执行时间最长的20 条 SQL 语句。例如:
SQL> select * from v$system_long_exec_sqls;
no rows

used time: 1.937(ms). Execute id is 60.
SQL>

16、查看触发器、过程、函数的创建脚本

select t2.name,t2.subtype$,t1.txt from systexts t1,sysobjects t2 where t2.id=t1.id where name='XXX'

17、查看表定义

get_ddl可以获取表、视图、物化视图、索引、全文索引、存储过程、函数、包、目录等的定义:
SQL>  select dbms_metadata.get_ddl('TABLE','DAVE','CNDBA');

LINEID     DBMS_METADATA.GET_DDL('TABLE','DAVE','CNDBA')                                                                                                                  
---------- --------------------------------------------------------------
1          CREATE TABLE "CNDBA"."DAVE"
(
"NAME" VARCHAR(128),
"ID" INT,
"SCHID" INT,
"TYPE$" VARCHAR(10),
"SUBTYPE$" VARCHAR(10),
"PID" INT,
"VERSION" INT,
"CRTDATE" DATETIME(6),
"INFO1" INT,
"INFO2" INT,
"INFO3" BIGINT,
"INFO4" BIGINT,
"INFO5" VARBINARY(128),
"INFO6" VARBINARY(2048),
"INFO7" BIGINT,
"INFO8" VARBINARY(1024),
"VALID" CHAR(1)) STORAGE(ON "MAIN", CLUSTERBTR) ;

used time: 11.026(ms). Execute id is 56.
SQL>

18、强制关闭用户连接

SQL> select  sess_id,curr_sch ,sql_text,user_name,trx_id ,clnt_host,clnt_ip,osname, vpooladdr from sys.v$sessions where sql_text<>'';

LINEID     SESS_ID              CURR_SCH SQL_TEXT                                                                                                                        
---------- -------------------- -------- --------------------------------------------------------------------------------------------------------------------------------
           USER_NAME TRX_ID               CLNT_HOST CLNT_IP              OSNAME    VPOOLADDR           
           --------- -------------------- --------- -------------------- --------- --------------------
1          140615618728712      SYSDBA   select COMMENT$ from SYS.SYSCOLUMNCOMMENTS where SCHNAME='SYS' and TVNAME='SYSOBJECTS' and COLNAME='SUBTYPE$'
           SYSDBA    1871                 ahzhixin  ::ffff:192.168.20.45 Windows 8 140615618661112

2          140615954273032      SYSDBA   select  sess_id,curr_sch ,sql_text,user_name,trx_id ,clnt_host,clnt_ip,osname, vpooladdr from sys.v$sessions where sql_text<>'';
           SYSDBA    1874                 dm2       ::ffff:127.0.0.1     Linux     140615954205432


used time: 1.935(ms). Execute id is 57.

然后根据SESSION_ID使用系统过程SP_CLOSE_SESSION(SESSION_ID)来kill 会话:
SQL> SP_CLOSE_SESSION(140615618728712);
DMSQL executed successfully
used time: 1.099(ms). Execute id is 58.
SQL>

DM 数据库采用的是线程架构,所有不能向Oracle那样直接kill 进程,只能调用sp_close_session()来kill 线程杀掉会话。https://www.cndba.cn/dave/article/3626

https://www.cndba.cn/dave/article/3626
https://www.cndba.cn/dave/article/3626https://www.cndba.cn/dave/article/3626

DM7 达梦数据库 线程 说明
https://www.cndba.cn/dave/article/3576https://www.cndba.cn/dave/article/3626https://www.cndba.cn/dave/article/3626https://www.cndba.cn/dave/article/3626

https://www.cndba.cn/dave/article/3626

版权声明:本文为博主原创文章,未经博主允许不得转载。

用户评论
* 以下用户言论只代表其个人观点,不代表CNDBA社区的观点或立场
dave

dave

关注

人的一生应该是这样度过的:当他回首往事的时候,他不会因为虚度年华而悔恨,也不会因为碌碌无为而羞耻;这样,在临死的时候,他就能够说:“我的整个生命和全部精力,都已经献给世界上最壮丽的事业....."

  • 2241
    原创
  • 3
    翻译
  • 550
    转载
  • 187
    评论
  • 访问:6768153次
  • 积分:4258
  • 等级:核心会员
  • 排名:第1名
精华文章
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        Copyright © 2016 All Rights Reserved. Powered by CNDBA · 皖ICP备2022006297号-1·

        QQ交流群

        注册联系QQ