Hi people : in a 10Gr1 EE dw-like database, I have the following non-partitioned, non-IOT (heap only) table, residing in a non-ASSM uniform-sized LMT tablespace :

Code:
chiappa@DWBAN:SQL>@desc TB_ORGAN_RECEIVE ;
 Nome                                      Nulo?    TYPE
 ----------------------------------------- -------- ----------------------------
 TYPE_ORGAN_REC                            NOT NULL VARCHAR2(1)
 DESC_TYPE_ORGAN_REC                                VARCHAR2(30)
 COD_ORGAN                                 NOT NULL NUMBER
 DESC_ORGAN_REC                                     VARCHAR2(35)
The table have few rows :

chiappa@DWBAN:SQL>select count(*) from TB_ORGAN_RECEIVE ;

COUNT(*)
------------------
4283

But it is occupying a lot of physical space :

Code:
chiappa@DWBAN:SQL>select * from user_segments where segment_name='TB_ORGAN_RECEIVE';

SEGMENT_NAME                        PARTITION_NAME                 SEGMENT_TYPE       TABLESPACE_NAME                             BYTES             BLOCKS            EXTENTS     INITIAL_EXTENT        NEXT_EXTENT        MIN_EXTENTS        MAX_EXTENTS       PCT_INCREASE          FREELISTS    FREELIST_GROUPS BUFFER_
----------------------------------- ------------------------------ ------------------ ------------------------------ ------------------ ------------------ ------------------ ------------------ ------------------ ------------------ ------------------ ------------------ ------------------ ------------------ -------
TB_ORGAN_RECEIVE                                                    TABLE              DW_INTERFACE_DAT                       1845493760             225280                440            4194304            4194304                  1         2147483645                  0                                       DEFAULT
I queried the unused space (via DBMS_SPACE), and there is no un-allocated space :

Code:
==============================================
Segment_name                 = TB_ORGAN_RECEIVE
Total Blocks                 = 225280
Total Bytes                  = 1845493760
Unused (Free) Blocks         = 0
Unused (Free) Bytes          = 0
Used Blocks                  = 225280
Used Bytes                   = 1845493760
Last used extents file id    = 5
Last used extents block id   = 3831816
Last used block              = 512
=============================================
And nope. SO, I guessed a white-space question (ie, the table someday was loaded with a lot of data, much of the data was deleted (not truncated), as normal the DELETE keep the now non-used space reserved to the segment, AND after that APPEND-mode loaded data above this. In this line of thought, I made a move :

chiappa@DWBAN:SQL>alter table TB_ORGAN_RECEIVE move;

Tabela alterada.


and yes, this was the point :


Code:
chiappa@DWBAN:SQL>select * from user_segments where segment_name='TB_ORGAN_RECEIVE';

SEGMENT_NAME                        PARTITION_NAME                 SEGMENT_TYPE       TABLESPACE_NAME                             BYTES             BLOCKS            EXTENTS     INITIAL_EXTENT        NEXT_EXTENT        MIN_EXTENTS        MAX_EXTENTS       PCT_INCREASE          FREELISTS    FREELIST_GROUPS BUFFER_
----------------------------------- ------------------------------ ------------------ ------------------------------ ------------------ ------------------ ------------------ ------------------ ------------------ ------------------ ------------------ ------------------ ------------------ ------------------ -------
TB_ORGAN_RECEIVE                                                    TABLE              DW_INTERFACE_DAT                          4194304                512                  1            4194304            4194304                  1         2147483645                  0                                       DEFAULT
So, my question is : how I can find/detect this type of situation ? As showed, I made a compare between NUM_ROWS and BYTES...

Regards,

Chiappa