DM巡检SQL

 

1、查看数据库版本号

select * from v$version;

select *from SYS.V$LICENSE

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

select NAME tablepace_name, total_size*SF_GET_PAGE_SIZE()/1024/1024||'M' ts_size

from V$TABLESPACE;

 

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

        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,

        d.FREE_SIZE*SF_GET_PAGE_SIZE()/1024/1024 ||'M' free_space,

        case when d.AUTO_EXTEND = 1 then '自动扩展' else '非自动扩展' end

        

from

        V$TABLESPACE t,

        V$DATAFILE d

where

        t.ID=d.GROUP_ID;

 

4、 查看控制文件

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

 

5、 查看日志文件

select path,rlog_size/1024/1024 as "大小M" from V$RLOGFILE;

 

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

select

        t1.NAME tablespace_name,

        t2.TOTAL_SIZE*SF_GET_PAGE_SIZE()/1024/1024||'M' total_space,

        t2.FREE_SIZE*SF_GET_PAGE_SIZE()/1024/1024 ||'M' free_space,

        t2.FREE_SIZE*100/t2.total_size||'%' as "FREE_SPACE%"

from

        V$TABLESPACE t1,

        V$DATAFILE t2

where

        t1.ID=t2.GROUP_ID;

 

7、查看数据库库对象

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、 查看数据库的创建日期和归档方式

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

注: DM7的CREATE_TIME指的是实例启动时间

 

9、查看还没提交的事务

 

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

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

 

10、查看等待（wait）情况

select CLASS_NAME,TOTAL_WAITS count from V$WAIT_CLASS;

 

11、查看object分类数量

select

        SUBTYPE$ object_type,

        count(1) quantity

from

        SYSOBJECTS

where

        SUBTYPE$ <>''

group by

        SUBTYPE$

union

select 'column', count(1) from SYSCOLUMNS;

 

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

select NAME inst_name from V$INSTANCE;

 

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

 

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

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

。

 

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

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

 

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

select

        t1.TXT     ,

        t2.SUBTYPE$,

        t2.NAME

from

        SYSTEXTS t1,

        SYSOBJECTS t2

where

        t2.id=t1.id;

 

17、查看表定义

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

Call SP_CREATE_SYSTEM_PACKAGES(1, DBMS_METADATA);    

 

然后执行查询：

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

 

18、强制关闭用户连接

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)；

19、查询数据库总大小

select sum(bytes)/1024/1024 from dba_segments;

 

20、查询用户大小

select owner,tablespace_name,sum(bytes)/1024/1024/1024 from dba_segments  t

where t.owner ='U_SJZX_SAP'

group by owner,tablespace_name