This discussion is archived
5 Replies Latest reply: Apr 27, 2011 6:21 AM by gokhanatil RSS

How to check partition wise tablespace usage

Venkat Newbie
Currently Being Moderated
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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points