REPORT SCHEMA is quite handy to get growth rate of your database/datafile/tablespace providing RMAN repository is used. I don’t remember what triggered that thought but I see it in my “to blog” list so here it goes.
Running several times REPORT SCHEMA AT TIME 'SYSDATE-n'; and processing the results will provide nice trend with granularity up to datafile. It can be processed and aggregated to tablespace or the whole database.
I don’t see that historical datafile information is stored in a repository so RMAN, probably, calculates or implies it from data about backups taken (BDF table?). That means that timeline precision depends in the backups frequency. I’ve run REPORT SCHEMA AT TIME with DEBUG ON quickly but it shows only calls to RMAN internal package and I didn’t have enough time to trace the queries.
2 Comments. Leave new
Hi Alex,
I made a trace of REPORT SCHEMA command and found the following sql statements for permanent and temporary tablespaces:
for REPORT SCHEMA for permanent tablespaces
SELECT RC_DATAFILE.FILE#, RC_DATAFILE.CREATION_CHANGE#, CREATION_TIME, NAME,
TABLESPACE_NAME, TS#, NULL, BLOCKS, BLOCK_SIZE, BYTES / 1024, NULL,
STOP_CHANGE#, READ_ONLY, RFILE#, DECODE(INCLUDED_IN_DATABASE_BACKUP, ‘YES’,
1, 0), AUX_NAME, RC_DATAFILE.DBINC_KEY, OFFR.OFFLINE_SCN, OFFR.ONLINE_SCN,
OFFR.ONLINE_TIME, DECODE(ENCRYPT_IN_BACKUP, ‘ON’, 1, ‘OFF’,2, 3) ENCRYPT
FROM
RC_DATAFILE, OFFR WHERE DB_KEY = :B4 AND RC_DATAFILE.DBINC_KEY = :B3 AND
OFFR.FILE#(+) = RC_DATAFILE.FILE# AND OFFR.CREATE_SCN(+) =
RC_DATAFILE.CREATION_CHANGE# AND OFFR.DBINC_KEY(+) = :B3 AND
OFFR.OFFR_STAMP(+) = 0 AND RC_DATAFILE.CREATION_CHANGE# :B1 ) ORDER BY RC_DATAFILE.FILE#
where :B4 = 1 (index of registered database within catalog)
:B3 = 2 (number of reincarnation of a database)
:B2 and :B1 = 900000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
for REPORT SCHEMA;
and :B2 and :B1 = 245800 in my case
that is equal to
l1> select LOW_SCN, LOW_TIME, COMPLETION_TIME from al where LOW_SCN = 245800;
LOW_SCN LOW_TIME COMPLETION_TIME
———- —————– —————–
245800 11/20/06 07:01:25 12/03/06 17:13:33
and SYSDATE was for Dec 3
for temporary tablespace the sql is
SELECT TF.FILE# TFNUMBER, TF.CREATE_SCN TFCREATIONSCN, TF.CREATE_TIME
TFCREATIONTIME, TFATT.FNAME FILENAME, TS.TS_NAME TSNAME, TS.TS# TSNUMBER,
DECODE(TFATT.AUTOEXTEND, ‘ON’, 16, 0) STATUS, TS.BIGFILE ISSFT,
TFATT.BLOCKS BLOCKS, TF.BLOCK_SIZE BLOCKSIZE, TFATT.MAX_SIZE MAXSIZE,
TFATT.NEXT_SIZE NEXTSIZE, TF.RFILE# RFNUMBER, DBINC.DBINC_KEY DBINCKEY
FROM
DBINC, TS, TF, TFATT WHERE DBINC.DBINC_KEY = TS.DBINC_KEY AND TS.DBINC_KEY =
TF.DBINC_KEY AND TS.TS# = TF.TS# AND TS.CREATE_SCN = TF.TS_CREATE_SCN AND
TF.DBINC_KEY = TFATT.DBINC_KEY AND TF.FILE# = TFATT.FILE# AND TF.CREATE_SCN
= TFATT.CREATE_SCN AND TFATT.END_CKP_KEY IS NULL AND DBINC.DB_KEY = :B5 AND
DBINC.DBINC_KEY = :B4 AND TF.DROP_SCN IS NULL AND (:B3 IS NULL OR
((TS.CREATE_SCN
Thanks Andrey. I’ll try it one day if I have time. Not many customers are using RMAN repository so we are not very keen on adopting it as general practice.