oracle 实用sql记录_oracle执行记录-优快云博客

merge into 被修改更新的表  table1   --目标表 可以用别名
using (SELECT *
        FROM 修改参照表 ) table2      --数据源表 可以是表、视图、子查询
on (table1.关联字段 = table2.关联字段 )   --关联条件
when matched then              --当关联条件成立时 更新，删除，插入的where部分为可选 
  --更新
  update set table1.字段1 = table2.字段1
      ,table1.字段2 = table2.字段2  
  where table1.关联字段 = table2.关联字段
--如果更新和删除同时存在，删除的条件一定要在更新的条件内，否则数据不能删除。
when not matched then      --当关联条件不成立时   
  --插入
  insert (table1.字段1
            ,table1.字段2) 
            values (
                table2.字段1
            ,table2.字段2);

每组内取第一条

SELECT * FROM (
        SELECT
            ROW_NUMBER () OVER (
                PARTITION BY -- 要分组的字段
                ORDER BY -- 每组内要排序的字段 
            ) indexNo, -- 每组内添加序号
            tmp .* 
        FROM 要查询的表 tmp 
        WHERE tmp.id  IS NOT NULL 
    )
WHERE indexNo = 1 -- 取序号 为1的那条数据

删除同一表中的重复数据

DELETE
FROM
    需要去重的表 t1
WHERE
    rowid NOT IN (
        SELECT
            max(rowid)
        FROM
            需要去重的表 t2
        WHERE
            t1.id = t2.id
    )

分组拼接文本listagg

类似mysql 的 groupconcat（）

with temp as(  
select '水果' dataeType,'苹果' dataName,2 indexNo from dual union  
select '水果' dataeType,'橘子' dataName,4 indexNo from dual union  
select '水果' dataeType,'荔枝' dataName,3 indexNo from dual union  
select '水果' dataeType,'香蕉' dataName,5 indexNo from dual union  
select '蔬菜' dataeType,'西红柿' dataName,8 indexNo from dual union  
select '蔬菜' dataeType,'黄瓜' dataName,7 indexNo from dual union  
select '动物' dataeType,'兔子' dataName,9 indexNo from dual   
)  
select dataeType,listagg(dataName,',') within GROUP (order by indexNo)  as Cities
from temp  
group by dataeType

查询被锁表和机器信息

SELECT
    b.owner
    , b.object_name
    , a.session_id
    , a.locked_mode
FROM
    v$locked_object a
    , dba_objects b
WHERE
    b.object_id = a.object_id;
    
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$sqlarea a, v$session s, v$locked_object l
  where l.session_id = s.sid
  and s.prev_sql_addr = a.address
  order by sid, s.serial#;

运维

查询表是否被锁

SELECT sid
FROM v$lock
WHERE id1 = (
    SELECT object_id
    FROM user_objects
    WHERE object_Name = '要查看的表')
    AND request = 0;

查询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;

查看正在执行sql的发起者的发放程序

SELECT A.serial#,OSUSER 电脑登录身份,
PROGRAM 发起请求的程序,
USERNAME 登录系统的用户名,
SCHEMANAME,
B.Cpu_Time 花费cpu的时间,
STATUS,
B.SQL_TEXT 执行的sql
FROM V$SESSION A
LEFT JOIN V$SQL B ON A.SQL_ADDRESS = B.ADDRESS
AND A.SQL_HASH_VALUE = B.HASH_VALUE
ORDER BY b.cpu_time DESC

Oracle查询当前连接的用户和执行的SQL

SELECT
    'alter system kill session ''' || SE.sid || ',' || SE.serial#|| ''';' 杀掉对应执行的sql
    , SE.sid
    , SE.serial#
    , PR.spid
    , SE.STATUS
    , SUBSTR(SE.program, 1, 20) PROG
    , SUBSTR(SE.machine, 1, 30) MACH
    , SQ.sql_text
FROM
    v$SESSION SE
    , v$sqlarea SQ
    , v$process PR
WHERE
    SE.paddr = PR.ADDR (+)
    AND SE.sql_address = SQ.address (+)
    AND schemaname <> 'SYS'
ORDER BY
    SE.sid;

查询oracle数据库占用资源比较大

Select b.USERNAME,
b.SID,
a.SQL_ID,
a.SQL_TEXT,
a.sql_fulltext,
b.EVENT,
a.executions,
trunc(((decode(a.EXECUTIONS,0,0,a.cpu_time / a.executions)) / 10000)) c_time, ---单位零点秒
trunc(((decode(a.EXECUTIONS,0,0,a.ELAPSED_TIME / a.executions)) / 10000)) e_time,
trunc(cpu_time/10000) cpu_time,
trunc(a.ELAPSED_TIME/10000) ELAPSED_TIME ,
a.DISK_READS,
a.BUFFER_GETS,
b.MACHINE,
b.PROGRAM
From v$sqlarea a, v$session b
Where executions > =0
And b.status = 'ACTIVE'
and a.SQL_ID = b.SQL_ID
-- and b.USERNAME='DB_WTDZ'
 and trunc(((a.cpu_time / a.executions) / 1000000))>5

查询oracle 表的字段约束

select * from user_constraints where table_name='要查询的表';

导出表注释

SELECT
    'comment on column '||table_name|| '.'|| column_name|| ' is '|| ''''||comments||''';'
    ,table_name
    ,column_name
    ,comments
FROM
    user_col_comments
WHERE
    table_name = '要查询的表名'
--    AND owner = 'xxxx'

导出用户创建脚本

SELECT
    'create user ' || username || ' identified by  ''change to your password'' default tablespace ' || DEFAULT_TABLESPACE || '  TEMPORARY TABLESPACE ' || TEMPORARY_TABLESPACE || ';'
FROM
    dba_users
WHERE
    username NOT IN (
        'SYS', 'SYSTEM', 'SCOTT', 'DBSNMP', 'CWKLP', 'OA', 'OA88'
    ) ;

查询未提交等待

SELECT
*
FROM
(
SELECT
a.inst_id
, a.sid
, a.serial#
, a.sql_id
, a.event
, a.status
, connect_by_isleaf AS isleaf
, sys_connect_by_path(SID, '<-') tree
, LEVEL AS tree_level
FROM
gv$session a
START WITH
a.blocking_session IS NOT NULL
CONNECT BY
nocycle a.sid = PRIOR a.blocking_session
)
WHERE
isleaf = 1
ORDER BY
tree_level ASC;

SELECT
DISTINCT s1.inst_id AS blocking_inst_id
, s1.username AS blocking_username
, s1.machine AS blocking_machine
, s1.module AS blocking_module
, s1.sid AS blocking_sid
, s1.audsid AS blocking_audsid
, s1.serial# AS blocking_serial#
, c1.sql_text AS blocking_sql_text
, s1.status AS blocking_staus
, s1.event AS blocking_event
, s2.inst_id AS waiting_inst_id
, s2.username AS waiting_username
, s2.machine waiting_machine
, s2.module AS waiting_module
, s2.sid AS waiting_sid
, s2.audsid AS waiting_audsid
, s2.serial# AS waiting_serial#
, c2.sql_text AS waiting_sql_text
, s2.status AS blocking_staus
, s2.event AS waiting_event
FROM
gv$lock l1
, gv$session s1
, gv$lock l2
, gv$session s2
, gv$sqlarea c1
, gv$sqlarea c2
, gv$process b1
, gv$process b2
WHERE
s1.sid = l1.sid
AND s2.sid = l2.sid
AND s1.inst_id = l1.inst_id
AND s2.inst_id = l2.inst_id
AND s1.paddr = b1.addr
AND s2.paddr = b2.addr
AND c1.SQL_ID = s1.PREV_SQL_ID
AND s2.sql_hash_value = c2.hash_value
AND l1.block > 0
AND l2.request > 0
AND l1.id1 = l2.id1
AND l1.id2 = l2.id2
UNION
SELECT
DISTINCT s1.inst_id AS blocking_inst_id
, s1.username AS blocking_username
, s1.machine AS blocking_machine
, s1.module AS blocking_module
, s1.sid AS blocking_sid
, s1.audsid AS blocking_audsid
, s1.serial# AS blocking_serial#
, c1.sql_text AS blocking_sql_text
, s1.status AS blocking_staus
, s1.event AS blocking_event
, s2.inst_id AS waiting_inst_id
, s2.username AS waiting_username
, s2.machine waiting_machine
, s2.module AS waiting_module
, s2.sid AS waiting_sid
, s2.audsid AS waiting_audsid
, s2.serial# AS waiting_serial#
, c2.sql_text AS waiting_sql_text
, s2.status AS blocking_staus
, s2.event AS waiting_event
FROM
gv$lock l1
, gv$session s1
, gv$lock l2
, gv$session s2
, gv$sqlarea c1
, gv$sqlarea c2
, gv$process b1
, gv$process b2
WHERE
s1.sid = l1.sid
AND s2.sid = l2.sid
AND s1.inst_id = l1.inst_id
AND s2.inst_id = l2.inst_id
AND s1.paddr = b1.addr
AND s2.paddr = b2.addr
AND c1.hash_value = s1.sql_hash_value
AND c1.address = s1.sql_address
AND s2.sql_hash_value = c2.hash_value
AND l1.block > 0
AND l2.request > 0
AND l1.id1 = l2.id1
AND l1.id2 = l2.id2