8 Replies Latest reply on Jul 2, 2014 9:05 PM by Gary Graham-Oracle

    dba view tablespace chart does not show anything

    2616065

      Hi, When I am using the dba view for storage, I select the tablespace and then the chart tab.  No matter what tablespace selected, I get "The data is of zero or negative value and cannot be displayed".  These are production tablespaces and most definitely have space allocated and used.

      Any ideas?

      Oh, the database versions are 10.2.0.5 and 11.2.0.4 and the sqldeveloper version is 4.0.1.

      Thank you

      Sam

        • 1. Re: dba view tablespace chart does not show anything
          JeMo

          Hi Sam,

          I cannot directly answer your question. If I were You, I would try to obtain the tablespace informations by another way in order to find out whether the problem is in the database or in the SQL Developer:

           

          I would execute this sql  (this SQL is executed by SQL Developer, while fetch the tablespace informations):

           

          (!!!!!!!!!!!!please test it in a test DB before you use it in production DB, there is absolutely no warranty!!!!!!!!!!)

           

          SELECT d.tablespace_name, d.file_name,d.file_id,

            round(MAX(d.bytes)/1024/1024,1) total_bytes,

            round((MAX(d.bytes) - NVL(SUM(f.bytes), 0))/1024/1024,1) used_bytes,

            round(NVL(SUM(f.Bytes), 0)/1024/1024,2) free_bytes,

            d.blocks,

            autoextensible,

            decode(round(maxbytes/1024/1024,1),32768,'UNLIMITED',  decode(round(maxbytes/1024/1024,1),32768,'UNLIMITED')) maxbytes,

            decode(d.maxblocks,4194302,'UNLIMITED',maxblocks) maxblocks,

            d.status,

            ROUND(SQRT(MAX(f.BLOCKS)/SUM(f.BLOCKS))*(100/SQRT(SQRT(COUNT(f.BLOCKS)) )), 2) frag_idx

          FROM DBA_FREE_SPACE f ,

            DBA_DATA_FILES d

          WHERE f.tablespace_name(+) = d.tablespace_name

          AND f.file_id(+)           = d.file_id

          GROUP BY d.file_name, d.file_id,d.blocks,autoextensible,maxbytes,maxblocks,status, d.tablespace_name

          union all

          SELECT d.tablespace_name, d.file_name,d.file_id,

            round(MAX(d.bytes)/1024/1024,1) total_bytes,

            round((MAX(d.bytes) - NVL(SUM(f.bytes_used), 0))/1024/1024,1) used_bytes,

            round(NVL(SUM(f.Bytes_used), 0)/1024/1024,2) free_bytes,

            d.blocks,

            autoextensible,

            decode(round(maxbytes/1024/1024,1),32768,'UNLIMITED',  decode(round(maxbytes/1024/1024,1),32768,'UNLIMITED')) maxbytes,

            decode(d.maxblocks,4194302,'UNLIMITED',maxblocks) maxblocks,

            d.status,

            ROUND(SQRT(MAX(f.BLOCKS_used)/SUM(f.BLOCKS_used))*(100/SQRT(SQRT(COUNT(f.BLOCKS_used)) )), 2) frag_idx

          FROM GV$TEMP_SPACE_HEADER f ,

            DBA_TEMP_FILES d

          WHERE f.tablespace_name(+) = d.tablespace_name

          AND f.file_id(+)           = d.file_id

          GROUP BY d.file_name, d.file_id,d.blocks,autoextensible,maxbytes,maxblocks,status, d.tablespace_name

           

           

          If the script will also show negative values, so it would be a database configuration issue, which I unfortunately not familiar with. If not, then it colud be an SQL Developer problem.

           

          I hope it helps You! Let me know, if You founded out, what the problem was.

          BR

          J. Moldawski

          • 2. Re: dba view tablespace chart does not show anything
            Gary Graham-Oracle

            Hi Sam,

            The Usage Chart tab works for me against my little test database.  The pie chart shows the % Tablespace Usage / User.  I suppose a user could consume such a small allocation that the actual or rounded usage could be zero -- a negative value would be a surprise!  If that's happening in your case, I suppose the chart should just ignore those users. Get us some extra details -- perhaps a bug should be logged.

             

            Regards,
            Gary

            SQL Developer Team

            • 3. Re: dba view tablespace chart does not show anything
              When I am using the dba view for storage, I select the tablespace and then the chart tab.  No matter what tablespace selected, I get "The data is of zero or negative value and cannot be displayed".  These are production tablespaces and most definitely have space allocated and used.

              Any ideas?

              Oh, the database versions are 10.2.0.5 and 11.2.0.4 and the sqldeveloper version is 4.0.1.

              I can't reproduce your problem using Sql Dev 4.0.1.14.48 and vanilla 11.2.0.1.0 DB

               

              Almost 100 tablespaces and the chart works just fine on all of them including SYSTEM, and SYSAUX.

              • 4. Re: dba view tablespace chart does not show anything
                2616065

                These databases have 600 plus users but only a few table owners.

                One schema has over 1300 tables and 2k plus indexes.  I've also tried an smallish databases.  I have version 4.0.1.14 Build Main -14.48.

                It isn't critical by any means but it would be nice for it to work.

                Thank you for your reply.

                Sam

                • 5. Re: dba view tablespace chart does not show anything
                  2616065

                  Hi,  Update: I imported our database into a 12c instance.  The tablespace chart still shows "The data is of zero or negative value and cannot be displayed".  It shows the same message for each tablespace.  I also upgraded sqldev to 4.0.2.15 build 15.21.

                  Sam

                  • 6. Re: dba view tablespace chart does not show anything
                    Gary Graham-Oracle

                    Sam,
                    Have you used Tools > Preferences > Database > Advanced to specify an Oracle Home or Oracle Instant Client, thereby overriding the default 11.2.0.3 ojdbc jar file that comes with the 4.0.x release/patches?  If so, which client versions have you tried? 

                     

                    When I originally answered back in April, I was using that default and the DBA view's Usage Chart tab for the Tablespaces node rendered those pie charts just fine.  Now, however, when alternating between 11.2.0.3 and 12c instant clients in Advanced I see that the 12c configuration reproduces the incorrect behavior you describe ("The data is of zero or negative value and cannot be displayed").

                     

                    --Gary

                     

                    P.S.:  The testing above was on Windows 7.  Testing on Linux with a full 12c Server install, I still see the same problem: default 11.2.0.3 ojdbc works, but not 12c.

                     

                    Message was edited by: Gary Graham

                    • 7. Re: dba view tablespace chart does not show anything
                      2616065

                      Thank you,  when I selected the old 11.2 oracle home, the charts work.

                      • 8. Re: dba view tablespace chart does not show anything
                        Gary Graham-Oracle

                        Thank you,  when I selected the old 11.2 oracle home, the charts work.

                        Just a follow-up note.  I had a 32-bit 12c instant client installed on Windows 7. After switching to a 64-bit 12c instant client, whether using the Thin or OCI/Thick JDBC driver, the Usage Chart renders correctly.  Not sure if 32-bit versus 64-bit made a difference, or if I had something else configured incorrectly.  Anyway, the 12c client seems OK.