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);
评论




