I probably knew this at one time, but today I was surprised to see that the
DBA_TABLESPACE_USAGE_METRICS view doesn't return any rows when run from a
Physical Standby that is open for read-only. I tried on a couple of standbys
to make sure it wasn't something about that one DB. DB Version is 12.1.0.2
A quick search didn't return any mentions of this, so I was wondering if anyone
on the list could tell me what is it about that view that would cause it to
return no rows against a Physical Standby that is open read-only?
In case it helps answer the question, the view text is below:
SELECT t.name, tstat.kttetsused, tstat.kttetsmsize,
(tstat.kttetsused / tstat.kttetsmsize) * 100 FROM sys.ts$ t, x$kttets tstat
WHERE t.online$ != 3 and t.bitmapped <> 0 and t.contents$
= 0 and bitand(t.flags, 16) <> 16 and t.ts# =
tstat.kttetstsnunion SELECT t.name, sum(f.allocated_space),
sum(f.file_maxsize), (sum(f.allocated_space)/sum(f.file_maxsize))*100
FROM sys.ts$ t, v$filespace_usage f WHERE t.online$ != 3 and
t.bitmapped <> 0 and t.contents$ <> 0 and f.flag = 6 and t.ts# =
f.tablespace_id GROUP BY t.name, f.tablespace_id, t.ts#union SELECT t.name,
sum(f.allocated_space), sum(f.file_maxsize),
(sum(f.allocated_space)/sum(f.file_maxsize))*100 FROM sys.ts$ t,
gv$filespace_usage f, gv$parameter param WHERE t.online$ != 3 and
t.bitmapped <> 0 and f.inst_id = param.inst_id and param.name =
'undo_tablespace' and t.name = param.value and f.flag = 6 and t.ts#
= f.tablespace_id GROUP BY t.name, f.tablespace_id, t.ts#;
Brad Peek