达梦数据库对Oracle兼容之SQL运维实战攻略

前言

Oracle作为目前市场排名第一的数据库管理系统，已广泛应用于各个行业领域。为了方便基于Oracle的应用向达梦数据库DM7移植，减轻数据库管理员的工作量，DM7做了大量的Oracle兼容性工作。
本文对日常Oracle运维所用查询，给出相应的DM7的解决/查询方式，以供参考。

1、查看数据库版本号

都是:

select * from v$version;

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

Oracle 11g:

select t.TABLESPACE_NAME, round(sum(BYTES/(1024*1024)),0) ts_size 
from DBA_TABLESPACES t, DBA_DATA_FILES d
where t.TABLESPACE_NAME = d.TABLESPACE_NAME
group by t.TABLESPACE_NAME;

DM7:

select NAME tablepace_name, total_size*SF_GET_PAGE_SIZE()/1024/1024||'M' ts_size 
from V$TABLESPACE;

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

Oracle 11g:

select TABLESPACE_NAME, FILE_ID, FILE_NAME, round(BYTES/(1024*1024),0) total_space
from DBA_DATA_FILES
order by TABLESPACE_NAME;

DM7:

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;

4、 查看控制文件

Oracle 11g:

select NAME from V$CONTROLFILE;

DM7:

select PARA_VALUE name from V$DM_INI where PARA_NAME='CTL_PATH';

5、 查看日志文件

Oracle 11g:

select MEMBER from V$LOGFILE;

DM7:

select PATH from V$RLOGFILE;

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

Oralce 11g:

select t1.TABLESPACE_NAME, t1.BYTES total, t2.BYTES used, t3.BYTES free,
(t2.BYTES*100)/t1.BYTES "% used", (t3.BYTES*100)/t1.BYTES "% free"
from SYS.SM$TS_AVAIL t1,SYS.SM$TS_USED t2,SYS.SM$TS_FREE t3
where t1.TABLESPACE_NAME=t2.TABLESPACE_NAME and t1.TABLESPACE_NAME=t3.TABLESPACE_NAME;

DM7:

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;

7、查看数据库库对象

Oracle 11g:

select OWNER, OBJECT_TYPE, STATUS, count(1) count# from ALL_OBJECTS group by OWNER, OBJECT_TYPE, STATUS;

DM7:

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;

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

Oracle 11g:

select CREATED, LOG_MODE, LOG_MODE from V$DATABASE;

DM7:

select CREATE_TIME start_time,case when ARCH_MODE = 'N' then '非归档模式' else '归档模式' end from V$DATABASE;

注: DM7的CREATE_TIME指的是实例启动时间。Oracle指的是创建库的时间。

9、查看还没提交的事务

Oracle 11g：

select * from V$LOCKED_OBJECT;
select * from V$TRANSACTION;

DM7:

select * from V$LOCK;
select * from V$TRX;

举例DML表操作未提交查询：

select t2.NAME from V$LOCK t1,SYSOBJECTS t2 where t1.TABLE_ID=t2.ID and SUBTYPE$='UTAB';

10、查看等待（wait）情况

Oracle 11g：

select V$WAITSTAT.CLASS, V$WAITSTAT.COUNT count, sum(V$SYSSTAT.VALUE) sum_value
from V$WAITSTAT, V$SYSSTAT where V$SYSSTAT.NAME in ('db block gets', 'consistent gets')
group by V$WAITSTAT.CLASS, V$WAITSTAT.COUNT;

DM7：

select CLASS_NAME,TOTAL_WAITS count from V$WAIT_CLASS;

11、查看object分类数量

Oracle 11g:

select decode (t1.TYPE#,1,'INDEX' , 2,'TABLE' , 3 , 'CLUSTER' , 4, 'VIEW' , 5 ,
'SYNONYM' , 6 , 'SEQUENCE' , 'OTHER' ) object_type , count(1) quantity from
SYS.OBJ$ t1 where t1.TYPE# > 1 group by decode (t1.TYPE#,1,'INDEX' , 2,'TABLE' , 3
, 'CLUSTER' , 4, 'VIEW' , 5 , 'SYNONYM' , 6 , 'SEQUENCE' , 'OTHER' )
union
select 'COLUMN' , count(1) from SYS.COL$;

DM7：

select SUBTYPE$ object_type, count(1) quantity from SYSOBJECTS where SUBTYPE$ <>'' group by SUBTYPE$
union
select 'column',count(1) from SYSCOLUMNS;

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

Oracle 11g：

select t2.NAME schema, sum(decode(t1.TYPE#, 1, 1, NULL)) indexes,
sum(decode(t1.TYPE#, 2, 1, NULL)) tables, sum(decode(t1.TYPE#, 3, 1, NULL))
clusters, sum(decode(t1.TYPE#, 4, 1, NULL)) views, sum(decode(t1.TYPE#, 5, 1,
NULL)) synonyms, sum(decode(t1.TYPE#, 6, 1, NULL)) sequences,
sum(decode(t1.TYPE#, 1, NULL, 2, NULL, 3, NULL, 4, NULL, 5, NULL, 6, NULL, 1))
others from SYS.OBJ$ t1, SYS.USER$ t2 where t1.TYPE# >= 1 and t2.USER# =
t1.OWNER# and t2.NAME <> 'PUBLIC' group by t2.NAME;

DM7：

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、查看有哪些数据库实例在运行

Oracle 11g：

select INST_NAME from V$ACTIVE_INSTANCES;

DM7：

select NAME inst_name from V$INSTANCE;

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

Oracle 11g 查看：

show parameter processes;

修改方法：alter system set processes=100 scope = spfile;
注：此处100为修改后的数目，重启数据库服务后生效。

DM7：

select PARA_VALUE from V$DM_INI where PARA_NAME='MAX_SESSIONS';

修改方法：修改data/实例名/下dm.ini中max_sessions的数值，重启数据库服务后生效。

15、为指定的表的列添加注释

Oracle 11g和DM的方法一样：

comment on column tablename.colname is '注释内容';

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

oracle11g：

select * from USER_SOURCE;
select * from USER_TRIGGERS;

这里因为Oracle不是一行显示完一个对象的所有定义，而是分行显示，所以要特别注意下line字段的行号。

DM7：

select t1.TXT,t2.SUBTYPE$,t2.NAME from SYSTEXTS t1,SYSOBJECTS t2 where t2.id=t1.id;

17、查看表定义

Oracle 11g：

select DBMS_METADATA.GET_DDL('TABLE','表名','用户名') from dual;

DM7：

需要先启用工具包dbms_metadata，如下：

Call SP_CREATE_SYSTEM_PACKAGES(1, DBMS_METADATA);    

然后执行查询：

select DBMS_METADATA.GET_DDL('TABLE','TEST','GUOQI');

18、强制关闭用户连接

Oracle 11g：

select SID,SERIAL# from V$SESSION where USERNAME='ERP';

使用此语句会返回一个进程列表,每行有两个数字,然后用数字替代下面的sid和serial

alter system kill session 'sid,serial';

例如：

alter system kill session '222,123';

DM7：

Select SESS_ID, CURR_SCH, SQL_TEXT, USER_NAME, TRX_ID, CLNT_HOST, CLNT_IP, OSNAME, VPOOLADDR
from SYS.v$sessions where SQL_TEXT<>'';

查出报错sql中使用对象的会话，然后记录SESSION_ID， 然后使用系统过程SP_CLOSE_SESSION(SESSION_ID)；

总结：

尽管达梦数据库做了大量的Oracle兼容性工作，但运维所用SQL或有不同，希望本次的对比总结分享能对大家有所裨益。