DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: space bound objects

Hybrid View

  1. #1
    Join Date
    Aug 2000
    Location
    NY
    Posts
    37

    Talking

    Can any body explain this query to me? All I know is it's used for space bound objects. I have no Idea what that is. I ran the query and got the result below. Can any body tell me what's wrong with my tablespace and how to correct it? I know there is something wrong but I can't tell what it is.


    QUESTION1
    SQL> SELECT a.table_name, a.next_extent, a.tablespace_name
    FROM all_tables a,
    ( SELECT tablespace_name, max(bytes) as big_chunk
    FROM dba_free_space
    GROUP BY tablespace_name ) f
    WHERE f.tablespace_name = a.tablespace_name
    AND a.next_extent > f.big_chunk

    SQL> /

    TABLE_NAME NEXT_EXTENT TABLESPACE_NAME
    ----------- ---------- ------- -------
    EMPLOYEE 460800000 ORANGE

    NOTE: This what I got after I queried the dba_segment view:
    SQL>
    1* select segment_name, segment_type, initial_extent, next_extent, max_extents,owner from dba_segments where segment_name = 'EMPLOYEE' and owner = 'JOSEPH'
    SQL> /

    SEGMENT_N SEGMENT_TYP INITIAL_EXTENT NEXT_EXTENT
    --------- ----------- -------------- ----------- -
    EMPLOYEE TABLE 460800000 460800000
    -----
    SEGMENT_OWNER MAX_EXTENTS
    ------------ -----------
    JOSEPH 500


    NOTE: This is the query is from dba_extents:
    SEGMENT_NAME BYTES TABLESPACE_NAM OWNER
    ------------ -------- -------- -----------
    EMPLOYEE 460800000 ORANGE JOSEPH



    thanks,
    charity

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    it means that there arent any free extent big enough in tablespace orange to accomodate next extent of table employee. May be you should try coalesce the tablespace first then run the query again.

    [Edited by pando on 01-15-2001 at 03:57 AM]

  3. #3
    Join Date
    Aug 2000
    Location
    NY
    Posts
    37
    Hi Pando and the rest,
    I coalesced the tablespace and run the query again and I got the same results
    SQL> SELECT a.table_name, a.next_extent, a.tablespace_name
    FROM all_tables a,
    ( SELECT tablespace_name, max(bytes) as big_chunk
    FROM dba_free_space
    GROUP BY tablespace_name ) f
    WHERE f.tablespace_name = a.tablespace_name
    AND a.next_extent > f.big_chunk

    SQL> /

    TABLE_NAME NEXT_EXTENT TABLESPACE_NAME
    ----------- ---------- ------- -------
    EMPLOYEE 460800000 ORANGE


    What do you think is the next thing to do?


  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    if your datafile has autoextend then Oracle will extend the datafile to accomodate the new extent,if not you will get an error and you will have to extend the datafile manually or add a new datafile

  5. #5
    Join Date
    Aug 2000
    Location
    NY
    Posts
    37
    thanks,
    I found the answer to the problem

    [Edited by charity on 01-15-2001 at 04:50 PM]

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