2

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.

Jon Heller
  • 34,999
  • 6
  • 74
  • 132
Mr. Llama
  • 20,202
  • 2
  • 62
  • 115
  • Is this a clustered (RAC) database? – Jon Heller Dec 05 '14 at 15:41
  • @JonHeller - Nope! All one one server and only one database instance. – Mr. Llama Dec 05 '14 at 16:23
  • 1
    I looked through a bunch of databases and found the same issue in about 10% of our databases. This is probably a bug but I can't explain it. – Jon Heller Dec 06 '14 at 02:04
  • @JonHeller - I think I've isolated what causes the issue. The duplicate entries only occur whenever a relatively (as compared to the tablespace size) massive table is created within a tablespace while a snapshot is being created. I'm still uncertain if it should be considered a bug or not. :\ – Mr. Llama Dec 11 '14 at 20:10
  • Sounds like a bug to me. I couldn't find anything on support.oracle.com. You'll probably need to create a service request to get an answer to this. If you do, please post the answer here so it can help someone else. – Jon Heller Dec 15 '14 at 05:17

0 Answers0