TABLE SHRINKING AND DATA FILE RESIZE

2 Comments

5
(1)

Step By Step Table Shrinking and Data File Resize

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.


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.

 1,889 Total Views,  2 Views Today

How useful was this post?

Click on a star to rate it!

Average rating 5 / 5. Vote count: 1

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?

Spread the Knowledge!

2 thoughts on “<a rel="bookmark" href="https://dbsguru.com/table-shrinking-and-data-file-resize">TABLE SHRINKING AND DATA FILE RESIZE</a>”

  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;

Leave a Reply

Your email address will not be published. Required fields are marked *

12 − ten =

Open chat
1
Contact Us:
Hi,

Greetings of the day! How can we help you?

Thanks.
Team DBsGuru
Direct Call:+91-9310167776
Email: info@dbsguru.com

Share Learn Grow!


Click on the below technical group to join us to share expertise in PostgreSQL and MySQL on WhatsApp group. The purpose of this group is only help to the DBA community.

PostgreSQL Technical Group.

MySQL technical Group.

Technical Links Powered by DBsGuru.

Thanks.
Team DBsGuru.