White space (space once used, but reserved) in tables
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
