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

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

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

3、秒時,會導(dǎo)致oracle數(shù)據(jù)庫反復(fù)重啟或者狀態(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é)點(diǎn)狀態(tài)srvctl status nodeapps -n 所有實(shí)例和服務(wù)的狀態(tài)srvctl status database -d racdb單個實(shí)例的狀態(tài)srvctl status instance -d racdb -i racdb2在數(shù)據(jù)庫全局命名服務(wù)的狀態(tài)srvctl status service -d racdb -s racdb_taf特定節(jié)點(diǎn)上節(jié)點(diǎn)應(yīng)用程序的狀態(tài)srv

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

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

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

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

9、閉數(shù)據(jù)數(shù)據(jù)的導(dǎo)入與導(dǎo)出數(shù)據(jù)導(dǎo)入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ù)泵導(dǎo)入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ù)導(dǎo)出備份整個數(shù)據(jù)庫exp system/manager inctype=complete file=gzdec-tutorl-070428.dmp增量型”增量導(dǎo)出exp system/manager inctype=incremental file=gzdec-tutorl-070428.dmp累計(jì)型”增量導(dǎo)出exp system/manager inctype=cumulative file=gzdec-tutorl-07

13、0428.dmp 導(dǎo)出一個完整數(shù)據(jù)庫exp system/manager file=bible_db log=dible_db full=y導(dǎo)出數(shù)據(jù)庫定義而不導(dǎo)出數(shù)據(jù)exp system/manager file=bible_db log=dible_db full=y rows=n導(dǎo)出一個或一組指定用戶所屬的全部表、索引和其他對象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ù)泵導(dǎo)出在命令行中使用要注意使用轉(zhuǎn)意字符因?yàn)?( )會被認(rè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進(jìn)程ps -ef|grep ora_|grep -v grep|awk print $2 |xargs kill -9顯示Oracle 的Unix 進(jìn)程 ps -ef|grep ora_|grep -v grep 查看消耗CPU 時間最長的進(jìn)程： ps -ef|grep oracle|sort +6|tail按照此列排序來獲得當(dāng)前高CPU 占用的用戶。例如： ps auxgw|sort +2|tail 顯示Oracle 的活動連接用戶數(shù)量 ps -ef|grep

17、$ORACLE_SID|grep -v grep|grep -v ora_|wc -l AIX下常用命令顯示aix 中的服務(wù)器設(shè)備信息 在AIX 中也可以使用lsdev 命令來查看硬件設(shè)備信息。 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 的工具。這個工具顯示服務(wù)器上的所有內(nèi)存的使用情 況，包括頁交換和內(nèi)存使用。例如： svmonsvmon -

18、P pid顯示文件被進(jìn)程誰占用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上設(shè)置的優(yōu)化值

20、vmo -p -o v_pinshm=1vmo -p -o maxpin%=75oradb2上設(shè)置的優(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應(yīng)用的需求，因此需要進(jìn)行一些調(diào)整。AIX操作系統(tǒng)中，一般將內(nèi)存的使用分成兩個部分，一個部分用于應(yīng)用程序運(yùn)行使用，稱為計(jì)算內(nèi)存（Computational），另一部分用于文件緩存，稱為文件緩存（Non-Comp），AIX操作系統(tǒng)通過 minperm%，maxperm%, maxclient%, strict_maxclient, lru_file_repage，minfree, maxfree, 等參數(shù)控制系統(tǒng)的內(nèi)存使用.在SAP應(yīng)用環(huán)境下建議將以上參數(shù)設(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)信息,表示的是當(dāng)前內(nèi)存中基于文件的page占的百分比. 從中可以判斷當(dāng)前可能的page replacement的機(jī)制. 如果numperm和nu

23、mclient在minperm和maxperm/maxclient之間, 參考lru_file_repage, 如果lru_file_repage=0, 將先replace文件類型的page, 如果lru_file_repage=1, 系統(tǒng)會平衡計(jì)算型和文件型page的repage情況來決定監(jiān)控AIX內(nèi)存使用情況列出內(nèi)存占用率排名前15名的進(jìn)程和相關(guān)信息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. 此值可能會比實(shí)際物理內(nèi)存的frame要大,因?yàn)橐恍゛ctive virtual memory可能會被寫入到paging space中. 表示的是當(dāng)前進(jìn)程使用的stack,變量,共享內(nèi)存段等類型的內(nèi)存,但是不包括進(jìn)程可能打開的文件所占用的內(nèi)存.fre Size of the free list fre物理內(nèi)存實(shí)際剩余的page數(shù)目pi Pages paged in from paging spacepo Pages paged out to

25、 paging space 正常情況下pi和po不應(yīng)該持續(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(在隊(duì)列中等待的線程數(shù)目)有可

27、能是paging in和out導(dǎo)致的.工程經(jīng)驗(yàn)avm可以作為長期監(jiān)控系統(tǒng)內(nèi)存使用率的趨勢分析,如果你有監(jiān)控軟件, 長時間監(jiān)控avm可以給你很好的內(nèi)存使用的趨勢. 雖然它不代表實(shí)際系統(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,應(yīng)該不會有大量的增長vmstat -vnump

28、erm 和numclient是一個比較好的系統(tǒng)信息,表示的是當(dāng)前內(nèi)存中基于文件的page占的百分比. 從中可以判斷當(dāng)前可能的page replacement的機(jī)制. 如果numperm和numclient在minperm和maxperm/maxclient之間,參考lru_file_repage, 如果lru_file_repage=0, 將先replace文件類型的page, 如果lru_file_repage=1, 系統(tǒng)會平衡計(jì)算型和文件型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類型)查看用戶連接的負(fù)載均衡狀態(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語句篇基礎(chǔ)操作用戶操作建立數(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;把用戶從一個表空間導(dǎo)入到另一表空間.回收用戶unlimited權(quán)限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ù)索引找到相應(yīng)表名字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ù)配置操作增加服務(wù)名show parameter servicealter system set service_names =, racdb_taf

44、 scope=both;在當(dāng)前實(shí)例中將 cluster_database 設(shè)置為 FALSE 來禁用集群實(shí)例參數(shù)alter system set cluster_database=false scope=spfile sid=racdb1;archive log list監(jiān)控查詢操作集群中所有正在運(yùn)行的實(shí)例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);運(yùn)行緩沖中的 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)，如果排序率大于閥值，那么應(yīng)當(dāng)增加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)計(jì)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)計(jì)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;查詢當(dāng)前的系統(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;查詢詳細(xì)的當(dāng)前系統(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對應(yīng)的數(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;查詢導(dǎo)致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;查詢導(dǎo)致DDL LOCK的詳細(xì)信息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;查詢事務(wù)使用的回滾段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