Check if a table has reached it maximum size?
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Check if a table has reached it maximum size?

  1. #1
    Join Date
    Jul 2002
    Posts
    6
    Hi,
    I'm a junior dba and was wondering how do you check if a table has reached it's maximum size??We have a database on an NT server and the following error is coming up on the system event log - "The server could not expand a table because the table reached the maximum size" withe an SQL error type 2009.Is there a view or a query that I can use that will tell me what tables are reaching their maxextents level??

    Thanks
    TheWing
    Born on a Different Cloud

  2. #2
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    DBA_SEGMENTS
    Reddy,Sam

  3. #3
    Join Date
    May 2002
    Posts
    2,645
    SQL error type? You mean ORA error type? 2009? That is related to a file size specification error.

    ORA-02009 the size specified for a file must not be zero

    Cause: A value of zero was specified in a SIZE or RESIZE clause of a file specification.

    Action: Use correct syntax, or, if allowed, omit the SIZE or RESIZE clause


    What is maximum size for a table? Do you mean you're out of space, max number of extents has been reached, or what? Tables don't reach a maximum size - they grow in accordance with the storage parameters you set.

  4. #4
    Join Date
    Jul 2002
    Posts
    6
    yeah, how do i check if maxextents has been reached?
    Born on a Different Cloud

  5. #5
    Join Date
    May 2002
    Posts
    2,645
    Try dba_tables

  6. #6
    Join Date
    Jul 2002
    Posts
    6
    And what columns in dba_tables will tell besides maxextents?Which column tells me the number of extents used??
    Born on a Different Cloud

  7. #7
    Join Date
    May 2002
    Posts
    2,645
    Do you know how to use the describe command? Do you have any DBA books?

  8. #8
    Join Date
    Jul 2002
    Posts
    6
    I know that much, so which of these when compared with each other will let me see which tables are reaching their maxextents level??

    SQL> desc dba_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>
    Born on a Different Cloud

  9. #9
    Join Date
    Oct 2001
    Location
    Madrid, Spain
    Posts
    763
    I think it's what you're looking for

    SET LINESIZE 500
    SET PAGESIZE 1000
    SET VERIFY OFF

    PROMPT
    PROMPT Tables nearing MAX_EXTENTS
    PROMPT **************************
    SELECT t.table_name,
    e.extents,
    t.max_extents,
    Trunc(t.initial_extent/1024) "INITIAL K",
    Trunc(t.next_extent/1024) "NEXT K"
    FROM all_tables t,
    (SELECT segment_name,
    owner,
    count(*) extents
    FROM dba_extents
    WHERE segment_type = 'TABLE'
    GROUP BY segment_name, owner) e
    WHERE e.segment_name = t.table_name
    AND e.owner = t.owner
    AND e.extents > (t.max_extents - 5)
    ORDER BY e.extents DESC
    /

    PROMPT Indexes nearing MAX_EXTENTS
    PROMPT ***************************
    SELECT i.index_name,
    e.extents,
    i.max_extents,
    Trunc(i.initial_extent/1024) "INITIAL K",
    Trunc(i.next_extent/1024) "NEXT K"
    FROM all_indexes i,
    (SELECT segment_name,
    owner,
    count(*) extents
    FROM dba_extents
    WHERE segment_type = 'INDEX'
    GROUP BY segment_name, owner) e
    WHERE e.segment_name = i.index_name
    AND e.owner = i.owner
    AND e.extents > (i.max_extents - 5)
    ORDER BY e.extents DESC
    /

  10. #10
    Join Date
    Jul 2002
    Posts
    6
    aarroyob - my hero!!!Way too hungover hence using this board for answer.

    Cheers
    TheWing
    Born on a Different Cloud

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