暂无图片
暂无图片
4
暂无图片
暂无图片
2
暂无图片

Oracle 清理长期不登陆的非系统用户相关SQL

原创 Chencheng 2022-02-14
991

1:找出近一月未登录的数据库用户信息:

11G(11g版本只能通过用户登陆审计表来查询用户最后一次的登陆时间):
alter session set nls_date_format='yyyy-mm-dd hh24:mi';
select username,created from dba_users where username='SYS'; --查找出当前数据库系统用户的创建日期
set line 150
col username for a20
col account_status for a20
col last_login_time for a30
select t1.username,t2.account_status,t1.login_time as last_login_time, created as create_time, round(to_number(sysdate - t1.login_time)) as "not_loggin_day" from
(select username,max(timestamp) as login_time from dba_audit_session where action_name='LOGON' and username in
(select username from dba_users) group by username) t1
left join (select username,account_status,created from dba_users) t2 on t2.username=t1.username
where round(to_number(sysdate - t1.login_time))>30 and to_char(created,'yyyy-mm-dd')!='SYS_CREATED_DATE' order by round(to_number(sysdate - t1.login_time)) desc;

拼接出锁定长期不登陆的非系统用户的sql
select 'alter user '||username||' account lock;' from dba_users where username in (select t1.username from
(select username,max(timestamp) as login_time from dba_audit_session where action_name='LOGON' and username in
(select username from dba_users) group by username) t1
left join (select username,account_status,created from dba_users) t2 on t2.username=t1.username
where round(to_number(sysdate - t1.login_time))>30 and to_char(created,'yyyy-mm-dd')!='2021-07-07');

19C(DB_USERS在19c新增'ORACLE_MAINTAINED','LAST_LOGIN'两个字段,当ORACLE_MAINTAINED=N时表明用户是非系统用户,LAST_LOGIN则可以表面用户最后登陆是时间):

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
set line 250
col username for a15
col account_status for a20
col last_logintime for a30
select username,account_status,to_date(to_char(t1.last_login,'yyyy-mm-dd hh24:mi')) as "LAST_LOGINTIME",created,round(to_number(sysdate-to_date(to_char(t1.last_login,'yyyy-mm-dd hh24:mi')))) not_loggin_day from dba_users t1
where round(to_number(sysdate-to_date(to_char(t1.last_login,'yyyy-mm-dd hh24:mi'))))>30 and ORACLE_MAINTAINED='N' order by not_loggin_day desc;

拼接出锁定长期不登陆的非系统用户的sql
select 'alter user ' ||username||' account lock;' from dba_users where username in (
select username from dba_users t1
where round(to_number(sysdate-to_date(to_char(t1.last_login,'yyyy-mm-dd hh24:mi'))))>30 and ORACLE_MAINTAINED='N' );

查看一个月未登录的用户占用空间大小:
11G:
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
col username for a15
col owner for a20
select username,created from dba_users where username='SYS';


select owner,ROUND(SUM(BYTES)/1024/1024)"USE(M)" from dba_segments
where owner in (
select t1.username from
(select username,max(timestamp) as login_time from dba_audit_session where action_name='LOGON' and username in
(select username from dba_users where to_char(created,'yyyy-mm-dd')!='SYS_CREATED_DATE') group by username) t1
where round(to_number(sysdate - t1.login_time))>30) group by owner order by SUM(BYTES) DESC ;


19c:
col owner for a20
select owner,ROUND(SUM(BYTES)/1024/1024)"USE(M)" from dba_segments
where owner in (
select username from dba_users t1
where round(to_number(sysdate-to_date(to_char(t1.last_login,'yyyy-mm-dd hh24:mi'))))>30 and t1.ORACLE_MAINTAINED='N')
group by owner order by SUM(BYTES) DESC ;


19c:
col owner for a20
select owner,ROUND(SUM(BYTES)/1024/1024)"USE(M)" from dba_segments
where owner in (
select username from dba_users t1
where round(to_number(sysdate-to_date(to_char(t1.last_login,'yyyy-mm-dd hh24:mi'))))>30 and t1.ORACLE_MAINTAINED='N')
group by owner order by SUM(BYTES) DESC ;





查看指定会话最后活动时间:
select username,sid,serial#,status,sql_id,osuser,logon_time,last_call_et/60/60/24 from gv$session where username in
(select t1.username from
(select username,max(timestamp) as login_time from dba_audit_session where action_name='LOGON' and username in
(select username from dba_users where to_char(created,'yyyy-mm-dd')!='SYS_CREATED_DATE' ) group by username) t1
where round(to_number(sysdate - t1.login_time))>30);

select 'alter system kill session '''||sid||','||serial#||''' immediate;' from v$session
where username in
(select t1.username from
(select username,max(timestamp) as login_time from dba_audit_session
where action_name='LOGON' and username in
(select username from dba_users where to_char(created,'yyyy-mm-dd')!='SYS_CREATED_DATE' ) group by username) t1
where round(to_number(sysdate - t1.login_time))>30);




「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论

virvle
暂无图片
2年前
评论
暂无图片 0
2年前
暂无图片 点赞
评论
chencheng
暂无图片
2年前
评论
暂无图片 0
2年前
暂无图片 点赞
评论