CNDBA社区
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/3578
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 线程杀掉会话。
DM7 达梦数据库 线程 说明
https://www.cndba.cn/dave/article/3576
版权声明：本文为博主原创文章，未经博主允许不得转载。
