Results 1 to 13 of 13

Thread: Get table space

  1. #1
    Join Date
    Jan 2004
    Location
    Bangalore
    Posts
    1

    Smile Unanswered: Get table space

    I cannot get the size of a table inside Oracle. Is there any special query is available ?

  2. #2
    Join Date
    Jan 2004
    Location
    Venezuela
    Posts
    151

    Re: Get table space

    You can do it so:

    SQL> desc joel_table
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    C1 NUMBER
    C2 VARCHAR2(5)

    SQL> select bytes from dba_segments
    2 where owner='SYSTEM' and segment_name='JOEL_TABLE';

    BYTES
    ----------
    32768

    SQL>
    Joel Pérez

  3. #3
    Join Date
    Jan 2004
    Location
    Venezuela
    Posts
    151

    Re: Get table space

    DBA_SEGMENTS
    DBA_SEGMENTS describes the storage allocated for all segments in the database.

    Related View
    USER_SEGMENTS describes the storage allocated for the segments owned by the current user's objects. This view does not display the OWNER, HEADER_FILE, HEADER_BLOCK, or RELATIVE_FNO columns.

    Column Datatype NULL Description
    OWNER
    VARCHAR2(30)
    Username of the segment owner

    SEGMENT_NAME
    VARCHAR2(81)
    Name, if any, of the segment

    PARTITION_NAME
    VARCHAR2(30)
    Object Partition Name (Set to NULL for non-partitioned objects)

    SEGMENT_TYPE
    VARCHAR2(17)
    Type of segment: INDEX PARTITION, TABLE PARTITION, TABLE, CLUSTER, INDEX, ROLLBACK, DEFERRED ROLLBACK, TEMPORARY, CACHE, LOBSEGMENT and LOBINDEX

    TABLESPACE_NAME
    VARCHAR2(30)
    Name of the tablespace containing the segment

    HEADER_FILE
    NUMBER
    ID of the file containing the segment header

    HEADER_BLOCK
    NUMBER
    ID of the block containing the segment header

    BYTES
    NUMBER
    Size in bytes, of the segment

    BLOCKS
    NUMBER
    Size, in Oracle blocks, of the segment

    EXTENTS
    NUMBER
    Number of extents allocated to the segment

    INITIAL_EXTENT
    NUMBER
    Size in bytes requested for the initial extent of the segment at create time. (Oracle rounds the extent size to multiples of 5 blocks if the requested size is greater than 5 blocks.)

    NEXT_EXTENT
    NUMBER
    Size in bytes of the next extent to be allocated to the segment

    MIN_EXTENTS
    NUMBER
    Minimum number of extents allowed in the segment

    MAX_EXTENTS
    NUMBER
    Maximum number of extents allowed in the segment

    PCT_INCREASE
    NUMBER
    Percent by which to increase the size of the next extent to be allocated

    FREELISTS
    NUMBER
    Number of process freelists allocated to this segment

    FREELIST_GROUPS
    NUMBER
    Number of freelist groups allocated to this segment

    RELATIVE_FNO
    NUMBER
    Relative file number of the segment header

    BUFFER_POOL
    VARCHAR2(7)
    Default buffer pool for the object

    http://download-west.oracle.com/docs...htm#REFRN23243
    Joel Pérez

  4. #4
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Attached is a little script that I run that tells me about the table structure
    size allocated, number extents, used space, unused space etc ...

    At the top of the script: CHANGE THE OWNER AND THE TABLE_NAME,
    Cut and paste into SQLPlus ... and have fun

    HTH
    Gregg
    Attached Files Attached Files

  5. #5
    Join Date
    Jan 2004
    Location
    Venezuela
    Posts
    151
    Originally posted by gbrabham
    Attached is a little script that I run that tells me about the table structure
    size allocated, number extents, used space, unused space etc ...

    At the top of the script: CHANGE THE OWNER AND THE TABLE_NAME,
    Cut and paste into SQLPlus ... and have fun

    HTH
    Gregg
    I am running the script that you sent but see what is happening:

    I changed this:

    v_user varchar2(50) := 'SYSTEM';
    v_object_name varchar2(50) := 'TABLE';
    v_object_type varchar2(15) := 'JOEL_TABLE';

    **************

    SQL> DESC JOEL_TABLE
    Name Null? Type
    ----------------------------------------- -------- ----------------------------

    C1 NUMBER
    C2 VARCHAR2(5)

    SQL> SHOW USER
    USER is "SYSTEM"
    SQL>
    SQL> @table_free_space
    declare
    *
    ERROR at line 1:
    ORA-01403: no data found
    ORA-06512: at line 30


    SQL> host type table_free_space.sql
    /**************************************************/
    /* Find the highwater mark in a table */
    /* gb - PSAConsulting */
    /**************************************************/

    set serveroutput on

    declare
    v_user varchar2(50) := 'SYSTEM';
    v_object_name varchar2(50) := 'TABLE';
    v_object_type varchar2(15) := 'JOEL_TABLE';
    v_total_blocks number;
    v_total_bytes number;
    v_unused_blocks number;
    v_unused_bytes number;
    v_last_used_extent_file_id number;
    ..........
    .........


    What is happening with the script ?
    Joel Pérez

  6. #6
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Is SYSTEM the owner of JOEL_TABLE ???

    v_user varchar2(50) := 'SYSADM';
    v_object_name varchar2(50) := 'CUSTOMER';
    v_object_type varchar2(15) := 'TABLE';

    Change the v_user to be the schema_owner (owner of the table)
    v_object_name - The table_name (or index_name)
    v_object_type - Either TABLE or INDEX ...

    No data found ... it is not find in all_tables (or all_indexes)
    for the table / owner combination ...

    HTH
    Gregg

  7. #7
    Join Date
    Jan 2004
    Location
    Venezuela
    Posts
    151
    Originally posted by gbrabham
    Is SYSTEM the owner of JOEL_TABLE ???

    v_user varchar2(50) := 'SYSADM';
    v_object_name varchar2(50) := 'CUSTOMER';
    v_object_type varchar2(15) := 'TABLE';

    Change the v_user to be the schema_owner (owner of the table)
    v_object_name - The table_name (or index_name)
    v_object_type - Either TABLE or INDEX ...

    No data found ... it is not find in all_tables (or all_indexes)
    for the table / owner combination ...

    HTH
    Gregg
    Yes,

    SQL> select owner from all_tables
    2 where table_name='JOEL_TABLE';

    OWNER
    ------------------------------
    SYSTEM

    SQL>
    Joel Pérez

  8. #8
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    I guess this is because num_rows and avg_row_len in the dba_tables are empty before ANALYZE. So first of all you have to ANALYZE the table (your test table).
    So, ANALYZE IT and then Run the Script.

    Anyhow, You can modify this Script by including EXCEPTION section with NO_DATA_FOUND exception.

    HTH
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  9. #9
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Do you have access to dba_extents and DBMS_SPACE.UNUSED_SPACE
    all_tables, and all_indexes ???

    Those are the only objects that are called ...

    The result should look like ....

    Object Name: CUSTOMER - TABLE Tablespace: CUSTOMER

    Number of Extents: 1 Initial: 8192 Next: 13631488
    ......Approx Rows: 163577856 Avg Row Len: 36
    Blocks in segment: 39954
    Bytes in segment(Size): 163651584
    MegaBytes in segment: 156.0703125
    Blocks not used: 765
    Bytes not used: 3133440
    MegaBytes not used: 2.98828125
    File ID - last extent with data(Block): 4
    File ID - last extent with data(Byte): 115673
    Last block with data: 2565


    hth
    Gregg

  10. #10
    Join Date
    Jan 2004
    Location
    Venezuela
    Posts
    151
    Look at this:

    *************************************************

    SQL> conn system@ldevcl2
    Enter password:
    Connected.
    SQL>
    SQL> analyze table joel_table compute statistics for table for all indexes;

    Table analyzed.

    SQL> @table_free_space
    declare
    *
    ERROR at line 1:
    ORA-01403: no data found
    ORA-06512: at line 30


    SQL> select * from joel_table;

    no rows selected

    SQL> desc joel_table
    Name Null? Type
    ----------------------------------------- -------- ----------------------------

    C1 NUMBER
    C2 VARCHAR2(5)

    SQL> insert into joel_table values ( 1, 'jjjj');

    1 row created.

    SQL> insert into joel_table values ( 1, 'jjjj');

    1 row created.

    SQL> insert into joel_table values ( 1, 'jjjj');

    1 row created.

    SQL> insert into joel_table values ( 1, 'jjjj');

    1 row created.

    SQL> analyze table joel_table compute statistics for table for all indexes;

    Table analyzed.

    SQL> @table_free_space
    declare
    *
    ERROR at line 1:
    ORA-01403: no data found
    ORA-06512: at line 30


    SQL> commit;

    Commit complete.

    SQL> analyze table joel_table compute statistics for table for all indexes;

    Table analyzed.

    SQL> @table_free_space
    declare
    *
    ERROR at line 1:
    ORA-01403: no data found
    ORA-06512: at line 30


    SQL> desc dbms_space
    PROCEDURE FREE_BLOCKS
    Argument Name Type In/Out Default?
    ------------------------------ ----------------------- ------ --------
    SEGMENT_OWNER VARCHAR2 IN
    SEGMENT_NAME VARCHAR2 IN
    SEGMENT_TYPE VARCHAR2 IN
    FREELIST_GROUP_ID NUMBER IN
    FREE_BLKS NUMBER OUT
    SCAN_LIMIT NUMBER IN DEFAULT
    PARTITION_NAME VARCHAR2 IN DEFAULT
    PROCEDURE UNUSED_SPACE
    Argument Name Type In/Out Default?
    ------------------------------ ----------------------- ------ --------
    SEGMENT_OWNER VARCHAR2 IN
    SEGMENT_NAME VARCHAR2 IN
    SEGMENT_TYPE VARCHAR2 IN
    TOTAL_BLOCKS NUMBER OUT
    TOTAL_BYTES NUMBER OUT
    UNUSED_BLOCKS NUMBER OUT
    UNUSED_BYTES NUMBER OUT
    LAST_USED_EXTENT_FILE_ID NUMBER OUT
    LAST_USED_EXTENT_BLOCK_ID NUMBER OUT
    LAST_USED_BLOCK NUMBER OUT
    PARTITION_NAME VARCHAR2 IN DEFAULT

    SQL> desc all_tables
    Name Null? Type
    ----------------------------------------- -------- ----------------------------

    OWNER NOT NULL VARCHAR2(30)
    TABLE_NAME NOT NULL VARCHAR2(30)
    TABLESPACE_NAME VARCHAR2(30)
    CLUSTER_NAME VARCHAR2(30)
    IOT_NAME VARCHAR2(30)
    PCT_FREE NUMBER
    PCT_USED NUMBER
    INI_TRANS NUMBER
    MAX_TRANS NUMBER
    INITIAL_EXTENT NUMBER
    NEXT_EXTENT NUMBER
    MIN_EXTENTS NUMBER
    MAX_EXTENTS NUMBER
    PCT_INCREASE NUMBER
    FREELISTS NUMBER
    FREELIST_GROUPS NUMBER
    LOGGING VARCHAR2(3)
    BACKED_UP VARCHAR2(1)
    NUM_ROWS NUMBER
    BLOCKS NUMBER
    EMPTY_BLOCKS NUMBER
    AVG_SPACE NUMBER
    CHAIN_CNT NUMBER
    AVG_ROW_LEN NUMBER
    AVG_SPACE_FREELIST_BLOCKS NUMBER
    NUM_FREELIST_BLOCKS NUMBER
    DEGREE VARCHAR2(10)
    INSTANCES VARCHAR2(10)
    CACHE VARCHAR2(5)
    TABLE_LOCK VARCHAR2(8)
    SAMPLE_SIZE NUMBER
    LAST_ANALYZED DATE
    PARTITIONED VARCHAR2(3)
    IOT_TYPE VARCHAR2(12)
    TEMPORARY VARCHAR2(1)
    SECONDARY VARCHAR2(1)
    NESTED VARCHAR2(3)
    BUFFER_POOL VARCHAR2(7)
    ROW_MOVEMENT VARCHAR2(8)
    GLOBAL_STATS VARCHAR2(3)
    USER_STATS VARCHAR2(3)
    DURATION VARCHAR2(15)
    SKIP_CORRUPT VARCHAR2(8)
    MONITORING VARCHAR2(3)
    CLUSTER_OWNER VARCHAR2(30)

    SQL> desc all_indexes
    Name Null? Type
    ----------------------------------------- -------- ----------------------------

    OWNER NOT NULL VARCHAR2(30)
    INDEX_NAME NOT NULL VARCHAR2(30)
    INDEX_TYPE VARCHAR2(27)
    TABLE_OWNER NOT NULL VARCHAR2(30)
    TABLE_NAME NOT NULL VARCHAR2(30)
    TABLE_TYPE CHAR(5)
    UNIQUENESS VARCHAR2(9)
    COMPRESSION VARCHAR2(8)
    PREFIX_LENGTH NUMBER
    TABLESPACE_NAME VARCHAR2(30)
    INI_TRANS NUMBER
    MAX_TRANS NUMBER
    INITIAL_EXTENT NUMBER
    NEXT_EXTENT NUMBER
    MIN_EXTENTS NUMBER
    MAX_EXTENTS NUMBER
    PCT_INCREASE NUMBER
    PCT_THRESHOLD NUMBER
    INCLUDE_COLUMN NUMBER
    FREELISTS NUMBER
    FREELIST_GROUPS NUMBER
    PCT_FREE NUMBER
    LOGGING VARCHAR2(3)
    BLEVEL NUMBER
    LEAF_BLOCKS NUMBER
    DISTINCT_KEYS NUMBER
    AVG_LEAF_BLOCKS_PER_KEY NUMBER
    AVG_DATA_BLOCKS_PER_KEY NUMBER
    CLUSTERING_FACTOR NUMBER
    STATUS VARCHAR2(8)
    NUM_ROWS NUMBER
    SAMPLE_SIZE NUMBER
    LAST_ANALYZED DATE
    DEGREE VARCHAR2(40)
    INSTANCES VARCHAR2(40)
    PARTITIONED VARCHAR2(3)
    TEMPORARY VARCHAR2(1)
    GENERATED VARCHAR2(1)
    SECONDARY VARCHAR2(1)
    BUFFER_POOL VARCHAR2(7)
    USER_STATS VARCHAR2(3)
    DURATION VARCHAR2(15)
    PCT_DIRECT_ACCESS NUMBER
    ITYP_OWNER VARCHAR2(30)
    ITYP_NAME VARCHAR2(30)
    PARAMETERS VARCHAR2(1000)
    GLOBAL_STATS VARCHAR2(3)
    DOMIDX_STATUS VARCHAR2(12)
    DOMIDX_OPSTATUS VARCHAR2(6)
    FUNCIDX_STATUS VARCHAR2(8)

    SQL> host type table_free_space.sql
    /**************************************************/
    /* Find the highwater mark in a table */
    /* gb - PSAConsulting */
    /**************************************************/

    set serveroutput on

    declare
    v_user varchar2(50) := 'SYSTEM';
    v_object_name varchar2(50) := 'TABLE';
    v_object_type varchar2(15) := 'JOEL_TABLE';

    ..................
    .................
    end;
    /

    SQL> show user
    USER is "SYSTEM"
    SQL>
    SQL>
    Joel Pérez

  11. #11
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    v_object_name varchar2(50) := 'TABLE';
    v_object_type varchar2(15) := 'JOEL_TABLE';

    You have this backwards ....

    v_object_name varchar2(50) := 'JOEL_TABLE';
    v_object_type varchar2(15) := 'TABLE';

    This should work better ... You changed object_type to JOEL_TABLE. The
    IF is looking for object_type = TABLE ... else consider this an INDEX ...
    The code CANNOT find JOEL_TABLE in ALL_INDEXES


    GREGG

  12. #12
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Did this take care of your problem Joel ???

    Gregg

  13. #13
    Join Date
    Jan 2004
    Location
    Venezuela
    Posts
    151
    Thanks. As you said, I was seeting in inverse way the parameters.
    Joel Pérez

Posting Permissions

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