oracle检查数据库状况,Oracle数据库状况检查详解
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,从入门到实践详细阅读
创立MySQL数据库一般包含以下过程：1.装置MySQL数据库服务器：保证你的核算机上现已装置了MySQL数据库服务器。假如没有，你可以从MySQL官方网站下载并装置。2....
2024-12-26 0
-
oracle打补丁,进程详解与留意事项详细阅读
在Oracle数据库中打补丁是一个重要的保护使命，以保证数据库的安全性和功用。以下是打补丁的一般进程：1.承认补丁类型：首要，需求承认需求打的补丁类型，如CriticalP...
2024-12-26 0
-
大数据核算,大数据核算概述详细阅读
1.数据搜集：从各种来历（如传感器、交际媒体、买卖记载等）搜集很多数据。2.数据存储：运用分布式文件体系（如Hadoop的HDFS）或NoSQL数据库（如MongoDB、C...
2024-12-26 0
-
mysql表分区,原理、类型与运用详细阅读
MySQL表分区是一种优化数据库功能的技能，它答应你将表中的数据分割成多个部分，每个部分称为一个分区。这样，数据库办理体系能够更高效地办理和查询数据，尤其是在处理很多数据时。M...
2024-12-26 0
-
数据库索引原理,什么是数据库索引？详细阅读
数据库索引是数据库办理体系中一种用于进步数据检索速度的数据结构。它类似于书本的目录，经过供给一种快速查找特定数据的办法，来削减查询操作所需的时刻。索引的基本原理1.索引的创...
2024-12-26 0
-
大数据处理软件,大数据处理软件概述详细阅读
1.Hadoop：一个开源结构，用于在大型集群上存储和处理大数据集。它供给了分布式文件体系（HDFS）和MapReduce核算模型，支撑批处理和实时处理。2.Spark：一...
2024-12-26 0
-
access数据库根底,Access数据库根底入门攻略详细阅读
1.数据库目标：表：用于存储数据，类似于Excel中的作业表。查询：用于检索、更新、删去或刺进表中的数据。窗体：用于输入、显现和修正数据。...
2024-12-26 0
-
怎样检查自己的大数据,怎么检查自己的大数据信息详细阅读
检查自己的大数据，一般需求经过以下几种方法：1.在线渠道账户：假如你运用的是一些供给数据服务的在线渠道（如交际媒体、购物网站、在线银行等），你一般能够在这些渠道的账户设置中找...
2024-12-26 0
-
mysql存储引擎,MySQL存储引擎概述详细阅读
1.InnoDB：这是MySQL的默许存储引擎，支撑业务处理、行级确定和外键束缚。InnoDB引擎供给了较高的数据一致性和完整性，适用于需求高可靠性和业务性的运用。2.My...
2024-12-26 0
-
mysql外键束缚,什么是外键束缚？详细阅读
MySQL中的外键束缚用于强制在两个表之间坚持数据的一致性。它保证了一个表中的数据只引证另一个表中实践存在的数据。例如，假如你有一个订单表和一个客户表，订单表中的客户ID字段能...
2024-12-26 0