| 订阅 | 在线投稿
分享
 
 
当前位置: 王朝网络 >> oracle >> Oracle shu ju ku wei hu chang yong SQL yu ju ji he
 

Oracle shu ju ku wei hu chang yong SQL yu ju ji he

2008-07-02 07:24:11 编辑來源:互联网 评论
 
本文为【Oracle数据库维护常用SQL语句集合】的拼音翻译版
  jin cheng xiang guan :
  1、 qiu dang qian hui hua de SID,SERIAL#
  SELECT Sid, Serial#
  FROM V$session
  WHERE Audsid = Sys_Context('USERENV', 'SESSIONID');
  2、 cha xun session de OS jin cheng ID
  SELECT p.Spid "OS Thread", b.NAME "Name-User", s.Program, s.Sid, s.Serial#,
  s.Osuser, s.Machine
  FROM V$process p, V$session s, V$bgprocess b
  WHERE p.Addr = s.Paddr
  AND p.Addr = b.Paddr
  And (s.sid=&1 or p.spid=&1)
  UNION ALL
  SELECT p.Spid "OS Thread", s.Username "Name-User", s.Program, s.Sid,
  s.Serial#, s.Osuser, s.Machine
  FROM V$process p, V$session s
  WHERE p.Addr = s.Paddr
  And (s.sid=&1 or p.spid=&1)
  AND s.Username IS NOT NULL;
  3、 gen ju sid cha kan dui ying lian jie zheng zai yun xing de sql
  SELECT /*+ PUSH_SUBQ */
  Command_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, '>' || Address Sql_Address, 'N' Status
  FROM V$sqlarea
  WHERE Address = (SELECT Sql_Address
  FROM V$session
  WHERE Sid = &sid );
  4、 cha zhao object wei na xie jin cheng suo yong
  SELECT p.Spid, s.Sid, s.Serial# Serial_Num, s.Username User_Name,
  a.TYPE Object_Type, s.Osuser Os_User_Name, a.Owner,
  a.OBJECT Object_Name,
  Decode(Sign(48 - Command), 1, To_Char(Command), 'Action Code #' || To_Char(Command)) Action,
  p.Program Oracle_Process, s.Terminal Terminal, s.Program Program,
  s.Status Session_Status
  FROM V$session s, V$access a, V$process p
  WHERE s.Paddr = p.Addr
  AND s.TYPE = 'USER'
  AND a.Sid = s.Sid
  AND a.OBJECT = '&obj'
  ORDER BY s.Username, s.Osuser
  5、 cha kan you na xie yong hu lian jie
  SELECT s.Osuser Os_User_Name,
  Decode(Sign(48 - Command),1,To_Char(Command),
  'Action Code #' || To_Char(Command)) Action,
  p.Program Oracle_Process, Status Session_Status, s.Terminal Terminal,
  s.Program Program, s.Username User_Name,
  s.Fixed_Table_Sequence Activity_Meter, '' Query, 0 Memory,
  0 Max_Memory, 0 Cpu_Usage, s.Sid, s.Serial# Serial_Num
  FROM V$session s, V$process p
  WHERE s.Paddr = p.Addr
  AND s.TYPE = 'USER'
  ORDER BY s.Username, s.Osuser
  6、 gen ju v.sid cha kan dui ying lian jie de zi yuan zhan yong deng qing kuang
  SELECT n.NAME, v.VALUE, n.CLASS, n.Statistic#
  FROM V$statname n, V$sesstat v
  WHERE v.Sid = &sid
  AND v.Statistic# = n.Statistic#
  ORDER BY n.CLASS, n.Statistic#
  7、 cha xun hao zi yuan de jin cheng (top session)
  SELECT s.Schemaname Schema_Name,
  Decode(Sign(48 - Command),
  1, To_Char(Command), 'Action Code #' || To_Char(Command)) Action,
  Status Session_Status, s.Osuser Os_User_Name, s.Sid, p.Spid,
  s.Serial# Serial_Num, Nvl(s.Username, '[Oracle process]') User_Name,
  s.Terminal Terminal, s.Program Program, St.VALUE Criteria_Value
  FROM V$sesstat St, V$session s, V$process p
  WHERE St.Sid = s.Sid
  AND St.Statistic# = To_Number('38')
  AND ('ALL' = 'ALL' OR s.Status = 'ALL')
  AND p.Addr = s.Paddr
  ORDER BY St.VALUE DESC, p.Spid ASC, s.Username ASC, s.Osuser ASC
  8、 cha kan suo (lock) qing kuang
  SELECT /*+ RULE */
  Ls.Osuser Os_User_Name, Ls.Username User_Name,
  Decode(Ls.TYPE,
  'RW', 'Row wait enqueue lock', 'TM', 'DML enqueue lock',
  'TX', 'Transaction enqueue lock', 'UL', 'User supplied lock') Lock_Type,
  o.Object_Name OBJECT,
  Decode(Ls.Lmode,
  1, NULL, 2, 'Row Share', 3, 'Row Exclusive',
  4, 'Share', 5, 'Share Row Exclusive', 6, 'Exclusive',
  NULL) Lock_Mode,
  o.Owner, Ls.Sid, Ls.Serial# Serial_Num, Ls.Id1, Ls.Id2
  FROM Sys.Dba_Objects o,
  (SELECT s.Osuser, s.Username, l.TYPE, l.Lmode, s.Sid, s.Serial#, l.Id1,
  l.Id2
  FROM V$session s, V$lock l
  WHERE s.Sid = l.Sid) Ls
  WHERE o.Object_Id = Ls.Id1
  AND o.Owner <> 'SYS'
  ORDER BY o.Owner, o.Object_Name
  9、 cha kan deng dai (wait) qing kuang
  SELECT Ws.CLASS, Ws.COUNT COUNT, SUM(Ss.VALUE) Sum_Value
  FROM V$waitstat Ws, V$sysstat Ss
  WHERE Ss.NAME IN ('db block gets', 'consistent gets')
  GROUP BY Ws.CLASS, Ws.COUNT
  10、 qiu process/session de zhuang tai
  SELECT p.Pid, p.Spid, s.Program, s.Sid, s.Serial#
  FROM V$process p, V$session s
  WHERE s.Paddr = p.Addr;
  11、 qiu shui zu sai le mou ge session(10g)
  SELECT Sid, Username, Event, Blocking_Session, Seconds_In_Wait, Wait_Time
  FROM V$session
  WHERE State IN ('WAITING')
  AND Wait_Class != 'Idle';
  12、 cha hui hua de zu sai
  col user_name format a32
  SELECT /*+ rule */
  Lpad(' ', Decode(l.Xidusn, 0, 3, 0)) || l.Oracle_Username User_Name,
  o.Owner, o.Object_Name, s.Sid, s.Serial#
  FROM V$locked_Object l, Dba_Objects o, V$session s
  WHERE l.Object_Id = o.Object_Id
  AND l.Session_Id = s.Sid
  ORDER BY o.Object_Id, Xidusn DESC;
  col username format a15
  col lock_level format a8
  col owner format a18
  col object_name format a32
  SELECT /*+ rule */
  s.Username,
  Decode(l.TYPE, 'tm', 'table lock', 'tx', 'row lock', NULL) Lock_Level,
  o.Owner, o.Object_Name, s.Sid, s.Serial#
  FROM V$session s, V$lock l, Dba_Objects o
  WHERE l.Sid = s.Sid
  AND l.Id1 = o.Object_Id(+)
  AND s.Username IS NOT NULL;
  13、 qiu deng dai de shi jian ji hui hua xin xi / qiu hui hua de deng dai ji hui hua xin xi
  SELECT Se.Sid, s.Username, Se.Event, Se.Total_Waits, Se.Time_Waited,
  Se.Average_Wait
  FROM V$session s, V$session_Event Se
  WHERE s.Username IS NOT NULL
  AND Se.Sid = s.Sid
  AND s.Status = 'ACTIVE'
  AND Se.Event NOT LIKE '%SQL*Net%'
  ORDER BY s.Username;
  SELECT s.Sid, s.Username, Sw.Event, Sw.Wait_Time, Sw.State,
  Sw.Seconds_In_Wait
  FROM V$session s, V$session_Wait Sw
  WHERE s.Username IS NOT NULL
  AND Sw.Sid = s.Sid
  AND Sw.Event NOT LIKE '%SQL*Net%'
  ORDER BY s.Username;
  14、 qiu hui hua deng dai de file_id/block_id
  col event format a24
  col p1text format a12
  col p2text format a12
  col p3text format a12
  SELECT Sid, Event, P1text, P1, P2text, P2, P3text, P3
  FROM V$session_Wait
  WHERE Event NOT LIKE '%SQL%'
  AND Event NOT LIKE '%rdbms%'
  AND Event NOT LIKE '%mon%'
  ORDER BY Event;
  SELECT NAME, Wait_Time
  FROM V$latch l
  WHERE EXISTS (SELECT 1
  FROM (SELECT Sid, Event, P1text, P1, P2text, P2, P3text, P3
  FROM V$session_Wait
  WHERE Event NOT LIKE '%SQL%'
  AND Event NOT LIKE '%rdbms%'
  AND Event NOT LIKE '%mon%') x
  WHERE x.P1 = l.Latch#);
  15、 qiu hui hua deng dai de dui xiang
  col owner format a18
  col segment_name format a32
  col segment_type format a32
  SELECT Owner, Segment_Name, Segment_Type
  FROM Dba_Extents
  WHERE File_Id = &File_Id
  AND &Block_Id BETWEEN Block_Id AND Block_Id + Blocks - 1;
  16、 qiu chu mou ge jin cheng , bing dui ta jin xing gen zong
  SELECT s.Sid, s.Serial#
  FROM V$session s, V$process p
  WHERE s.Paddr = p.Addr
  AND p.Spid = &1;
  Exec Dbms_System.Set_Sql_Trace_In_Session(&1, &2, TRUE);
  Exec Dbms_System.Set_Sql_Trace_In_Session(&1, &2, FALSE);
  17、 qiu dang qian session de gen zong wen jian
  SELECT P1.VALUE || '/' || P2.VALUE || '_ora_' || p.Spid || '.ora' Filename
  FROM V$process p, V$session s, V$parameter P1, V$parameter P2
  WHERE P1.NAME = 'user_dump_dest'
  AND P2.NAME = 'instance_name'
  AND p.Addr = s.Paddr
  AND s.Audsid = Userenv('SESSIONID')
  AND p.Background IS NULL
  AND Instr(p.Program, 'CJQ') = 0;
  18、 qiu chu suo ding de dui xiang
  SELECT Do.Object_Name, Session_Id, Process, Locked_Mode
  FROM V$locked_Object Lo, Dba_Objects Do
  WHERE Lo.Object_Id = Do.Object_Id;【原文】【汉音对照
 
 
 
 
 
 
 
 
日版宠物情人插曲《Winding Road》歌词

日版宠物情人2017的插曲,很带节奏感,日语的,女生唱的。 最后听见是在第8集的时候女主手割伤了,然后男主用嘴帮她吸了一下,插曲就出来了。 歌手:Def...

兄弟共妻,我成了他们夜里的美食

老钟家的两个儿子很特别,就是跟其他的人不太一样,魔一般的执着。兄弟俩都到了要结婚的年龄了,不管自家老爹怎么磨破嘴皮子,兄弟俩说不娶就不娶,老父母为兄弟两操碎了心...

如何磨出破洞牛仔裤?牛仔裤怎么剪破洞?

把牛仔裤磨出有线的破洞 1、具体工具就是磨脚石,下面垫一个硬物,然后用磨脚石一直磨一直磨,到把那块磨薄了,用手撕开就好了。出来的洞啊很自然的。需要猫须的话调几...

我就是扫描下图得到了敬业福和爱国福

先来看下敬业福和爱国福 今年春节,支付宝再次推出了“五福红包”活动,表示要“把欠大家的敬业福都还给大家”。 今天该活动正式启动,和去年一样,需要收集“五福”...

冰箱异味产生的原因和臭味去除的方法

有时候我们打开冰箱就会闻到一股异味,冰箱里的这种异味是因为一些物质发出的气味的混合体,闻起来让人恶心。 产生这些异味的主要原因有以下几点。 1、很多人有这种习...

 
 
jin cheng xiang guan : 1、 qiu dang qian hui hua de SID,SERIAL# SELECT Sid, Serial# FROM V$session WHERE Audsid = Sys_Context('USERENV', 'SESSIONID'); 2、 cha xun session de OS jin cheng ID SELECT p.Spid "OS Thread", b.NAME "Name-User", s.Program, s.Sid, s.Serial#, s.Osuser, s.Machine FROM V$process p, V$session s, V$bgprocess b WHERE p.Addr = s.Paddr AND p.Addr = b.Paddr And (s.sid=&1 or p.spid=&1) UNION ALL SELECT p.Spid "OS Thread", s.Username "Name-User", s.Program, s.Sid, s.Serial#, s.Osuser, s.Machine FROM V$process p, V$session s WHERE p.Addr = s.Paddr And (s.sid=&1 or p.spid=&1) AND s.Username IS NOT NULL; 3、 gen ju sid cha kan dui ying lian jie zheng zai yun xing de sql SELECT /*+ PUSH_SUBQ */ Command_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, '>' || Address Sql_Address, 'N' Status FROM V$sqlarea WHERE Address = (SELECT Sql_Address FROM V$session WHERE Sid = &sid ); 4、 cha zhao object wei na xie jin cheng suo yong SELECT p.Spid, s.Sid, s.Serial# Serial_Num, s.Username User_Name, a.TYPE Object_Type, s.Osuser Os_User_Name, a.Owner, a.OBJECT Object_Name, Decode(Sign(48 - Command), 1, To_Char(Command), 'Action Code #' || To_Char(Command)) Action, p.Program Oracle_Process, s.Terminal Terminal, s.Program Program, s.Status Session_Status FROM V$session s, V$access a, V$process p WHERE s.Paddr = p.Addr AND s.TYPE = 'USER' AND a.Sid = s.Sid AND a.OBJECT = '&obj' ORDER BY s.Username, s.Osuser 5、 cha kan you na xie yong hu lian jie SELECT s.Osuser Os_User_Name, Decode(Sign(48 - Command),1,To_Char(Command), 'Action Code #' || To_Char(Command)) Action, p.Program Oracle_Process, Status Session_Status, s.Terminal Terminal, s.Program Program, s.Username User_Name, s.Fixed_Table_Sequence Activity_Meter, '' Query, 0 Memory, 0 Max_Memory, 0 Cpu_Usage, s.Sid, s.Serial# Serial_Num FROM V$session s, V$process p WHERE s.Paddr = p.Addr AND s.TYPE = 'USER' ORDER BY s.Username, s.Osuser 6、 gen ju v.sid cha kan dui ying lian jie de zi yuan zhan yong deng qing kuang SELECT n.NAME, v.VALUE, n.CLASS, n.Statistic# FROM V$statname n, V$sesstat v WHERE v.Sid = &sid AND v.Statistic# = n.Statistic# ORDER BY n.CLASS, n.Statistic# 7、 cha xun hao zi yuan de jin cheng (top session) SELECT s.Schemaname Schema_Name, Decode(Sign(48 - Command), 1, To_Char(Command), 'Action Code #' || To_Char(Command)) Action, Status Session_Status, s.Osuser Os_User_Name, s.Sid, p.Spid, s.Serial# Serial_Num, Nvl(s.Username, '[Oracle process]') User_Name, s.Terminal Terminal, s.Program Program, St.VALUE Criteria_Value FROM V$sesstat St, V$session s, V$process p WHERE St.Sid = s.Sid AND St.Statistic# = To_Number('38') AND ('ALL' = 'ALL' OR s.Status = 'ALL') AND p.Addr = s.Paddr ORDER BY St.VALUE DESC, p.Spid ASC, s.Username ASC, s.Osuser ASC 8、 cha kan suo (lock) qing kuang SELECT /*+ RULE */ Ls.Osuser Os_User_Name, Ls.Username User_Name, Decode(Ls.TYPE, 'RW', 'Row wait enqueue lock', 'TM', 'DML enqueue lock', 'TX', 'Transaction enqueue lock', 'UL', 'User supplied lock') Lock_Type, o.Object_Name OBJECT, Decode(Ls.Lmode, 1, NULL, 2, 'Row Share', 3, 'Row Exclusive', 4, 'Share', 5, 'Share Row Exclusive', 6, 'Exclusive', NULL) Lock_Mode, o.Owner, Ls.Sid, Ls.Serial# Serial_Num, Ls.Id1, Ls.Id2 FROM Sys.Dba_Objects o, (SELECT s.Osuser, s.Username, l.TYPE, l.Lmode, s.Sid, s.Serial#, l.Id1, l.Id2 FROM V$session s, V$lock l WHERE s.Sid = l.Sid) Ls WHERE o.Object_Id = Ls.Id1 AND o.Owner <> 'SYS' ORDER BY o.Owner, o.Object_Name 9、 cha kan deng dai (wait) qing kuang SELECT Ws.CLASS, Ws.COUNT COUNT, SUM(Ss.VALUE) Sum_Value FROM V$waitstat Ws, V$sysstat Ss WHERE Ss.NAME IN ('db block gets', 'consistent gets') GROUP BY Ws.CLASS, Ws.COUNT 10、 qiu process/session de zhuang tai SELECT p.Pid, p.Spid, s.Program, s.Sid, s.Serial# FROM V$process p, V$session s WHERE s.Paddr = p.Addr; 11、 qiu shui zu sai le mou ge session(10g) SELECT Sid, Username, Event, Blocking_Session, Seconds_In_Wait, Wait_Time FROM V$session WHERE State IN ('WAITING') AND Wait_Class != 'Idle'; 12、 cha hui hua de zu sai col user_name format a32 SELECT /*+ rule */ Lpad(' ', Decode(l.Xidusn, 0, 3, 0)) || l.Oracle_Username User_Name, o.Owner, o.Object_Name, s.Sid, s.Serial# FROM V$locked_Object l, Dba_Objects o, V$session s WHERE l.Object_Id = o.Object_Id AND l.Session_Id = s.Sid ORDER BY o.Object_Id, Xidusn DESC; col username format a15 col lock_level format a8 col owner format a18 col object_name format a32 SELECT /*+ rule */ s.Username, Decode(l.TYPE, 'tm', 'table lock', 'tx', 'row lock', NULL) Lock_Level, o.Owner, o.Object_Name, s.Sid, s.Serial# FROM V$session s, V$lock l, Dba_Objects o WHERE l.Sid = s.Sid AND l.Id1 = o.Object_Id(+) AND s.Username IS NOT NULL; 13、 qiu deng dai de shi jian ji hui hua xin xi / qiu hui hua de deng dai ji hui hua xin xi SELECT Se.Sid, s.Username, Se.Event, Se.Total_Waits, Se.Time_Waited, Se.Average_Wait FROM V$session s, V$session_Event Se WHERE s.Username IS NOT NULL AND Se.Sid = s.Sid AND s.Status = 'ACTIVE' AND Se.Event NOT LIKE '%SQL*Net%' ORDER BY s.Username; SELECT s.Sid, s.Username, Sw.Event, Sw.Wait_Time, Sw.State, Sw.Seconds_In_Wait FROM V$session s, V$session_Wait Sw WHERE s.Username IS NOT NULL AND Sw.Sid = s.Sid AND Sw.Event NOT LIKE '%SQL*Net%' ORDER BY s.Username; 14、 qiu hui hua deng dai de file_id/block_id col event format a24 col p1text format a12 col p2text format a12 col p3text format a12 SELECT Sid, Event, P1text, P1, P2text, P2, P3text, P3 FROM V$session_Wait WHERE Event NOT LIKE '%SQL%' AND Event NOT LIKE '%rdbms%' AND Event NOT LIKE '%mon%' ORDER BY Event; SELECT NAME, Wait_Time FROM V$latch l WHERE EXISTS (SELECT 1 FROM (SELECT Sid, Event, P1text, P1, P2text, P2, P3text, P3 FROM V$session_Wait WHERE Event NOT LIKE '%SQL%' AND Event NOT LIKE '%rdbms%' AND Event NOT LIKE '%mon%') x WHERE x.P1 = l.Latch#); 15、 qiu hui hua deng dai de dui xiang col owner format a18 col segment_name format a32 col segment_type format a32 SELECT Owner, Segment_Name, Segment_Type FROM Dba_Extents WHERE File_Id = &File_Id AND &Block_Id BETWEEN Block_Id AND Block_Id + Blocks - 1; 16、 qiu chu mou ge jin cheng , bing dui ta jin xing gen zong SELECT s.Sid, s.Serial# FROM V$session s, V$process p WHERE s.Paddr = p.Addr AND p.Spid = &1; Exec Dbms_System.Set_Sql_Trace_In_Session(&1, &2, TRUE); Exec Dbms_System.Set_Sql_Trace_In_Session(&1, &2, FALSE); 17、 qiu dang qian session de gen zong wen jian SELECT P1.VALUE || '/' || P2.VALUE || '_ora_' || p.Spid || '.ora' Filename FROM V$process p, V$session s, V$parameter P1, V$parameter P2 WHERE P1.NAME = 'user_dump_dest' AND P2.NAME = 'instance_name' AND p.Addr = s.Paddr AND s.Audsid = Userenv('SESSIONID') AND p.Background IS NULL AND Instr(p.Program, 'CJQ') = 0; 18、 qiu chu suo ding de dui xiang SELECT Do.Object_Name, Session_Id, Process, Locked_Mode FROM V$locked_Object Lo, Dba_Objects Do WHERE Lo.Object_Id = Do.Object_Id;
󰈣󰈤
  免责声明:本文仅代表作者个人观点,与王朝网络无关。王朝网络登载此文出于传递更多信息之目的,并不意味着赞同其观点或证实其描述,其原创性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
上海柏雅居酒店模拍
清晨美丽的风景
梦清园的靓丽女生
妖艳的食人花(7)
且行且攝------"色"影大連
【做股票的朋友看过来-华尔街的铜牛】
端午节后闲逛
捐款赈灾吴川行 花絮
 
>>返回首页<<
 为你推荐
 
 
 转载本文
 UBB代码 HTML代码
复制到剪贴板...
 
 热帖排行
 
 
 
 
©2005- 王朝网络 版权所有