空间使用

1.表空间使用情况

SQL> grant select on dba_free_space to xag;
SQL> grant select on dba_data_files to xag;
SQL> grant select on v_$temp_extent_pool to xag;
SQL> grant select on v_$temp_space_header to xag;
SQL> grant select on dba_temp_files to xag;


create or replace view v_xag_tablespace_stat as
select tablespace_type,tablespace_name,autoext
       ,max_mb,used_mb,round(100 * used_mb / max_mb) pct_used
from
(
 select 'USER' as tablespace_type,a.tablespace_name as tablespace_name,autoext
       ,a.bytes_alloc - nvl(b.bytes_free, 0) as used_mb
       ,a.maxbytes as max_mb
 from
 (
   select f.tablespace_name,f.autoextensible as autoext
          ,trunc(sum(f.bytes)/power(2,20)) as bytes_alloc
          ,trunc(sum(decode(f.autoextensible,'YES',f.maxbytes,'NO',f.bytes))/power(2,20)) as  maxbytes
   from dba_data_files f
   group by tablespace_name,autoextensible
 ) a left join
 (
   select f.tablespace_name,trunc(sum(f.bytes)/power(2,20)) as bytes_free
   from dba_free_space f
   group by tablespace_name
 ) b on (a.tablespace_name=b.tablespace_name)
 union all
 select 'TEMP' as tablespace_type,h.tablespace_name as tablespace_name,f.autoextensible as autoext
       ,trunc(sum(nvl(p.bytes_used, 0)) / power(2, 20)) used_mb
       ,trunc(sum(decode(f.autoextensible,'YES',f.maxbytes,'NO',f.bytes))/power(2, 20)) max_mb
 from v$temp_space_header h left join v$temp_extent_pool p on(p.file_id=h.file_id and p.tablespace_name=h.tablespace_name)
     join dba_temp_files f on(f.file_id = h.file_id and f.tablespace_name = h.tablespace_name)
 group by h.tablespace_name,autoextensible
);

2.闪回区使用情况

SQL> grant select on v_$recovery_file_dest to xag;

create or replace view v_xag_fra as
select t.NAME
,trunc(t.SPACE_LIMIT/(1024*1024*1024)) as SPACE_LIMIT
,trunc(t.SPACE_USED/(1024*1024*1024)) as SPACE_USED
,trunc(100*SPACE_USED/SPACE_LIMIT) as PCT_USED
,trunc(t.SPACE_RECLAIMABLE/(1024*1024*1024)) as SPACE_RECLAIMABLE
,t."NUMBER_OF_FILES"
from sys.v_$recovery_file_dest t;

3.數據文件（磁盤）讀寫統計

select name,phyrds,phywrts,readtim,writetim
from v$filestat a,v$datafile b
where a.file#=b.file#
order by readtim desc

1   /u02/oradata/MPPAY/users_mpop_03.dbf    759630038   73441631    12633838    1269690
2   /u02/oradata/MPPAY/users_mpop.dbf   667802985   65248802    12616519    1219589
3   /u02/oradata/MPPAY/users_mpop_02.dbf    638656138   62120112    11754107    1059727
4   /u02/oradata/MPPAY/users_mpop_temp.dbf  614929104   59710400    11520040    1053707
5   /u02/oradata/MPPAY/users_mpop_04.dbf    700788426   61686392    11240767    1027103
6   /u02/oradata/MPPAY/I_USERS_MPOP1.dbf    324136523   155920043   7287802 2304233

4.臨時文件（磁盤）讀寫統計

select name,phyrds,phywrts,readtim,writetim
from v$tempstat a,v$tempfile b
where a.file#=b.file#
order by readtim desc

1   /u02/oradata/MPPAY/temp01.dbf   93067618    19891171    925 6390
2   /u02/oradata/MPPAY/risk_data_temp.dbf   642150  644978  3   42
3   /u02/oradata/MPPAY/message_send_data_temp.dbf   46  0   2   0
4   /u02/oradata/MPPAY/activity_znq_0928_temp.dbf   58  36  1   1
5   /u02/oradata/MPPAY/users_activity_2019_temp.dbf 55  3   1   0
6   /u02/oradata/MPPAY/lottery_temp20.dbf   25  4   0   0
7   /u02/oradata/MPPAY/lottery_temp.dbf 7   0   0   0

5.日志切换的时间间隔

select b.recid,b.first_time as start_time,a.recid, a.first_time as end_time
,round((a.first_time-b.first_time)*24*60,1) minutes
from v$log_history a,v$log_history b
where a.recid = b.recid+1
and b.first_time>trunc(sysdate)-15

6.大表数据大量删除后空间整理

1. 删除大部分数据，留下小部分数据。我们可以把需要保留的数据转移到别的表，然后再把大表drop掉，然后改名就行了；
          a) create table tablename_min as select * from tablename_max a where 需要保留的数据.
          b) drop table tablename_max ;
          c) rename tablename_min as tablename_max ;
       这样就能清除这个大表的hwm，而且释放掉其他空间。
---------------------------------------------------------------------------------------
使用shrink table的方式。

         a) 我们可以先用delete from tablename_max;

         b) 由于我们进行了数据的delete 所以造成了 tablename_max 这张表的数据稀疏,数据块并没有减少，hwm也没有减少，这样就会影响全表扫描需要访问更多的数据块。这时我们可以通过shrink来重组数据使数据分布更紧密，同时降低HWM释放空闲数据块。

        c)由于需要移动行数据，数据的rowid会发生变化，所以需要设置表的row movement属性：
            alter table tablename_max enable row movement; --开启行迁移功能。
            alter table tablename_max shrink space compact;--(可以在压缩期间进行DML操作和查询) ,收缩表，不会降低hwm
            alter table tablename_max shrink space; --( 调整HWM时将阻塞DML操作),收缩表，并且降低hwm

            alter table tablename_max shrink space cascade;--收缩表并降低hwm，并且回收相应的索引。

             由于我们删除了大量的数据 ，相应的索引也进行了删除，这时需要对索引进行收缩。

            alter index idxname shrink space;
       注意：shrink table只会针对assm（自动段空间管理）的表有用，否则会报： ORA-10635: Invalid segment or tablespace type。

                 alter table tablename_max enable row movement语句会造成引用表tablename_max的对象(如存储过程、包、视图等)变为无效。执行完成后，最好执行一下utlrp.sql来编译无效的对象。

                
   由于是通过DML操作进行的，会产生大量redo，注意archivelog目录的空间大小问题；同时undo表空间也会暴增。