用主表里的字段更新从表里的字段
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;