TABLE SHRINKING AND DATA FILE RESIZE in Oracle

DBsGuru 5 mins2
()

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 –

  1. 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.

Related Articles

, ,


Loading

How useful was this post?

Click on a star to rate it!

Average rating / 5. Vote count:

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

DBsGuru

Hello and welcome to DBsGuru,DBsGuru is a group of experienced DBA professionals and serves databases and their related community by providing technical blogs, projects, training. Technical blogs are the source of vast information not about databases but its related product like middleware, PL/SQL, replication methodology, and so on.Thanks for the visits!Share Learn Grow!

2 thoughts on “TABLE SHRINKING AND DATA FILE RESIZE in Oracle”

  1. SQL> select ‘alter database datafile ”’ || file_name || ”’ resize ‘ || ceil( (nvl(hwm,1)&&blksize)/1024/1024 ) || ‘m;’ cmd from dba_data_files a,( select file_id, max(block_id+blocks-1) 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;
    Enter value for amp: ?????

    1. Appreciate your efforts and highlight it which save efforts. SQL corrected, Actually its issue with code highlighter tool which automatically changes values from “&” to “amp;” and below is SQL along with O/P:

      SQL> select ‘alter database datafile ”’||file_name||”’ resize ‘||ceil( (nvl(hwm,1)*&&blksize)/1024/1024 )||’m;’ as cmd 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 ;

      CMD
      ———————————————————————————————————————————————————————————————————————————————————————————————————————————————
      alter database datafile ‘/u01/app/oracle/oradata/labdb01/users01.dbf’ resize 2m;
      alter database datafile ‘/u01/app/oracle/oradata/labdb01/example01.dbf’ resize 1219m;
      alter database datafile ‘/u01/app/oracle/oradata/labdb01/sysaux01.dbf’ resize 641m;

Comments are closed.

Related Articles