日常运维Script

#####################TEMP_SQL#############################

SET LINESIZE 120

SET PAUSE OFF

COLUMN username FORMAT A10
COLUMN sql FORMAT A100
COLUMN sidinfo FORMAT A12
COLUMN size_m FORMAT A10
COLUMN total_m FORMAT A10
COLUMN used_m FORMAT A10
COLUMN free_m FORMAT A10
COLUMN tablespace_name FORMAT A15
COLUMN users FORMAT 999

select  a.tablespace_name,
        a.CURRENT_USERS users,
        ((select sum(blocks) from dba_temp_files where tablespace_name='TEMP')*b.value)/1024/1024 || 'm' size_m,
        (a.TOTAL_BLOCKS*b.value)/1024/1024 || 'm' total_m,
        (a.USED_BLOCKS*b.value)/1024/1024 || 'm' used_m,
        (a.FREE_BLOCKS*b.value)/1024/1024 || 'm' free_m,
        (
        select round((s.tot_used_blocks/f.total_blocks)*100) || '%'
        from (  select sum(used_blocks) tot_used_blocks
                from v$sort_segment
                where tablespace_name='TEMP') s,
                (select sum(blocks) total_blocks
                from dba_temp_files
                where tablespace_name='TEMP') f
        ) "used %"
from v$sort_segment a,
     v$parameter b
where b.name = 'db_block_size';

select s.sid||','||s.serial# sidinfo, s.username, s.module, u.extents, u.blocks, s.logon_time, s.status, s.program,
(select sql_text from v$sqltext where address = s.sql_address and piece=0) sql
from v$session s, v$sort_usage u
where s.saddr = u.session_addr
and u.contents = 'TEMPORARY'
order by u.blocks, s.username;

###################UNDO_SQL#############################

SELECT TABLESPACE_NAME, ROUND ( (USED / TOTAL_SIZE) * 100, 2) USED_RATE
  FROM (SELECT A.TABLESPACE_NAME, TOTAL_SIZE, USED
          FROM (  SELECT TABLESPACE_NAME,
                         ROUND (SUM (BYTES) / 1024 / 1024 / 1024, 2) TOTAL_SIZE
                    FROM DBA_DATA_FILES
                   WHERE 1 = 1 AND TABLESPACE_NAME LIKE 'UNDOTBS%'
                GROUP BY TABLESPACE_NAME) A,
               (  SELECT TABLESPACE_NAME,
                         ROUND (SUM (BYTES) / 1024 / 1024 / 1024, 2) USED
                    FROM DBA_UNDO_EXTENTS
                   WHERE 1 = 1 AND STATUS = 'UNEXPIRED'
                GROUP BY TABLESPACE_NAME) B
         WHERE 1 = 1 AND A.TABLESPACE_NAME = B.TABLESPACE_NAME);

##################TBS_USAGE_SQL###################

select x.tablespace_name,y.bytes/1024/1024 total_size_mb,x.bytes/1024/1024 free_size_mb,(y.bytes-x.bytes)/1024/1024 use_size_mb,round((y.bytes-x.bytes)/y.bytes,4)*100 usage
from
(select tablespace_name,sum(bytes) bytes from dba_free_space group by tablespace_name) x,
(select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) y
where x.tablespace_name=y.tablespace_name
order by  5 desc;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31401608/viewspace-2149920/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31401608/viewspace-2149920/