script：dba常用管理脚本收集(三)
ID';
spool off
------------------------------------------------------------------------------------------------------------------------------------------------------
inactive.sql
select count(*) from v$session where status='INACTIVE' and last_call_et >3600 ;
--------------------------------------------------------------------------------------------------------------------------------------------------
mv_refresh.sql
set time on
set timing on
set echo on
set feedback on
spool mv_refresh.log
alter session set db_file_multiblock_read_count=128;
exec dbms_mview.refresh('SA.ICCM_TABLE_BUS_ORG','C');
commit;
exec dbms_mview.refresh('SA.ICCM_TABLE_BUS_ORG','F');
exec dbms_mview.refresh('SA.ICCM_TABLE_BUS_ORG','F');
exec dbms_mview.refresh('SA.ICCM_TABLE_BUS_ORG','F');
exec dbms_mview.refresh('SA.ICCM_TABLE_BUS_ORG','F');
exec dbms_mview.refresh('SA.ICCM_TABLE_BUS_ORG','F');
exec dbms_mview.refresh('SA.ICCM_TABLE_BUS_ORG','F');
exec dbms_mview.refresh('SA.ICCM_TABLE_BUS_ORG','F');
spool off
exit
-------------------------------------------------------------------------------------------------------------------------------------------------------------
pin_objects.sql
Exec Sys.Dbms_Shared_Pool.Keep('owner.object_name','q');-- sequeence
Exec Sys.Dbms_Shared_Pool.keep('owner.object_name','P'); - Procedure
------------------------------------------------------------------------------------------------------------------------------------------------------------
privs.sql--------------> To find the User privileges details by using this sql
set time on;
set timing on;
set echo on;
spool priv.log
declare
cursor c1 is
select * from (
select /*+ Rule */ GRANTEE,GRANTED_ROLE priv from dba_role_privs
union
select /*+ Rule */ GRANTEE,PRIVILEGE priv from dba_sys_privs )
where grantee not in(
'SYS','OUTLN','WMSYS','DBSNMP','SYSTEM','PERFSTAT',
'OEM_MONITOR', 'JAVADEBUGPRIV','AQ_USER_ROLE',
'LOGSTDBY_ADMINISTRATOR','PUBLIC','AQ_ADMINISTRATOR_ROLE','CONNECT',
'DBA','IBSORA','ITBOSS','OEM_MONITOR','ORDPLUGINS','JAVADEBUGPRIV',
'ORDSYS','RECOVERY_CATALOG_OWNER','RESOURCE','oasis',
'XDK','MDSYS','OUTLN','AQ_ADMINISTRATOR_ROLE','CONNECT','CTXSYS','DBSNMP','EXP_FULL_DATABASE','IMP_FULL_DATABASE'
) order by 1
;
cursor c2 (v_priv varchar2,v_grantee varchar2) is
select /*+ Rule */ distinct role, TABLE_NAME ,'OWNER' owner, PRIVILEGE
from role_tab_privs where role=v_priv and PRIVILEGE <>
'SELECT'
union
select /*+ Rule */ GRANTEE,TABLE_NAME,OWNER,PRIVILEGE from dba_tab_privs where grantee =v_grantee and PRIVILEGE <> 'SELECT';
v_grantee varchar2(50);
v_priv varchar2(50);
v_role varchar2(50);
v_Tab varchar2(100);
v_tab_priv varchar2(100);
Begin
--dbms_output.put_line('UserName|Priv/Role|TableName|Grants');
execute immediate ('truncate table oasis.privs');
for x in c1
loop
v_grantee := x.grantee;
v_priv := x.priv;
--dbms_output.put_line(v_grantee||'|'||v_priv);
insert into oasis.privs (grantee,priv) values (x.grantee,x.priv);
--dbms_output.put_line(x.grantee||'|'||x.priv);
for y in c2(x.priv,x.grantee)