Vishal desai’s Oracle Blog

May 14, 2012

Datafile resizing

Filed under: Tools — vishaldesai @ 3:28 pm

One of our DW environment is very dynamic in terms of space utilization and as client is planning to move production environment to Exadata they do not want to want to invest anything in existing SAN infrastructure which is Netapp. Priorities of work done by Data Analysts and Product creation team changes every other day and Data analysts are given quota on USERS tablespace and all Product creation team uses DATA tablespace. Both USERS and DATA tablespaces are mapped to same ASM Disk group. I could have used one tablespace only but have not done it so as I have seen Data Anaysts writing cartesian join and filling up entire tablespace/mount points and security. To cut long story short, I have to do manual tablespace managment for USERS and DATA depending upon requirement. Below is the script that will display data files that can be resized to release free space from data files above High Water Mark (HVM).

 

 

VDESAI@db > @freespace

TABLESPACE_NAME              TOTAL_SPACE  FREE_SPACE   AUTO_GROW LARGEST_EXTENT  USED_SPACE PCTFULL PCTAUTOGROW
---------------------------- ----------- ----------- ----------- -------------- ----------- ------- -----------
....
USERS                            181,733      62,208     305,274         11,200     119,525      66         168
....


TOTAL_SPACE  FREE_SPACE  USED_SPACE
----------- ----------- -----------
  7,611,993   2,104,437   5,507,556

VDESAI@db > @release_tbs_space.sql
Please enter tablespace name            : USERS
--alter database datafile '+DG_DATA_CSPROD/users_36.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_02.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_38.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_46.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_19.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_13.dbf' resize m--frag size0m;
alter database datafile '+DG_DATA_CSPROD/users_56.dbf' resize 3166m--total size4830m;
--alter database datafile '+DG_DATA_CSPROD/users_56.dbf' resize 3166m--frag size-1728m;
--alter database datafile '+DG_DATA_CSPROD/users_37.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_07.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_32.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_23.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_26.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_50.dbf' resize m--frag size0m;
alter database datafile '+DG_DATA_CSPROD/users_43.dbf' resize 3886m--total size4974m;
--alter database datafile '+DG_DATA_CSPROD/users_43.dbf' resize 3886m--frag size-1152m;
--alter database datafile '+DG_DATA_CSPROD/users_03.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_31.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_09.dbf' resize m--frag size0m;
alter database datafile '+DG_DATA_CSPROD/users_57.dbf' resize 427m--total size2411m;
--alter database datafile '+DG_DATA_CSPROD/users_57.dbf' resize 427m--frag size-2048m;
--alter database datafile '+DG_DATA_CSPROD/users_47.dbf' resize m--frag size0m;
alter database datafile '+DG_DATA_CSPROD/users_41.dbf' resize 59492m--total size65508m;
--alter database datafile '+DG_DATA_CSPROD/users_41.dbf' resize 59492m--frag size-6080m;
--alter database datafile '+DG_DATA_CSPROD/users_54.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_53.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_05.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_33.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_21.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_16.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_14.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_25.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_22.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_48.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_20.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_06.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_34.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_30.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_15.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_52.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_11.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_01.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_18.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_35.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_12.dbf' resize m--frag size0m;
alter database datafile '+DG_DATA_CSPROD/users_55.dbf' resize 3922m--total size4946m;
--alter database datafile '+DG_DATA_CSPROD/users_55.dbf' resize 3922m--frag size-1088m;
alter database datafile '+DG_DATA_CSPROD/users_42.dbf' resize 46718m--total size47166m;
--alter database datafile '+DG_DATA_CSPROD/users_42.dbf' resize 46718m--frag size-512m;
--alter database datafile '+DG_DATA_CSPROD/users_49.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_39.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_24.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_29.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_08.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_10.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_28.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_51.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_04.dbf' resize m--frag size0m;
alter database datafile '+DG_DATA_CSPROD/users_44.dbf' resize 3901m--total size5245m;
--alter database datafile '+DG_DATA_CSPROD/users_44.dbf' resize 3901m--frag size-1408m;
--alter database datafile '+DG_DATA_CSPROD/users_27.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_40.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_17.dbf' resize m--frag size0m;
Total space reclaimation:13568MB
VDESAI@db > 


VDESAI@db > alter database datafile '+DG_DATA_CSPROD/users_56.dbf' resize 3166m--total size4830m;

Database altered.

VDESAI@db > alter database datafile '+DG_DATA_CSPROD/users_43.dbf' resize 3886m--total size4974m;

Database altered.

VDESAI@db > alter database datafile '+DG_DATA_CSPROD/users_57.dbf' resize 427m--total size2411m;

Database altered.

VDESAI@db > alter database datafile '+DG_DATA_CSPROD/users_41.dbf' resize 59492m--total size65508m;

Database altered.

VDESAI@db > alter database datafile '+DG_DATA_CSPROD/users_55.dbf' resize 3922m--total size4946m;

Database altered.

VDESAI@db > alter database datafile '+DG_DATA_CSPROD/users_42.dbf' resize 46718m--total size47166m;

Database altered.

VDESAI@db > alter database datafile '+DG_DATA_CSPROD/users_44.dbf' resize 3901m--total size5245m;

Database altered.

VDESAI@db > @freespace

TABLESPACE_NAME              TOTAL_SPACE  FREE_SPACE   AUTO_GROW LARGEST_EXTENT  USED_SPACE PCTFULL PCTAUTOGROW
---------------------------- ----------- ----------- ----------- -------------- ----------- ------- -----------
....
USERS                            168,166      48,640     312,378         11,200     119,526      71         186
....


VDESAI@db > l
  1  declare
  2  cursor c1 is select tablespace_name,file_id,max(block_id) mbid from dba_extents where tablespace_name='&&tbs'
  3  group by tablespace_name,file_id;
  4  fs number;
  5  fn varchar2(100);
  6  ts number;
  7  x number;
  8  gtot number:=0;
  9  tffs number:=0;
 10  ffrags number:= 0;
 11  begin
 12  for v1 in c1 loop
 13     select sum(bytes)/1024/1024 into fs from dba_free_space where tablespace_name = v1.tablespace_name and
 14          file_id = v1.file_id and block_id>v1.mbid;
 15          select file_name,bytes/1024/1024 into fn,ts from dba_data_files where file_id=v1.file_id and tablespace_name=v1.tablespace_name;
 16          select sum(bytes/1024/1024) into tffs from
 17          (select a.bytes/1024/1024 as bytes from dba_free_space a where file_id=v1.file_id and tablespace_name=v1.tablespace_name
 18           union all
 19           select 0.000001 as bytes from dual);
 20  x:=(ts-fs)+64;
 21  if ts>x then
 22  dbms_output.put_line('alter database datafile ' || '''' || fn || '''' || ' resize ' ||  round(x) || 'm' || '--total size' || round(ts) || 'm;' );
 23
 24  gtot :=  gtot + (ts-x);
 25  end if;
 26
 27  ffrags := nvl(tffs - fs,0);
 28  dbms_output.put_line('--alter database datafile ' || '''' || fn || '''' || ' resize ' ||  round(x) || 'm' || '--frag size' || round(ffrags) || 'm;');
 29
 30  end loop;
 31
 32  dbms_output.put_line('Total space reclaimation:' || gtot || 'MB');
 33* end;
VDESAI@db > 
About these ads

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Silver is the New Black Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: