HomeЛюди и блогиRelated VideosMore From: BhagyaRaj Katta

Table Shrinking in Oracle Database

44 ratings | 6145 views
1.Shrink the Table: Shrinking is started from 10g. In this method I’m using user u1 and table name sm1. Now I’m deleting some rows in sm1 COUNT ---------- 1048576 Table sm1 has 1048576 rows. [email protected]: delete from sm1 where deptno=10; 262144 rows deleted. I deleted above number of rows. Rows COUNT ---------- 786432 And I’m giving commit [email protected]: commit; Commit complete. So now we have 786432 rows in sm1 table. Now see the following command [email protected]: select OWNER,TABLESPACE_NAME,SEGMENT_NAME,SEGMENT_TYPE,BYTES/1024/1024||' mb'"space",BLOCKS,EXTENTS from dba_segments where tablespace_name like 'U%TS'; OWNER TABLESPACE_NAME SEGMENT_NAME SEGMENT_TYPE space BLOCKS EXTENTS ----- --------------- ------------- ------------- ------ ---------- ---------- U1 U1TS SM1 TABLE 29 mb 3712 44 After I deleted some rows in sm1 table still above result showing same values, so now our duty is shrink this table. This is done by following 2 ways, i By using COMPACT key word: In this method shrinking is done in two phases. In the first phase all fragmented space are just defragmented, but still the High Water Mark is persist with last used block only. That mean used free blocks are not de allocated and HWM is not updated here. Issue the following command before use shrink command. [email protected] alter table sm1 enable row movement; Table altered. There is particular use with above command, when we shrink the table all rows are moves to contiguous blocks, so here row movement should be done. By default the row movement is disabled for any table, so above command enabled the row movement. Then execute shrink command now. [email protected]: alter table sm1 shrink space compact; Table altered. Now see the space of table by using below command. [email protected]: select OWNER,TABLESPACE_NAME,SEGMENT_NAME,SEGMENT_TYPE,BYTES/1024/1024||' mb'"space",BLOCKS,EXTENTS from dba_segments where tablespace_name like 'U%TS'; OWNER TABLESPACE_NAME SEGMENT_NAME SEGMENT_TYPE space BLOCKS EXTENTS ----- --------------- ------------- ------------- ------ ---------- ---------- U1 U1TS SM1 TABLE 29 mb 3712 44 So here seems nothing happened with above shrink command, but internally the fragmented space is defragmented. But the high water mark is not updated, used free blocks are also not de allocated. For de allocating the used blocks we have to execute below command. This is the second phase. [email protected]: alter table sm1 shrink space; Table altered. Now see the space by using below command. [email protected]: select OWNER,TABLESPACE_NAME,SEGMENT_NAME,SEGMENT_TYPE,BYTES/1024/1024||' mb'"space",BLOCKS,EXTENTS from dba_segments where tablespace_name like 'U%TS'; OWNER TABLESPACE_NAME SEGMENT_NAME SEGMENT_TYPE space BLOCKS EXTENTS ----- --------------- ------------- ------------- ---------- ---------- ---------- U1 U1TS SM1 TABLE 20.8125 mb 2664 36 So now the space of sm1 table is reduced. Note: Actually the alter table sm1 shrink space command will complete these two phases of the shrinking of table at a time. But here we done shrink process in two phases because when we use alter table sm1 shrink space command the table locked temporarily some time period, during this period users unable to access the table. So if we use alter table sm1 shrink space compact command the table is not locked but space is defragmented. When we not in business hours issue the second phase shrink command then users are won’t get any problem. ii Because of above method the table dependent objects are goes to invalid state, to overcome this problem we have to use below command. [email protected]: alter table sm1 shrink space cascade; Table altered. The above command also shrinks the space of all dependent objects. We also do this in two phases like above two phases. See the below command. [email protected]: alter table sm1 shrink space compact cascade; Table altered. And then [email protected]: alter table sm1 shrink space cascade; Table altered. Transporting tablespace to different platform by Using RMAN : https://www.youtube.com/watch?v=CN401PUKK4A Oracle EBS apps Upgrade from 12 2 to 12 2 5 (start CD 51) : https://www.youtube.com/watch?v=zeO4goqR70Y Transport tablespace by using RMAN.: https://www.youtube.com/watch?v=YG6kWX7Par8
Html code for embedding videos on your blog
Text Comments (12)
Satish K Rajendran (3 months ago)
Super bro .. but small doubt .. u should have committed the transaction after issuing delete statement right??
poojan Das (5 months ago)
thank you bro !!
Prashanth Kannan (1 year ago)
Hi Bhagyaraj, very nicely explained.
jyoti barasker (2 years ago)
nice video sir
Arun Bonthu (2 years ago)
Thanks brother. Great effort. Thank you for sharing you Knowledge. Please make more videos about Performance Tuning. The way you Explaining is understandable. Please do more videos. Thank you so much. Can i have your Email ID
BhagyaRaj Katta (2 years ago)
+Arun Bonthu Thank you very much for watching my video, [email protected] is my mail id
Indunil Hettiarachchi (3 years ago)
Thanks BhagyaRaj. Good effort and it's easy to understand the logic behind the shrinking. keep it up posting more videos.
BhagyaRaj Katta (2 years ago)
+Indunil Hettiarachchi Thank you
Ala'a Eddin Al-Qadi (3 years ago)
Thank you very much for the effort and valuable information!
BhagyaRaj Katta (2 years ago)
+Ala'a Eddin Al-Qadi Thank you
Thanks BhagyaRaj...its clear and it helps me timely...thanks a lot and expecting more such videos...can I have your email id
BhagyaRaj Katta (2 years ago)
+Mohanraj ReginaSwaminathan Thanks for watching this video, [email protected]

Would you like to comment?

Join YouTube for a free account, or sign in if you are already a member.