DM7 常用的运维SQL语句

1、查看数据库版本号

SQL> select * from v$version;

 

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

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

 

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;

 

4、 查看控制文件

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

 

5、 查看日志文件

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

 

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;

 

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;

 

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

SQL> select create_time start_time,case when arch_mode = 'n' then '非归档模式' else '归档模式' end from v$database;
 

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

 

10、查看等待（wait）情况

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

11、查看object分类数量

SQL> 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、查看数据库实例信息：

SQL> select name inst_name from v$instance;

 

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

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

SQL> sp_set_para_value (2,'MAX_SESSIONS',1000);
 
然后重启生效。

 

15、 确定高负载的 SQL
SQL> select * from v$long_exec_sqls;
SQL> select * from v$system_long_exec_sqls;
 

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

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

17、查看表定义

SQL>  select dbms_metadata.get_ddl('TABLE','DAVE','CNDBA');

 

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

然后根据SESSION_ID使用系统过程SP_CLOSE_SESSION(SESSION_ID)来kill 会话：
SQL> SP_CLOSE_SESSION(140615618728712);