I'm not entirely sure if this is a bug or something that I'm doing wrong, but here goes:
I'm trying to pull historical tablespace information from the DBA_HIST_TBSPC_SPACE_USAGE
data dictionary view. The documentation says that the snap_id
value is the unique snapshot ID.
However, consider the following query:
SELECT snap_id, tablespace_id, rtime, snap_qty
FROM
(
SELECT
a.*,
COUNT(*) OVER (PARTITION BY snap_id, tablespace_id) AS snap_qty
FROM dba_hist_tbspc_space_usage a
)
WHERE snap_qty > 1
ORDER BY snap_qty DESC, snap_id, tablespace_id, rtime
If snap_id
were unique, I would expect that no snap_id
would contain a given tablespace_id
multiple times. The idea being "one snapshot, one entry per tablespace".
However, that's not the results I get:
SNAP_ID TABLESPACE_ID RTIME SNAP_QTY
---------- ------------- ------------------------- ----------
50874 8 12/04/2014 20:03:41 3
50874 8 12/04/2014 20:13:41 3
50874 8 12/04/2014 20:16:40 3
50295 51 11/28/2014 19:24:12 2
50295 51 11/28/2014 19:31:09 2
50494 33 11/30/2014 21:06:49 2
50494 33 11/30/2014 21:16:41 2
50609 12 12/02/2014 01:48:26 2
50609 12 12/02/2014 02:01:01 2
50643 30 12/02/2014 10:28:54 2
50643 30 12/02/2014 10:31:30 2
50684 25 12/02/2014 20:39:33 2
50684 25 12/02/2014 20:46:07 2
...
Is this a bug? Is this expected behavior? (Should I submit a bug report?)
Edit: This doesn't seem to be occurring on all of our servers, only a small percent of them. However, its occurring on both Oracle 12 and Oracle 11.