9 Replies Latest reply: Jul 26, 2011 1:44 PM by 878171 RSS

    how to get table size for partitioned table

    878171
      Hi,
      I've searched on various site and found a few scripts people posted.

      1.This query doesn't give size of a partitioned table
      select segment_name , tablespace_name, bytes/1024, blocks, extents
      from user_segments
      where segment_type='table'


      2.found a script from Calros Romeu https://www.oraclespot.com, this is a good query as it gave total size, table, index and lobs, but didn't give partitioned table.

      anyone know how and willing to share their script?

      Thank you,
      YOW
        • 1. Re: how to get table size for partitioned table
          sb92075
          user9117767 wrote:
          Hi,
          I've searched on various site and found a few scripts people posted.

          1.This query doesn't give size of a partitioned table
          select segment_name , tablespace_name, bytes/1024, blocks, extents
          from user_segments
          where segment_type='table'


          2.found a script from Calros Romeu https://www.oraclespot.com, this is a good query as it gave total size, table, index and lobs, but didn't give partitioned table.

          anyone know how and willing to share their script?

          Thank you,
          YOW
          select segment_name , tablespace_name, bytes/1024, blocks, extents
          from user_segments
          where segment_type='table'
          and segment_name = '<your_table_name>'
          • 2. Re: how to get table size for partitioned table
            878171
            Thank you.
            yes I did that, in the result my table didn't show up.

            I also ran this,

            select segment_name , tablespace_name, bytes/1024, blocks, extents
            from dba_segments
            where segment_type='table'
            and owner='<owner_name>'
            and segment_name = '<your_table_name>'

            Again, I didn't get anything in return.
            Is it because the table is partitioned?

            Edited by: user9117767 on 26-Jul-2011 10:36 AM
            • 3. Re: how to get table size for partitioned table
              878171
              I should say also, when I did
              select segment_name, tablespace_name, bytes/1024, blocks, extents
              from dba_segments
              where segment_type='TABLE'
              and seyment_name='mytable_name'
              and owner='owner_name'

              This return nothing also. Is it because it is partitioned?
              • 4. Re: how to get table size for partitioned table
                sb92075
                user9117767 wrote:
                Thank you.
                yes I did that, in the result my table didn't show up
                perhaps table is owned by different user.
                perhaps table is figment of your imagination.
                • 5. Re: how to get table size for partitioned table
                  878171
                  haha, no I wish it was my imagination


                  I did in fact verified select * from all_objects where object_name ='<obj_name>', it is the same owner as I thought.
                  • 6. Re: how to get table size for partitioned table
                    sb92075
                    user9117767 wrote:
                    haha, no I wish it was my imagination


                    I did in fact verified select * from all_objects where object_name ='<obj_name>', it is the same owner as I thought.
                    enjoy your mystery since you provide NO useful details

                    You're On Your Own (YOYO!)
                    • 7. Re: how to get table size for partitioned table
                      P.Forstmann
                      What is your 4 digits Oracle version ?

                      You need to query with SEGMENT_TYPE='TABLE PARTITION' instead of 'TABLE':

                      SQL> select * from v$version;
                      
                      BANNER
                      --------------------------------------------------------------------------------
                      Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
                      PL/SQL Release 11.2.0.1.0 - Production
                      CORE    11.2.0.1.0      Production
                      TNS for Linux: Version 11.2.0.1.0 - Production
                      NLSRTL Version 11.2.0.1.0 - Production
                      
                      SQL> connect appli/appli
                      Connected.
                      SQL> drop table sd purge;
                      
                      Table dropped.
                      
                      SQL> show user
                      USER is "APPLI"
                      SQL> CREATE TABLE sd
                        2    (pid     NUMBER,
                        3     sd   DATE,
                        4     cid  NUMBER,
                        5     tp NUMBER)
                        6  PARTITION BY RANGE(sd)
                        7   (PARTITION sd1 VALUES LESS THAN (TO_DATE('01-APR-2010', 'DD-MON-YYYY')),
                        8    PARTITION sd2 VALUES LESS THAN (TO_DATE('01-JUL-2011', 'DD-MON-YYYY')),
                        9    PARTITION sd3 VALUES LESS THAN (MAXVALUE)
                       10   )
                       11  ;
                      
                      Table created.
                      
                      SQL> connect /
                      Connected.
                      SQL> column segment_name format a15
                      SQL> column tablespace_name format a10
                      SQL> select segment_name , tablespace_name, segment_type, bytes, blocks, extents
                        2  from dba_segments
                        3  where owner='APPLI'
                        4  and segment_name = 'SD';
                      
                      SEGMENT_NAME    TABLESPACE SEGMENT_TYPE            BYTES     BLOCKS    EXTENTS
                      --------------- ---------- ------------------ ---------- ---------- ----------
                      SD              USERS      TABLE PARTITION         65536          8          1
                      SD              USERS      TABLE PARTITION         65536          8          1
                      SD              USERS      TABLE PARTITION         65536          8          1
                      
                      SQL>
                      SQL>
                      SQL> select sum(bytes)
                        2  from dba_segments
                        3  where owner='APPLI'
                        4  and segment_name = 'SD'
                        5  and segment_type = 'TABLE PARTITION';
                      
                      SUM(BYTES)
                      ----------
                          196608
                      
                      SQL>
                      You can also simply omit SEGMENT_TYPE: this should also work:
                      SQL>
                      SQL> select sum(bytes)
                        2  from dba_segments
                        3  where owner='APPLI'
                        4  and segment_name = 'SD';
                      
                      SUM(BYTES)
                      ----------
                          196608
                      
                      SQL>
                      Edited by: P. Forstmann on 26 juil. 2011 20:20
                      • 8. Re: how to get table size for partitioned table
                        user8731171
                        change as per required


                        select substr(table_name,1,20) partitioned_table,
                        substr(column_name,1,20) key_column,
                        count(*) no_stats,
                        count(*)/total_partitions*100 "NO_STAT%"
                        from (
                        select table_name,
                        column_name,
                        partition_name,
                        low_value,
                        count(*) over (partition by table_name, column_name)
                        total_partitions
                        from user_part_col_statistics
                        where (table_name,column_name) in
                        (select name,column_name
                        from user_part_key_columns
                        where object_type = 'TABLE')
                        )
                        where low_value is null
                        group by table_name,
                        column_name,
                        total_partitions
                        /
                        • 9. Re: how to get table size for partitioned table
                          878171
                          oracle version is 10.2.0.4.0.
                          Thank you very much for your input, I was able to find the table size using your advise to search segment_type = 'TABLE PARTITION';