oracle DBA 常用監(jiān)控腳本

1、oracle DBA 常用監(jiān)控腳本一、數(shù)據(jù)庫構(gòu)架體系1、表空間的監(jiān)控是一個(gè)重要的任務(wù),我們必須時(shí)刻關(guān)心表空間的設(shè)置,是否滿足現(xiàn)在應(yīng)用的需求,以下的語句可以查詢到表空間的詳細(xì)信息SELECT TABLESPACE_NAME,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS,PCT_INCREASE,MIN_EXTLEN,STATUS,CONTENTS,LOGGING,ALLOCATION_TYPE, - Remove these columns if runningFROM DBA_TABLESPACESORDER BY TABLESPACE_

2、NAME;2、對于某些數(shù)據(jù)文件沒有設(shè)置為自動(dòng)擴(kuò)展的表空間來說,如果表空間滿了,就將意味著數(shù)據(jù)庫可能會(huì)因?yàn)闆]有空間而停止下來。監(jiān)控表空間,最主要的就是監(jiān)控剩余空間的大小或者是使用率。以下是監(jiān)控表空間使用率與剩余空間大小的語句SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M",BLOCKSSUM_BLOCKS,SPACE-NVL(FREE_SPACE,0 "USED_SPACE(M",ROUND(1-NVL(FREE_SPACE,0/SPACE*100,2 "USED_RATE(%",FREE_SPA

3、CE"FREE_SPACE(M"FROM(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES/(1024*1024,2 SPACE,SUM(BLOCKS BLOCKSFROM DBA_DATA_FILESGROUP BY TABLESPACE_NAME D,(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES/(1024*1024,2 FREE_SPACEFROM DBA_FREE_SPACEGROUP BY TABLESPACE_NAME FWHERE D.TABLESPACE_NAME = F.TABLESPACE_N

4、AME(+UNION ALL -if have tempfileSELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M",BLOCKS SUM_BLOCKS,USED_SPACE "USED_SPACE(M",ROUND(NVL(USED_SPACE,0/SPACE*100,2"USED_RATE(%",NVL(FREE_SPACE,0 "FREE_SPACE(M"FROM(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES/(1024*1024,2 SPA

5、CE,SUM(BLOCKSBLOCKSFROM DBA_TEMP_FILESGROUP BY TABLESPACE_NAME D,(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED/(1024*1024,2 USED_SPACE, ROUND(SUM(BYTES_FREE/(1024*1024,2 FREE_SPACEFROM V$TEMP_SPACE_HEADERGROUP BY TABLESPACE_NAME FWHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+3、除了監(jiān)控表空間的剩余空間,有時(shí)候我們也有必要了解

6、一下該表空間是否具有自動(dòng)擴(kuò)展空間的能力,雖然我們建議在生產(chǎn)系統(tǒng)中預(yù)先分配空間。以下語句將完成這一功能SELECT T.TABLESPACE_NAME,D.FILE_NAME,D.AUTOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATUSFROM DBA_TABLESPACES T,DBA_DATA_FILES DWHERE T. TABLESPACE_NAME =D. TABLESPACE_NAMEORDER BY TABLESPACE_NAME,FILE_NAME4、我相信使用字典管理的表空間的也不少吧,因?yàn)樽值涔芾淼谋砜臻g中,每個(gè)表的下一個(gè)區(qū)間的大小是不可以預(yù)料的

7、,所以我們必須監(jiān)控那些表在字典管理的表空間中的下一個(gè)區(qū)間的分配將會(huì)引起性能問題或由于是非擴(kuò)展的表空間而導(dǎo)致系統(tǒng)停止。以下語句檢查那些表的擴(kuò)展將引起表空間的擴(kuò)展。SELECT A.OWNER,A.TABLE_NAME,A.NEXT_EXTENT,A.TABLESPACE_NAMEFROM ALL_TABLES A,(SELECT TABLESPACE_NAME, MAX(BYTES BIG_CHUNKFROM DBA_FREE_SPACEGROUP BY TABLESPACE_NAME FWHERE F.TABLESPACE_NAME = A.TABLESPACE_NAMEAND A.NEXT_

8、EXTENT > F.BIG_CHUNK5、段的占用空間與區(qū)間數(shù)也是很需要注意的一個(gè)問題,如果一個(gè)段的占用空間太大,或者跨越太多的區(qū)間(在字典管理的表空間中,將有嚴(yán)重的性能影響,如果段沒有可以再分配的區(qū)間,將導(dǎo)致數(shù)據(jù)庫錯(cuò)誤。所以,段的大小與區(qū)間監(jiān)控也是一個(gè)很重要的工作SELECT S.OWNER,S.SEGMENT_NAME,S.SEGMENT_TYPE,S.PARTITION_NAME,ROUND(BYTES/(1024*1024,2 "USED_SPACE(M",EXTENTS USED_EXTENTS,S.MAX_EXTENTS,S.BLOCKS ALLOCAT

9、ED_BLOCKS,S.BLOCKS USED_BOLCKS,S.PCT_INCREASE,S.NEXT_EXTENT/1024 "NEXT_EXTENT(K" FROM DBA_SEGMENTS SWHERE S.OWNER NOT IN ('SYS','SYSTEM'ORDER BY Used_Extents DESC6、對象的空間分配與空間利用情況,除了從各個(gè)方面的分析,如分析表,查詢r(jià)owid等方法外,其實(shí)oracle 提供了一個(gè)查詢空間的包dbms_space,如果我們稍封裝一下,將是非常好用的一個(gè)東西。CREATE OR REPL

10、ACE PROCEDURE show_space(p_segname in varchar2,p_type in varchar2 default 'TABLE' ,p_owner in varchar2 default userASv_segname varchar2(100;v_type varchar2(10;l_free_blks number;l_total_blocks number;l_total_bytes number;l_unused_blocks number;l_unused_bytes number;l_LastUsedExtFileId number

11、;l_LastUsedExtBlockId number;l_LAST_USED_BLOCK number;PROCEDURE p( p_label in varchar2, p_num in number ISBEGINdbms_output.put_line( rpad(p_label,40,'.'| p_num ;END;BEGINv_segname := upper(p_segname;v_type := p_type;if (p_type = 'i' or p_type = 'I' thenv_type := 'INDEX

12、9;end if;if (p_type = 't' or p_type = 'T' thenv_type := 'TABLE'end if;if (p_type = 'c' or p_type = 'C' thenv_type := 'CLUSTER'end if;-以下部分不能用于ASSMdbms_space.free_blocks( segment_owner => p_owner,segment_name => v_segname,segment_type => v_type

13、,freelist_group_id => 0,free_blks => l_free_blks ;-以上部分不能用于ASSMdbms_space.unused_space( segment_owner => p_owner,segment_name => v_segname,segment_type => v_type,total_blocks => l_total_blocks,total_bytes => l_total_bytes,unused_blocks => l_unused_blocks,unused_bytes => l_

14、unused_bytes,LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId, LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId, LAST_USED_BLOCK => l_LAST_USED_BLOCK ;-顯示結(jié)果p( 'Free Blocks', l_free_blks ;p( 'Total Blocks', l_total_blocks ;p( 'Total Bytes', l_total_bytes ;p( 'Unus

15、ed Blocks', l_unused_blocks ;p( 'Unused Bytes', l_unused_bytes ;p( 'Last Used Ext FileId', l_LastUsedExtFileId ;p( 'Last Used Ext BlockId', l_LastUsedExtBlockId ;p( 'Last Used Block', l_LAST_USED_BLOCK ;END;執(zhí)行結(jié)果將如下所示SQL> set serveroutput on;SQL> exec show_sp

16、ace('test'Free Blocks (1Total Blocks (8Total Bytes (65536Unused Blocks (6Unused Bytes (49152Last Used Ext FileId (1Last Used Ext BlockId (48521Last Used Block (2PL/SQL procedure successfully completed8、數(shù)據(jù)庫的索引如果有比較頻繁的Delete操作,將可能導(dǎo)致索引產(chǎn)生很多碎片,所以,在有的時(shí)候,需要對所有的索引重新REBUILD,以便合并索引塊,減少碎片,提高查詢速度。SQL>

17、; set heading offSQL> set feedback offSQL> spool d:index.sqlSQL> SELECT 'alter index ' | index_name | ' rebuild '|'tablespace INDEXES storage(initial 256K next 256K pctincrease 0;'FROM all_indexesWHERE ( tablespace_name != 'INDEXES'OR next_extent != ( 256 * 1

18、024 AND wner = USERSQL>spool off這個(gè)時(shí)候,我們打開spool出來的文件,就可以直接運(yùn)行了。9、表的主鍵是必要的,沒有主鍵的表可以說是不符合設(shè)計(jì)規(guī)范的,所以我們需要監(jiān)控表是否有主鍵SELECT table_nameFROM all_tablesWHERE wner = USERMINUSSELECT table_nameFROM all_constraintsWHERE wner = USERAND constraint_type = 'P'二、性能監(jiān)控1、數(shù)據(jù)緩沖區(qū)的命中率已經(jīng)不是性能調(diào)整中的主要問題了,但是,過低的命中率肯定是不可以的,在

19、任何情況下,我們必須保證有一個(gè)大的data buffer和一個(gè)高的命中率。這個(gè)語句可以獲得整體的數(shù)據(jù)緩沖命中率,越高越好SELECT a.VALUE + b.VALUE logical_reads,c.VALUE phys_reads,round(100*(1-c.value/(a.value+b.value,4 hit_ratioFROM v$sysstat a,v$sysstat b,v$sysstat cWHERE a.NAME='db block gets'AND b.NAME='consistent gets'AND c.NAME='physi

20、cal reads'2、庫緩沖說明了SQL語句的重載率,當(dāng)然,一個(gè)SQL語句應(yīng)當(dāng)被執(zhí)行的越多越好,如果重載率比較高,就考慮增加共享池大小或者是提高Bind變量的使用以下語句查詢了Sql語句的重載率,越低越好SELECT SUM(pins total_pins,SUM(reloads total_reloads,SUM(reloads/SUM(pins*100 libcache_reload_ratioFROM v$librarycache3、用戶鎖,數(shù)據(jù)庫的鎖有的時(shí)候是比較耗費(fèi)資源的,特別是發(fā)生鎖等待的時(shí)候,我們必須找到發(fā)生等待的鎖,有可能的話,殺掉該進(jìn)程。這個(gè)語句將查找到數(shù)據(jù)庫中所有

21、的DML語句產(chǎn)生的鎖,還可以發(fā)現(xiàn),任何DML語句其實(shí)產(chǎn)生了兩個(gè)鎖,一個(gè)是表鎖,一個(gè)是行鎖?？梢酝ㄟ^alter system kill session sid,serial#來殺掉會(huì)話SELECT /*+ rule */ s.username,decode(l.type,'TM','TABLE LOCK','TX','ROW LOCK',NULL LOCK_LEVEL,o.owner,o.object_name,o.object_type,s.sid,s.serial#,s.terminal,s.machine,gram,

22、s.osuserFROM v$session s,v$lock l,dba_objects oWHERE l.sid = s.sidAND l.id1 = o.object_id(+AND s.username is NOT NULL4、鎖與等待,如果發(fā)生了鎖等待,我們可能更想知道是誰鎖了表而引起誰的等待以下的語句可以查詢到誰鎖了表,而誰在等待。SELECT /*+ rule */ lpad(' ',decode(l.xidusn ,0,3,0|l.oracle_username User_name,o.owner,o.object_name,o.object_type,s.s

23、id,s.serial#FROM v$locked_object l,dba_objects o,v$session sWHERE l.object_id=o.object_idAND l.session_id=s.sidORDER BY o.object_id,xidusn DESC以上查詢結(jié)果是一個(gè)樹狀結(jié)構(gòu),如果有子節(jié)點(diǎn),則表示有等待發(fā)生。如果想知道鎖用了哪個(gè)回滾段,還可以關(guān)聯(lián)到V$rollname,其中xidusn就是回滾段的USN5、如果發(fā)生了事務(wù)或鎖,想知道哪些回滾段正在被使用嗎?其實(shí)通過事務(wù)表,我們可以詳細(xì)的查詢到事務(wù)與回滾段之間的關(guān)系。同時(shí),如果關(guān)聯(lián)會(huì)話表,我們則可以知道是哪個(gè)會(huì)

24、話發(fā)動(dòng)了這個(gè)事務(wù)。SELECT s.USERNAME,s.SID,s.SERIAL#,t.UBAFIL "UBA filenum",t.UBABLK "UBA Block number",t.USED_UBLK "Number os undo Blocks Used",t.START_TIME,t.STATUS,t.START_SCNB,t.XIDUSN RollID,r.NAME RollNameFROM v$session s,v$transaction t,v$rollname rWHERE s.SADDR=t.SES_ADDR

25、AND t.XIDUSN=r.usn7、如果利用會(huì)話跟蹤或者是想查看某個(gè)會(huì)話的跟蹤文件,那么查詢到OS上的進(jìn)程或線程號(hào)是非常重要的,因?yàn)槲募牧蠲?就包含這個(gè)信息,以下的語句可以查詢到進(jìn)程或線程號(hào),由此就可以找到對應(yīng)的文件。SELECT p1.value|''|p2.value|'_ora_'|p.spid filenameFROMv$process p,v$session s,v$parameter p1,v$parameter p2WHERE = 'user_dump_dest'AND = 'db_n

26、ame'AND p.addr = s.paddrAND s.audsid = USERENV ('SESSIONID'8、在ORACLE 9i中,可以監(jiān)控索引的使用,如果沒有使用到的索引,完全可以刪除掉,減少DML操作時(shí)的操作。以下就是開始索引監(jiān)控與停止索引監(jiān)控的腳本set heading offset echo offset feedback offset pages 10000spool start_index_monitor.sqlSELECT 'alter index '|owner|'.'|index_name|' mo

27、nitoring usage;'FROM dba_indexesWHERE wner = USER;spool offset heading onset echo onset feedback on-set heading offset echo offset feedback offset pages 10000spool stop_index_monitor.sqlSELECT 'alter index '|owner|'.'|index_name|' nomonitoring usage;'FROM dba_indexesWHERE

28、 wner = USER;spool offset heading onset echo onset feedback on如果需要監(jiān)控更多的用戶,可以將owner=User改寫成別的監(jiān)控結(jié)果在視圖v$object_usage中查詢感謝fenng,他提供了一個(gè)更新版的show_space腳本CREATE OR REPLACE PROCEDURE show_space( p_segname IN VARCHAR2,p_owner IN VARCHAR2 DEFAULT USER,p_type IN VARCHAR2 DEFAULT 'TABLE',p_partition IN V

29、ARCHAR2 DEFAULT NULL - This procedure uses AUTHID CURRENT USER so it can query DBA_*- views using privileges from a ROLE and so it can be installed- once per database, instead of once per user who wanted to use it. AUTHID CURRENT_USERasl_free_blks number;l_total_blocks number;l_total_bytes number;l_

30、unused_blocks number;l_unused_bytes number;l_LastUsedExtFileId number;l_LastUsedExtBlockId number;l_LAST_USED_BLOCK number;l_segment_space_mgmt varchar2(255;l_unformatted_blocks number;l_unformatted_bytes number;l_fs1_blocks number; l_fs1_bytes number;l_fs2_blocks number; l_fs2_bytes number;l_fs3_bl

31、ocks number; l_fs3_bytes number;l_fs4_blocks number; l_fs4_bytes number;l_full_blocks number; l_full_bytes number;- Inline procedure to print out numbers nicely formatted- with a simple label.PROCEDURE p( p_label in varchar2, p_num in number ISBEGINdbms_output.put_line( rpad(p_label,40,'.' |

32、to_char(p_num,'999,999,999,999' ;END;BEGIN- This query is executed dynamically in order to allow this procedure- to be created by a user who has access to DBA_SEGMENTS/TABLESPACES - via a role as is customary.- NOTE: at runtime, the invoker MUST have access to these two- views!- This query d

33、etermines if the object is an ASSM object or not.BEGINEXECUTE IMMEDIATE'select ts.segment_space_managementFROM dba_segments seg, dba_tablespaces tsWHERE seg.segment_name = :p_segnameAND (:p_partition is null orseg.partition_name = :p_partitionAND seg.owner = :p_ownerAND seg.tablespace_name = ts.

34、tablespace_name'INTO l_segment_space_mgmtUSING p_segname, p_partition, p_partition, p_owner; EXCEPTIONWHEN too_many_rows THENdbms_output.put_line( 'This must be a partitioned table, use p_partition => ' RETURN;END;- If the object is in an ASSM tablespace, we must use this API- call to

35、 get space information; else we use the FREE_BLOCKS- API for the user managed segments.IF l_segment_space_mgmt = 'AUTO'THENdbms_space.space_usage( p_owner, p_segname, p_type, l_unformatted_blocks,l_unformatted_bytes, l_fs1_blocks, l_fs1_bytes,l_fs2_blocks, l_fs2_bytes, l_fs3_blocks, l_fs3_by

36、tes,l_fs4_blocks, l_fs4_bytes, l_full_blocks, l_full_bytes, p_partition;p( 'Unformatted Blocks ', l_unformatted_blocks ;p( 'FS1 Blocks (0-25 ', l_fs1_blocks ;p( 'FS2 Blocks (25-50 ', l_fs2_blocks ;p( 'FS3 Blocks (50-75 ', l_fs3_blocks ;p( 'FS4 Blocks (75-100',

37、 l_fs4_blocks ;p( 'Full Blocks ', l_full_blocks ;ELSEdbms_space.free_blocks(segment_owner => p_owner,segment_name => p_segname,segment_type => p_type,freelist_group_id => 0,free_blks => l_free_blks;p( 'Free Blocks', l_free_blks ;END IF;- And then the unused space API c

38、all to get the rest of the- information.dbms_space.unused_space( segment_owner => p_owner,segment_name => p_segname,segment_type => p_type,partition_name => p_partition,total_blocks => l_total_blocks,total_bytes => l_total_bytes,unused_blocks => l_unused_blocks,unused_bytes =>

39、; l_unused_bytes,LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,LAST_USED_BLOCK => l_LAST_USED_BLOCK ;p( 'Total Blocks', l_total_blocks ;p( 'Total Bytes', l_total_bytes ;p( 'Total MBytes', trunc(l_total_bytes/1024/10

40、24 ;p( 'Unused Blocks', l_unused_blocks ;p( 'Unused Bytes', l_unused_bytes ;p( 'Last Used Ext FileId', l_LastUsedExtFileId ;p( 'Last Used Ext BlockId', l_LastUsedExtBlockId ;p( 'Last Used Block', l_LAST_USED_BLOCK ;END;隱含參數(shù):select a.ksppinm "parameter &qu

41、ot;, a.ksppdesc "descriptoin "from x$ksppi a,x$ksppcv b,x$ksppsv cwhere a.indx=b.indx and a.indx=c.indx and a.ksppinm like '/_%' escape '/' Check OS process id from Oracle sidselect spid from v$processwhere addr in ( select paddr from v$session where sid=$sid Check Oracle s

42、id from OS process idselect sid from v$sessionwhere paddr in ( select addr from v$process where spid=$pid Check current SQL in a sessionselect SQL_TEXT from V$SQLTEXTwhere HASH_VALUE =( select SQL_HASH_VALUE from v$sessionwhere sid = &sidorder by PIECEChecking v$session_waitselect * from v$sessi

43、on_waitwhere event not like 'rdbms%'and event not like 'SQL*N%'and event not like '%timer'Dictionary Cache HitsSELECT sum(getmisses/sum(gets FROM v$rowcache;/*It should be < 15%, otherwise Add share_pool_size*/Check DB object name from file id and block#select owner,segmen

44、t_name,segment_typefrom dba_extentswhere file_id = $fno and &dno between block_id and block_id + blocks 1 #尋找hot blockselect /*+ ordered */e.owner |'.'| e.segment_name segment_name,e.extent_id extent#,x.dbablk - e.block_id + 1 block#,x.tch,l.child#fromsys.v$latch_children l,sys.x$bh x,sy

45、s.dba_extents = 'cache buffers chains' andl.sleeps > &sleep_count andx.hladdr = l.addr ande.file_id = x.file# andx.dbablk between e.block_id and e.block_id + e.blocks - 1;#找出每個(gè)文件上的等待事件select ,kf.count from v$datafile df,x$kcbfwait kf where (kf.indx+1=df.file#;#找出引起

46、等待事件的SQL語句.select sql_text from v$sqlarea a,v$session b,v$session_wait c wherea.address=b.sql_address and b.sid=c.sid and c.event=$ll#監(jiān)控共享池中哪個(gè)對象引起了大的內(nèi)存分配SELECT * FROM X$KSMLRU WHERE ksmlrsiz > 0;判斷你是從pfile啟動(dòng)還是spfile啟動(dòng)的簡單方法!判斷你是從pfile啟動(dòng)還是spfile啟動(dòng)的簡單方法!select decode(count(*, 1, 'spfile', &#

47、39;pfile' from v$spparameterwhere rownum=1and isspecified='TRUE'/DECODE-spfileORACLE常用技巧和腳本ORACLE常用技巧和腳本1.如何查看ORACLE的隱含參數(shù)?ORACLE的顯式參數(shù),除了在INIT.ORA文件中定義的外,在svrmgrl中用"show parameter *",可以顯示。但ORACLE還有一些參數(shù)是以“_”,開頭的。如我們非常熟悉的“_offline_rollback_segments”等。這些參數(shù)可在sys.x$ksppi表中查出。語句:“sele

48、ct ksppinm from x$ksppi where substr(ksppinm,1,1='_' ”2.如何查看安裝了哪些ORACLE組件?進(jìn)入$ORACLE_HOME/orainst/,運(yùn)行./inspdver,顯示安裝組件和版本號(hào)。3.如何查看ORACLE所占用共享內(nèi)存的大小?可用UNIX命令“ipcs”查看共享內(nèi)存的起始地址、信號(hào)量、消息隊(duì)列。在svrmgrl下,用“oradebug ipc”,可看出ORACLE占用共享內(nèi)存的分段和大小。example:SVRMGR> oradebug ipc- Shared memory -Seg Id Address S

49、ize1153 7fe000 7841154 800000 4194304001155 19800000 671088644.如何查看當(dāng)前SQL*PLUS用戶的sid和serial#?在SQL*PLUS下,運(yùn)行:“select sid, serial#, status from v$sessionwhere audsid=userenv('sessionid'”5.如何查看當(dāng)前數(shù)據(jù)庫的字符集?在SQL*PLUS下,運(yùn)行:“select userenv('language' from dual;”或:“select userenv('lang' f

50、rom dual; ”6.如何查看數(shù)據(jù)庫中某用戶,正在運(yùn)行什么SQL語句?根據(jù)MACHINE、USERNAME或SID、SERIAL#,連接表V$SESSION和V$SQLTEXT,可查出。SQL*PLUS語句:“SELECT SQL_TEXT FROM V$SQL_TEXT T, V$SESSION S WHERET.ADDRESS=S.SQL_ADDRESSAND T.HASH_VALUE=S.SQL_HASH_VALUEAND S.MACHINE='XXXXX' OR USERNAME='XXXXX' - 查看某主機(jī)名,或用戶名/”7.如何刪除表中的重復(fù)記

51、錄?例句:DELETEFROM table_name aWHERE rowid > ( SELECT min(rowidFROM table_name bWHERE b.pk_column_1 = a.pk_column_1and b.pk_column_2 = a.pk_column_2 ;8.手工臨時(shí)強(qiáng)制改變服務(wù)器字符集以sys或system登錄系統(tǒng),sql*plus運(yùn)行:“create database character set us7ascii;".有以下錯(cuò)誤提示:* create database character set US7ASCIIERROR at lin

52、e 1:ORA-01031: insufficient privileges實(shí)際上,看v$nls_parameters,字符集已更改成功。但重啟數(shù)據(jù)庫后,數(shù)據(jù)庫字符集又變回原來的了。該命令可用于臨時(shí)的不同字符集服務(wù)器之間數(shù)據(jù)倒換之用。9.怎樣查詢每個(gè)instance分配的PCM鎖的數(shù)目用以下命令:select count(* "Number of hashed PCM locks" from v$lock_element wherebitand(flags,4<>0/select count(* "Number of fine grain PCM lo

53、cks" from v$lock_elementwhere bitand(flags,4=0/10. 怎么判斷當(dāng)前正在使用何種SQL優(yōu)化方式?用explain plan產(chǎn)生EXPLAIN PLAN,檢查PLAN_TABLE中ID=0的POSITION列的值。e.g.select decode(nvl(position,-1,-1,'RBO',1,'CBO' from plan_table where id=0/11. 做EXPORT時(shí),能否將DUMP文件分成多個(gè)?ORACLE8I中EXP增加了一個(gè)參數(shù)FILESIZE,可將一個(gè)文件分成多個(gè):EXP SC

54、OTT/TIGER FILE=(ORDER_1.DMP,ORDER_2.DMP,ORDER_3.DMP FILESIZE=1G TABLES=ORDER;其他版本的ORACLE在UNIX下可利用管道和split分割:mknod pipe psplit -b 2048m pipe order & #將文件分割成,每個(gè)2GB大小的,以order為前綴的文件:#orderaa,orderab,orderac,. 并將該進(jìn)程放在后臺(tái)。EXP SCOTT/TIGER FILE=pipe tables=order戶如何有效地利用數(shù)據(jù)字典用戶如何有效地利用數(shù)據(jù)字典ORACLE的數(shù)據(jù)字典是數(shù)據(jù)庫的重要

55、組成部分之一,它隨著數(shù)據(jù)庫的產(chǎn)生而產(chǎn)生, 隨著數(shù)據(jù)庫的變化而變化,體現(xiàn)為sys用戶下的一些表和視圖。數(shù)據(jù)字典名稱是大寫的英文字符。數(shù)據(jù)字典里存有用戶信息、用戶的權(quán)限信息、所有數(shù)據(jù)對象信息、表的約束條件、統(tǒng)計(jì)分析數(shù)據(jù)庫的視圖等。我們不能手工修改數(shù)據(jù)字典里的信息。很多時(shí)候,一般的ORACLE用戶不知道如何有效地利用它。dictionary全部數(shù)據(jù)字典表的名稱和解釋,它有一個(gè)同義詞dictdict_column全部數(shù)據(jù)字典表里字段名稱和解釋如果我們想查詢跟索引有關(guān)的數(shù)據(jù)字典時(shí),可以用下面這條SQL語句:SQL>select * from dictionary where instr(comme

56、nts,'index'>0;如果我們想知道user_indexes表各字段名稱的詳細(xì)含義,可以用下面這條SQL語句:SQL>select column_name,comments from dict_columns wheretable_name='USER_INDEXES'依此類推,就可以輕松知道數(shù)據(jù)字典的詳細(xì)名稱和解釋,不用查看ORACLE的其它文檔資料了。下面按類別列出一些ORACLE用戶常用數(shù)據(jù)字典的查詢使用方法。一、用戶查看當(dāng)前用戶的缺省表空間SQL>select username,default_tablespace from us

57、er_users;查看當(dāng)前用戶的角色SQL>select * from user_role_privs;查看當(dāng)前用戶的系統(tǒng)權(quán)限和表級權(quán)限SQL>select * from user_sys_privs;SQL>select * from user_tab_privs;二、表查看用戶下所有的表SQL>select * from user_tables;查看名稱包含log字符的表SQL>select object_name,object_id from user_objectswhere instr(object_name,'LOG'>0;查看某

58、表的創(chuàng)建時(shí)間SQL>select object_name,created from user_objects whereobject_name=upper('&table_name'查看某表的大小SQL>select sum(bytes/(1024*1024 as "size(M" from user_segmentswhere segment_name=upper('&table_name'查看放在ORACLE的內(nèi)存區(qū)里的表SQL>select table_name,cache from user_tabl

59、es where instr(cache,'Y'>0;三、索引查看索引個(gè)數(shù)和類別SQL>select index_name,index_type,table_name from user_indexes order by table_name;查看索引被索引的字段SQL>select * from user_ind_columns where index_name=upper('&index_name'查看索引的大小SQL>select sum(bytes/(1024*1024 as "size(M" from

60、 user_segmentswhere segment_name=upper('&index_name'四、序列號(hào)查看序列號(hào),last_number是當(dāng)前值SQL>select * from user_sequences;五、視圖查看視圖的名稱SQL>select view_name from user_views;查看創(chuàng)建視圖的select語句SQL>set view_name,text_length from user_views;SQL>set long 2000; 說明:可以根據(jù)視圖的text_length值設(shè)定set long 的大小SQL>select text from user_views where view_name=upper('&view_name'六、同義詞查看同義