Oracle 估算备份大小和计算备份集占用空间

查某一天的备份信息

SQL> select to_char(sysdate ,'yyyymmdd') from dual;

    TO_CHAR(

    --------

    20170829

SQL> select blocks from dba_data_files where file_id = 4;

    BLOCKS

    ----------

       640    #这个文件640个块

SQL> select file#,recid,

to_char(creation_time,'yyyymmddhh24:mi:ss'),

datafile_blocks,

blocks,

to_char(completion_time,'yyyymmdd hh24:mi:ss') 

from v$backup_datafile

where trunc(completion_time)=to_date('20170829','yyyymmdd') or trunc(completion_time)=trunc(sysdate)  and file#=4 order by file#,recid;

     FILE# RECID TO_CHAR(CREATION_ DATAFILE_BLOCKS     BLOCKS

----------     ----------     -----------------             ---------------         ----------

TO_CHAR(COMPLETIO

-----------------

4    6 20090815 00:17:30    640        433

20170829 05:44:16

数据文件4 共有 640个块，备份集包含433个块，每个块大小8K

所以数据文件4共 5M

备份结果433块，备份集3.4M

所有备份集占用空间大小：

SQL> select sum(blocks)*8/1024 from v$backup_datafile;

SUM(BLOCKS)*8/1024

------------------

8.0234375

计算每天的备份量

SQL> select round(sum(blocks)*8/1024,0) || 'M' from v$backup_datafile where trunc(completion_time)=to_date('20170829','yyyymmdd');

ROUND(SUM(BLOCKS)*8/1024,0)||'M'

-----------------------------------------

3M