DBA_HIST_TBSPC_SPACE_USAGE duplicate SNAP_ID

629 views Asked by At

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.

0

There are 0 answers