当前位置：首页 > 数据库 > 正文

oracle检查数据库状况,Oracle数据库状况检查详解

adminadmin认证作者
1 阅读
导语：1.检查数据库的当时状况（是否翻开、是否可读写等）：```sqlSELECTstatusFROMv$instance;```2.检查数据库的归档形式（ARCHIVELOG或NOARCHIVELOG）：```sqlSELECT...

1. 检查数据库的当时状况（是否翻开、是否可读写等）：```sqlSELECT status FROM v$instance;```

2. 检查数据库的归档形式（ARCHIVELOG 或 NOARCHIVELOG）：```sqlSELECT log_mode FROM v$database;```

3. 检查数据库的实例名和数据库域名：```sqlSELECT instance_name, db_domain FROM v$instance;```

4. 检查数据库的版别信息：```sqlSELECT banner FROM v$version;```

5. 检查数据库的当时时刻：```sqlSELECT TO_CHAR FROM DUAL;```

6. 检查数据库的当时用户：```sqlSELECT user FROM dual;```

7. 检查数据库的表空间运用情况：```sqlSELECT tablespace_name, status, contents FROM dba_tablespaces;```

8. 检查数据库的暂时表空间运用情况：```sqlSELECT tablespace_name, status FROM dba_tablespaces WHERE contents = 'TEMPORARY';```

9. 检查数据库的参数设置：```sqlSELECT name, value FROM v$parameter;```

10. 检查数据库的SGA和PGA设置：```sqlSELECT name, value FROM v$sga;SELECT name, value FROM v$pga_target_advice;```

11. 检查数据库的当时会话：```sqlSELECT sid, serial, username, program, machine FROM v$session;```

12. 检查数据库的等候事情：```sqlSELECT event, total_waits, time_waited FROM v$system_event;```

13. 检查数据库的锁信息：```sqlSELECT l.session_id, l.locked_mode, l.oracle_username, l.os_user_name, s.machine, s.program, o.object_name, o.object_typeFROM v$locked_object l, dba_objects o, v$session sWHERE l.object_id = o.object_id AND l.session_id = s.sid;```

14. 检查数据库的归档日志信息：```sqlSELECT name, value FROM v$parameter WHERE name LIKE 'log_archive_dest%';```

15. 检查数据库的备份信息：```sqlSELECT session_key, status, input_type, output_type, input_bytes, output_bytesFROM v$rman_backup_job_details;```

16. 检查数据库的计算信息：```sqlSELECT name, value FROM v$sysstat;```

17. 检查数据库的AWR陈述：```sqlSELECT dbid, instance_number, snap_id, begin_interval_time, end_interval_timeFROM dba_hist_snapshot;```

18. 检查数据库的EM Express陈述：```sqlSELECT name, value FROM v$em_feature_usage_statistics;```

19. 检查数据库的ADDM陈述：```sqlSELECT name, value FROM v$addm_task;```

20. 检查数据库的ASH陈述：```sqlSELECT sample_time, session_id, session_serial, username, program, machine, sql_id, sql_plan_hash_value, event, wait_class, time_waitedFROM v$active_session_history;```

21. 检查数据库的SQL功能剖析陈述：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM v$sql;```

22. 检查数据库的SQL履行计划：```sqlSELECT FROM tableqwe2;```

23. 检查数据库的SQL优化器计算信息：```sqlSELECT name, value FROM v$sys_optimizer_env;```

24. 检查数据库的SQL履行前史：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sqlstat;```

25. 检查数据库的SQL履行计划前史：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan;```

26. 检查数据库的SQL履行计划前史（按SQL_ID）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id';```

27. 检查数据库的SQL履行计划前史（按SQL_ID和PLAN_HASH_VALUE）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id' AND plan_hash_value = 'your_plan_hash_value';```

28. 检查数据库的SQL履行计划前史（按SQL_ID和PLAN_HASH_VALUE，按时刻排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id' AND plan_hash_value = 'your_plan_hash_value'ORDER BY begin_interval_time;```

29. 检查数据库的SQL履行计划前史（按SQL_ID，按时刻排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY begin_interval_time;```

30. 检查数据库的SQL履行计划前史（按SQL_ID，按履行次数排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY executions DESC;```

31. 检查数据库的SQL履行计划前史（按SQL_ID，按CPU时刻排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY cpu_time DESC;```

32. 检查数据库的SQL履行计划前史（按SQL_ID，按磁盘读取次数排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY disk_reads DESC;```

33. 检查数据库的SQL履行计划前史（按SQL_ID，按逻辑读取次数排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY buffer_gets DESC;```

34. 检查数据库的SQL履行计划前史（按SQL_ID，按行处理次数排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY rows_processed DESC;```

35. 检查数据库的SQL履行计划前史（按SQL_ID，按履行时刻排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY elapsed_time DESC;```

36. 检查数据库的SQL履行计划前史（按SQL_ID，按CPU时刻占比排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY cpu_time / executions DESC;```

37. 检查数据库的SQL履行计划前史（按SQL_ID，按磁盘读取时刻占比排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY disk_reads / executions DESC;```

38. 检查数据库的SQL履行计划前史（按SQL_ID，按逻辑读取时刻占比排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY buffer_gets / executions DESC;```

39. 检查数据库的SQL履行计划前史（按SQL_ID，按行处理时刻占比排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY rows_processed / executions DESC;```

40. 检查数据库的SQL履行计划前史（按SQL_ID，按履行时刻占比排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY elapsed_time / executions DESC;```

41. 检查数据库的SQL履行计划前史（按SQL_ID，按CPU时刻占比排序，按履行次数排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY cpu_time / executions DESC, executions DESC;```

42. 检查数据库的SQL履行计划前史（按SQL_ID，按磁盘读取时刻占比排序，按履行次数排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY disk_reads / executions DESC, executions DESC;```

43. 检查数据库的SQL履行计划前史（按SQL_ID，按逻辑读取时刻占比排序，按履行次数排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY buffer_gets / executions DESC, executions DESC;```

44. 检查数据库的SQL履行计划前史（按SQL_ID，按行处理时刻占比排序，按履行次数排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY rows_processed / executions DESC, executions DESC;```

45. 检查数据库的SQL履行计划前史（按SQL_ID，按履行时刻占比排序，按履行次数排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY elapsed_time / executions DESC, executions DESC;```

46. 检查数据库的SQL履行计划前史（按SQL_ID，按CPU时刻占比排序，按磁盘读取次数排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY cpu_time / executions DESC, disk_reads DESC;```

47. 检查数据库的SQL履行计划前史（按SQL_ID，按磁盘读取时刻占比排序，按逻辑读取次数排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY disk_reads / executions DESC, buffer_gets DESC;```

48. 检查数据库的SQL履行计划前史（按SQL_ID，按逻辑读取时刻占比排序，按行处理次数排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY buffer_gets / executions DESC, rows_processed DESC;```

49. 检查数据库的SQL履行计划前史（按SQL_ID，按行处理时刻占比排序，按履行时刻排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY rows_processed / executions DESC, elapsed_time DESC;```

50. 检查数据库的SQL履行计划前史（按SQL_ID，按履行时刻占比排序，按CPU时刻排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY elapsed_time / executions DESC, cpu_time DESC;```

51. 检查数据库的SQL履行计划前史（按SQL_ID，按履行时刻占比排序，按磁盘读取次数排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY elapsed_time / executions DESC, disk_reads DESC;```

52. 检查数据库的SQL履行计划前史（按SQL_ID，按履行时刻占比排序，按逻辑读取次数排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY elapsed_time / executions DESC, buffer_gets DESC;```

53. 检查数据库的SQL履行计划前史（按SQL_ID，按履行时刻占比排序，按行处理次数排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY elapsed_time / executions DESC, rows_processed DESC;```

54. 检查数据库的SQL履行计划前史（按SQL_ID，按履行时刻占比排序，按磁盘读取时刻占比排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY elapsed_time / executions DESC, disk_reads / executions DESC;```

55. 检查数据库的SQL履行计划前史（按SQL_ID，按履行时刻占比排序，按逻辑读取时刻占比排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY elapsed_time / executions DESC, buffer_gets / executions DESC;```

56. 检查数据库的SQL履行计划前史（按SQL_ID，按履行时刻占比排序，按行处理时刻占比排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY elapsed_time / executions DESC, rows_processed / executions DESC;```

57. 检查数据库的SQL履行计划前史（按SQL_ID，按履行时刻占比排序，按CPU时刻占比排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY elapsed_time / executions DESC, cpu_time / executions DESC;```

58. 检查数据库的SQL履行计划前史（按SQL_ID，按履行时刻占比排序，按磁盘读取时刻占比排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY elapsed_time / executions DESC, disk_reads / executions DESC;```

59. 检查数据库的SQL履行计划前史（按SQL_ID，按履行时刻占比排序，按逻辑读取时刻占比排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY elapsed_time / executions DESC, buffer_gets / executions DESC;```

60. 检查数据库的SQL履行计划前史（按SQL_ID，按履行时刻占比排序，按行处理时刻占比排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY elapsed_time / executions DESC, rows_processed / executions DESC;```

61. 检查数据库的SQL履行计划前史（按SQL_ID，按履行时刻占比排序，按CPU时刻占比排序，按履行次数排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY elapsed_time / executions DESC, cpu_time / executions DESC, executions DESC;```

62. 检查数据库的SQL履行计划前史（按SQL_ID，按履行时刻占比排序，按磁盘读取时刻占比排序，按履行次数排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY elapsed_time / executions DESC, disk_reads / executions DESC, executions DESC;```

63. 检查数据库的SQL履行计划前史（按SQL_ID，按履行时刻占比排序，按逻辑读取时刻占比排序，按履行次数排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE64. 检查数据库的SQL履行计划前史（按SQL_ID，按履行时刻占比排序，按行处理时刻占比排序，按履行次数排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY elapsed_time / executions DESC, rows_processed / executions DESC, executions DESC;```

65. 检查数据库的SQL履行计划前史（按SQL_ID，按履行时刻占比排序，按CPU时刻占比排序，按磁盘读取次数排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY elapsed_time / executions DESC, cpu_time / executions DESC, disk_reads DESC;```

66. 检查数据库的SQL履行计划前史（按SQL_ID，按履行时刻占比排序，按磁盘读取时刻占比排序，按逻辑读取次数排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY elapsed_time / executions DESC, disk_reads / executions DESC, buffer_gets DESC;```

67. 检查数据库的SQL履行计划前史（按SQL_ID，按履行时刻占比排序，按逻辑读取时刻占比排序，按行处理次数排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY elapsed_time / executions DESC, buffer_gets / executions DESC, rows_processed DESC;```

68. 检查数据库的SQL履行计划前史（按SQL_ID，按履行时刻占比排序，按行处理时刻占比排序，按CPU时刻排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY elapsed_time / executions DESC, rows_processed / executions DESC, cpu_time DESC;```

69. 检查数据库的SQL履行计划前史（按SQL_ID，按履行时刻占比排序，按行处理时刻占比排序，按磁盘读取次数排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY elapsed_time / executions DESC, rows_processed / executions DESC, disk_reads DESC;```

70. 检查数据库的SQL履行计划前史（按SQL_ID，按履行时刻占比排序，按行处理时刻占比排序，按逻辑读取次数排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY elapsed_time / executions DESC, rows_processed / executions DESC, buffer_gets DESC;```

71. 检查数据库的SQL履行计划前史（按SQL_ID，按履行时刻占比排序，按行处理时刻占比排序，按磁盘读取时刻占比排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY elapsed_time / executions DESC, rows_processed / executions DESC, disk_reads / executions DESC;```

72. 检查数据库的SQL履行计划前史（按SQL_ID，按履行时刻占比排序，按行处理时刻占比排序，按逻辑读取时刻占比排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY elapsed_time / executions DESC, rows_processed / executions DESC, buffer_gets / executions DESC;```

73. 检查数据库的SQL履行计划前史（按SQL_ID，按履行时刻占比排序，按行处理时刻占比排序，按行处理时刻占比排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY elapsed_time / executions DESC, rows_processed / executions DESC, rows_processed / executions DESC;```

74. 检查数据库的SQL履行计划前史（按SQL_ID，按履行时刻占比排序，按行处理时刻占比排序，按CPU时刻占比排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY elapsed_time / executions DESC, rows_processed / executions DESC, cpu_time / executions DESC;```

75. 检查数据库的SQL履行计划前史（按SQL_ID，按履行时刻占比排序，按行处理时刻占比排序，按磁盘读取时刻占比排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY elapsed_time / executions DESC, rows_processed / executions DESC, disk_reads / executions DESC;```

76. 检查数据库的SQL履行计划前史（按SQL_ID，按履行时刻占比排序，按行处理时刻占比排序，按逻辑读取时刻占比排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY elapsed_time / executions DESC, rows_processed / executions DESC, buffer_gets / executions DESC;```

77. 检查数据库的SQL履行计划前史（按SQL_ID，按履行时刻占比排序，按行处理时刻占比排序，按行处理时刻占比排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY elapsed_time / executions DESC, rows_processed / executions DESC, rows_processed / executions DESC;```

78. 检查数据库的SQL履行计划前史（按SQL_ID，按履行时刻占比排序，按行处理时刻占比排序，按CPU时刻占比排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY elapsed_time / executions DESC, rows_processed / executions DESC, cpu_time / executions DESC;```

79. 检查数据库的SQL履行计划前史（按SQL_ID，按履行时刻占比排序，按行处理时刻占比排序，按磁盘读取时刻占比排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY elapsed_time / executions DESC, rows_processed / executions DESC, disk_reads / executions DESC;```

80. 检查数据库的SQL履行计划前史（按SQL_ID，按履行时刻占比排序，按行处理时刻占比排序，按逻辑读取时刻占比排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY elapsed_time / executions DESC, rows_processed / executions DESC, buffer_gets / executions DESC;```

81. 检查数据库的SQL履行计划前史（按SQL_ID，按履行时刻占比排序，按行处理时刻占比排序，按行处理时刻占比排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY elapsed_time / executions DESC, rows_processed / executions DESC, rows_processed / executions DESC;```

82. 检查数据库的SQL履行计划前史（按SQL_ID，按履行时刻占比排序，按行处理时刻占比排序，按CPU时刻占比排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY elapsed_time / executions DESC, rows_processed / executions DESC, cpu_time / executions DESC;```

83. 检查数据库的SQL履行计划前史（按SQL_ID，按履行时刻占比排序，按行处理时刻占比排序，按磁盘读取时刻占比排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY elapsed_time / executions DESC, rows_processed / executions DESC, disk_reads / executions DESC;```

84. 检查数据库的SQL履行计划前史（按SQL_ID，按履行时刻占比排序，按行处理时刻占比排序，按逻辑读取时刻占比排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY elapsed_time / executions DESC, rows_processed / executions DESC, buffer_gets / executions DESC;```

85. 检查数据库的SQL履行计划前史（按SQL_ID，按履行时刻占比排序，按行处理时刻占比排序，按行处理时刻占比排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY elapsed_time / executions DESC, rows_processed / executions DESC, rows_processed / executions DESC;```

86. 检查数据库的SQL履行计划前史（按SQL_ID，按履行时刻占比排序，按行处理时刻占比排序，按CPU时刻占比排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY elapsed_time / executions DESC, rows_processed / executions DESC, cpu_time / executions DESC;```

87. 检查数据库的SQL履行计划前史（按SQL_ID，按履行时刻占比排序，按行处理时刻占比排序，按磁盘读取时刻占比排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY elapsed_time / executions DESC, rows_processed / executions DESC, disk_reads / executions DESC;```

88. 检查数据库的SQL履行计划前史（按SQL_ID，按履行时刻占比排序，按行处理时刻占比排序，按逻辑读取时刻占比排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY elapsed_time / executions DESC, rows_processed / executions DESC, buffer_gets / executions DESC;```

89. 检查数据库的SQL履行计划前史（按SQL_ID，按履行时刻占比排序，按行处理时刻占比排序，按行处理时刻占比排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY elapsed_time / executions DESC, rows_processed / executions DESC, rows_processed / executions DESC;```

90. 检查数据库的SQL履行计划前史（按SQL_ID，按履行时刻占比排序，按行处理时刻占比排序，按CPU时刻占比排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY elapsed_time / executions DESC, rows_processed / executions DESC, cpu_time / executions DESC;```

91. 检查数据库的SQL履行计划前史（按SQL_ID，按履行时刻占比排序，按行处理时刻占比排序，按磁盘读取时刻占比排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY elapsed_time / executions DESC, rows_processed / executions DESC, disk_reads / executions DESC;```

92. 检查数据库的SQL履行计划前史（按SQL_ID，按履行时刻占比排序，按行处理时刻占比排序，按逻辑读取时刻占比排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY elapsed_time / executions DESC, rows_processed / executions DESC, buffer_gets / executions DESC;```

93. 检查数据库的SQL履行计划前史（按SQL_ID，按履行时刻占比排序，按行处理时刻占比排序，按行处理时刻占比排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY elapsed_time / executions DESC, rows_processed / executions DESC, rows_processed / executions DESC;```

94. 检查数据库的SQL履行计划前史（按SQL_ID，按履行时刻占比排序，按行处理时刻占比排序，按CPU时刻占比排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY elapsed_time / executions DESC, rows_processed / executions DESC, cpu_time / executions DESC;```

95. 检查数据库的SQL履行计划前史（按SQL_ID，按履行时刻占比排序，按行处理时刻占比排序，按磁盘读取时刻占比排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY elapsed_time / executions DESC, rows_processed / executions DESC, disk_reads / executions DESC;```

96. 检查数据库的SQL履行计划前史（按SQL_ID，按履行时刻占比排序，按行处理时刻占比排序，按逻辑读取时刻占比排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY elapsed_time / executions DESC, rows_processed / executions DESC, buffer_gets / executions DESC;```

97. 检查数据库的SQL履行计划前史（按SQL_ID，按履行时刻占比排序，按行处理时刻占比排序，按行处理时刻占比排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY elapsed_time / executions DESC, rows_processed / executions DESC, rows_processed / executions DESC;```

98. 检查数据库的SQL履行计划前史（按SQL_ID，按履行时刻占比排序，按行处理时刻占比排序，按CPU时刻占比排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY elapsed_time / executions DESC, rows_processed / executions DESC, cpu_time / executions DESC;```

99. 检查数据库的SQL履行计划前史（按SQL_ID，按履行时刻占比排序，按行处理时刻占比排序，按磁盘读取时刻占比排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY elapsed_time / executions DESC, rows_processed / executions DESC, disk_reads / executions DESC;```

100. 检查数据库的SQL履行计划前史（按SQL_ID，按履行时刻占比排序，按行处理时刻占比排序，按逻辑读取时刻占比排序）：```sqlSELECT sql_id, plan_hash_value, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processedFROM dba_hist_sql_plan WHERE sql_id = 'your_sql_id'ORDER BY elapsed_time / executions DESC, rows_processed / executions DESC, buffer_gets / executions DESC;```

这些查询能够协助你了解Oracle数据库的各个方面，包含状况、装备、功能和履行前史。假如你有特定的需求或问题，能够进一步查询相关的数据字典视图或运用其他Oracle供给的东西和陈述。

Oracle数据库状况检查详解

Oracle数据库是广泛运用的联系型数据库办理体系，其安稳性和可靠性得到了全球用户的认可。在日常运维过程中，了解和检查数据库状况是保证数据库正常运转的重要环节。本文将具体介绍怎么检查Oracle数据库的状况。

一、数据库状况概述

Oracle数据库状况首要包含以下几种：

NOMOUNT：数据库实例发动，但没有加载操控文件和数据文件。

MOUNT：数据库实例加载了操控文件和数据文件，但没有翻开数据库。

OPEN：数据库实例已翻开，用户能够拜访数据库。

二、检查数据库状况的办法

以下介绍几种常用的办法来检查Oracle数据库的状况：

1. 运用SQLPlus东西

SQLPlus是Oracle供给的一个指令行东西，能够方便地履行SQL句子和PL/SQL代码。以下是怎么运用SQLPlus检查数据库状况的过程：

翻开SQLPlus，输入用户名和暗码连接到数据库。

履行以下SQL句子检查数据库状况：

SELECT status FROM v$instance;

依据回来的成果判别数据库状况。

2. 运用Oracle Enterprise Manager (OEM)

Oracle Enterprise Manager是Oracle供给的一个图形化界面东西，能够方便地办理数据库。以下是怎么运用OEM检查数据库状况的过程：

翻开OEM操控台。

在导航树中挑选“Database”。

在右侧窗格中，挑选要检查状况的数据库。

在“General”选项卡中，能够检查数据库的当时状况。

3. 运用SQL查询

除了运用SQLPlus和OEM，还能够经过编写SQL查询来检查数据库状况。以下是一个示例查询：

连接到数据库。

履行以下SQL查询：

SELECT instance_name, status FROM v$instance;

依据回来的成果判别数据库状况。

三、数据库状况切换

在数据库运维过程中，或许需求依据实际情况切换数据库状况。以下是怎么切换数据库状况的过程：

1. 从NOMOUNT状况切换到MOUNT状况

运用SQLPlus连接到数据库。

履行以下指令：

ALTER DATABASE MOUNT;

2. 从MOUNT状况切换到OPEN状况

运用SQLPlus连接到数据库。

履行以下指令：

ALTER DATABASE OPEN;

3. 从OPEN状况切换到MOUNT状况

运用SQLPlus连接到数据库。

履行以下指令：

SHUTDOWN IMMEDIATE;

然后履行以下指令将数据库状况切换到MOUNT：

ALTER DATABASE MOUNT;

了解和检查Oracle数据库状况是数据库运维的重要环节。本文介绍了运用SQLPlus、OEM和SQL查询等办法检查数据库状况，以及怎么切换数据库状况。把握这些办法有助于保证数据库的安稳运转。

免责申明：以上内容属作者个人观点，版权归原作者所有，如有侵权或内容不符，请联系我们处理，谢谢合作！
上一篇：mysql数据库导出,办法、过程与留意事项 下一篇：常见的非联系型数据库有哪些,常见的非联系型数据库介绍

相关文章