--查看数据库实例状态

SELECT instance_name, host_name, startup_time, status, database_status

  FROM v$instance;

--查看数据库信息

SELECT NAME, log_mode, open_mode

  FROM v$database a;

--查看本机监听器进程

ps -ef|grep lsn|grep -v grep

-e 显示所有进程。

-f 显示更完整的进程信息

-v：显示不包含匹配文本的所有行。

--查看控制文件的状态

SELECT status, NAME

  FROM v$controlfile;

--查看redo日记的状态

SELECT group#, status, TYPE, MEMBER

  FROM v$logfile;

--查看表空间的状态

SELECT tablespace_name, status

  FROM dba_tablespaces;

--查看数据文件的状态

SELECT NAME, status

  FROM v$datafile;

--查看无效对象

SELECT owner, object_name, object_type, status

  FROM dba_objects

 WHERE status != 'VALID'

   AND owner != 'SYS'

   AND owner != 'SYSTEM';

--v$resource_limit 资源检查

SELECT resource_name, initial_allocation,CURRENT_UTILIZATION, max_utilization, limit_value

  FROM v$resource_limit ;

--v$resource_limit 数据库连接状态

SELECT COUNT(1)

  FROM v$session a

SELECT sid, serial#, username, program, machine, status

  FROM v$session;

--检查表空间使用情况

SELECT f.tablespace_name, a.total "total (M)", f.free "free (M)", round((f.free / a.total) * 100) "% Free"

  FROM (SELECT tablespace_name, SUM(bytes / (1024 * 1024)) total

          FROM dba_data_files

         GROUP BY tablespace_name) a,

       (SELECT tablespace_name, round(SUM(bytes / (1024 * 1024))) free

          FROM dba_free_space

         GROUP BY tablespace_name) f

 WHERE a.tablespace_name = f.tablespace_name(+)

 ORDER BY "% Free";

--表空间是否具有自动扩展空间

SELECT t.tablespace_name, d.file_name, d.autoextensible, d.bytes, d.maxbytes, d.status

  FROM dba_tablespaces t, dba_data_files d

 WHERE t. tablespace_name = d. tablespace_name

 ORDER BY tablespace_name, file_name

--检查下一扩展与表空间的最大扩展值

SELECT a.table_name, a.next_extent, a.tablespace_name

  FROM all_tables a,

       (SELECT tablespace_name, MAX(bytes) AS big_chunk

          FROM dba_free_space

         GROUP BY tablespace_name) f

 WHERE f.tablespace_name = a.tablespace_name

   AND a.next_extent > f.big_chunk

UNION

SELECT a.index_name, a.next_extent, a.tablespace_name

  FROM all_indexes a,

       (SELECT tablespace_name, MAX(bytes) AS big_chunk

          FROM dba_free_space

         GROUP BY tablespace_name) f

 WHERE f.tablespace_name = a.tablespace_name

   AND a.next_extent > f.big_chunk;

--检查数据库的等待事件

SELECT sid, event, p1, p2, p3, wait_time, seconds_in_wait

  FROM v$session_wait

 WHERE event NOT LIKE 'SQL%'

   AND event NOT LIKE 'rdbms%';

--查找前十条性能差的sql

SELECT *

  FROM (SELECT parsing_user_id executions, sorts, command_type, disk_reads, sql_text

          FROM v$sqlarea

         ORDER BY disk_reads DESC)

 WHERE rownum < 10;

--查找前十条性能差的sql

SELECT username, sid, opname, round(sofar * 100 / totalwork, 0) || '%' AS progress, time_remaining, sql_text

  FROM v$session_longops, v$sql

 WHERE time_remaining <> 0

   AND sql_address = address

   AND sql_hash_value = hash_value;

--监控数据库某用户在运行什么SQL

SELECT sql_text

  FROM v$sqltext t, v$session s

 WHERE t.address = s.sql_address

   AND t.hash_value = s.sql_hash_value

   AND s.machine = 'XXXXX'

    OR username = 'XXXXX' -- 查看某主机名，或用户名