Useful rman catalog queries

You can use the rman catalog to track the growth of datafiles over time:

col tablespace_name format a20
col file# format 999
col backed_up_bytes format 99,999,999,999
col file_bytes format 99,999,999,999
select b.db_name, d.tablespace_name, b.file#, to_char(completion_time, ‘DD-MM-YYYY’),
b.blocks* b.block_size backed_up_bytes, b.datafile_blocks*b.block_size file_bytes
from rc_backup_datafile b, rc_datafile d
where tablespace_name like ‘&tablespace%’
and b.db_name = ‘&database’
and b.db_name = d.db_name
and d.file# = b.file#
order by completion_time
/

DB_NAME TABLESPACE_NAME FILE# TO_CHAR(CO BACKED_UP_BYTES FILE_BYTES
——– ——————– —– ———- ————— —————
TS01 BVR_INDEXES 16 15-04-2005 2,407,890,944 5,242,880,000
TS01 BVR_INDEXES 16 15-04-2005 2,407,890,944 5,242,880,000
.
.
.
TS01 BVR_INDEXES 16 31-12-2005 6,028,001,280 6,815,744,000
TS01 BVR_INDEXES 16 06-01-2006 6,187,974,656 6,815,744,000
TS01 BVR_INDEXES 16 07-01-2006 6,188,015,616 6,815,744,000

172 rows selected.

SQL>

select db_name, tablespace_name, file#, bytes
from rc_datafile;

DB_NAME TABLESPACE_NAME FILE# BYTES
——– —————————— ———- —————-
WHP2 BVR_INDEXES 16 6,815,744,000
TIPS SYSTEM 1 524,288,000
SCP1 SYSTEM 1 629,145,600

Advertisements