Hi all,
Please see the folllowing test:
Code:
SQL> set linesize 10000
SQL> spool c:\temp\hwm.txt
SQL> set timing on
SQL> select count(*) from TEST;
COUNT(*)
----------
2798098
Elapsed: 00:08:24.01
SQL> SELECT tablespace_name "Tablespace Name",
2 bytes/1048576 "Size(M)"
3 FROM dba_segments
4 where owner='TEST';
Tablespace Name Size(M
------------------------------------------------------------------------------------------ ---------
TEST_DATA 145.5
TEST_DATA 48.5
TEST_DATA 56.5
Elapsed: 00:00:01.04
SQL> delete from TEST;
2798098 rows deleted.
SQL> ALTER TABLE TEST.TEST MOVE
2 /
Table altered.
Elapsed: 00:00:13.03
SQL> ALTER INDEX TEST.TEST REBUILD LOGGING NOREVERSE TABLESPACE TEST_DATA
2 /
Index altered.
Elapsed: 00:00:00.02
SQL> ALTER INDEX TEST.TEST_PK REBUILD LOGGING NOREVERSE TABLESPACE TEST_DATA
2 /
Index altered.
Elapsed: 00:00:00.04
SQL> SELECT tablespace_name "Tablespace Name",
2 bytes/1048576 "Size(M)"
3 FROM dba_segments
4 where owner='TEST';
Tablespace Name Size(M
------------------------------------------------------------------------------------------ ---------
TEST_DATA 100.5
TEST_DATA 48.5
TEST_DATA 56.5
Elapsed: 00:00:00.06
SQL> SPOOL OFF
SQL>
Why HWM is not shrink close to zero after I move tables and rebuild index???? I intentionally move it on the same tablespace, I also tried to move to different tablespace but it doens't help.
could someone please tell me why???