- Kiran Dalvi
- 21 Nov, 2021
- 0 Comments
- 2 Mins Read
Exercise on high water mark and shrink table
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
Â
Â