from dba_users where account_status='OPEN' order by 1,2;
set heading off
set feedback off
select '##check user privilege and role' from dual;
set heading on
set feedback on
select owner,table_name,grantor,privilege from dba_tab_privs where grantee in
(select username from exp_check);
select grantee,privilege from dba_sys_privs where grantee in (select username
from exp_check) union select grantee,privilege from dba_sys_privs where grantee
in (select granted_role from dba_role_privs where grantee in (select username
from exp_check));
set heading off
set feedback off
select '##check user index' from dual;
set heading on
set feedback on
select owner,count(*) from dba_indexes where owner in (select username from
exp_check) group by owner order by 1,2;
set heading off
set feedback off
select '##check user lob' from dual;
set heading on
set feedback on
select l.owner,l.table_name,l.segment_name,sum(s.Bytes)/1024/1024/1024 GB from
dba_lobs l left join dba_segments s on l.segment_name=s.segment_name where
l.owner in (select username from exp_check) group by
l.owner,l.table_name,l.segment_name order by 4;
set heading off
set feedback off
select '##check user object count' from dual;
set heading on
set feedback on
select owner,object_type,status,count(*) from dba_objects where owner in
(select username from exp_check) and object_type !='LOB' group by
owner,object_type,status order by 1,2;
set heading off
set feedback off
select '##check user object in system/sysaux' from dual;
set heading on
set feedback on
select owner,segment_name,tablespace_name from dba_segments where owner in
(select username from exp_check) and tablespace_name in ('system','sysaux');
/*
if user segment data in sys tablespace
table:alter table tab_name move tablespace tbs_name;
index:alter index index_name rebuild tablespace tbs_name;
*/
set heading off
set feedback off
select '##check invalid object' from dual;
set heading on
set feedback on
select owner,object_type,count(*) from dba_objects
where owner in (select username from exp_check)
and status='INVALID'
and object_type not in ('INDEX','LOB') group by owner,object_type order by
文档被以下合辑收录
评论