本文共 2605 字，大约阅读时间需要 8 分钟。

Oracle????????????

???????????????????????Oracle???????????????????????SQL?????????????????????????????

1. ??????????

???????????????????????????????????????????????

SELECT username, sid, serial#, program, terminalFROM v$sessionORDER BY username, sid, serial#;

?????????????????????ID???ID?????????????????????????????????

???????????????????????????????????????

SELECT username, sid, serial#, program, terminal, event, wait_timeFROM v$session_waitWHERE event LIKE '%buffer busy%' OR event LIKE '%write complete%';

2. ?????????

???????????????????????????????????????????????????

SELECT index_name, table_name, monitoring, used, start_monitoring, end_monitoringFROM v$object_usageWHERE index_name = 'HRDT_INDEX1';

????????????????????????????????????????????

3. ????I/O??

???????????????????????I/O?????????????????????

SELECT file#, name, status, bytes, phyreads, phywrtsFROM v$datafileWHERE file# = (SELECT file# FROM v$filestat WHERE status = 'ONLINE');

???????????????????????????????????????????????

4. ???????

??????????????????????????????????????????

SELECT name, extents, rsssize, xacts, waits, gets, optsize, statusFROM v$rollnameJOIN v$rollstat ON rollname.usn = rollstat.usnWHERE status = 'ONLINE';

?????????????????????????????????????????????????

5. ???????ASSM???

Oracle????????ASSM???????????????????????????ASSM????

SELECT COUNT(*) / (SELECT value FROM v$waitstat WHERE class = 'free list') * 100 pctFROM v$waitstatJOIN v$sysstat ON v$sysstat.statistic# = v$sysstat.statistic#JOIN v$sysstat ON v$sysstat.statistic# = v$sysstat.statistic#WHERE v$waitstat.class = 'free list';

???????????????????????ASSM???????

6. ???????

?????????????????????????????????????????????

SELECT granted_role || decode(admin_option, 'YES', 'With Admin Option', null) || what_granted, granteeFROM v$role_privsWHERE grantee = ' SYSTEM ';

???????SYSTEM??????????????????????

7. ???????

??????????????????????????????????????

SELECT username, sid, serial#, program, terminalFROM v$sessionORDER BY username, sid, serial#;

?????????ALTER SYSTEM KILL SESSION??????????????????????????

8. ???I/O?????

??????I/O????????????????????????????????I/O?????

SELECT file#, name, status, bytes, phyreads, phywrtsFROM v$datafileJOIN v$filestat ON file# = file#WHERE status = 'ONLINE';

????????????????I/O???????????????

9. ???????

???????????????????????????????????????????

SELECT name, extents, rsssize, xacts, waits, gets, optsize, statusFROM v$rollnameJOIN v$rollstat ON rollname.usn = rollstat.usnWHERE status = 'ONLINE';

?????????????????????????????????????????????????

?????????????Oracle????????????????????????????????