5 Replies Latest reply: Apr 27, 2011 8:21 AM by GokhanAtil RSS

    How to check partition wise tablespace usage

    Venkat
      Hi,

      Table space Test01 is 99.66% full. This test01 tablespace used by table raw_meterial.

      Raw_meterial table contain 6 subpartitions, partition type is range partition.

      output i want to like:

      Partion_name size_in_MB, used_in_MB, free_in_MB, PCT used.

      Pls suggest which dba_views i need check or provide if any one had a query.

      Regards,
      Venkat
        • 1. Re: How to check partition wise tablespace usage
          GokhanAtil
          Venkat wrote:
          Hi,

          Table space Test01 is 99.66% full. This test01 tablespace used by table raw_meterial.

          Raw_meterial table contain 6 subpartitions, partition type is range partition.

          output i want to like:

          Partion_name size_in_MB, used_in_MB, free_in_MB, PCT used.

          Pls suggest which dba_views i need check or provide if any one had a query.

          Regards,
          Venkat
          Subpartitions or partitions?

          For partitions, you can use such a query:
          SELECT   s.partition_name, CEIL (s.BYTES / 1024 / 1204) size_in_MB,
                   CEIL ((p.blocks - p.empty_blocks) / p.blocks * s.BYTES / 1024 / 1204 ) used_in_MB,
                   CEIL (p.empty_blocks / p.blocks * s.BYTES / 1024 / 1204) free_in_MB,
                   CEIL ((p.blocks - p.empty_blocks) / p.blocks  * 100 )  PCT_used
              FROM dba_tab_partitions p, dba_segments s
             WHERE p.table_owner = s.owner
               AND p.partition_name = s.partition_name
               AND p.table_name = s.segment_name
               AND p.table_owner = 'SCHEMA_NAME'
               AND p.table_name = 'TABLE_NAME'
          ORDER BY partition_position;
          I'm sure you can fetch similar info from dba_tab_subpartitions.

          Important thing is you need to analyze the partitions (or subpartitions) otherwise "empty_blocks" will return null.

          Best Regards,

          Gokhan

          -------------------------------------------------------
          If this question is answered, please mark appropriate posts as correct/helpful and the thread as closed. Thanks
          • 2. Re: How to check partition wise tablespace usage
            Venkat
            Hi gikhan,

            Thank you for given guidence.

            My table contains 6 partitions and each partition contains 6 subpartitions.

            i have modified your query by using dba_tab_subpartitions. But still emplty

            SELECT p.subpartition_name, CEIL (s.BYTES / 1024 / 1204) size_in_MB,
            CEIL ((p.blocks - p.empty_blocks) / p.blocks * s.BYTES / 1024 / 1204 ) used_in_MB,
            CEIL (p.empty_blocks / p.blocks * s.BYTES / 1024 / 1204) free_in_MB,
            CEIL ((p.blocks - p.empty_blocks) / p.blocks * 100 ) PCT_used
            FROM dba_tab_subpartitions p, dba_segments s
            WHERE p.table_owner = s.owner
            AND p.partition_name = s.partition_name
            AND p.table_name = s.segment_name
            AND p.table_owner = 'PMAX'
            AND p.table_name = 'BILLCHRG'
            ORDER BY subpartition_position;
            • 3. Re: How to check partition wise tablespace usage
              GokhanAtil
              Run this but be sure that you have analyzed the subpartitions:
              SELECT p.subpartition_name, CEIL (s.BYTES / 1024 / 1204) size_in_MB,
              CEIL ((p.blocks - p.empty_blocks) / p.blocks * s.BYTES / 1024 / 1204 ) used_in_MB,
              CEIL (p.empty_blocks / p.blocks * s.BYTES / 1024 / 1204) free_in_MB,
              CEIL ((p.blocks - p.empty_blocks) / p.blocks * 100 ) PCT_used
              FROM dba_tab_subpartitions p, dba_segments s
              WHERE p.table_owner = s.owner
              AND p.subpartition_name = s.partition_name
              AND p.table_name = s.segment_name
              AND p.table_owner = 'PMAX'
              AND p.table_name = 'BILLCHRG'
              ORDER BY subpartition_position;
              Edit: I changed "AND p.partition_name = s.partition_name" to "AND p.subpartition_name = s.partition_name"

              Edited by: GokhanAtil on 27.Nis.2011 15:54
              • 4. Re: How to check partition wise tablespace usage
                Venkat
                Hi Gokhan,

                I got details. Thanks alot for ur help.

                I have one doubt..that

                in your query you mentined that p.subpartition_name = s.partition_name

                Actully subpartiton_name and partition name is not equal.

                But without any erorr i got right output.

                subpartionname=BILLCHRG_2011_05_5 and partition_name=BILLCHRG_2011_05.

                Regards,
                Venkat.
                • 5. Re: How to check partition wise tablespace usage
                  GokhanAtil
                  Venkat wrote:
                  Hi Gokhan,

                  I got details. Thanks alot for ur help.

                  I have one doubt..that

                  in your query you mentined that p.subpartition_name = s.partition_name

                  Actully subpartiton_name and partition name is not equal.

                  But without any erorr i got right output.

                  subpartionname=BILLCHRG_2011_05_5 and partition_name=BILLCHRG_2011_05.

                  Regards,
                  Venkat.
                  There's no column for sub-partitions in dba_segments. So it holds the subpartition name if the segment belongs to a subpartition.

                  Best Regards,

                  Gokhan

                  -------------------------------------------------------
                  If this question is answered, please mark appropriate posts as correct/helpful. Thanks