一、数据库信息
1、数据库时间
select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') AS dbtime from dual;
2、主机OS类型
SELECT dbms_utility.port_string FROM dual;
3、主机OS位数
select metadata from sys.kopm$;
4、参数配置信息
SELECT * FROM v$parameter t WHERE t.ISDEFAULT = 'FALSE';
5、资源限制信息
SELECT * FROM v$resource_limit;
6、数据库版本
select * from v$version;
7、核心组件版本
select * from dba_registry;
8、实例信息
select * from v$instance;
9、数据库信息
select * from v$database;
二、客户端连接信息
1、SESSION信息
select * from v$session;
2、PROCESS信息
select * from v$process;
三、锁信息
1、查看当前锁定对象
SELECT p.spid, s.serial#, a.object_name, k.session_id, k.oracle_username, k.os_user_name
FROM v$process p, v$session s, v$locked_object k, all_objects a
WHERE p.addr = s.paddr AND s.process = k.process AND a.object_id = k.object_id;
2、查看当前enqueue锁类型
select s.program,s.sql_hash_value,
w.event,w.state,w.seconds_in_wait,w.wait_time,
decode(w.event,'enqueue',chr(bitand(w.p1,-16777216)/16777215)||chr(bitand(w.p1,16711680)/65535)||', MODE: '||decode(bitand(p1,65536),0, 'None',1, 'Null (NULL)',2, 'Row-S (SS)',3, 'Row-X (SX)',4, 'Share (S)',5, 'S/Row-X (SSX)',6, 'Exclusive (X)',bitand(p1,65536))||', Waiting For: '||LOCKWAIT||',Object : '||ROW_WAIT_OBJ#||' at datafile : '||ROW_WAIT_FILE#||', Block : '|| ROW_WAIT_BLOCK#||', Row : '||ROW_WAIT_ROW#,w.p1) p1,w.p2,w.p3
from v$session s,v$session_wait w
where s.sid=w.sid
and s.status='ACTIVE'
and w.event not in (select event from perfstat.stats$idle_event);
四、SQL定位和处理
1、Session Kill
ALTER SYSTEM KILL SESSION '$SID,$SERIAL';
2、Session SQL
SELECT a.SID, b.SERIAL#, a.EVENT, b.MACHINE, b.USERNAME, c.HASH_VALUE, c.SQL_TEXT
FROM v$sqlarea c, v$session b, v$session_wait a
WHERE a.SID = b.SID AND b.SQL_HASH_VALUE = c.HASH_VALUE AND a.event NOT IN (
SELECT * FROM stats$idle_event);
3、根据hash_value定位SQL
select * from v$sqltext t where t.hash_value=:hash_value order by t.piece;
4、根据spid定位SQL
SELECT sql_text
FROM v$sqltext a
WHERE (a.hash_value, a.address) IN (
SELECT DECODE (sql_hash_value,
0, prev_hash_value,
sql_hash_value
),
DECODE (sql_hash_value, 0, prev_sql_addr, sql_address)
FROM v$session b
WHERE b.paddr = (SELECT addr
FROM v$process c
WHERE c.spid = :spid))
ORDER BY piece ASC
五、表空间查看
1、表空间使用情况
SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_MB "表空间大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' "使用比",
F.TOTAL_BYTES "空闲空间(M)",
F.MAX_BYTES "最大块(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 1
2、表空间的free space
select tablespace_name,
count(*) as extends,
round(sum(bytes) / 1024 / 1024, 2) as MB,
sum(blocks) as blocks
from dba_free_space
group by tablespace_name;
3、表空间总容量
select tablespace_name, sum(bytes) / 1024 / 1024 as MB
from dba_data_files
group by tablespace_name;
4、表空间使用率
select total.tablespace_name,
round(total.MB, 2) as Total_MB,
round(total.MB - free.MB, 2) as Used_MB,
round((1 - free.MB / total.MB) * 100, 2) || '%' as Used_Pct
from (select tablespace_name, sum(bytes) / 1024 / 1024 as MB
from dba_free_space
group by tablespace_name) free,
(select tablespace_name, sum(bytes) / 1024 / 1024 as MB
from dba_data_files
group by tablespace_name) total
where free.tablespace_name = total.tablespace_name;
六、重做日志
1、查看日志组
SELECT * FROM v$log;
2、查看日志组成员
SELECT * FROM v$logfile;
3、日志切换
ALTER SYSTEM SWITCH LOGFILE;
4、归档当前日志
alter system archive log current;
5、查看是否为归档模式
select name,log_mode from v$database;
6、查看归档相关参数
SELECT * FROM v$parameter t WHERE t.name like '%log%';