oracle 实用sql记录

zhulijunyou

已于 2023-11-28 14:13:34 修改

阅读量50

点赞数

文章标签： oracle sql 数据库

于 2023-11-09 13:51:44 首次发布

本文链接：https://blog.csdn.net/zhulijunyou/article/details/134309755

版权

oracle 实用sql记录

查询Oracle正在执行的sql语句及执行该语句的用户

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

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

查询

存在则更新否则插入

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'
    ) ;