oracle中有关空间使用率、空间回收脚本汇总

--查询表空间使用率

select total.tablespace_name,

round(total.size_GB,2) as Total_GB,

round(total.size_GB-free.size_GB,2) as Used_GB,

round((1-free.size_GB/total.size_GB)*100,2)||'%' as Used_Pct

from 

(select tablespace_name,sum(bytes)/1024/1024/1024 as size_GB

from dba_free_space 

group by tablespace_name) free ,

(select tablespace_name,sum(bytes)/1024/1024/1024 as size_GB

from dba_data_files

group by tablespace_name) total

where total.tablespace_name=free.tablespace_name

order by Used_Pct desc

--查询表大小排名

select a.owner,a.segment_name,a.size_gb from  (select  t.owner,t.segment_name,sum(t.bytes/1024/1024/1024) size_gb from dba_segments t where t.owner not in ('SYS','SYSTEM','SYSMAN','XDB','OUTLN','DBSNMP','WMSYS','EXFSYS','DBADMIN')

and t.segment_type not in ('INDEX SUBPARTITION','INDEX','INDEX PARTITION')

group by t.owner,t.segment_name ) a

order by a.size_gb desc 

--查询索引大小排名

select a.owner,a.segment_name,a.size_gb from  (select  t.owner,t.segment_name,sum(t.bytes/1024/1024/1024) size_gb from dba_segments t where t.owner not in ('SYS','SYSTEM','SYSMAN','XDB','OUTLN','DBSNMP','WMSYS','EXFSYS','DBADMIN')

and t.segment_type not in ('TABLE SUBPARTITION','TABLE','TABLE PARTITION')

group by t.owner,t.segment_name ) a

order by a.size_gb desc

--收缩数据文件

col name for a40  

col resizecmd for a80  

  

select a.file#,a.name,a.bytes/1024/1024 CurrentMB,  

       ceil(HWM * a.block_size)/1024/1024 ResizeTo,  

       (a.bytes - HWM * a.block_size)/1024/1024 ReleaseMB,  

       'alter database datafile '''||a.name||''' resize '||  

       ceil(HWM * a.block_size/1024/1024) || 'M;' ResizeCMD  

from v$datafile a,  

     (select file_id,max(block_id+blocks-1) HWM  

       from dba_extents  

       group by file_id) b  

where a.file# = b.file_id(+)  

and (a.bytes - HWM *block_size)>0  

order by 5  

--收缩临时文件

select 'alter database tempfile '''||a.name ||''' reize '||b.siz||'M;' from v$tempfile a,  

     (select tmsize.maxblk*bk.value/1024/1024 siz from  

         (select nvl(max(segblk#),128) maxblk  from v$sort_usage) tmsize,  

         (select value From v$parameter where name = 'db_block_size') bk) b

--收缩临时表空间

ALTER TABLESPACE TEMP SHRINK SPACE

--创建临时表空间组

ALTER TABLESPACE TEMP TABLESPACE GROUP TEMP_GROUP

--收縮undo表空間

create undo TABLESPACE undotbs2

alter system set undo_tablespace='undotbs2' scope=both 

drop TABLESPACE undotbs1 

--收缩表段

alter table t1 enable row movement; 

alter table t1 shrink space cascade;

alter table t1 disable row movement; 

--表段空间使用情况排名

select t.owner,

       t.table_name,

       t.tablespace_name,

       t.last_analyzed,

       round(t.blocks * 8 / 1024, 2) as allocated_space_mb,

       round(t.avg_row_len * t.num_rows / 1024 / 1024, 2) as used_space_mb,

       round(t.blocks * 8 * t.pct_free / 100 / 1024, 2) as reserved_space_mb,

       round(t.blocks * 8 / 1024 - t.avg_row_len * t.num_rows / 1024 / 1024,

             2) as wasted_space_mb

  from dba_tables t

 order by 8 desc nulls last

--查询索引碎片率、高度(碎片率超过30%,高度大于3建议重建索引)

 analyze index index_name validate structure

 select t.height,t.name,t.del_lf_rows_len,t.lf_rows_len,t.(del_lf_rows_len/lf_rows_len)*100 as index_fragment_pct from index_stats t where t.name='index_name'

--查询临时表空间使用率

--11g

select tablespace_name,round(free_space/1024/1024/1024,2) "free(GB)",round(tablespace_size/1024/1024/1024,2) "total(GB)",round(nvl(free_space,0)*100/tablespace_size,3) "Free percent"

from dba_temp_free_space

--10g

SELECT temp_used.tablespace_name,total - used as "Free",total as "Total",round(nvl(total - used, 0) * 100 / total, 3) "Free percent"

FROM 

(SELECT tablespace_name, SUM(bytes_used)/1024/1024 used

FROM GV$TEMP_SPACE_HEADER

GROUP BY tablespace_name) temp_used,

(SELECT tablespace_name, SUM(bytes)/1024/1024 total

FROM dba_temp_files

GROUP BY tablespace_name) temp_total

WHERE temp_used.tablespace_name = temp_total.tablespace_name

--查看消耗临时表空间的会话以及sql

SELECT S.sid,

SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used,

        S.serial# sid_serial,

        s.INST_ID,

        S.username,

        S.osuser,

        P.spid,

        S.module,

        P.program,

        T.tablespace,

        q.sql_text,

        nvl(S.sql_id,S.PREV_SQL_ID) sql_id

       

   FROM gv$sort_usage T,

        gv$session S,

        dba_tablespaces TBS,

        gv$process P,

        gv$sql    q

  WHERE     T.session_addr = S.saddr

        AND t.INST_ID = S.INST_ID

        AND S.paddr = P.addr

        AND S.INST_ID = P.INST_ID

        AND nvl(S.sql_id,S.PREV_SQL_ID)=q.sql_id

        AND T.tablespace = TBS.tablespace_name

GROUP BY S.sid,

        S.serial#,

        s.INST_ID,

        S.username,

        S.osuser,

        P.spid,

        S.module,

        P.program,

        TBS.block_size,

        T.tablespace,

        q.sql_text,

        nvl(S.sql_id,S.PREV_SQL_ID)

order by 2 desc

--通过ash 查询消耗临时表空间的会话、sql

select t.SAMPLE_TIME,t.SESSION_ID,t.SQL_ID,t.TEMP_SPACE_ALLOCATED from gv$active_session_history t order by  t.TEMP_SPACE_ALLOCATED desc nulls last 

--查询消耗回滚表空间的会话、sql

select distinct s.machine,s.program,s.sid,round(t.used_ublk*8/1024,2) undo_MB,used_urec undo_records,s.status,l.sql_text,l.sql_id

from v$transaction t,gv$session s,v$sqlstats l

where t.ses_addr=s.saddr

and s.sql_id=l.sql_id(+)order by undo_MB;