1、查看表名
有时，我们记不住精确的表名，通过以下可以查看
2、查看数据库版本
3、查看实例
4、查看数据库相关配置
5、查看所有表空间使用情况
SELECT F.TABLESPACE_NAME, (T.TOTAL_SPACE - F.FREE_SPACE) / 1024 "USED (GB)", F.FREE_SPACE / 1024 "FREE (GB)", T.TOTAL_SPACE / 1024 "TOTAL(GB)", (ROUND((F.FREE_SPACE / T.TOTAL_SPACE) * 100)) || '%' PER_FREE FROM (SELECT TABLESPACE_NAME, ROUND(SUM(BLOCKS * (SELECT PARA_VALUE / 1024 FROM V$DM_INI WHERE PARA_NAME ='GLOBAL_PAGE_SIZE') / 1024)) FREE_SPACE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F, (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES/1048576)) TOTAL_SPACE FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) T WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME; |
6、查看数据库缓冲池的命中率
select name "缓冲池名称", sum(page_size)*sf_get_page_size/1024/1024 "缓冲池大小mb", sum(rat_hit)/count(*) "命中率" from v$bufferpool group by name； |
sf_get_page_size() 数据库创建时指定的页大小
7、数据库相关信息
select name "数据库名称" , create_time "创建时间", decode（arch_mode,'Y','是','否'） "是否归档", decode(status$,'1','启动','2','启动,redo完成','3','MOUNT','4','打开','5','挂起','6','关闭') "数据库状态", decode(ROLE$,'0','普通','1','主机','2','备机') "数据库模式" from v$database; |
8、查看阻塞会话
select s.sess_id "会话号", s.sql_text "sql语句", s.state "会话状态", s.user_name "会话用户", s.create_time "会话创建时间", clnt_ip "会话IP", l.trx_id "事务ID", l.lmode "锁模式", l.table_id "表号", decode(l.blocked,1,'被阻塞','未阻塞') from v$sessions s, v$lock l where s.trx_id=l.trx_id and decode(l.blocked,1,'被阻塞','未阻塞')='被阻塞'; |
9、查看sql执行时间（从大到小）
select top_sql_text "sql文本", start_time "开始时间", time_used/1000/1000 "执行时间（毫秒）", n_logic_read "逻辑读数量", n_phy_read "物理读数量", affected_rows "影响行数" from V$SQL_HISTORY order by time_used desc; |
10、统计数据库中系统或会话级别的等待事件
select event "等待事件名称", total_waits "等待事件发生次数", time_waited_micro /1000 "等待事件总用时（ms）", average_wait_micro/1000 "平均等待事件（ms）" from v$system_event order by average_wait_micro desc; |
11、数据库中各个内存组件使用情况
select name "内存池名称", org_size/1024/1024 "参数文件配置大小（MB）", reserved_size/1024/1024 "实际使用大小（MB）" from v$mem_pool order by reserved_size desc; |
