Create a testing table called test
SQL> create table test tablespace users as select * from
dba_objects;
Table created.
We look at the size of the table test, the
table size is around 3MB now
SQL> select
owner,segment_name,tablespace_name, sum(bytes)/1024/1024 "Size in MB
" from dba_segments
2 where segment_name='TEST'
3 group by
owner,segment_name,tablespace_name ;
OWNER SEGMENT_NA TABLESPACE
Size in MB
------------------------------ ----------
---------- ----------
SYS TEST USERS 3
We now delete all the rows in the table and
commit the statement.
SQL> delete from test;
22906 rows deleted.
SQL> commit;
Commit complete.
If we look at the size of the table now,
the table size is still 3MB. This demonstrated that the table didn’t shrink
after we delete data from the table.
SQL> select
owner,segment_name,tablespace_name, sum(bytes)/1024/1024 "Size in MB
" from dba_segments
2 where segment_name='TEST'
3 group by
owner,segment_name,tablespace_name ;
OWNER SEGMENT_NA TABLESPACE
Size in MB
------------------------------ ----------
---------- ----------
SYS TEST USERS 3
To shrink the table, what I will do now is
enable the row movement feature of the table.
SQL> ALTER TABLE test ENABLE ROW
MOVEMENT;
Table altered.
SQL> alter table test shrink space;
Table altered.
SQL> select
owner,segment_name,tablespace_name, sum(bytes)/1024/1024 "Size in MB
" from dba_segments
2 where segment_name='TEST'
3 group by
owner,segment_name,tablespace_name
4 ;
OWNER SEGMENT_NA TABLESPACE
Size in MB
------------------------------ ----------
---------- ----------
SYS TEST USERS .0625
Filed under:
administration,
Oracle