【文章內(nèi)容簡(jiǎn)介】 重做日志緩存區(qū)的命中率，應(yīng)該小于1% SELECT name, gets, misses, immediate_gets, immediate_misses, Decode(gets, 0, 0, misses / gets * 100) ratio1, Decode(immediate_gets + immediate_misses, 0, 0, immediate_misses / (immediate_gets + immediate_misses) * 100) ratio2 FROM v$latch WHERE name IN (39。redo allocation39。, 39。redo copy39。)。 監(jiān)控內(nèi)存和硬盤的排序比率，最好使它小于 ，增加 sort_area_sizeSELECT name, value FROM v$sysstat WHERE name IN (39。sorts (memory)39。, 39。sorts (disk)39。)。 監(jiān)控當(dāng)前數(shù)據(jù)庫(kù)誰在運(yùn)行什么SQL語句 SELECT osuser, username, sql_text from v$session a, v$sqltext b where = order by address, piece。 監(jiān)控字典緩沖區(qū) SELECT SUM(PINS) EXECUTIONS, SUM(RELOADS) CACHE MISSES WHILE EXECUTING, (SUM(PINS RELOADS)) / SUM(PINS) LIB CACHE FROM V$LIBRARYCACHE。SELECT SUM(GETS) DICTIONARY GETS, SUM(GETMISSES) DICTIONARY CACHE GET MISSES, (SUM(GETS GETMISSES USAGE FIXED)) / SUM(GETS) ROW CACHE FROM V$ROWCACHE?！癓IB CACHE“與“ROW CACHE”。否則需要調(diào)大SGA的空間。 查看Lockselect , , , , , decode(, 39。TM39。, 39。TM DML Enqueue39。, 39。TX39。, 39。TX Trans Enqueue39。, 39。UL39。, 39。UL User39。, || 39。 Other Type39。) LOCKTYPE, substr(, 1, 10) OBJECT, owner, , , decode(, 1, 39。No Lock39。, 2, 39。Row Share39。, 3, 39。Row Exclusive39。, 4, 39。Share39。, 5, 39。Shr Row Excl39。, 6, 39。Exclusive39。, null) lmode, decode(, 1, 39。No Lock39。, 2, 39。Row Share39。, 3, 39。Row Excl39。, 4, 39。Share39。, 5, 39。Shr Row Excl39。, 6, 39。Exclusive39。, null) request from v$lock l, v$session s, $ u, $ t where = and != 39。BACKGROUND39。 and = and = 。 捕捉運(yùn)行很久的SQLselect username, sid, opname, round(sofar * 100 / totalwork, 0) || 39。%39。 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?！?查看數(shù)據(jù)表的參數(shù)信息SELECT partition_name, table_name, high_value, high_value_length, tablespace_name, pct_free, pct_used, ini_trans, max_trans, initial_extent, next_extent, min_extent, max_extent, pct_increase, FREELISTS, freelist_groups, LOGGING, BUFFER_POOL, num_rows, blocks, empty_blocks, avg_space, chain_t, avg_row_len, sample_size, last_analyzed FROM WHERE table_owner = 39。HS_HIS39。 AND table_name = 39。HISBANKTRADE39。WHERE table_name = :tname AND table_owner = :towner ORDER BY partition_position,partition_name?！　?查看還沒提交的事務(wù)select * from v$locked_object。select * from v$transaction。 查找object為哪些進(jìn)程所用select , , serial_num, user_name, object_type, os_user_name, , object_name, decode(sign(48 mand), 1, to_char(mand), 39。Action Code 39。 || to_char(mand)) action, oracle_process, terminal, program, session_status from v$session s, v$access a, v$process p where = and = 39。USER39。 and = and = 39。FUNDREAL39。 order by , 。 查看catched objectSELECT owner, name, db_link, namespace, type, sharable_mem, loads, executions, locks, pins, kept FROM v$db_object_cache where owner LIKE 39。HS_%39。 查看V$SQLAREASELECT SQL_TEXT, SHARABLE_MEM, PERSISTENT_MEM, RUNTIME_MEM, SORTS, VERSION_COUNT, LOADED_VERSIONS, OPEN_VERSIONS, USERS_OPENING, EXECUTIONS, USERS_EXECUTING, LOADS, FIRST_LOAD_TIME, INVALIDATIONS, PARSE_CALLS, DISK_READS, BUFFER_GETS, ROWS_PROCESSED FROM V$SQLAREA。 有關(guān)connection的相關(guān)信息l 查看有哪些用戶連接select , serial_num, os_user_name, decode(sign(48 mand), 1, to_char(mand), 39。Action Code 39。 || to_char(mand)) action, oracle_process, status session_status, terminal, program, user_name, activity_meter from v$session s, v$process p where = and = 39。USER39。 order by , 。l 2)select , , , from v$statname n, v$sesstat v where = amp。sid and = order by , 。l 3)根據(jù)sid查看對(duì)應(yīng)連接正在運(yùn)行的sqlselect /*+ PUSH_SUBQ */ mand_type, sql_text, sharable_mem, persistent_mem, runtime_mem, sorts, version_count, loaded_versions, open_versions, users_opening, executions, users_executing, loads, first_load_time, invalidations, parse_calls, disk_reads, buffer_gets, rows_processed, sysdate start_time, sysdate finish_time, 39。39。 || address sql_address, 39。N39。 status from v$sqlarea where address = (select sql_address from v$session where sid = amp。sid)。8. 備份 數(shù)據(jù)邏輯備份 exp將數(shù)據(jù)庫(kù)內(nèi)的各對(duì)象以二進(jìn)制方式下載成dmp文件，方便數(shù)據(jù)遷移。buffer：下載數(shù)據(jù)緩沖區(qū)，以字節(jié)為單位，缺省依賴操作系統(tǒng)consistent：下載期間所涉及的數(shù)據(jù)保持read only，缺省為ndirect：使用直通方式 ，缺省為nfeeback：顯示處理記錄條數(shù)，缺省為0，即不顯示file：輸出文件，filesize：輸出文件大小，缺省為操作系統(tǒng)最大值indexes：是否下載索引，缺省為n，這是指索引的定義而非數(shù)據(jù)log：log文件，缺省為無，在標(biāo)準(zhǔn)輸出顯示owner：指明下載的用戶名query：選擇記錄的一個(gè)子集rows：是否下載表記錄tables：輸出的表名列表216。 下載整個(gè)實(shí)例exp hs_user/handsome file= log= full=y consistent=y direct=yuser應(yīng)具有dba權(quán)限216。 下載某個(gè)用戶所有對(duì)象exp hs_user/handsome file= log= owner=dbuser buffer=409