3 Replies Latest reply: Sep 23, 2013 2:14 PM by Lubiez Jean-Valentin RSS

    Tablespace size

    977650

      Hi,

       

      how can i know how many terrabytes of space i have been allocated?

       

      How much of it i have used and how much is left.

      ALso if possible i would like to know how much of space is allocated to all users using same tablespace.

       

       

      i am using oracle 11g.

        • 1. Re: Tablespace size
          EdStevens

          977650 wrote:

           

          Hi,

           

          how can i know how many terrabytes of space i have been allocated?

           

          How much of it i have used and how much is left.

          ALso if possible i would like to know how much of space is allocated to all users using same tablespace.

           

           

          i am using oracle 11g.

           

          Look at what is revealed in DBA_DATA_FILES and DBA_SEGMENTS.

          • 2. Re: Tablespace size
            Frank Kulash

            Hi,

             

            It's all in the data dictionary, though it's unclear exactly what parts you're interested in.

             

            DBA_DATA_FILES has information about current file sizes.

            DBA_EXTENTS has information about how much space is currently being used.  You could subtract the amount currently in use from the total amount of space (found in DBA_DATA_FILES) to find out how much space is left, but that could be misleading, because data files can be set to expand automatically when users ask for more space.

            DBA_TS_QUOTAS has information about quotas (that is, limits on space that each user can take, independent of what is available), as well as actual amounts used.  Values of -1 in the BYTES or BLOCKS columns mean that the user is allowed to take whatever space is in the file.

            • 3. Re: Tablespace size
              Lubiez Jean-Valentin

              977650 wrote:

               

              Hi,

               

              how can i know how many terrabytes of space i have been allocated?

               

              How much of it i have used and how much is left.

              ALso if possible i would like to know how much of space is allocated to all users using same tablespace.

               

               

              i am using oracle 11g.

               

              Hello,

               

              The view DBA_FREE_SPACE may also gives informations about the free extents in the Tablespaces:

               

              DBA_FREE_SPACE

               

              Also the following query will give you the space used by Users on a specific Tablespace:

               

              select owner, sum(bytes)/(1024*1024) "Used Mo"
              from dba_segments
              where tablespace_name = '<TABLESPACE>'
              group by owner;
              
              

               

               

              Hope this help.

              Best regards,

              Jean-Valentin Lubiez