Oracle 作为目前市场排名第一的数据库管理系统，已广泛应用于各个行业领域。为了方便基于 Oracle 的应用向达梦数据库 DM7 移植，减轻数据库管理员的工作量，DM7 做了大量的 Oracle 兼容性工作。

• Oracle 环境

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

Linux 系统：2.6.32-220.el6.x86_64

• DM7 环境

DM Database Server x64 V7.1.5.95-Build(2016.06.28-70305)ENT

Linux 系统：2.6.32-220.el6.x86_64

查看数据库版本号

都是：select * from v$version;

查看表空间的名称及大小

• 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;

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

• 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;

查看控制文件

• Oracle 11g

select NAME from V$CONTROLFILE;

• DM7

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

查看日志文件

• Oracle 11g

select MEMBER from V$LOGFILE;

• DM7

select PATH from V$RLOGFILE;

查看表空间的使用情况

• Oracle 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;

查看数据库库对象

• 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;

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

• 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 指的是创建库的时间。

查看还没提交的事务

• 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';

查看等待 (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;