space bound objects

[charity]

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

[pando]

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]

[charity]

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?

[pando]

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

[charity]

thanks,
I found the answer to the problem

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