set linesize 145set pagesize 1000set trimout onset trimspool onSet Feedback offset timing offset verify offpromptprompt -- ----------------------------------------------------------------------- ---prompt -- List of oracle's processes ---prompt -- ----------------------------------------------------------------------- ---promptSet linesize 145Set Pagesize 50column username heading "Utilis." format A10column commande heading "Fonction" format A13column status heading "Etat" format A4column logon heading "Date|Connexion" format A14column command heading "C" format 99column sid heading "Id" format 9999column serial heading "Serial#" format 99999column spid heading "Unix" format A7column terminal heading "Terminal" format A11column lockwait heading "Lockwait" format A8column program heading "Programme" format A35 word_wrappedcolumn nb_sess heading "Nb. Sess." format 9999999column last_call_et heading "Last|Call" format A9select s.sid , s.serial# serial , p.spid , substr(s.username,1,8) username , s.terminal , s.command , decode(s.command, 1,'Create table' , 2,'Insert' , 3,'Select' , 6,'Update' , 7,'Delete' , 9,'Create index' ,10,'Drop index' ,11,'Alter index' ,12,'Drop table' ,13,'Create seq' ,14,'Alter sequence' ,15,'Alter table' ,16,'Drop sequ.' ,17,'Grant' ,19,'Create syn.' ,20,'Drop syn.' ,21,'Create view' ,22,'Drop view' ,23,'Validate index' ,24,'create proced.' ,25,'Alter procedure' ,26,'Lock table' ,42,'Alter session' ,44,'Commit' ,45,'Rollback' ,46,'Savepoint' ,47,'PL/SQL Exec' ,48,'Set Transaction' ,60,'Alter trigger' ,62,'Analyse Table' ,63,'Analyse index' ,71,'Create Snapshot Log' ,72,'Alter Snapshot Log' ,73,'Drop Snapshot Log' ,74,'Create Snapshot' ,75,'Alter Snapshot' ,76,'drop Snapshot' ,85,'Truncate table' , 0,'No command', '? : '||s.command) commande , to_char(s.logon_time,'DD-MM-YY HH24:MI') logon , substr(s.status,1,4) status , floor(s.last_call_et/3600)||':'|| floor(mod(s.last_call_et,3600)/60)||':'|| mod(mod(s.last_call_et,3600),60) last_call_et , s.lockwait , Substr(s.program,1,20) program from v$session s , v$process p where s.paddr = p.addr order by s.status desc , s.last_call_et desc , P.spid;Promptpromptprompt -- ----------------------------------------------------------------------- ---prompt -- Active / Inactive Sessions ---prompt -- ----------------------------------------------------------------------- ---Set Heading Off Column Status heading "Utilis." format A50Select '-- Time : '||Time||' - Process : '||Proc||' - Session '||Sess Status From ( Select To_Char(Sysdate, 'HH24:MI') Time From Dual ) , ( Select Count(*) Proc From V$Process ) , ( Select Count(*) Sess From V$Session );Set Heading OnPromptcolumn status heading "Etat" format A10Select Initcap(S.Status) status , Count(*) nb_sess From V$Session S Group By Initcap(S.Status);Promptpromptprompt -- ----------------------------------------------------------------------- ---prompt -- Active / Sessions In Progress ... ---prompt -- ----------------------------------------------------------------------- ---column pct heading "Pro.|(%)" format 9999column username heading "Utilis." format A8column machine heading "Machine" format A12column program heading "Program" format A12column modu heading "Module" format A15column sql heading "Sql" format A60 word_wrappedcolumn Sta_Time heading "Start|Time" format A18column LUTime heading "Last|Update|Time" format A18column Time_Left heading "Time|Left" format A10Select sn.sid , substr(sn.username,1,8) username , Trunc(sl.sofar/sl.totalwork * 100) pct , sn.machine machine , sn.program program , sn.module modu , to_char(start_time,'DD-MON-YY HH:MI:SS') Sta_Time , to_char(last_update_time,'DD-MON-YY HH:MI:SS') LUTime , To_Char(To_Date(TIME_REMAINING,'SSSSS'),'HH24:MI:SS') Time_Left From v$session_longops sl , v$session sn where sn.status = 'ACTIVE' and sl.sid = sn.sid and sl.sofar != sl.totalwork;-- Select -- substr(sn.username,1,8) username-- , Trunc(sl.sofar/sl.totalwork * 100) pct-- , sn.machine machine-- , sn.program program -- , sn.module modu-- , sa.sql_text sql-- From-- v$session_longops sl-- , v$session sn-- , v$sqlarea sa-- where-- sn.status = 'ACTIVE'-- and -- sl.sid = sn.sid-- and -- sn.sql_address = sa.address (+)-- and -- sl.sofar != sl.totalwork-- ;PromptPrompt
--------------------------------------------------------------------------------------------------------------------------------
-- ----------------------------------------------------------------------- ---- List of oracle's processes ---- ----------------------------------------------------------------------- -- Date Last Id Serial# Unix Utilis. Terminal C Fonction Connexion Etat Call Lockwait Programme----- ------- ------- ---------- ----------- --- ------------- -------------- ---- --------- -------- ----------------------------------- 13 13 770212 SAPEUR 0 No command 04-02-07 20:40 INAC 110:43:30 xxxxxxxxxxxxxxxxxxxxxxxxxx@ 19 6 2224320 SAPEUR 3 Select 04-02-07 20:40 INAC 110:43:28 xxxxxxxxxxxxxxxxxxxxxxxxxx@ 14 9 2490590 SAPEUR 0 No command 04-02-07 20:40 INAC 65:15:48 xxxxxxxxxxxxxxxxxxxxxxxxxx@ 12 10 1941726 SAPEUR 0 No command 04-02-07 20:40 INAC 18:11:24 xxxxxxxxxxxxxxxxxxxxxxxxxx@ 18 7 2416786 SAPEUR 0 No command 04-02-07 20:40 INAC 17:45:7 xxxxxxxxxxxxxxxxxxxxxxxxxx@ 9 19 381058 SAPEUR 0 No command 04-02-07 20:40 INAC 17:45:6 xxxxxxxxxxxxxxxxxxxxxxxxxx@ 10 64 2625622 SAPEUR 0 No command 06-02-07 17:21 INAC 17:44:54 xxxxxxxxxxxxxxxxxxxxxxxxxx@ 15 5 2302022 SAPEUR 0 No command 04-02-07 20:40 INAC 17:42:14 xxxxxxxxxxxxxxxxxxxxxxxxxx@ 23 7 2166848 SAPEUR 0 No command 04-02-07 20:40 INAC 4:53:3 xxxxxxxxxxxxxxxxxxxxxxxxxx@ 24 4 2195658 SAPEUR 0 No command 04-02-07 20:40 INAC 1:52:55 xxxxxxxxxxxxxxxxxxxxxxxxxx@ 17 10 2474038 SAPEUR 0 No command 04-02-07 20:40 INAC 0:53:3 xxxxxxxxxxxxxxxxxxxxxxxxxx@ 26 54473 2596880 SAPEUR 0 No command 08-02-07 16:11 INAC 0:53:3 xxxxxxxxxxxxxxxxxxxxxxxxxx@ 33 12270 585862 SAPEUR 0 No command 08-02-07 16:04 INAC 0:53:3 xxxxxxxxxxxxxxxxxxxxxxxxxx@ 30 5 2457792 SAPEUR 0 No command 04-02-07 20:40 INAC 0:18:3 xxxxxxxxxxxxxxxxxxxxxxxxxx@ 34 62098 2027740 SAPEUR 0 No command 09-02-07 11:08 INAC 0:15:2 xxxxxxxxxxxxxxxxxxxxxxxxxx@ 41 17482 2146464 SAPEUR 0 No command 08-02-07 17:44 INAC 0:13:3 xxxxxxxxxxxxxxxxxxxxxxxxxx@ 29 3 2199712 SAPEUR 0 No command 04-02-07 20:40 INAC 0:3:3 xxxxxxxxxxxxxxxxxxxxxxxxxx@ 40 29264 2654462 SAPEUR 0 No command 09-02-07 09:49 INAC 0:3:3 xxxxxxxxxxxxxxxxxxxxxxxxxx@ 16 4 2027740 SAPEUR 0 No command 04-02-07 20:40 INAC 0:1:12 xxxxxxxxxxxxxxxxxxxxxxxxxx@ 25 15 2330720 SAPEUR 0 No command 04-02-07 20:40 INAC 0:0:3 xxxxxxxxxxxxxxxxxxxxxxxxxx@ 48 11950 2588908 SYS pts/0 3 Select 09-02-07 11:23 ACTI 0:0:0 sqlplus@xxxxxxxx (-- ----------------------------------------------------------------------- ---- Active / Inactive Sessions ---- ----------------------------------------------------------------------- ---- Time : 11:23 - Process : 30 - Session 30Etat Nb. Sess.---------- ---------Active 9Inactive 21-- ----------------------------------------------------------------------- ---- Active / Sessions In Progress ... ---- ----------------------------------------------------------------------- --
