Table Shrinking in Oracle Database

Опубликовано: 10 Октябрь 2024
на канале: BhagyaRaj Katta

14,451

157

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.

U1@hrdev: delete from sm1 where deptno=10;

262144 rows deleted.

I deleted above number of rows.

Rows COUNT
----------
786432

And I’m giving commit

U1@hrdev: commit;

Commit complete.

So now we have 786432 rows in sm1 table. Now see the following command

SYS@hrdev: 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.

U1@hrdev 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.

U2@hrdev: alter table sm1 shrink space compact;

Table altered.

Now see the space of table by using below command.

SYS@hrdev: 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.

U2@hrdev: alter table sm1 shrink space;

Table altered.

Now see the space by using below command.

SYS@hrdev: 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.

U2@hrdev: 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.

U2@hrdev: alter table sm1 shrink space compact cascade;
Table altered.

And then

U2@hrdev: alter table sm1 shrink space cascade;
Table altered.

Transporting tablespace to different platform by Using RMAN :    • Transporting tablespace to different ...
Oracle EBS apps Upgrade from 12 2 to 12 2 5 (start CD 51) :    • Oracle EBS apps Upgrade from 12 2 to ...
Transport tablespace by using RMAN.:    • Transport tablespace by using RMAN.