Reseting High Water Mark of Oracle Table

Note:
This method only works if segment space management of tablespace where table is residing is auto. If segment space management is Manual for the tablespace then you cannot benefit from below method.

1. Create temporary table

I created a temporary table by selecting data from a big table. I used this simple method to only import schema (not data) from source table.

CREATE TABLE FARHAT.USER_LOG1 SELECT * FROM FARHAT.USER_LOG WHERE 1=2;

I inserted using append (Direct path ) loading to bypass buffer cache in order to save time.

INSERT /*+ APPEND */ INTO FARHAT.USER_LOG1 SELECT * FROM MOHE.USER_LOG WHERE ROWNUM <=2100000;

COMMIT;

Note: if you are not comfortable with above method you can simply create temp table from source table using below query

CREATE TABLE FARHAT.USER_LOG1 SELECT * FROM FARHAT.USER_LOG WHERE ROWNUM <=2100000;

COMMIT;

(This will create a table by copying 2.1 million rows from source table)

--------------------------------------------------------------------------------------

2. Now Analyze table 

ANALYZE TABLE FARHAT.USER_LOG1 COMPUTE STATISTICS;

3. Check space usage of table.

select
    a.owner,
    a.table_name,
    b.blocks                        alcblks,
    a.blocks                        usdblks,
    (b.blocks-a.empty_blocks-1)     hgwtr
from
    dba_tables a,
    dba_segments b
where
    a.table_name=b.segment_name
    and a.owner=b.owner
    --and a.owner not in('SYS','SYSTEM')
    --and a.blocks <> (b.blocks-a.empty_blocks-1)
    and a.owner = 'FARHAT'    
    --like upper('FARHAT')||'%'
    --and a.table_name = 'USER_LOG1'
order by 1,2;

OWNER TABLE_NAME ALCBLKS USDBLKS HGWTR

FARHAT USER_LOG1 8 294494 130048

4. Now truncate table to reclaim storage and shrink space.

SQL> TRUNCATE TABLE FARHAT.USER_LOG1;

Table truncated.

Elapsed: 00:00:00.61
SQL> ALTER TABLE FARHAT.USER_LOG1 SHRINK SPACE;
ALTER TABLE FARHAT.USER_LOG1 SHRINK SPACE
*
ERROR at line 1:
ORA-10636: ROW MOVEMENT is not enabled

Row Movement for table is not enabled. First enable row movement

SQL> ALTER TABLE FARHAT.USER_LOG1 ENABLE ROW MOVEMENT;

Table altered.

Shrink space

SQL> ALTER TABLE FARHAT.USER_LOG1 SHRINK SPACE;

Table altered.

SQL>

Check space usage again

Select blocks "Ever Used", empty_blocks "Never Used", num_rows "Total rows"
From DBA_tables where table_name='USER_LOG1'
AND OWNER='FARHAT';

 Ever Used Never Used Total rows
---------- ---------- ----------
    294494         418     2105520

1 row selected.
--------------------------------------------------------------
5. Now Analyze table to update statistics.

ANALYZE TABLE FARHAT.USER_LOG1 COMPUTE STATISTICS;

6. Re-run the query

select
    a.owner,
    a.table_name,
    b.blocks                        alcblks,
    a.blocks                        usdblks,
    (b.blocks-a.empty_blocks-1)     hgwtr
from
    dba_tables a,
    dba_segments b
where
    a.table_name=b.segment_name
    and a.owner=b.owner
    --and a.owner not in('SYS','SYSTEM')
    --and a.blocks <> (b.blocks-a.empty_blocks-1)
    and a.owner = 'FARHAT'    
    and a.table_name = 'USER_LOG1'
order by 1,2;


OWNER TABLE_NAME ALCBLKS USDBLKS HGWTR

FARHAT USER_LOG1 8 0 -1


7. Check space usage using 2nd query

Select blocks "Ever Used", empty_blocks "Never Used", num_rows "Total rows"
From DBA_tables where table_name='USER_LOG1'
AND OWNER='FARHAT';

 Ever Used Never Used Total rows
---------- ---------- ----------
         0          8          0
1 row selected.