SQL 笔记

   用主表里的字段更新从表里的字段

update 从表 c
   set c.字段 = (select z.字段
                          from 主表 z
                         where c.外键 =z.主键)
 where exists (select 1
          from 主表 z
         where c.外键 =z.主键
           and 其他条件...);

 

   查询单个表的所有主外键关系

select a.owner       主键拥有者,
       a.table_name  主键表,
       b.column_name 主键列,
       C.OWNER       外键拥有者,
       c.table_name  外键表,
       d.column_name 外键列
  from user_constraints a
  left join user_cons_columns b on a.constraint_name = b.constraint_name
  left join user_constraints C ON C.R_CONSTRAINT_NAME = a.constraint_name
  left join user_cons_columns d on c.constraint_name = d.constraint_name
 where a.constraint_type = 'P'
   and a.table_name = upper('basic_credibility')
 order by a.table_name;

  

   查询外键约束的列名

select * from user_cons_columns cl where cl.constraint_name = 外键名称;

  

   查找长时间不用的session

SELECT s.username,
       s.status,
       s.machine,
       osuser,
       spid,
       'kill -9 ' || spid UNIX_level_kill,
       'alter system kill session ' || '''' || s.sid || ',' || s.serial# ||
       ''';' Oracle_level_kill,
       TO_CHAR(logon_time, 'dd/mm/yyyy hh24:mi:ss') logon_time,
       last_call_et idle_time,
       TO_CHAR(TRUNC(last_call_et / 3600, 0)) || ' ' || ' HRS ' ||
       TO_CHAR(TRUNC((last_call_et - TRUNC(last_call_et / 3600, 0) * 3600) / 60,
                     0)) || ' MINS' idle_time_hour_minute,
       module
  FROM v$session s, v$process p
 WHERE TYPE = 'USER'
   AND p.addr = s.paddr
   AND status = upper('inactive')
      -- AND SUBSTR (machine, 1, 19) NOT IN ('machine')
   AND last_call_et > 60 * 60 * 2
-- session idle time more than 1 hour
 ORDER BY last_call_et desc;