Exercise on high water mark and shrink table
Exercise on high water mark and shrink table
oracle configurationintermediate
by OracleDba
19 views
📋Steps in this guide1/16
1
Overview
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
2
Section 2
********
create user NN10 identified by abc123;
grant connect, resource, unlimited tablespace to nn10;
*************
2. Size of table after truncate:
*************
3
Section 3
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;
4
Section 4
BEFORE TRUNCATE
SIZE of the table before truncate
What is the size of table now?
Bytes =
Extents =
Blocks =
5
Section 5
col owner format a10
col segment_name format a10
select
owner,
segment_name,
count(*) as extents,
6
Section 6
sum(blocks) blocks,
sum(bytes)/1024 as size_kb,
sum(bytes)/1024/1024 as size_mb
from
dba_extents
where
7
Section 7
owner=’NN10′
group by
owner,
segment_name;
BEFORE TRUNCATE
Truncate table nn10.t1;
8
Section 8
AFTER TRUNCATE
Has the space released back to datafile?
AFTER TRUNCATE (YES RELEASED ALL SPACE TO DATAFILE)
**************************************************************************************************************************************************************************************************************************
************
3. Size of table after delete
9
Section 9
************
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;
10
Section 10
select count(*) from nn10.t1;
BEFORE DELETE
col owner format a10
col segment_name format a10
select
owner,
11
Section 11
segment_name,
count(*) as extents,
sum(blocks) blocks,
sum(bytes)/1024 as size_kb,
sum(bytes)/1024/1024 as size_mb
from
12
Section 12
dba_extents
where
owner=’NN10′
group by
owner,
Segment_name;
13
Section 13
delete nn10.t1;
commit;
select count(*) from nn10.t1;
What is the size of table after delete?
Bytes =
Extents =
14
Section 14
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.
15
Section 15
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 =
16
Section 16
Blocks =
AFTER SHRINK THE SPACE IS RELEASED TO THE DATAFILE
DATAFILE BEFORE SHRINK
DATAFILE AFTER SHRINK
Comments (0)
Please to add comments
No comments yet. Be the first to comment!