DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Table Size

  1. #1
    Join Date
    Oct 2001
    Posts
    52

    Lightbulb Table Size

    Hi,
    1. How can I check the size of a table? is it using the initial_extents from user_tables?

    2. How to reorg a table if it grow too large?

    Please advise.

    Thank in advance.

  2. #2
    Join Date
    Jun 2001
    Location
    California
    Posts
    124
    1)
    Select sum(bytes) from dba_segments where segment_name = ;
    or
    If u need actual blocks used (below the HWM) for that table do the following
    SELECT COUNT (DISTINCT SUBSTR(rowid,1,15)) "Used" FROM table_name;

    2) Export/Import or MOVE command.

  3. #3
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    1)
    Code:
    Select 
      Count(Distinct Substr(Rowid, 1, 15))*{Block_Size}/1024/1024 "Table Size in MB" 
    from 
      {Table_Name}
    ;
    2) Move.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by abhaysk
    1)
    Code:
    Select 
      Count(Distinct Substr(Rowid, 1, 15))*{Block_Size}/1024/1024 "Table Size in MB" 
    from 
      {Table_Name}
    ;
    I wonder what this query is supposed to tell you about the table size in MB.... More or less nothing. But we've been through that many times in this forum....
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Apr 2003
    Location
    South Carolina
    Posts
    148
    Here's a script that will tell you the allocated space for the
    table and the used space for the table ...

    Change the OWNER, TABLE_NAME in the top of the script for your
    structure


    /**************************************************/
    /* Find the highwater mark in a table */
    /* ALTER TABLE dquon DEALLOCATE UNUSED KEEP 20K; */
    /* gb - PSAConsulting */
    /**************************************************/

    set serveroutput on

    declare
    v_user varchar2(50) := 'SYSADM';
    v_object_name varchar2(50) := 'PART';
    v_object_type varchar2(15) := 'TABLE';
    v_total_blocks number;
    v_total_bytes number;
    v_unused_blocks number;
    v_unused_bytes number;
    v_last_used_extent_file_id number;
    v_last_used_extent_block_id number;
    v_last_used_block number;
    v_partition_name varchar2(50);
    v_initial number;
    v_next number;
    v_number_extents number;
    v_num_rows number;
    v_avg_row_len number;
    v_tablespace_name varchar2(30);

    begin

    IF v_object_type = 'TABLE'
    THEN
    SELECT initial_extent,next_extent,num_rows,avg_row_len, tablespace_name
    INTO v_initial, v_next, v_num_rows, v_avg_row_len, v_tablespace_name
    FROM all_tables
    WHERE owner = v_user
    AND table_name = v_object_name;
    ELSE
    SELECT initial_extent,next_extent, tablespace_name
    INTO v_initial, v_next, v_tablespace_name
    FROM all_indexes
    WHERE owner = v_user
    AND index_name = v_object_name;
    END IF;

    SELECT count(*)
    INTO v_number_extents
    FROM dba_extents
    WHERE owner = v_user
    AND segment_name = v_object_name;

    DBMS_SPACE.UNUSED_SPACE( v_user
    ,v_object_name
    ,v_object_type
    ,v_total_blocks
    ,v_total_bytes
    ,v_unused_blocks
    ,v_unused_bytes
    ,v_last_used_extent_file_id
    ,v_last_used_extent_block_id
    ,v_last_used_block
    ,v_partition_name );

    dbms_output.put_line('Object Name: '||v_object_name||' - '||v_object_type||' Tablespace: '||v_tablespace_name);
    dbms_output.put_line('. ');
    dbms_output.put_line('Number of Extents: '||v_number_extents||' Initial: '||v_initial||' Next: '||v_next);
    dbms_output.put_line('......Approx Rows: '||v_num_rows||' Avg Row Len: '||v_avg_row_len);
    dbms_output.put_line('Blocks in segment: '||v_total_blocks);
    dbms_output.put_line('Bytes in segment(Size): '||v_total_bytes);
    dbms_output.put_line('MegaBytes in segment: '||v_total_bytes/1048576);
    dbms_output.put_line('Blocks not used: '||v_unused_blocks);
    dbms_output.put_line('Bytes not used: '||v_unused_bytes);
    dbms_output.put_line('MegaBytes not used: '||v_unused_bytes/1048576);
    dbms_output.put_line('File ID - last extent with data(Block): '||v_last_used_extent_file_id);
    dbms_output.put_line('File ID - last extent with data(Byte): '||v_last_used_extent_block_id);
    dbms_output.put_line('Last block with data: '||v_last_used_block);

    end;
    /


    HTH
    Gregg

  6. #6
    Join Date
    Sep 2003
    Location
    China
    Posts
    72
    SELECT a.table_name,
    a.tablespace_name,
    ROUND(SUM (b.bytes) / 1024 / 1024, 3) gigabytes
    FROM user_tables a,
    user_extents b
    WHERE a.table_name = b.segment_name
    AND a.tablespace_name = b.tablespace_name
    GROUP BY a.tablespace_name, a.table_name
    ORDER BY gigabytes DESC, table_name ASC;

    This will show all the tables sizes in a schema. Modify it to show only the table you want.

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253

    Re: Table Size

    Originally posted by gschuah
    2. How to reorg a table if it grow too large?
    I guess it depends what steps are acceptable to you.

    Obviously, you could delete data. I expect that isn't what you meant, though.

    If you have a table for which the data is pretty static, meaning it's hardly ever modified, then you could consider compressing it.

    If you have been deleting data and the free space is not getting reused, then you may have too low a PCTUSED value, which sets the used-space threshold below which a block can be reused for the insert of new rows.

    To do a "one off" removal of the empty space you could use the "move table" command to get he gaps refilled -- "alter table my_table move pctfree 10 pctused 85" would rebuild the table and change pctused at the same time.

    If you have columns which are commonly null, then you could place them as the last columns in the table definition.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  8. #8
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by jmodic
    I wonder what this query is supposed to tell you about the table size in MB.... More or less nothing. But we've been through that many times in this forum....
    Wud'nt that query tell you how many blocks it has used, I mean the blocks that actually the table has data and not allocted, ofcourse allocated size will be higher than this..

    I am not sure wat is that u wana tell?

    Well what i meant by the ** "Table Size in MB" ** was that the actual size used by table. I wonder wat is the mistake in knowing actual Size as u can always query DBA views to know Allocated one.

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  9. #9
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by abhaysk
    I am not sure wat is that u wana tell?
    Ok, let's see:
    Code:
    SQL>  SELECT value FROM v$parameter
      2  WHERE name = 'db_block_size';
    
    VALUE
    --------------------------------------------
    8192
    
    SQL> CREATE TABLE test1
      2  (c1 VARCHAR2(4000),
      3   c2 VARCHAR2(4000),
      4   c3 VARCHAR2(4000),
      5   c4 VARCHAR2(4000),
      6   c5 VARCHAR2(4000))
      7  PCTFREE 0;
    
    Table created.
    
    SQL> INSERT INTO test1 (c1,c2,c3,c4,c5) VALUES
      2  (RPAD('x',4000,'x'),
      3   RPAD('x',4000,'x'),
      4   RPAD('x',4000,'x'),
      5   RPAD('x',4000,'x'),
      6   RPAD('x',4000,'x'));
    
    1 row created.
    
    SQL> /
    
    1 row created.
    
    SQL> /
    
    1 row created.
    
    SQL> /
    
    1 row created.
    
    SQL> /
    
    1 row created.
    
    SQL> COMMIT;
    
    Commit complete.
    
    SQL> SELECT COUNT(*) FROM test1;
    
      COUNT(*)
    ----------
             5
    So what we have now is a table with 5 rows in it, each row holding 5 columns, each of them filled with 4000 bytes. So each row should occupy about 20 Kbytes, all of them together abot 100 Kbytes. And what does your query return?
    Code:
    SQL> SELECT 
      2    COUNT(DISTINCT SUBSTR(ROWID, 1, 15))*8196/1024 
      3    AS "Table Size in KBytes" 
      4  FROM test1;
    
    Table Size in KBytes
    --------------------
              40,0195313
    
    SQL>
    It is more than evident that those 5 rows can not fit into just 40 Kbytes.

    Well what i meant by the ** "Table Size in MB" ** was that the actual size used by table. I wonder wat is the mistake in knowing actual Size
    Another problematic term - "actual size used by table". What is the "actual size used by table"? Sum of all table blocks that contain at least one row? Doesn't make sence to me, as it tells you nothing about how full those blocks actually are. Your query returns the same result no matter if each block contains only one tiny record of few bytes (and each block still has more than 95% free space left for new records or for updates of existing one, for example) or if each block is 100% fully packed with data, not able to accept a single byte more.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  10. #10
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by jmodic
    Ok, let's see:
    So what we have now is a table with 5 rows in it, each row holding 5 columns, each of them filled with 4000 bytes. So each row should occupy about 20 Kbytes, all of them together abot 100 Kbytes. And what does your query return?
    Code:
    SQL> SELECT 
      2    COUNT(DISTINCT SUBSTR(ROWID, 1, 15))*8196/1024 
      3    AS "Table Size in KBytes" 
      4  FROM test1;
    
    Table Size in KBytes
    --------------------
              40,0195313
    
    SQL>
    Ok you got so many KBs?????? ( for jus 5 rows, confused here )...

    Ok lets see what i get..

    Code:
    US18_DEV_DBA> SELECT value FROM v$parameter
      2  WHERE name = 'db_block_size';
    
    VALUE
    ------------------------------------------------------------------------
    4096
    
    US18_DEV_DBA> 
    US18_DEV_DBA> CREATE TABLE test_2
      2  (c1 VARCHAR2(4000),
      3  c2 VARCHAR2(4000),
      4  c3 VARCHAR2(4000),
      5  c4 VARCHAR2(4000),
      6  c5 VARCHAR2(4000))
      7  PCTFREE 0;
    
    Table created.
    
    US18_DEV_DBA> 
    US18_DEV_DBA> INSERT INTO test_2 (c1,c2,c3,c4,c5) VALUES
      2  (RPAD('x',4000,'x'),
      3  RPAD('x',4000,'x'),
      4  RPAD('x',4000,'x'),
      5  RPAD('x',4000,'x'),
      6  RPAD('x',4000,'x'));
    
    1 row created.
    
    US18_DEV_DBA> /
    
    1 row created.
    
    US18_DEV_DBA> /
    
    1 row created.
    
    US18_DEV_DBA> /
    
    1 row created.
    
    US18_DEV_DBA> /
    
    1 row created.
    
    US18_DEV_DBA> COMMIT;
    
    Commit complete.
    
    US18_DEV_DBA> 
    US18_DEV_DBA> SELECT COUNT(*) FROM test_2;
    
                COUNT(*)
    --------------------
                       5
    
    US18_DEV_DBA> SELECT 
      2  COUNT(DISTINCT SUBSTR(ROWID, 1, 15))*4096/1024 
      3  AS "Table Size in KBytes" 
      4  FROM test_2;
    
    Table Size in KBytes
    --------------------
                      20
    
    
    US18_DEV_DBA> ed
    Wrote file afiedt.buf
    
      1  SELECT
      2  COUNT(DISTINCT SUBSTR(ROWID, 1, 15)) AS "No Of Blocks Used By Table"
      3* FROM test_2
    US18_DEV_DBA> /
    
    No Of Blocks Used By Table
    --------------------------
                             5
    The one which should have taken min of 100KB is well within 20KB??
    Thus we need to understand a bit more on how the space is used up....

    Originally posted by jmodic
    Another problematic term - "actual size used by table". What is the "actual size used by table"? Sum of all table blocks that contain at least one row? Doesn't make sence to me, as it tells you nothing about how full those blocks actually are. Your query returns the same result no matter if each block contains only one tiny record of few bytes (and each block still has more than 95% free space left for new records or for updates of existing one, for example) or if each block is 100% fully packed with data, not able to accept a single byte more.
    Its upto one how one thinks, if you want the effeciency of the utilazation of blocks then you gotta take an AVG & interpret then
    But i dont think the original poster wanted that, if yes then we can giv him that query as well !!

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width