TABLE SHRINKING AND DATA FILE RESIZE in Oracle

September 19, 2020

DATABASE

Step By Step Table Shrinking and Data File Resize in Oracle

Due to less budget sometimes the client asks to shrink the mount point space where our datafiles exist and if developers said that the table size is getting high and due to that our database size is increasing so, in both scenarios we can shrink those tables and resize the datafiles in Oracle.

Here are the steps to shrink the table and resize the datafiles.

TABLE SHRINKING –

For shrinking the table we need to gather the table stats –

exec dbms_stats.GATHER_TABLE_STATS(OWNNAME=>'DBSGURU',TABNAME=>'dbs_data',ESTIMATE_PERCENT=>25,DEGREE=>4,CASCADE=>TRUE);

2) Before shrink we need to enable the row movement. Row movement is mainly applied to partition tables. It allows rows to be moved across partitions.

alter table DBSGURU.dbs_data enable row movement;

alter table DBSGURU.dbs_data shrink space cascade;

alter table DBSGURU.dbs_data disable row movement;

3) After shrinking the table we need to Rebuild the table index and then will gather table stats again.

alter index DBSGURU.dbs_data_ind rebuild; (Optional)

exec dbms_stats.GATHER_TABLE_STATS(OWNNAME=>'DBSGURU',TABNAME=>'dbs_data',ESTIMATE_PERCENT=>25,DEGREE=>4,CASCADE=>TRUE);

Note: Here alter index rebuild is not mandatory.

DATAFILE RESIZING –

4) Alter datafile with HWM using below query –

first query will let you know the db_block_size –

SQL> set verify off
column file_name format a50 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings format 999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report
SQL> SQL> SQL> SQL> SQL>
column value new_val blksize
select value from v$parameter where name = 'db_block_size';

Second query will give you Alter datafile with HWM –

select 'alter database datafile '''||file_name||''' resize '||ceil( (select 'alter database datafile '''||file_name||''' resize '||ceil( (nvl(hwm,1)*&&blksize)/1024/1024 )||'m;' as cmd, bytes/1024/1024 from dba_data_files a, ( select file_id, max(block_id+blocks-1) as hwm from dba_extents group by file_id ) b where a.file_id = b.file_id(+) and ceil(blocks*&&blksize/1024/1024)- ceil((nvl(hwm,1)* &&blksize)/1024/1024 ) > 0 ;

SQL> alter database datafile '/u02/dbsguru/dbguru_data01.dbf' resize 7992m;

SQL> alter database datafile '/u02/dbsguru/dbguru_data02.dbf' resize 7993m;

SQL> alter database datafile '/u02/dbsguru/dbguru_data03.dbf' resize 7980m;

SQL> alter database datafile '/u02/dbsguru/dbguru_data04.dbf' resize 7998m;

SQL> alter database datafile '/u02/dbsguru/dbguru_data05.dbf' resize 7970m;

This document is only for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.

Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the latest update. Click here to know more about our pursuit.

DATABASE, ORACLE, TABLESPACE

Step By Step Table Shrinking and Data File Resize in Oracle

Related Articles