1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to list out the table size of all table

Discussion in 'SQL PL/SQL' started by viachan, Nov 22, 2011.

  1. viachan

    viachan Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Hong Kong
    Hi ,

    I am using sql plus 9i. Currently, I forgot which table stored my document (in data type of BLOG).
    So I want to list all table name in the schema and their corresponding table size. I think the the table which stored document is the biggest in size.

    I only know how to list all tables:
    Select table_name FROM all_tables;


    Any idea/suggestion/link will be appreciated.

    Thanks in advance.

    Via
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    There is no BLOG datatype, however there are CLOB and BLOB. You should be searching user_tab_columns for columns of datatype BLOB and return the table_name and the column_name as this may refresh your memory.
     
    viachan likes this.
  3. viachan

    viachan Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Hong Kong
    Thank you. My typing mistake. It should be BLOB.

    How to find the size of the table ? Is there any function to find the size of the table ?

    I want to list:
    table_name [size]
    table_1 1MB
    table_2 0.9MB
    table_3 2MB
    ...........
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    You can try the following query.

    Code (SQL):

    SELECT
       segment_name           TABLE_NAME,      
       SUM(bytes)  table_size
    FROM
       USER_SEGMENTS
    WHERE
       SEGMENT_TYPE='TABLE'  
    GROUP BY segment_name
     
     
    viachan likes this.