10 Replies Latest reply on Dec 18, 2007 1:04 PM by 588514

    incongruencies between dba_data_files , dba_segments and dba_free_space

    588514
      Hi all,
      I run this query:
      select sum(bytes), 'USED' from dba_segments where tablespace_name = '&tsname' union
      select sum(bytes), 'FREE' from dba_free_space where tablespace_name = '&tsname' union
      select sum(bytes), 'TOTL' from dba_data_files where tablespace_name = '&tsname';

      and I get as a result:

      220200960     FREE
      10989076480     USED
      11010048000     TOTL

      So, free_space + used_space is LESS than the space occupied by the datafiles! Why ?
      If I try to query sum(blocks) instead of sum(bytes) I get the same results... So: how can I get more space than the existing one?

      Thaks for every reply,

      andrea
        • 1. Re: incongruencies between dba_data_files , dba_segments and dba_free_space
          604693
          free_space + used_space is LESS than the space occupied by the datafiles
          I found them more then the the space occupied by the datafiles.
          220200960 FREE
          10989076480 USED
          free+used=11209277440
          totl =11010048000
          • 2. Re: incongruencies between dba_data_files , dba_segments and dba_free_space
            Girish Sharma
            DBA_FREE_SPACE
            BYTES=Size of the extent in bytes

            DBA_SEGMENTS
            BYTES=Size,in bytes of the segment

            DBA_DATA_FILES
            BYTES=Size of the file in bytes

            I think now you will be clear.
            • 3. Re: incongruencies between dba_data_files , dba_segments and dba_free_space
              Niall Litchfield
              Hi all,
              I run this query:
              select sum(bytes), 'USED' from dba_segments where
              tablespace_name = '&tsname' union
              select sum(bytes), 'FREE' from dba_free_space where
              tablespace_name = '&tsname' union
              select sum(bytes), 'TOTL' from dba_data_files where
              tablespace_name = '&tsname';

              and I get as a result:

              220200960     FREE
              10989076480     USED
              11010048000     TOTL

              So, free_space + used_space is LESS than the space
              occupied by the datafiles! Why ?
              If I try to query sum(blocks) instead of sum(bytes) I
              get the same results... So: how can I get more space
              than the existing one?
              your figures don't add up as pointed out in the first reply - you may have may have got the query wrong, but there are occasions when those views report incorrect figures - this is documented on metalink. However consider this if it's just a typos somewhere
              SYS @ db11g >accept tsname prompt 'Enter tablespace name '
              Enter tablespace name SYSTEM
              SYS @ db11g >select sum(bytes), 'USED' from dba_segments where tablespace_name = '&tsname' union
                2  select sum(bytes), 'FREE' from dba_free_space where tablespace_name = '&tsname' union
                3  select sum(bytes), 'TOTL' from dba_data_files where tablespace_name = '&tsname';
              old   1: select sum(bytes), 'USED' from dba_segments where tablespace_name = '&tsname' union
              new   1: select sum(bytes), 'USED' from dba_segments where tablespace_name = 'SYSTEM' union
              old   2: select sum(bytes), 'FREE' from dba_free_space where tablespace_name = '&tsname' union
              new   2: select sum(bytes), 'FREE' from dba_free_space where tablespace_name = 'SYSTEM' union
              old   3: select sum(bytes), 'TOTL' from dba_data_files where tablespace_name = '&tsname'
              new   3: select sum(bytes), 'TOTL' from dba_data_files where tablespace_name = 'SYSTEM'
              
              SUM(BYTES) 'USE
              ---------- ----
                 1638400 FREE
               784728064 USED
               786432000 TOTL
              
              SYS @ db11g >
              SYS @ db11g >SELECT 786432000 - 784728064 - 1638400 FROM DUAL;
              
              786432000-784728064-1638400
              ---------------------------
                                    65536
              You'll see that I'm 64k "short" - My system tablespace is locally managed, relatively small and resides in one file. The 64k represents the space allocation bitmap IIRC. It isn't a segment or free space, but it is in the datafile.

              Niall Litchfield
              http://www.orawin.info
              • 4. Re: incongruencies between dba_data_files , dba_segments and dba_free_space
                588514
                free_space + used_space is LESS than the space
                occupied by the datafiles
                I found them more then the the space occupied by the
                datafiles.
                Yes, I was wrong then typing. In fact, I was surprised because free_space + used_space is MORE than the space occupied by the datafile itself
                • 5. Re: incongruencies between dba_data_files , dba_segments and dba_free_space
                  561093
                  free_space + used_space is LESS than the space
                  occupied by the datafiles
                  I found them more then the the space occupied by
                  the
                  datafiles.
                  Yes, I was wrong then typing. In fact, I was
                  surprised because free_space + used_space is MORE
                  than the space occupied by the datafile itself
                  Free Space + Used Space can go beyond the datafile file size. Have a look at:

                  http://momendba.blogspot.com/2007/12/inconsistency-between-dbadatafiles.html
                  • 6. Re: incongruencies between dba_data_files , dba_segments and dba_free_space
                    588514
                    DBA_FREE_SPACE
                    BYTES=Size of the extent in bytes

                    DBA_SEGMENTS
                    BYTES=Size,in bytes of the segment

                    DBA_DATA_FILES
                    BYTES=Size of the file in bytes

                    I think now you will be clear.
                    uhmmm...unfortunatly not:
                    I expect each datafile to contain:
                    - datafile header (and space allocation bitmat if the tablespace is locally managed)
                    - extents of a tablespace
                    - free extents
                    - free unusable space in the "tail" of the file, if the tail is smaller than the next extent of every object in the tablespace.

                    So I expect that sum(bytes) from dba_segments + sum(bytes) from dba_free_space <= sum(bytes) from dba_data_files, and the same should appen if I sum up blocks rather than bytes

                    By the way, I rerun the query and now everything seems ok: now I get
                    220200960     FREE
                    10779361280     USED
                    11010048000     TOTL
                    It seems like Oracle released 200 M of space while I was running the query, and for some seconds this space appeared both in the dba_segments and in the dba_free_space.
                    I'm quite sure of this, beacuse I run the query more times before posting...

                    Thanks for every answer!
                    • 7. Re: incongruencies between dba_data_files , dba_segments and dba_free_space
                      Niall Litchfield
                      I thought that that (double counting dropped objects) had been fixed in a 10.1 patchset? But thanks for pointing out a rather obvious thing I should have mentioned.

                      In addition there are a number of bugs in dba_segments/dba_extents for example 4771672. And I meant to mention bug 3270428 which might apply to the poster as well

                      Niall Litchfield
                      http://www.orawin.info
                      • 9. Re: incongruencies between dba_data_files , dba_segments and dba_free_space
                        Niall Litchfield
                        first one is incorrect figures in the views following a parallel index create. second one is incorrect figures when a segment contains more than a million blocks. There are others.

                        Niall Litchfield
                        http://www.orawin.info/
                        • 10. Re: incongruencies between dba_data_files , dba_segments and dba_free_space
                          588514
                          I thought that that (double counting dropped objects)
                          had been fixed in a 10.1 patchset? But thanks for
                          pointing out a rather obvious thing I should have
                          mentioned.
                          It doesn't look fixed to me: the above situation is taken from a 10.2.0.3 database. I tried to make a simple test on a 11.1.0.6 0 database: I created and dropped a table without purging the recyclebin and here's what I got:

                          select sum(blocks),'USEDS' from dba_segments where tablespace_name = 'USERS' union
                          select sum(blocks),'USEDE' from dba_extents where tablespace_name = 'USERS' union
                          select sum(blocks),'FREE ' from dba_free_space where tablespace_name = 'USERS' union
                          select sum(blocks),'TOTAL' from dba_data_files where tablespace_name = 'USERS';

                          152     FREE
                          480     USEDE
                          504     USEDS
                          640     TOTAL

                          looking carefully, I noticed that dba_segments does include droppen objects (i.e. segment whose name is like 'BIN$%'), whereas dba_extents and dba_free_space consider dropped extents as free space!
                          So a statement like

                          select (select sum(blocks) from dba_extents where tablespace_name = 'USERS' ) +
                          ( select sum(blocks) from dba_free_space where tablespace_name = 'USERS' ) from dual;

                          will always give a number lass than (or equal to)

                          select sum(blocks) from dba_data_files where tablespace_name = 'USERS'

                          since both dba_extents and dba_free_space consider dropped objects as free space / unused extents, whereas

                          select (select sum(blocks) from dba_segments where tablespace_name = 'USERS' ) +
                          ( select sum(blocks) from dba_free_space where tablespace_name = 'USERS' ) from dual;

                          can give a number greater than

                          select sum(blocks) from dba_data_files where tablespace_name = 'USERS'

                          beacuse dropped objects are counted twice.

                          That's all, I think.
                          Thanks for your suggestions!