精华内容
下载资源
问答
  • oracle 查看锁表sql语句(导致锁表的sql)、解锁语句 –查看锁表进程SQL语句1: –被锁对象表、数据库对象表、数据session表关联来获取被锁对象对应的是那个session; select sess.sid, sess.serial#, lo....

    –查看锁表进程SQL语句1:

    –被锁对象表、数据库对象表、数据session表关联来获取被锁对象对应的是那个session;
    select sess.sid,
    sess.serial#,
    lo.oracle_username,
    lo.os_user_name,
    ao.object_name,
    lo.locked_mode
    from v l o c k e d o b j e c t l o , d b a o b j e c t s a o , v locked_object lo, dba_objects ao, v lockedobjectlo,dbaobjectsao,vsession sess
    where ao.object_id = lo.object_id and lo.session_id = sess.sid;

    –查看锁表进程SQL语句2:
    select * from v s e s s i o n t 1 , v session t1, v sessiont1,vlocked_object t2 where t1.sid = t2.SESSION_ID;

    –查看导致锁表的sql语句是那一条

    select l.session_id sid,
    s.serial#,
    l.locked_mode,
    l.oracle_username,
    s.user#,
    l.os_user_name,
    s.machine,
    s.terminal,
    a.sql_text,
    a.action
    from v s q l a r e a a , v sqlarea a, v sqlareaa,vsession s, v$locked_object l
    where l.session_id = s.sid
    and s.prev_sql_addr = a.address
    order by sid, s.serial#;

    –杀掉锁表进程:

    –通过上面的查询获取SID和serial#,替换下面的x,y,就可以解除被锁的状态
    alter system kill session ‘x,y’;

    展开全文
  • oracle 查询sid 运行的sql语句

    千次阅读 2012-10-16 10:55:21
    这是当前运行的sql语句 select sid,  v$session.username 用户名,  last_call_et 持续时间,  status 状态,  LOCKWAIT 等待锁,  machine 用户电脑名,  logon_time 开始登入时间,  sql_text

    这是当前运行的sql语句

    select sid,
            v$session.username 用户名,
            last_call_et 持续时间,
            status 状态,
            LOCKWAIT 等待锁,
            machine 用户电脑名,
            logon_time 开始登入时间,
            sql_text
      from v$session, v$process, v$sqlarea
    where paddr = addr
        and sql_hash_value = hash_value 
        and status = 'ACTIVE'
        and v$session.username is not null
    order by last_call_et desc;
    ------------------------------------------------------------------------------------

    根据sid查询已经执行过的sql

    select sql_text from v$sqlarea a,v$session b where a.SQL_ID=b.PREV_SQL_ID and b.SID=&sid;
    --------------------------------------------------------------------------------------

    根据sid查询当前执行的sql

    select sql_text from v$sqlarea where (hash_value,address)=(select sql_hash_value,sql_address from v$session where sid=&sid);
    -----------------------------------------------------------------------------------------

    查询oracle 锁表情况

    select sess.sid,
         sess.serial#,
         lo.oracle_username,
         lo.os_user_name,
         ao.object_name,
         lo.locked_mode
         from v$locked_object lo,
         dba_objects ao,
         v$session sess
    where ao.object_id = lo.object_id and lo.session_id = sess.sid

    ---------------------------------------------------------------------------------------------

    杀掉锁表的语句

    如有記錄則表示有lock,記錄下SID和serial# ,將記錄的ID替換下面的738,1429,即可解除LOCK
    alter system kill session '1793,25320';
    展开全文
  • [size=large]--查询Oracle正在执行的sql语句及执行该语句的用户 SELECT b.sid oracleID, b.username 登录Oracle用户名, b.serial#, spid 操作系统ID, paddr, sql_text 正在执行的SQL, b.machine 计算.....
    [size=large]--查询Oracle正在执行的sql语句及执行该语句的用户
    
    SELECT b.sid oracleID,
    b.username 登录Oracle用户名,
    b.serial#,
    spid 操作系统ID,
    paddr,
    sql_text 正在执行的SQL,
    b.machine 计算机名
    FROM v$process a, v$session b, v$sqlarea c
    WHERE a.addr = b.paddr
    AND b.sql_hash_value = c.hash_value;

    --查出oracle当前的被锁对象
    SELECT l.session_id sid,
    s.serial#,
    l.locked_mode 锁模式,
    l.oracle_username 登录用户,
    l.os_user_name 登录机器用户名,
    s.machine 机器名,
    s.terminal 终端用户名,
    o.object_name 被锁对象名,
    s.logon_time 登录数据库时间
    FROM v$locked_object l, all_objects o, v$session s
    WHERE l.object_id = o.object_id
    AND l.session_id = s.sid
    ORDER BY sid, s.serial#;
    --kill掉当前的锁对象可以为
    alter system kill session 'sid, s.serial#'; --sid,s.serial# 需替换为对应进程的sid,s.serial#。

    --查找前十条性能差的sql.
    SELECT *
    FROM (select PARSING_USER_ID,
    EXECUTIONS,
    SORTS,
    COMMAND_TYPE,
    DISK_READS,
    sql_text
    FROM v$sqlarea
    order BY disk_reads DESC)
    where ROWNUM < 10;[/size]
    展开全文
  • ORACLE SQL DEVELOPER 优化SQL语句

    千次阅读 2016-11-08 14:39:05
    利用oracle sql developer 工具轻松实现SQL优化。

    SQL Tuning Advisor in Oracle SQL Developer 3.2

     

    Overview

    1. Purpose

      This tutorial shows you how to use the SQL Tuning Advisor feature in Oracle SQL Developer 3.2.

      Time to Complete

      Approximately 40 minutes

      Introduction

      The SQL Tuning Advisor analyzes high-volume SQL statements and offers tuning recommendations. It takes one or more SQL statements as an input and invokes the Automatic Tuning Optimizer to perform SQL tuning on the statements. It can run against any given SQL statement. The SQL Tuning Advisor provides advice in the form of precise SQL actions for tuning the SQL statements along with their expected performance benefits. The recommendation or advice provided relates to the collection of statistics on objects, creation of new indexes, restructuring of the SQL statement, or creation of a SQL profile. You can choose to accept the recommendation to complete the tuning of the SQL statements.

      Oracle Database can automatically tune SQL statements by identifying problematic SQL statements and implementing tuning recommendations using the SQL Tuning Advisor. You can also run the SQL Tuning Advisor selectively on a single or a set of SQL statements that have been identified as problematic.

      In this tutorial, you learn how to run and review the recommendations of the SQL Tuning Advisor.

      Note: Tuning Advisor is part of the Tuning Pack, one of the Oracle management packs and is available for purchase with Enterprise Edition. For more information see The Oracle Technology Network or the online documentation.

      Hardware and Software Requirements

      The following is a list of hardware and software requirements:

      • Oracle Database 11g Enterprise Edition with access to the Tuning and Diagnostic management packs and with the sample schema installed.
      • Oracle SQL Developer 3.2.

      Prerequisites

      • Download Oracle SQL Developer 3.2 here.

      Note: For best results, use Firefox or Chrome browsers to view this tutorial.

     

    Creating a Database Connection

    1. The first step to managing database objects using Oracle SQL Developer 3.2 is to create a database connection.

      Perform the following steps to create a database connection:

      Note: If you already have database connections for HR and SYSTEM, you do not need to perform the following steps. You can move toProviding Privileges to the Hr Usertopic.

      If you have installed the SQL Developer icon on your desktop, click the icon to start your SQL Developer and move to Step 4. If you do not have the icon located on your desktop, perform the following steps to create a shortcut to launch SQL Developer 3.2 from your desktop.

      Open the directory where the SQL Developer 3.2 is located, right-click sqldeveloper.exe (on Windows) or sqldeveloper.sh (on Linux) and select Send to > Desktop (create shortcut).

      On the desktop, you will find an icon named Shortcut to sqldeveloper.exe. Double-click the icon to open SQL Developer 3.2.

      Note: To rename it, select the icon and then press F2 and enter a new name.


      Your Oracle SQL Developer opens up.

      In the Connections navigator, right-click Connections and select New Connection.

      The New / Select Database Connection dialog opens. Enter the connection details as follows and clickTest.

      Connection Name: system

      Username: system

      Password: <your_password > (Select Save Password)

      Hostname: localhost

      SID: <your_own_SID>


      Check for the status of the connection on the left-bottom side(above the Help button). It should readSuccess. ClickSave and then click Connect.

      In the Connections navigator, to create a new connection to the hr schema, right-clickConnections and selectNew Connection.

      The New / Select Database Connection dialog opens. Enter the connection details as follows and clickTest.

      Connection Name: hr

      Username: hr

      Password: <your_password > (Select Save Password)

      Hostname: localhost

      SID: <your_own_SID>

      Check for the status of the connection on the left-bottom side(above the Help button). It should readSuccess. ClickSave and then click Connect.

     

    Providing Privileges and Removing the Existing Statistics on the HR User

    1. A user requires certain privileges to run the SQL Tuning Advisor. Also, in order to collect and manage statistics on the HR schema, the existing statistics need to be cleared. Below are the steps to grant SQL Tuning Advisor privileges and to remove the existing statistics on the hr user.

      Click SQL Worksheet  and select system user.


      To grant privileges to the hr user to run the SQL Tuning Advisor, enter the following lines of code. Click Run Script.
      grant advisor to hr;
      grant administer sql tuning set to hr;


      The output for the statements is displayed.


      The Oracle database allows you to collect statistics of many different kinds in order to improve performance. To illustrate some of the features the SQL Tuning Advisor offers, clear the existing statistics from the HR schema.

      To delete the schema statistics, enter the following line of code.

      exec DBMS_STATS.DELETE_SCHEMA_STATS ('hr');

      Select the statement and click Run Statement

      With the DBMS_STATS package you can view and modify optimizer statistics gathered for database objects.TheDELETE_SCHEMA_STATS procedure deletes statistics for an entire schema.


      The outputs for the statements are displayed.


     

    Running the SQL Tuning Advisor on a SQL statement

    1. In this topic, you run the SQL Tuning Advisor on a SQL statement. Four types of analysis are performed by the SQL Tuning Advisor on the SQL statement.
      All the recommendations are displayed in the Overview. You can also view each recommendation individually.

      Open the SQL Worksheet for the hr user by clicking SQL Worksheet.


      Enter the following SQL statement in the worksheet.
      select sum(e.salary), avg(e.salary), count(1), e.department_id from departments d, employees e group by e.department_id order by e.department_id;

      Select the SQL statement and click SQL Tuning Advisor .


      The SQL Tuning Advisor output appears.


      In the left navigator, click Statistics. In this analysis, objects with stale or missing statistics are identified and appropriate recommendations are made to remedy the problem.


      In the left navigator, click SQL Profile. Here, the SQL Tuning Advisor recommends to improve the execution plan by the generation of a SQL Profile.


      Click the Detail tabbed page to view the SQL Profile Finding.


      In the left navigator, click Indexes. This recommends whether the SQL statement might benefit from an index. If necessary, new indexes that can significantly enhance query performances are identified and recommended.


      Click the Overview tabbed page. In this case, there are no index recommendations.


      In the left navigator, click Restructure SQL. In this analysis, relevant suggestions are made the restructure selected SQL statements for improved performance.



     

    Implementing SQL Tuning Advisor Recommendations

    1. You can implement the SQL Tuning Advisor recommendation feature. This will enable you to update the statistics in hr schema. Perform the following steps to implement the SQL Tuning Advisor recommendations:

      In the Connections navigator, right-click hr and select Gather Schema Statistics....


      In Gather Schema Statistics, select Estimate Percent as100 from the drop-down list so that all rows in each table are read. This ensures that the statistics are as accurate as possible.


      Click Apply.


      A confirmation message appears. Click OK.


      To run the SQL Tuning Advisor on the SQL statement again, select the SQL statement and click SQL Tuning Advisor.


      The SQL Tuning Advisor output appears. By gathering statistics, the Statistics and SQL Profile advice is now removed.


      In the left navigator, click each of the SQL Tuning Advisor Implement Type to check if all the recommendations have been implemented.


      Note the issues reported to you:

      Note the issues reported to you:

      Note that the Restructure SQL recommendation to remove an unused table remains.

      Remove the "departments" table in the SQL statement and click SQL Advisor.

      select sum(e.salary), avg(e.salary), count(1), e.department_id from employees e
      group by e.department_id order by e.department_id;


      The output appears. All of the advice recommendations have been removed.



     

    Summary


    1. In this tutorial, you have learned how to:

      • Create a Database Connection
      • Provide Privileges to the HR User
      • Run the SQL Tuning Advisor on a SQL statement
      • Implement SQL Tuning Advisor Recommendations

      Credits

      • Lead Curriculum Developer: Dimpi Sarmah, Sharon Stephen
      • Other Contributors: Ashley Chen, Swarnapriya Shridhar, Nancy Greenberg


    展开全文
  • 查询Oracle正在执行的sql语句

    万次阅读 2011-06-28 14:31:00
    --查询Oracle正在执行的sql语句及执行该语句的用户 SELECT b.sid oracleID, b.username 登录Oracle用户名, b.serial#, spid 操作系统ID, paddr, sql_text 正在执行的SQL, b.machine 计算机名 FROM v$proces
  •  Oracle诊断或调优经常需要做的就是查看SQL语句的执行计划,很多时候我们需要得到sql语句在不同场景、不同时间段的执行计划。     一,通过explain plan命令获得sql语句的执行计划。  explain plan的命令...
  • SQL> select sid,event,p1,p1text from v$session_wait; 如果发现存在大量db file scattered read及db file sequential read等待.... 使用下面的SQL语句可以得到指定SID的sql内容 SELECT sql_text FROM v$s
  • 原文出处:http://blog.csdn.net/jlds123/article/details/6572559 ----------------------- --查询Oracle正在执行的sql语句及执行该语句的用户 [sql] view plain copy   SELECT b.sid oracleID,
  • --查询Oracle正在执行的sql语句及执行该语句的用户 SELECT b.sid oracleID, b.username 登录Oracle用户名, b.serial#, spid 操作系统ID, paddr, sql_text 正在执行的SQL, b.machine 计算机名 FR
  • 查询oracle正在执行的sql语句

    千次阅读 2014-03-12 15:39:23
    1、显示oracle最近执行的sql信息 SELECT b.sid oracleID,   --b.username 登录Oracle用户名,   b.serial#,   spid 操作系统ID,   --paddr,   --c.LAST_LOAD_TIME,  -- b.SID,  --
  • oracle笔记一(sql语句方面)

    万次阅读 2011-07-04 09:23:50
    oracle笔记一(sql语句方面) 一.sql语句--================================================1.增加主键 alter table TABLE_NAME add constraint KEY_NAM
  • 一、oracle中查找某段时间执行的操作记录 select sql_text, module, first_load_time from v$sqlarea where first_load_time > '2019-02-02/02:02:02' and first_load_time < '2019-02-02/02:02:02' or...
  • 可以通过以下sql查询Oracle正在执行的sql语句及执行该语句的用户: SELECT b.sid oracleID, b.username 登录Oracle用户名, b.serial#, spid 操作系统ID, paddr, sql_text 正在执行的SQL, ...
  • Oracle高资源消耗SQL语句定位

    千次阅读 2015-05-29 15:31:32
    Oracle SQL语句资源消耗监控最常用的系统视图有v$sql、v$sqlarea、v$sqltext和v$session。本文我们先了解这些视图的作用与区别,然后了解如何定位高资源消耗SQL语句,最后再了解一下各视图字段具体含义。 相关...
  • 把自己用过的一些oracle监控语句分享给大家,希望能给大家带来帮助
  • oracle中的常用sql语句

    千次阅读 2018-07-08 00:01:34
    常用SQL查询: 1、查看表空间的名称及大小 select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size from dba_tablespaces t, dba_data_files d where t.tablespace_name = d.tablespace_name group ...
  • 监听/监测oracle数据库执行的SQL语句

    千次阅读 2016-11-14 09:54:40
    Select a.Sid, a.SERIAL#, a.status, a.USERNAME, –哪个用户运行的SQL d.SPID 进程号, b.sql_text SQL内容, a.MACHINE 计算机名称, a.MODULE 运行方式, to_ch
  •  在诊断数据库系统性能的过程中,总会涉及到跟踪效率低下的sql语句Oracle数据库10g包含一种新的实用程序trcsess,它可以让 您基于会话ID或模块名称之类的条件,有选择地从大量跟踪文件中抽取出跟踪数据,...
  • 在自动化测试过程中有时候我们需要自动跑一些sql脚本来维护自动化测试数据库,那么这时候我们可以选择批处理命令来执行sql语句。 环境准备: 执行sql服务器需要安装有oracle客户端。 步骤如下: 1、新建一个sql...
  • 介绍如何终止 Oracle 连接会话/进程和运行中的 SQL 语句,包括使用 GV$SESSION 和 GV$PROCESS 等视图查看会话和对应的系统进程信息,然后使用 ALTER SYSTEM KILL SESSION 或者 ALTER SYSTEM DISCONNECT SESSION 命令...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 39,804
精华内容 15,921
关键字:

oracle通过sid查看sql语句