Oracle數(shù)據(jù)庫常用操作手冊

1、ORACLE 數(shù)據(jù)庫常用操作手冊SHELL篇RAC維護命令RAC啟動與停止啟動單一節(jié)點數(shù)據(jù)庫srvctl start nodeapps -n srvctl start asm -n srvctl start instance -d -i emctl start dbconsole停止單一節(jié)點數(shù)據(jù)庫emctl stop dbconsolesrvctl stop instance -d -i srvctl stop asm -n srvctl stop nodeapps -n 開啟關閉監(jiān)聽lsnrctl stoplsnrctl start或srvctl stop listener -n srvct

2、l start listener -n 停止啟動服務srvctl stop service -d gzedusrvctl start service -d gzedu停止啟動全局數(shù)據(jù)庫srvctl stop database -d basesrvctl start database -d base關閉整個群集數(shù)據(jù)庫root權限crs_stop -all 或crsctl stop crs啟動整個群集數(shù)據(jù)庫root權限crs_start all或crsctl start crs停止群集守護進程/etc/init.d/init.crs stop在Oracle環(huán)境中,當RAC不同節(jié)點間的時間差超過30

3、秒時,會導致oracle數(shù)據(jù)庫反復重啟或者狀態(tài)異常.重新啟動整個數(shù)據(jù)庫群集Usage: crs_stop resource_name . -f -q attrib=value . crs_stop -c cluster_member . -q attrib=value . crs_stop -all -qcrs_stop -all crs_start -allcrs_stop ora.oradb3.gsdRAC配置修改修改VIP地址oifcfg getif -globaloifcfg setif -global eth0/:publicoifcfg iflist/etc/init.d/init

4、.crs stopsrvctl modify nodeapps -n rac1 -A 91/eth0RAC數(shù)據(jù)庫檢查查看群集數(shù)據(jù)庫狀態(tài)srvctl status database -d crs_stat -t檢查單一節(jié)點狀態(tài)srvctl status nodeapps -n 所有實例和服務的狀態(tài)srvctl status database -d racdb單個實例的狀態(tài)srvctl status instance -d racdb -i racdb2在數(shù)據(jù)庫全局命名服務的狀態(tài)srvctl status service -d racdb -s racdb_taf特定節(jié)點上節(jié)點應用程序的狀態(tài)srv

5、ctl status nodeapps -n linux1ASM 實例的狀態(tài)srvctl status asm -n linux1列出配置的所有數(shù)據(jù)庫srvctl config database顯示 RAC 數(shù)據(jù)庫的配置srvctl config database -d racdb顯示指定集群數(shù)據(jù)庫的所有服務srvctl config service -d racdb顯示節(jié)點應用程序的配置 （VIP、GSD、ONS、監(jiān)聽器）srvctl config nodeapps -n linux1 -a -g -s -l顯示 ASM 實例的配置srvctl config asm -n linux1查詢v

6、ote的路徑磁盤信息crsctl query css votedisk查看節(jié)點狀態(tài)srvctl status nodeapps -n oradb3srvctl status instance -d gzedu -i gzedu1檢查crs健康情況crsctl check crscrsctl start resourcesgsdctl stopgsdctl status數(shù)據(jù)庫維護命令數(shù)據(jù)庫的啟動與停止正常啟動單機數(shù)據(jù)庫su - oraclelsnrctl startsqlplus /nologconnect /as sysdbastartupsqlplus /nolog connect /as

7、sysdba startup nomount alter database mount alter database open startup的幾個選項 nomount只啟動實例，不安裝和打開數(shù)據(jù)庫 mount啟動實例、安裝數(shù)據(jù)庫但不打開數(shù)據(jù)庫，此參數(shù)用于修改數(shù)據(jù)庫的運行模式或進行數(shù)據(jù)庫恢復，如更改數(shù)據(jù)庫的為歸檔模式:alter database archivelog; alter database open; archive log listread only只讀屬性 read write讀寫屬性 open打開數(shù)據(jù)庫 restrict表示數(shù)據(jù)庫以受限制方式打開 pfile選項，用于參數(shù)文件破

8、壞，或者另外指定參數(shù)文件，如：startup pfile=/u01/user/proddb.ora force以強制方式啟動數(shù)據(jù)庫，可以用于重新啟動數(shù)據(jù)庫 以上參數(shù)可以聯(lián)合使用啟動管理控制臺$emctl start dbconsole啟動iSQLPLUS$isqlplusctl start停止單機數(shù)據(jù)庫sqlplus /nologconnect /as sysdbashutdown immediateshutdown有四個參數(shù) normal 需要等待所有的用戶斷開連接immediate 等待用戶完成當前的語句 transactional 等待用戶完成當前的事務 abort 不做任何等待，直接關

9、閉數(shù)據(jù)數(shù)據(jù)的導入與導出數(shù)據(jù)導入imp fromuser=tutor1 touser=tutor1 file=gzdec-tutor1-060221.dmp;imp open_teacher/teacher987 file=db4_TJXJY_070723.dmp fromuser=tjxjy touser=open_teacher tables=(bbs,rbbs)imp open_netcsou/netcsou987 file=db4_netcsou_070713.dmp fromuser=netcsou touser=open_netcsou grants=n數(shù)據(jù)泵導入impdp dire

10、ctory=backup_dir dumpfile=base-base_msgz-091111.dmp schemas=base_msgz include=TABLE:in(RAC_USER_ROLE) logfile=base_msgz_export.logimpdp tt_gd_lms/888888 directory=expdp_dir dumpfile=gzedu-gd_lms-090810.dmp schemas=gd_lms remap_schema=gd_lms:tt_gd_lms remap_tablespace=gd_lms:tt_gd_lmsimpdp directory=

11、backup_dir dumpfile=base-base_msgz-091111.dmp schemas=base_msgz parallel=4 table_exists_action=replace tables=RAC_USER_ROLE impdp directory=backup_dir dumpfile=base-base_msgz-091111.dmp schemas=base_msgz include=TABLE:in(RAC_USER_ROLE) logfile=base_msgz_export.logimpdp fvdb/fvdb directory=admin_dir

12、dumpfile=FVDB.DMP schemas=fvdb remap_tablespace=fvdb:ts_fvdb logfile=fvdb_export.log數(shù)據(jù)導出備份整個數(shù)據(jù)庫exp system/manager inctype=complete file=gzdec-tutorl-070428.dmp增量型”增量導出exp system/manager inctype=incremental file=gzdec-tutorl-070428.dmp累計型”增量導出exp system/manager inctype=cumulative file=gzdec-tutorl-07

13、0428.dmp 導出一個完整數(shù)據(jù)庫exp system/manager file=bible_db log=dible_db full=y導出數(shù)據(jù)庫定義而不導出數(shù)據(jù)exp system/manager file=bible_db log=dible_db full=y rows=n導出一個或一組指定用戶所屬的全部表、索引和其他對象exp system/manager file=seapark log=seapark owner=seaparkexp system/manager file=seapark log=seapark owner=(seapark,amy,amyc,harold)e

14、xp system/fitness1388 file=gzedu_open-bayi_new.dmp owner=open_bayi數(shù)據(jù)泵導出在命令行中使用要注意使用轉(zhuǎn)意字符因為 ( )會被認為特殊字符expdp directory=test dumpfile=sfca09.dump logfile= sfca09.log schemas=sfcdata include=table:like SFCA%expdp directory=erp schemas=wfl content=data_only exclude=table:IN(WFS_TB_FCSMAIL,WFS_TB_WFMAS,WF

15、S_TB_WFDTL) dumpfile=wfl16.dump logfile=wfl16.loginclude/exclude 例子：include=table:”in(DB,TS)”或者include=table:”like %E%”或者include=function,package,procedure,table:”=EMP”或者exclude=SEQUENCE,TABLE:”IN (EMP,DEPT)”expdp schemas=base_digischool directory=backup_dir dumpfile=base-base_digischool-20091118.dm

16、p parallel=4 logfile=base_digischool_export.logLinux下常命令查殺所有oracle進程ps -ef|grep ora_|grep -v grep|awk print $2 |xargs kill -9顯示Oracle 的Unix 進程 ps -ef|grep ora_|grep -v grep 查看消耗CPU 時間最長的進程： ps -ef|grep oracle|sort +6|tail按照此列排序來獲得當前高CPU 占用的用戶。例如： ps auxgw|sort +2|tail 顯示Oracle 的活動連接用戶數(shù)量 ps -ef|grep

17、$ORACLE_SID|grep -v grep|grep -v ora_|wc -l AIX下常用命令顯示aix 中的服務器設備信息 在AIX 中也可以使用lsdev 命令來查看硬件設備信息。 lsdev -C 在AIX 下查看系統(tǒng)核心參數(shù) 我們需要使用lsattr 命令。例如： lsattr -El sys0 aix 下顯示內(nèi)存大?。?/usr/sbin/lsattr -E -l sys0 -a realmem 使用Aix 的svmon 工具 IBM AIX 提供一個叫做 svmon 的工具。這個工具顯示服務器上的所有內(nèi)存的使用情 況，包括頁交換和內(nèi)存使用。例如： svmonsvmon -

18、P pid顯示文件被進程誰占用fuser -u /dev/lv_index2_2gAIX下查看邏輯卷的大小dbfsize /dev/rlv_gzdec01_2gLinux 下看內(nèi)核限制參數(shù)ulimit -aAIX內(nèi)存優(yōu)化參數(shù)和命令集查看內(nèi)存參數(shù)vmo -a |grep strict_maxclient strict_maxclient = 1vmo -a |grep lru_file_repage lru_file_repage = 1vmo -a |grep minperm% minperm% = 10vmo -a |grep maxclient% maxclient% = 20vmo -a

19、 |grep maxperm% maxperm% = 20vmo -a |grep minfree minfree = 960vmo -a |grep maxfree maxfree = 1088vmo -a |grep strict_maxclientvmo -a |grep lru_file_repagevmo -a |grep minperm%vmo -a |grep maxclient%vmo -a |grep maxperm%vmo -a |grep minfreevmo -a |grep maxfreevmo -a |grep strict_maxpermoradb1上設置的優(yōu)化值

20、vmo -p -o v_pinshm=1vmo -p -o maxpin%=75oradb2上設置的優(yōu)化值vmo -p -o v_pinshm=1vmo -p -o maxpin%=75vmo -p -o minperm%=3vmo -p -o maxclient%=8vmo -p -o maxperm%=8vmo -p -o minperm%=5vmo -p -o maxperm%=90vmo -p -o maxclient%=90vmo -p -o minperm%=10 -o maxperm%=20 -o maxclient%=20 -o strict_maxperm=1 -o stri

21、ct_maxclient=1應用的需求，因此需要進行一些調(diào)整。AIX操作系統(tǒng)中，一般將內(nèi)存的使用分成兩個部分，一個部分用于應用程序運行使用，稱為計算內(nèi)存（Computational），另一部分用于文件緩存，稱為文件緩存（Non-Comp），AIX操作系統(tǒng)通過 minperm%，maxperm%, maxclient%, strict_maxclient, lru_file_repage，minfree, maxfree, 等參數(shù)控制系統(tǒng)的內(nèi)存使用.在SAP應用環(huán)境下建議將以上參數(shù)設置為： vmo -p -o strict_maxclient=0 vmo -p -o lru_file_repag

22、e=0 vmo -p -o minperm%=3 vmo -p -o maxclient%=8 vmo -p -o maxperm%=8 vmo -p -o minfree=CPU數(shù)量*120 vmo -p -o maxfree=CPU數(shù)量*128如果CPU數(shù)量是12，則minfree=1440, maxfree=1536 使用AIX 并行I/O (Concurrent I/O) 來提高數(shù)據(jù)庫的性能numperm 和numclient是一個比較好的系統(tǒng)信息,表示的是當前內(nèi)存中基于文件的page占的百分比. 從中可以判斷當前可能的page replacement的機制. 如果numperm和nu

23、mclient在minperm和maxperm/maxclient之間, 參考lru_file_repage, 如果lru_file_repage=0, 將先replace文件類型的page, 如果lru_file_repage=1, 系統(tǒng)會平衡計算型和文件型page的repage情況來決定監(jiān)控AIX內(nèi)存使用情況列出內(nèi)存占用率排名前15名的進程和相關信息svmon -Pt15 | perl -e while()print if($.=2|$&!$s+);$.=0 if(/-+$/)顯示內(nèi)存使用信息10行vmstat 1 10avm Active virtual pagesavm定義為the n

24、umber of virtual-memory working segment pages that have actually been touched. 此值可能會比實際物理內(nèi)存的frame要大,因為一些active virtual memory可能會被寫入到paging space中. 表示的是當前進程使用的stack,變量,共享內(nèi)存段等類型的內(nèi)存,但是不包括進程可能打開的文件所占用的內(nèi)存.fre Size of the free list fre物理內(nèi)存實際剩余的page數(shù)目pi Pages paged in from paging spacepo Pages paged out to

25、 paging space 正常情況下pi和po不應該持續(xù)為非0值;fr Pages freed (page replacement).sr Pages scanned by replacement algorithm 正常情況下fr和sr基本一致;r Average number of runnable kernel threads over the sampling interval. Runnable refers to threads that are ready but waiting to run and to those threads already running. 正常情況

26、下一般r5b Average number of kernel threads placed in the VMM wait queue (awaiting resource, awaiting input/output) over the sampling interval.wa CPU idle time during which the system had outstanding disk/NFS I/O request(s). See detailed description above. b和wa正常的情況下都不大,高的wa(I/O wait)和高的b(在隊列中等待的線程數(shù)目)有可

27、能是paging in和out導致的.工程經(jīng)驗avm可以作為長期監(jiān)控系統(tǒng)內(nèi)存使用率的趨勢分析,如果你有監(jiān)控軟件, 長時間監(jiān)控avm可以給你很好的內(nèi)存使用的趨勢. 雖然它不代表實際系統(tǒng)用了多少內(nèi)存,但是作為趨勢判斷還是非常有效的. 判斷內(nèi)存是否缺少內(nèi)存的一個工程依據(jù): fre少于minfree并且有持續(xù)的page in和page out出現(xiàn). 其它的值:fr,sr,r,b,wa可以作為一些參考的值.vmstat -s作為page ins, page outs, paging space page ins和paging space page outs,應該不會有大量的增長vmstat -vnump

28、erm 和numclient是一個比較好的系統(tǒng)信息,表示的是當前內(nèi)存中基于文件的page占的百分比. 從中可以判斷當前可能的page replacement的機制. 如果numperm和numclient在minperm和maxperm/maxclient之間,參考lru_file_repage, 如果lru_file_repage=0, 將先replace文件類型的page, 如果lru_file_repage=1, 系統(tǒng)會平衡計算型和文件型page的repage情況來決定svmon -GIn use work表示的是正在使用的work類型的內(nèi)存; in use pers表示的是正在使用的p

29、ersistent類型的內(nèi)存(JFS類型); in use clnt表示的正在使用的client類型的內(nèi)存(包括remote文件系統(tǒng)和Enhanced JFS類型)查看用戶連接的負載均衡狀態(tài)select instance_name,host_name,NULL AS failover_type,NULL AS failover_method,NULL AS failed_over FROM v$instance UNION SELECT NULL,NULL,failover_type,failover_method,failed_over FROM v$session WHERE userna

30、me=BASE_TUTOR;select instance_name,host_name,NULL AS failover_type,NULL AS failover_method,NULL AS failed_over FROM v$instance UNION SELECT NULL,NULL,failover_type,failover_method,failed_over FROM v$session WHERE username=SYSTEM;AIX 下mount NSF文件nfso -o nfs_use_reserved_ports=1AIX下使用nmon 每10分抓一次資源使用情

31、況./nmon_aix53 -f -s 600 -c 10000SQL語句篇基礎操作用戶操作建立數(shù)據(jù)庫用戶create user tutor1 identified by tutor18756default tablespace developtemporary tablespace temp;grant dba to tutor1;grant create session to tutor1;alter user tutor1 quota 300M on develop;建立數(shù)據(jù)庫用戶(TOAD)CREATE USER FVDB IDENTIFIED BY FVDB DEFAULT TABL

32、ESPACE FVDB TEMPORARY TABLESPACE TEMP PROFILE DEFAULT QUOTA UNLIMITED ON FVDB;GRANT CREATE SESSION TO FVDB WITH ADMIN OPTION;GRANT DBA TO FVDB WITH ADMIN OPTION;ALTER USER FVDB DEFAULT ROLE DBA;限制用戶會話數(shù)create profile limit_2 limit sessions_per_user 2;alter system set resource_limit=true;ALTER USER xx

33、x_user profile limit_2;ALTER USER xxx_user profile default; 取消解鎖用戶alter user system account unlock;修改用戶密碼和顯示用戶信息alter user sys identified by 密碼;alter user system identified by 密碼;select username,password from dba_users where username=SYS;orapwd file=/oracle/10gr2/db/dbs/orapwee2 password= entries=10

34、 force=y;orapwd file=/dev/rlv_pwdfile_240m password= entries=10 force=y刪除用戶drop user tutor1 cascade;表空間操作建表空間CREATE TABLESPACE FVDB DATAFILE D:ORADATAFVDB04.DBF SIZE 4096M AUTOEXTEND OFF, D:ORADATAFVDB03.DBF SIZE 4096M AUTOEXTEND OFF, D:ORADATAFVDB02.DBF SIZE 4096M AUTOEXTEND OFF, D:ORADATAFVDB01.DB

35、F SIZE 4096M AUTOEXTEND OFFLOGGINGEXTENT MANAGEMENT LOCAL AUTOALLOCATEBLOCKSIZE 8KSEGMENT SPACE MANAGEMENT AUTOFLASHBACK ON;create user scott identified by tiger default tablespace users;grant dba, resource, connect to scott;alter database datafile +RACDB_DATA1/racdb/datafile/users.264.570913355 res

36、ize 1024m;alter tablespace users add datafile +RACDB_DATA1 size 1024m autoextend off;create tablespace indx datafile +RACDB_DATA1 size 1024m autoextend on next 50m maxsize unlimited extent management local autoallocate segment space management auto;alter database datafile +RACDB_DATA1/racdb/datafile

37、/system.262.570913215 resize 800m;alter database datafile +RACDB_DATA1/racdb/datafile/sysaux.260.570913287 resize 500m;alter tablespace undotbs1 add datafile +RACDB_DATA1 size 1024m autoextend on next 50m maxsize 2048m;alter tablespace undotbs2 add datafile +RACDB_DATA1 size 1024m autoextend on next

38、 50m maxsize 2048m;alter database tempfile +RACDB_DATA1/racdb/tempfile/temp.258.570913303 resize 1024m;select tablespace_name, file_namefrom dba_data_filesunionselect tablespace_name, file_namefrom dba_temp_files;把用戶從一個表空間導入到另一表空間.回收用戶unlimited權限r(nóng)evoke unlimited tablespace from childpalace_demo;alte

39、r user childpalace_demo quota 0 on develop;alter user childpalace_demo quota 0 on users;alter user childpalace_demo quota unlimited on childpalace;如何將表移動表空間?ALTER TABLE TABLE_NAME MOVE TABLESPACE_NAME;批量修改表的表空間select alter table | table_name | move tablespace filmmusicdata; from user_tables where ta

40、blespace_name=USERS; 如何將索引移動表空間?ALTER INDEX INDEX_NAME REBUILD TABLESPACE TABLESPACE_NAME;批量修改索引表空間select table_name,tablespace_name from user_tables ;select index_name,tablespace_name from user_indexes ;select alter table | table_name | move tablespace netcs; from user_tables where tablespace_name=

41、USERS;select alter index | index_name | rebuild tablespace netcs; from user_indexes where tablespace_name=USERS;#REBUILD LOG類型索引如果有 LOB 類型的索引（形如：SYS_IL0000033021C00010$），REBUILD時也會報錯。此類索引是創(chuàng)建表時自動生成的，所以需要重建表和其它的索引。根據(jù)索引找到相應表名字select table_name from user_indexes where index_name=SYS_IL0000082969C00009$;

42、 查出此表上的所有索引select index_name from user_indexes where table_name=CMS_INFO_HIS;重命名表名字rename EI_T_TRANSFER to EI_T_TRANSFER_OLD; 新建表create table EI_T_TRANSFER as select * from EI_T_TRANSFER_OLD;此步以后，LOB索引已經(jīng)自動創(chuàng)建了，后面就不用再操作了重命名表上的索引（第二步查出的除LOB外的所有索引）alter index PK_EI_T_TRANSFER rename to PK_EI_T_TRANSFER_

43、OLD;alter index IDX_T_TRANSFER_USERID rename to IDX_T_TRANSFER_USERID_OLD; 查出索引的創(chuàng)建語句select dbms_metadata.get_ddl(INDEX,PK_EI_T_TRANSFER_OLD) from dual;select dbms_metadata.get_ddl(INDEX,IDX_T_TRANSFER_USERID_OLD) from dual;#參數(shù)配置操作增加服務名show parameter servicealter system set service_names =, racdb_taf

44、 scope=both;在當前實例中將 cluster_database 設置為 FALSE 來禁用集群實例參數(shù)alter system set cluster_database=false scope=spfile sid=racdb1;archive log list監(jiān)控查詢操作集群中所有正在運行的實例SELECT inst_id , instance_number inst_no , instance_name inst_name , parallel , status , database_status db_status , active_state state , host_nam

45、e hostFROM gv$instanceORDER BY inst_id;位于磁盤組中的所有數(shù)據(jù)文件select name from v$datafileunionselect member from v$logfileunionselect name from v$controlfileunionselect name from v$tempfile;屬于“RACDB_DATA1”磁盤組的所有 ASM 磁盤SELECT pathFROM v$asm_diskWHERE group_number IN (select group_number from v$asm_diskgroup wh

46、ere name = RACDB_DATA1);運行緩沖中的 SQL 語句： COLUMN instance_name FORMAT a13COLUMN host_name FORMAT a9COLUMN failover_method FORMAT a15COLUMN failed_over FORMAT a11SELECT instance_name , host_name , NULL AS failover_type , NULL AS failover_method , NULL AS failed_overFROM v$instanceUNIONSELECT NULL , NULL

47、 , failover_type , failover_method , failed_overFROM v$sessionWHERE username = SYSTEM;識別出5個最消耗cpu時間的SQL查詢(適用于9i以上版本)select * from(select * from v$sql order by cpu_time desc) qwhere rownum=5;診斷系統(tǒng)中磁盤上已經(jīng)完成的排序數(shù)量育內(nèi)存中的排序數(shù)量的百分比（一般小于0.05)，如果排序率大于閥值，那么應當增加sort_area_sizeselect disk.value disk,mem.value memory

48、,round(disk.value/mem.value)*100,2) sort_ratiofrom v$sysstat disk, v$sysstat memwhere = sorts (disk) and = sorts (memory)每次查詢最多讀盤次數(shù)的10個SQL查詢select * from(select round(disk_reads/decode(executions,0,-99999999,executions) disk_reads_per_exec, v$sql.* from v$sqlorder by disk_reads_per_exec desc)Qwhere

49、rownum=10;數(shù)據(jù)文件的讀寫統(tǒng)計select fs.file#,fs.phyrds,fs.phywrts from v$filestat fs,v$datafile df where fs.file#=df.file#; SELECT name,phyrds,phywrts FROM v$datafile df,v$filestat fsWHERE df.file# =fs.file#; 臨時文件的讀寫統(tǒng)計select file_name, fs.phyrds reads, fs.phywrts writes from v$datafile df,v$filestat fswhere d

50、f.file#=fs.file#;根據(jù)SID查找語名SELECT T2.SID, T2.SERIAL#, T3.SQL_TEXT FROM V$SESSION T2, V$SQLTEXT T3 WHERE T2.SID IN (168) AND T2.SQL_ADDRESS = T3.ADDRESS;根據(jù)FILE_ID & BLOCK_ID獲得對象名稱SELECT /*+ RULE*/ owner, segment_name, segment_type FROM dba_extents WHERE file_id = &file_id AND &block_id BETWEEN block_i

51、d AND block_id + blocks - 1;根據(jù)操作系統(tǒng)PID,查詢SESSION信息SELECT a.sid, a.serial#, b.spid, a.terminal, a.machine, gram, a.osuser FROM v$session a, v$process b WHERE a.paddr = b.addr AND b.spid = &SPID;根據(jù)SESSION SID,查詢操作系統(tǒng)PIDSELECT a.sid, a.serial#, b.spid, a.terminal, a.machine, gram, a.osuser FROM v$session

52、 a, v$process b WHERE a.paddr = b.addr AND a.sid = &SID;查詢用戶正在執(zhí)行的SQLSELECT sql_text FROM v$sqltext WHERE hash_value = (SELECT sql_hash_value FROM v$session WHERE sid = &sid) ORDER BY piece;查詢當前的系統(tǒng)等待事件SELECT * FROM v$session_wait WHERE event NOT LIKE %SQL*Net% AND event NOT LIKE %rdbms% AND event NOT

53、 LIKE %timer% AND event NOT LIKE %jobq% ORDER BY event, seconds_in_wait;查詢詳細的當前系統(tǒng)等待事件SELECT s.sid, s.username, w.seq#, w.event, w.p1text, w.p1, w.p2text, w.p2, w.p3text, w.p3, w.seconds_in_wait, w.state, s.logon_time, s.osuser, gram FROM v$session s, v$session_wait w WHERE s.sid = w.sid AND w.event

54、NOT LIKE %SQL*Net% AND w.event NOT LIKE %rdbms% AND w.event NOT LIKE %timer% AND w.event NOT LIKE %jobq% ORDER BY w.event, w.seconds_in_wait;查詢等待db file sequential/scattered read的Session正在執(zhí)行的SQLSELECT s.sid, s.username, t.hash_value, t.piece, t.sql_text FROM v$session s, v$session_wait w, v$sqltext

55、t WHERE s.sid = w.sid AND s.sql_hash_value = t.hash_value AND w.event IN (db file sequential read, db file scattered read) ORDER BY s.sid, t.piece;查詢等待db file sequential/scattered read對應的數(shù)據(jù)庫對象SELECT /*+ RULE*/ s.sid, s.username, w.seq#, w.event, d.segment_type, d.owner | . | d.segment_name AS segmen

56、t_name, w.seconds_in_wait, w.state, s.logon_time FROM v$session s, v$session_wait w, dba_extents d WHERE s.sid = w.sid AND d.file_id = w.p1 AND w.p2 BETWEEN d.block_id AND d.block_id + d.blocks - 1 AND w.event IN (db file sequential read, db file scattered read) ORDER BY w.event, segment_name;查詢導致LO

57、CK的SID,SPID,LOCKED_OBJECT,LOCK_TYPE等信息SELECT /*+ RULE*/ l.sid, p.spid, s.username,s.logon_time, s.osuser, gram, l.type, CASE l.TYPE WHEN TM THEN O.object_name WHEN TX THEN END as OBJECT_NAME, DECODE (l.lmode, 0, 0=NONE, 1, 1=NULL, 2, 2=RS, 3, 3=RX, 4, 4=S, 5, 5=SRX, 6, 6=X) lmode, CASE l.request WHE

58、N 0 THEN ELSE BLOCKED BY | l.id2 END as BLOCKED, CASE l.block WHEN 0 THEN ELSE l.id2 | IS BLOCKING END as BLOCKING, l.request, l.ctime FROM v$lock l, v$session s, dba_objects o, v$process p WHERE l.type in (TX, TM) AND s.paddr = p.addr AND l.sid = s.sid AND l.id1 = o.object_id(+) ORDER BY s.username

59、, l.sid, l.ctime;查詢導致DDL LOCK的詳細信息SELECT s.sid, p.spid, s.username, a.owner | . | a.NAME AS OBJECT_NAME, a.TYPE, a.mode_held, a.mode_requested, s.osuser, s.logon_time, gram FROM dba_ddl_locks a, v$session s, v$process p WHERE s.sid = a.session_id AND s.paddr = p.addr AND (a.mode_held = Exclusive OR

60、a.mode_requested = Exclusive) ORDER BY s.USERNAME, a.NAME;查詢事務使用的回滾段SELECT s.username, s.sid, s.serial#, t.ubafil UBA filenum, t.ubablk UBA Block number, t.used_ublk Number of undo Blocks Used, t.start_time, t.status, t.start_scnb, t.xidusn rollid, rollname FROM v$session s, v$transaction t, v$rolln