【正文】 by segment_name。 查看控制文件select name from v$controlfile。 查看日志文件select member from v$logfile。 查看表空間的使用情況select sum(bytes) / (1024 * 1024) as free_space, tablespace_name from dba_free_space group by tablespace_name。SELECT , TOTAL, USED, FREE, ( * 100) / % USED, ( * 100) / % FREE FROM $TS_AVAIL A, $TS_USED B, $TS_FREE C WHERE = AND = 。 查看數(shù)據(jù)庫對(duì)象select owner, object_type, status, count(*) count from all_objects group by owner, object_type, status。 查看數(shù)據(jù)庫的版本Select version FROM Product_ponent_version Where SUBSTR(PRODUCT, 1, 6) = 39。Oracle39。 查看Oracle字符集 select * from $ where name = 39。NLS_CHARACTERSET39。 在某個(gè)用戶下找所有的索引 select , , uniqueness, column_name from user_ind_columns, user_indexes where = and = order by , , , column_position。 表、索引的存儲(chǔ)情況檢查 select segment_name, sum(bytes), count(*) ext_quan from dba_extents where tablespace_name = 39。amp。tablespace_name39。 and segment_type = 39。TABLE39。 group by tablespace_name, segment_name。select segment_name, count(*) from dba_extents where segment_type = 39。INDEX39。 and owner = 39。amp。owner39。 group by segment_name。 查看數(shù)據(jù)庫的創(chuàng)建日期和歸檔方式Select Created, Log_Mode, Log_Mode From V$Database。 顯示所有數(shù)據(jù)庫對(duì)象的類別和大小 select type, count(name) num_instances, sum(source_size) source_size, sum(parsed_size) parsed_size, sum(code_size) code_size, sum(error_size) error_size, sum(source_size) + sum(parsed_size) + sum(code_size) + sum(error_size) size_required from dba_object_size group by type order by 1。 設(shè)置RAC為歸檔模式？步驟：1. 以SYSDBA身份登陸2個(gè)節(jié)點(diǎn)，執(zhí)行alter system set cluster_database=false scope =spfile sid=’*’。設(shè)置歸檔路徑alter system set log_archive_start=true scope=spfile。2. 2個(gè)節(jié)點(diǎn)shutdown immediate3. 在一個(gè)節(jié)點(diǎn)上執(zhí)行startup mountalter database archivelog。shutdown immediate。alter database open。alter system set cluster_database=true scope =spfile sid=’*’。shutdown immediate分別啟動(dòng)2個(gè)節(jié)點(diǎn)，修改完畢6. AWR報(bào)告與9i 中的statspack相似，awr報(bào)告也需要兩個(gè)快照，才能生成這兩個(gè)時(shí)間點(diǎn)之間的性能報(bào)告。$sqlplus / as sysdba216。 生成快照一（10g中自動(dòng)會(huì)每個(gè)整點(diǎn)都會(huì)生成一個(gè)快照）SQL exec ()。216。 (間隔一段時(shí)間)生成快照二SQL exec ()。216。 生成報(bào)告SQL @?/rdbms/admin/7. Troubleshooting常用性能相關(guān)SQL，監(jiān)控?cái)?shù)據(jù)庫性能的SQL語句。 什么時(shí)侯需要重建索引？答：（1）表上頻繁發(fā)生update,delete操作在分析(analyze)指定索引之后，查詢index_stats的height字段的值，如果這個(gè)值=4 ,則最好重建（rebuild）這個(gè)索引。雖然這個(gè)規(guī)則不是總是正確，但如果這個(gè)值一直都是不變的，則這個(gè)索引也就不需重建。（2）表上發(fā)生了alter table ..move操作（move操作導(dǎo)致了rowid變化）。怎樣查詢某進(jìn)程正在運(yùn)行的SQL？答：可以執(zhí)行以下的查詢語句:select from v$session se,v$sql sq,v$process prwhere =(+)and =(+)and =and =39。進(jìn)程號(hào)39。怎樣終止某用戶的會(huì)話？答：執(zhí)行以下的查詢語句查出用戶的會(huì)話id：Select sid,serial,username,machine,programFrom v$session再利用查出的會(huì)話sid和serial號(hào)終于用戶會(huì)話：Alter system kill session ‘sid,serial’怎樣查詢表空間使用率？答：執(zhí)行以下語句進(jìn)行查詢：SELECT , TOTAL, USED, FREE, ( * 100) / % USED, ( * 100) / % FREE FROM $TS_AVAIL A, $TS_USED B, $TS_FREE C WHERE = AND = order by % USED DESC。怎樣查詢正在使用臨時(shí)表空間的語句？答：執(zhí)行以下的查詢語句：SELECT , , ,*to_number(rtrim()) as Space,tablespace,segtype,sql_textFROM v$sort_usage su,v$parameter p,v$session se,v$sql sWHERE =39。db_block_size39。AND =AND =AND =ORDER BY ,。怎樣查詢當(dāng)前的系統(tǒng)等待事件？答：執(zhí)行以下的查詢語句：SELECT , , , , , , , , , , , , , , FROM v$session s, v$session_wait wWHERE = AND NOT LIKE 39。%SQL*Net%39。AND NOT LIKE 39。%rdbms%39。AND NOT LIKE 39。%timer%39。AND NOT LIKE 39。%jobq%39。ORDER BY , 。怎樣遷移lob字段表空間？答：ALTER TABLE 表名 MOVE LOB(39。LOB字段名39。) STORE AS(TABLESPACE 表空間名)。怎樣遷移分區(qū)表表空間？答：alter table 表名 move PARTITION 表分區(qū)名 tablespace 另外的表空間名。 監(jiān)控事務(wù)的等待 select event, sum(decode(wait_Time, 0, 0, 1)) Prev, sum(decode(wait_Time, 0, 1, 0)) Curr, count(*) Totol from v$session_Wait group by event order by 4。 查看一些等待信息：select sid, event from v$session_wait where event not like 39。SQL%39。 and event not like 39。%ipc%39。查看是否存在下面等常見的等待事件：216。 buffer busy waits，216。 free buffer waits，216。 db file sequential read,216。 db file scattered read,216。 enqueue,latch free,216。 log file parallel write,216。 log file sync 查看等待(wait)情況SELECT v$, v$ count, SUM(v$) sum_value FROM v$waitstat, v$sysstat WHERE v$ IN (39。db block gets39。, 39。consistent gets39。) group by v$, v$。　　 回滾段查看select rownum, Name, v$ Extents, v$ Size_in_Bytes, v$ XActs, v$ Gets, v$ Waits, v$ Writes, status from v$rollstat, , v$rollname where v$(+) = and v$(+) = v$ order by rownum。 回滾段的爭(zhēng)用情況 select name, waits, gets, waits / gets Ratio from v$rollstat a, v$rollname b where = 。 監(jiān)控表空間的 I/O 比例 select name, file, pyr, pbr, pyw, pbw from v$filestat f, dba_data_files df where = order by 。 監(jiān)控文件系統(tǒng)的 I/O 比例 select substr(, 1, 2) , substr(, 1, 30) Name, , , , from v$datafile a, v$filestat b where = 。 監(jiān)控 SGA 的命中率 select + logical_reads, phys_reads, round(100 * (( + ) ) / ( + )) BUFFER HIT RATIO from v$sysstat a, v$sysstat b, v$sysstat c where = 38 physical read total multi block requests and = 39 physical read total bytes and = 40。 physical write total IO requests 監(jiān)控 SGA 中字典緩沖區(qū)的命中率 select parameter, gets, Getmisses, getmisses / (gets + getmisses) * 100 miss ratio, (1 (sum(getmisses) / (sum(gets) + sum(getmisses)))) * 100 Hit ratio from v$rowcache where gets + getmisses 0 group by parameter, gets, getmisses。 監(jiān)控 SGA 中共享緩存區(qū)的命中率，應(yīng)該小于1% select sum(pins) Total Pins, sum(reloads) Total Reloads, sum(reloads) / sum(pins) libcache from v$librarycache。select sum(pinhits reloads) / sum(pins) * 100 hit radio, sum(reloads) / sum(pins) reload percent from v$librarycache。 臨控 SGA 中