Exercise on high water mark and shrink table

What is a high water mark?

When data is deleted, table will not release that space back to tablespace. This space is kept for future use. This empty space which is waiting for future rows is consider as high water mark of the table.

How to reduce the hight water mark?

use the shrink command.

********

1. Create new user

********

create user NN10 identified by abc123;

grant connect, resource, unlimited tablespace to nn10;

*************

2. Size of table after truncate:

*************

create table nn10.t1 as select * from dba_objects;

insert into nn10.t1 select * from nn10.t1;

insert into nn10.t1 select * from nn10.t1;

insert into nn10.t1 select * from nn10.t1;

commit;

select count(*) from nn10.t1;

BEFORE TRUNCATE 

SIZE of the table before truncate 

What is the size of table now?

Bytes =

Extents =

Blocks =

col owner format a10

col segment_name format a10

select 

owner, 

segment_name, 

count(*) as extents, 

sum(blocks) blocks,  

sum(bytes)/1024 as size_kb,

sum(bytes)/1024/1024 as size_mb

from 

dba_extents

where 

owner=’NN10′

group by 

owner, 

segment_name;

BEFORE TRUNCATE

Truncate table nn10.t1;

AFTER TRUNCATE

Has the space released back to datafile?

AFTER TRUNCATE (YES RELEASED ALL SPACE TO DATAFILE)

**************************************************************************************************************************************************************************************************************************

************

3. Size of table after delete

************

insert into nn10.t1 select * from dba_objects;

insert into nn10.t1 select * from nn10.t1;

insert into nn10.t1 select * from nn10.t1;

insert into nn10.t1 select * from nn10.t1;

commit;

select count(*) from nn10.t1;

BEFORE DELETE

col owner format a10

col segment_name format a10

select 

owner, 

segment_name, 

count(*) as extents, 

sum(blocks) blocks,  

sum(bytes)/1024 as size_kb,

sum(bytes)/1024/1024 as size_mb

from 

dba_extents

where 

owner=’NN10′

group by 

owner, 

Segment_name;

delete nn10.t1;

commit;

select count(*) from nn10.t1;

What is the size of table after delete?

Bytes =

Extents =

Blocks =

BEFORE DELETE

Has the space released back to datafile?

AFTER DELETE (NO THE SPACE IS NOT RELEASED TO THE DATAFILE)

Delete operation will not release the space back to the datafile unless you shrink the table.

To shrink the table, “row movement” will have to be enable.

Alter table nn10.t1 enable row movement;

and then shrink the table

Alter table nn10.t1 shrink space;

Now, check the size of table again after shrink command.

Bytes =

Extents =

Blocks =

AFTER SHRINK THE SPACE IS RELEASED TO THE DATAFILE 

DATAFILE BEFORE SHRINK

DATAFILE AFTER SHRINK