+ Reply to Thread
Results 1 to 7 of 7

how to check table size in oracle 9i

  1. how to check table size in oracle 9i

    how to check table size in oracle 9i

    PLz help


  2. Re: how to check table size in oracle 9i

    "manish" wrote in news:1150512490.732340.18750
    @c74g2000cwc.googlegroups.com:

    > how to check table size in oracle 9i
    >
    > PLz help
    >
    >


    SELECT SUM(BYTES) FROM DBA_SEGMENTS WHERE SEGMENT_NAME = 'MY_TABLE';

  3. Re: how to check table size in oracle 9i

    "manish" wrote in news:1150512490.732340.18750
    @c74g2000cwc.googlegroups.com:

    > how to check table size in oracle 9i
    >
    > PLz help
    >
    >


    SELECT SUM(BYTES) FROM DBA_SEGMENTS WHERE SEGMENT_NAME = 'MY_TABLE';

  4. Re: how to check table size in oracle 9i


    manish wrote:
    > how to check table size in oracle 9i
    >
    > PLz help


    Hi,

    Let me walk you through a example.

    I want to find the size for the following table:

    OWNER TABLESPACE_NAME
    TABLE_NAME
    ------------------------------ ------------------------------
    ------------------------------
    PERF USERS
    BACKUP_DETAIL

    SQL> show parameter db_block_size

    NAME TYPE VALUE
    ------------------------------------ -----------
    ------------------------------
    db_block_size integer 8192

    You can calculte in 2 ways both are mentioned below:

    select owner,segment_name,
    bytes/1024 as totsize_bytes,
    (blocks*8192)/1024 as totsize_block
    from dba_segments
    where owner='PERF'
    and segment_name='CONN';

    OWNER SEGMENT_NA BYTESIZE BLOCKSIZE
    ------ ---------- ---------- ----------
    PERF CONN 64 64

    Hope this helps.

    Regards,
    Vasant


  5. Re: how to check table size in oracle 9i


    manish wrote:
    > how to check table size in oracle 9i
    >
    > PLz help


    Hi,

    Let me walk you through a example.

    I want to find the size for the following table:

    OWNER TABLESPACE_NAME
    TABLE_NAME
    ------------------------------ ------------------------------
    ------------------------------
    PERF USERS
    BACKUP_DETAIL

    SQL> show parameter db_block_size

    NAME TYPE VALUE
    ------------------------------------ -----------
    ------------------------------
    db_block_size integer 8192

    You can calculte in 2 ways both are mentioned below:

    select owner,segment_name,
    bytes/1024 as totsize_bytes,
    (blocks*8192)/1024 as totsize_block
    from dba_segments
    where owner='PERF'
    and segment_name='CONN';

    OWNER SEGMENT_NA BYTESIZE BLOCKSIZE
    ------ ---------- ---------- ----------
    PERF CONN 64 64

    Hope this helps.

    Regards,
    Vasant


  6. Re: how to check table size in oracle 9i


    vasant.naidu@gmail.com wrote:
    > manish wrote:
    > > how to check table size in oracle 9i
    > >
    > > PLz help

    >
    > Hi,
    >
    > Let me walk you through a example.
    >
    > I want to find the size for the following table:
    >
    > OWNER TABLESPACE_NAME
    > TABLE_NAME
    > ------------------------------ ------------------------------
    > ------------------------------
    > PERF USERS
    > BACKUP_DETAIL
    >
    > SQL> show parameter db_block_size
    >
    > NAME TYPE VALUE
    > ------------------------------------ -----------
    > ------------------------------
    > db_block_size integer 8192
    >
    > You can calculte in 2 ways both are mentioned below:
    >
    > select owner,segment_name,
    > bytes/1024 as totsize_bytes,
    > (blocks*8192)/1024 as totsize_block
    > from dba_segments
    > where owner='PERF'
    > and segment_name='CONN';
    >
    > OWNER SEGMENT_NA BYTESIZE BLOCKSIZE
    > ------ ---------- ---------- ----------
    > PERF CONN 64 64
    >
    > Hope this helps.
    >
    > Regards,
    > Vasant


    That's wonderful, but where, exactly, is the size for the BACKUP_DETAIL
    table? I see the size for the CONN table, which, according to your
    stated problem, isn't relevant to the discussion. And why is the
    calculated byte size based upon the blocks and db_block_size still
    referred to as BLOCKSIZE? It isn't, it's the total bytes based upon
    the block count; the column header is misleading. You also assume,
    possibly wrongly, that the user has DBA access.

    The query should be thus for a table in the user's schema:

    select segment_name,
    bytes/1024 as totsize_bytes,
    (blocks*8192)/1024 as totbytes_by_blocks
    from user_segments
    where segment_name = upper('&1');

    Should the user possess DBA privileges in the database:

    select owner, segment_name,
    bytes/1024 as totsize_bytes,
    (blocks*8192)/1024 as totbytes_by_blocks
    from dba_segments
    where owner = upper('&1')
    and segment_name = upper('&2');

    These are as generic as possible, allowing the user to submit any valid
    segment name (and owner, in the dba_segments query) in order to produce
    results. Of course to truly answer the original question one must also
    restrict the segment_type to 'TABLE':

    "how to check table size in oracle 9i"

    Therefore the query would be:

    select segment_name,
    bytes/1024 as totsize_bytes,
    (blocks*8192)/1024 as totbytes_by_blocks
    from user_segments
    where segment_name = upper('&1')
    and segment_type = 'TABLE';

    Or:

    select owner, segment_name,
    bytes/1024 as totsize_bytes,
    (blocks*8192)/1024 as totbytes_by_blocks
    from dba_segments
    where owner = upper('&1')
    and segment_name = upper('&2')
    and segment_type = 'TABLE';

    As for the size of the BACKUP_DETAIL table, well, we're still waiting
    for that answer. Possibly you can finally provide that information.


    David Fitzjarrell


  7. Re: how to check table size in oracle 9i


    vasant.naidu@gmail.com wrote:
    > manish wrote:
    > > how to check table size in oracle 9i
    > >
    > > PLz help

    >
    > Hi,
    >
    > Let me walk you through a example.
    >
    > I want to find the size for the following table:
    >
    > OWNER TABLESPACE_NAME
    > TABLE_NAME
    > ------------------------------ ------------------------------
    > ------------------------------
    > PERF USERS
    > BACKUP_DETAIL
    >
    > SQL> show parameter db_block_size
    >
    > NAME TYPE VALUE
    > ------------------------------------ -----------
    > ------------------------------
    > db_block_size integer 8192
    >
    > You can calculte in 2 ways both are mentioned below:
    >
    > select owner,segment_name,
    > bytes/1024 as totsize_bytes,
    > (blocks*8192)/1024 as totsize_block
    > from dba_segments
    > where owner='PERF'
    > and segment_name='CONN';
    >
    > OWNER SEGMENT_NA BYTESIZE BLOCKSIZE
    > ------ ---------- ---------- ----------
    > PERF CONN 64 64
    >
    > Hope this helps.
    >
    > Regards,
    > Vasant


    That's wonderful, but where, exactly, is the size for the BACKUP_DETAIL
    table? I see the size for the CONN table, which, according to your
    stated problem, isn't relevant to the discussion. And why is the
    calculated byte size based upon the blocks and db_block_size still
    referred to as BLOCKSIZE? It isn't, it's the total bytes based upon
    the block count; the column header is misleading. You also assume,
    possibly wrongly, that the user has DBA access.

    The query should be thus for a table in the user's schema:

    select segment_name,
    bytes/1024 as totsize_bytes,
    (blocks*8192)/1024 as totbytes_by_blocks
    from user_segments
    where segment_name = upper('&1');

    Should the user possess DBA privileges in the database:

    select owner, segment_name,
    bytes/1024 as totsize_bytes,
    (blocks*8192)/1024 as totbytes_by_blocks
    from dba_segments
    where owner = upper('&1')
    and segment_name = upper('&2');

    These are as generic as possible, allowing the user to submit any valid
    segment name (and owner, in the dba_segments query) in order to produce
    results. Of course to truly answer the original question one must also
    restrict the segment_type to 'TABLE':

    "how to check table size in oracle 9i"

    Therefore the query would be:

    select segment_name,
    bytes/1024 as totsize_bytes,
    (blocks*8192)/1024 as totbytes_by_blocks
    from user_segments
    where segment_name = upper('&1')
    and segment_type = 'TABLE';

    Or:

    select owner, segment_name,
    bytes/1024 as totsize_bytes,
    (blocks*8192)/1024 as totbytes_by_blocks
    from dba_segments
    where owner = upper('&1')
    and segment_name = upper('&2')
    and segment_type = 'TABLE';

    As for the size of the BACKUP_DETAIL table, well, we're still waiting
    for that answer. Possibly you can finally provide that information.


    David Fitzjarrell


+ Reply to Thread