Hi,
I am on v 10.2.0.4, on Windows.
I have a table, which has 70 columns (no LOB or long columns). Couple of varchar2(4000) columns, but most of the columns are not very wide. If I look in user_segments, this table has taken 1.5GB space, but there is not much data in there (1500 records). No index on the table.
SQL> select segment_name, bytes from user_segments where segment_name = 'A_STRANGE_TABLE';
SEGMENT_NAME BYTES
------------------------- ------------------------
A_STRANGE_TABLE 1,571,815,424.000
SQL> alter table A_STRANGE_TABLE move ;
Table altered.
SQL> select segment_name, bytes from user_segments where segment_name = 'A_STRANGE_TABLE';
SEGMENT_NAME BYTES
------------------------- ------------------------
A_STRANGE_TABLE 1,610,612,736.000
/* ALTER TABLE .. MOVE has increased size of the segment.. strange?
I tried ALTER TABLE ... MOVE again */
SQL> alter table A_STRANGE_TABLE move ;
Table altered.
SQL> select segment_name, bytes from user_segments where segment_name = 'A_STRANGE_TABLE';
SEGMENT_NAME BYTES
------------------------- ------------------------
A_STRANGE_TABLE 1,571,815,424.000 /* Back to same size */
/* **** CTAS **** */
SQL> create table temp1 as select * from A_STRANGE_TABLE;
Table created.
SQL> select segment_name, bytes from user_segments where segment_name = 'TEMP1';
SEGMENT_NAME BYTES
------------------------- ------------------------
TEMP1 720,896.000
When I create a table using the same table (CTAS), the newly created table takes very less space.
After ALTER TABLE ... MOVE, I tried DEALLOCATE UNUSED, no change in space.
Why is this segment showing so much occupied space?
Thanks in advance