Oracle 查询状态 自检-LMLPHP

Tips:
fnd_lobs表会保存我们上传的一些文件和Form界面“文件“-“导出”的文件。如果不定期清理了话,会出现文件上传失败,或者是导出按钮可以点击,但是点击完以后没有任何反应。这个时候我们应该看下fnd_lobs所属的表空间APPS_TS_MEDIA是不是已经满了。我们可以通过以下的命令删除“导出”的文件。
delete fnd_lobs fl WHERE fl.program_name='export';

--查看数据库实例状态
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' -- 查看某主机名,或用户名
05-13 04:14