script：dba常用管理脚本收集(五)
------------------------------------------------------------------
sync.sql
set time on
set timing on
set echo on
set feedback on
set linesize 1000
SELECT s.sid, s.serial#, s.username,
s.program,i.block_changes FROM v$session s,
v$sess_io i WHERE s.sid = i.sid and s.status='ACTIVE' and i.block_changes>10000
ORDER BY 5,1, 2, 3, 4;
----------------------------------------------------------------------------------------------------------------------------------------------------------
cpu_cost.sql
SELECT /*+ Rule */ 'SID : '||sid||chr(10)||
'Serial# '||serial#||chr(10)||
'Username : '||username||chr(10)||
'Logon Time : '||logon_time||chr(10)||
'Last Called (in Secs) :'||last_call_et||chr(10)||
'Status : '||status||chr(10)||
'SQL Address : '||address||chr(10)||
'HASH Value : '||hash_value||chr(10)||
'Buffer Gets : '||buffer_gets||chr(10)||
'Executions : '||executions||chr(10)||
'Buffer Gets / Execution :'||buffer_gets/executions||chr(10)||
'Text of SQL : '||sql_text
from ( select sid, serial#, username, logon_time, last_call_et, address, hash_value, buffer_gets, executions,
buffer_gets/executions,sql_text, status
FROM v$sqlarea, v$session
WHERE buffer_gets > 50000 and executions>0
and sql_address = address
and sql_hash_value = hash_value
order by 8
)
/
-----------------------------------------------------------------------------------------------------------------------------------------------------------
db_growth.sql
SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY OFF
COLUMN month FORMAT a7 HEADING 'Month'
COLUMN growth FORMAT 999,999,
999,999,999 HEADING 'Growth (Bytes)'
BREAK ON report
COMPUTE SUM OF growth ON report
SELECT
TO_CHAR(creation_time, 'RRRR-MM') month
, SUM(bytes)/1024/1024/1024 growth
FROM sys.v_$datafile
GROUP BY TO_CHAR(creation_time, 'RRRR-MM')
ORDER BY TO_CHAR(creation_time, 'RRRR-MM');
-------------------------------------------------------------------------------------------------------------------------------------------------------
db_size.sql
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
round(free.poo / 1024 / 1024 / 1024) || ' GB' "Used space"
, round(free.poo / 1024 / 1024 / 1024) || ' GB' "Free space"
from (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
, (select sum(bytes) as poo
from dba_free_space) free
group by free.poo
/
-------------------------------------------------------------------------------------------------------------------------------------------------------
compile.sql
set echo on
set time on
set timing on
spool compile_procedure.sql
Select 'Alter Procedure '||owner||'.'||object_name ||' compile ;' from dba_objects where object_type='PROCEDURE'
and owner in ('SA') and status='INVALID';
Select 'Alter PACKAGE '||owner||'.'||object_name ||' compile ;' from dba_objects where object_type='PACKAGE' and owner
in ('SA') and status='INVALID';
Select 'Alter TRIGGER '||owner||'.'||object_name ||' compile ;' from dba_objects where object_type='TRIGGER' and owner
in ('SA') and status='INVAL